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,则无需执行该语句)

 

计算机