Mysql5.7 单表 4亿数据迁移到新表的快速实现方案

导出

show variables like '%secure%'; 必须是导入到这个文件下，如果为null 需要配置my.cnf ，重启mysql

SELECT * INTO OUTFILE '/var/lib/mysql-files/sgk09.txt' FIELDS TERMINATED BY ';' FROM `sgk-qqpass`

 

 导入：

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

 然后我们还得改一下导入前修改参数和禁止索引更新

修改mysql 配置，重启数据库

BULK_INSERT_BUFFER_SIZE=128M

MYISAM_SORT_BUFFER_SIZE=128M

key_buffer_size = 128M

关闭索引，会导入更快
mysql> alter table `sgk-qqpass_old `disable keys; 

LOAD DATA INFILE '/var/lib/mysql-files/sgk09.txt' INTO TABLE `sgk-qqpass_old` FIELDS TERMINATED BY ';'(name1,pass,email,site,salt,other,id,text);

 插入完成打开索引

 mysql> alter table table_name enable keys。

可能会出现Table xxx  is marked as crashed and should be repaired

解决办法：myisamchk --recover --quick /var/lib/mysql/sgk09/sgk\@002dqqpass_old --sort_buffer_size=2G 

或者：

/usr/bin/myisamchk -r -f /var/lib/mysql/sgk01/sgk01\@002dweibo --sort_buffer_size=3G --key_buffer_size=2G --read_buffer_size=2G --write_buffer_size=2G

https://www.bilibili.com/read/cv13543042

插曲
上面只是对这个mysiam类型的表进行导入数据的一个优化，但是对于innodb这样的表，这种方式并不能提高导入数据的效率，可以有以下几种方式来提高导入数据的效率
（1）因为innodb的表是按照主键的顺序来保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
（2）在导入数据之前关闭唯一性校验，及设置set unique_checks=0,就是对它进行一个关闭，等插入完之后，再进行一个恢复即可。在设置的时候关闭自动提交，如下：
（3）set autocommit=0,然后当插入完成之后，再进行设置为1。

 

 

LOAD DATA INFILE 'fxxxx_9.txt' INTO TABLE fxxxxtest FIELDS TERMINATED BY ';' (id, info_id, field_id, element_id, TYPE, @a, @a, @a, @a, @a, VALUE, @a, @a, create_user_id, create_time, @a, @a, @a, @a);

 

 注意到上面导入的时候，过滤了一些字段，这些字段在导出的原始表中有，但是新表中不需要，所有通过这种声明字段列表的方式，以@的方式过滤掉对应的字段。