注册登录

MySQL备份数据太慢？sql大文件怎么导出导入？

概述：

本篇讲解：如何快速导入由逻辑备份产生的SQL脚本，其他文件形式暂不讲解。

日常开发中，大家肯定遇到过这些需求：“ 数据迁移、数据恢复、新建从库 ” 等等一系列任务，因为做这些需求我们肯定知道，会涉及到 大量的数据 的处理。

大量的数据，会导致我们处理数据进度有时会很慢很慢，那么我们总得找一些方案来解决，对吧。

其实，这也是有一些小技巧的，可以大大增加我们数据的处理速度，那么就开始吧~~~

小技巧 - 方案一

开场先注意：导出 或 导入数据，尽可能的使用 MySQL 自带命令工具 ，不要使用一些图形化的工具 （Navicat...）。因为 MySQL 命令行工具至少要比图形化工具快 2 倍 。

命令工具行方式：

# 导出整个实例
mysqldump -u用户名 -p密码 --all-databases > all_database.sql

# 导出指定库
mysqldump -u用户名 -p密码 --databases testdb > testdb.sql

# 导出指定表
mysqldump -u用户名 -p密码 testdb test_tb > test_tb.sql

# 导入指定SQL文件 （指定导入testdb库中）
mysql -u用户名 -p密码 testdb < testdb.sql

小技巧 - 方案二

修改参数方式：

在 MySQL 中，有这么一对参数很有意思，分别是：

“ innodb_flush_log_at_trx_commit ” 
      与
“ sync_binlog ”

安全性考虑，这个参数默认是 1 ，为了快速导入sql 数据，可临时修改默认参数值。

参数一: innodb_flush_log_at_trx_commit默认值为1，可设置为0、1、2

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

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

innodb_flush_log_at_trx_commit设置为 2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

参数二: sync_binlog默认值为1，可设置为[0,N)

当 sync_binlog =0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当 sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注意：这两个参数可以在线修改，若想快速导入，可按照以下命令行

# 1.进入MySQL命令行 临时修改这两个参数
set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 2000;

# 2.执行SQL脚本导入
mysql -uroot -pxxxxxx testdb < testdb.sql

# 3.导入完成 再把参数改回来
set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;

小技巧 - 方案三

这种场景也很熟悉，就是新建从库，并且不需要生成 binlog 日志。

解决方式很简单，在 sql 脚本开头增加：

set sql_log_bin=0;

然后继续执行导入，这样速度也会加快。（如MySQL没开启binlog，则无需执行该语句）

 

计算机