千万条数据怎么快速导入导出MySQL
网址：https://github.com/mydumper/mydumper
环境搭建
安装
依赖
yum install -y cmake gcc gcc-c++ git make
yum install -y glib2-devel openssl-devel pcre-devel zlib-devel libzstd-devel
yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake
wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
解压安装
tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1/
cmake .
make
make install
安装位置
-- Install configuration: ""
-- Installing: /usr/local/bin/mydumper
-- Set runtime path of "/usr/local/bin/mydumper" to ""
-- Installing: /usr/local/bin/myloader
-- Set runtime path of "/usr/local/bin/myloader" to ""
测试是否安装成功
mydumper --help
创建SQL语句
SET GLOBAL local_infile=ON;DROP DATABASE IF EXISTS myd_test;
CREATE DATABASE myd_test;
插入数据
插入数据一定要注意，如果你是使用可视化工具点5-8次这样就可以了，否则的话插入的数据会非常多
mydumper_aipk_uuid表
-- Tables and views
CREATE table IF NOT EXISTS mydumper_aipk_uuid (id int primary key auto_increment, val varchar(36));
INSERT into mydumper_aipk_uuid (val) values (uuid());
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
char_hex_test表
CREATE TABLE `char_hex_test` ( `id` binary(16) NOT NULL, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `val` (`val`));
INSERT INTO char_hex_test values (unhex(replace(uuid(),'-','')), rand()*1000);
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;INSERT INTO `char_hex_test` VALUES("ÈCY e1í´\0\'_÷M",539);
我这里是最后一页，反着数据挺多的
这是另外一张表
mydumper使用
普通使用
mydumper -u root -p 123456 --threads=16 -o /www/mydumper/all_backup/
如果数据库装在docker
指定下端口就可以了，也不需要进入到容器内部，速度还是很快的
mydumper -u root -p 123456 -h 127.0.0.1 -P 3306 --threads=16 -o /www/mydumper/all_backup/
这是两个比较牛逼的表
关于一些参数参考
mydumper
Application Options:-B, --database 要备份的数据库，不指定则备份所有库-T, --tables-list 需要备份的表，名字用逗号隔开-o, --outputdir 备份文件输出的目录-s, --statement-size 生成的insert语句的字节数，默认1000000-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB-c, --compress Compress output files压缩输出文件-e, --build-empty-files 如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）-x, --regex Regular expression for 'db.table' matching 使用正则表达式匹配'db.table'-i, --ignore-engines Comma delimited list of storage engines to ignore忽略的存储引擎，用逗号分割-m, --no-schemas Do not dump table schemas with the data不备份表结构，只备份数据-d, --no-data Do not dump table data备份表结构，不备份数据-G, --triggers Dump triggers备份触发器-E, --events Dump events-R, --routines Dump stored procedures and functions备份存储过程和函数-k, --no-locks 不使用临时共享只读锁，使用这个选项会造成数据不一致--less-locking Minimize locking time on InnoDB tables.减少对InnoDB表的锁施加时间-l, --long-query-guard 设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）-K, --kill-long-queries Kill long running queries (instead of aborting)杀掉长查询 (不退出)-D, --daemon Enable daemon mode启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备-I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下-L, --logfile 使用的日志文件名(mydumper所产生的日志), 默认使用标准输出--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.--skip-tz-utc --use-savepoints 使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist--lock-all-tables Use LOCK TABLE for all, instead of FTWRL-U, --updated-since Use Update_time to dump only tables updated in the last U days--trx-consistency-only Transactional consistency only-h, --host 连接的主机名-u, --user 用来备份的用户名-p, --password 用户密码-P, --port 连接端口-S, --socket 使用socket通信时的socket文件-t, --threads 开启的备份线程数，默认是4-C, --compress-protocol 压缩与mysql通信的数据-V, --version 显示版本号-v, --verbose 输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
myloader使用
创建数据库
为了演示方便，在docker中新建一个数据库，端口为3306
docker run --name mysql -p 3306:3306 -v mysql_data:/var/lib/mysql -v mysql_config:/etc/mysql --restart=always --privileged=true -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0.33
可以看到这个数据库中什么也没有
导入数据
myloader -u root -p 123456 -h 127.0.0.1 -P 3304 --threads=16 -o -d /www/mydumper/all_backup
# 将这个目录下所有数据都存到 myd_test 数据库中
myloader -u root -p 123456 -h 127.0.0.1 -P 3304 --threads=16 --database=myd_test -d /www/mydumper/all_backup
myloader参数
-d, --directory Directory of the dump to import之前备份好的现在需要导入的文件夹-q, --queries-per-transaction Number of queries per transaction, default 1000每次事物执行的查询数量，默认是1000-o, --overwrite-tables Drop tables if they already exist如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构-B, --database An alternative database to restore into还原到指定的数据库-s, --source-db Database to restore选择被还原的数据库，将这个数据库数据还原到--database指定的数据库里-e, --enable-binlog Enable binary logging of the restore data启用还原数据的二进制日志-h, --host 连接的主机名-u, --user 用来备份的用户名-p, --password 用户密码-P, --port 连接端口-S, --socket 使用socket通信时的socket文件-t, --threads 开启的备份线程数，默认是4-C, --compress-protocol 压缩与mysql通信的数据-V, --version 显示版本号-v, --verbose 输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
)
![[C++]内存管理：串顺序存储的内存回收](http://pic.xiahunao.cn/yaotu/[C++]内存管理：串顺序存储的内存回收)
