mysql快速导入数据方式

参考链接：
https://mp.weixin.qq.com/s?src=11&timestamp=1599273969&ver=2565&signature=ZZRrPPkUgP7pS2PR7BiSx3yJKMMs81JiXE3DwjKTEEQ9XhTv54c83AKitS8kZuKfkHcmlncbSoq-M1yPs9RqUmIW-KdvrCv56LQsB6lW5M3CBbsLvS*0dcHfiMSJly32&new=1

1、描述：导入大量数据时，如何快速导入数据，可通过修改下列参数的方式成倍提高速度

1.1 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit 设置为 0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

1.2 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit设置为 1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去。

1.3 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit设置为 2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
当 sync_binlog =0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当 sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

2.具体操作步骤

2.1 查看当前mysql的配置参数

show global variables where variable_name = ‘innodb_flush_log_at_trx_commit’;
show global variables where variable_name = ‘sync_binlog’;

2.2 进入MySQL命令行 临时修改这两个参数

set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 2000;

2.3 执行SQL脚本导入

mysql -uroot -pxxxxxx testdb < testdb.sql

2.4 导入完成 再把参数改回来

set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;

• [备注]：导入时若提示GTID错误，需重置GTID，建议导入时，关闭bin-log；
  mysql -uroot -p -e ‘reset master’;

• 【扩展】据说source比直接导入快；
  mysql -uroot -p < /sdyunlei/backups/mysql/databases_20200903.sql
  source /sdyunlei/backups/mysql/databases_20200905.sql;

####################################################################

• 查看数据导入时的进度方式：
  导入sql时，查看当前执行到的语句；结合less(-m)查询sql脚本，可得到导入进度；
  show processlist;

下面命令可直接显示进度条；
pv -t -p databases_20200903.sql | mysql -uroot -p

• 查询
  –show global status where Variable_name in(‘com_select’,‘com_insert’,‘com_delete’,‘com_update’);
  查询出当前四种操作的总次数
  x1
  y1
  z1
  w1
  –select sleep(60)
  延时60秒
  –show global status where Variable_name in(‘com_select’,‘com_insert’,‘com_delete’,‘com_update’);
  一分钟后四种操作的总次数
  x2
  y2
  z2
  w2

计算每秒 各种操作的次数
每秒查询： （x1-x2）/60
每秒插入： （y1-y2）/60
每秒删除： （z1-z2）/60
每秒更新： （w1-w2）/60