文章声明：此文基于木子实操撰写
生产环境：CentOS Linux release 7.9.2009 (Core)，mysql Ver 14.14 Distrib 5.7.33  
问题关键字：MySQL 5.7 快速导入导出大SQL文件及简单参数调优

前述

昨天的发文《CentOS 7.9安装与配置MySQL 5.7》是为了今天测试环境而部署，但遇到一个迁移数据库，大家都会遇到的问题，导入、导出大文件数据库慢问题，尝试了很多种方法，最终发现这种方法最管用，先做一个总结性的输出。
47GB SQL文件从阿里云RDS导出至公司机房花费4小时左右，每秒钟大约3.3MB/s，也就是47*1024/240/60*8=26.72/Mbps/s(因公司机房带宽总共50Mbps，为防止影响其它业务限制了单节点下载速度，所以正常如果你的带宽够大，下载的速度应该更快。)
再将47GB SQL文件导入数据库服务器，整个花费3.5小时左右，导入完成以后，整个数据量为：116GB(服务器配置：16C64G)

快速导出

参考说明：
-q: 直接转存
-t: 不写表创建信息(这里需要注意，因为导出的时候没有导出表创建信息，所以在导入的时候，必须先导入表结构，再导入数据，不然会直接报错)
--single-transaction: 参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。此参数需要InnoDB引擎支持，目前MySQL 5.7默认采用InnoDB引擎。
--set-gtid-purged: 因为木子是从阿里云RDS只读库导出(MySQL集群)，所以需要添加这个参数，不然会出现警告信息，主要原因在于：在MySQL5.6以后，加入了全局事务ID(GTID)来强化数据库的主备一致性、故障恢复、以及容错能力。

# 未开启GTID的MySQL导出
nohup mysqldump -uxxx -pxxx -q -e -t --single-transaction db_name > /db_name.sql &

# 开启GTID的MySQL导出，如果不添加--set-gtid-purged=OFF警告
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events

# 导入时错误
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

# 如果你已经导出来GTID的SQL，又不想重新再去导出，可以使用以下方法导入：
mysql> reset slave all;
mysql> reset master;
mysql> source /db_name.sql

# 开启GTID的MySQL导出
nohup mysqldump -uxxx -pxxx -q -e -t --single-transaction --set-gtid-purged=OFF db_name > /db_name.sql &

复制

快速导入参数说明

如果你不想了解整个操作过程，可以跳过此段，直接进入[开始导入数据]。以下参数对于MySQL性能优化有很大帮助，这里简单说一下木子在/etc/my.cnf
中配置的参考，详细如下所示:

innodb_buffer_pool_size: 默认大小为128M，用于缓存索引和数据的内存大小，这个当然是越大越好，数据读写在内存中非常快，减少了对磁盘的读写。当数据提交或满足检查点(checkpoint)条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用，根据经验推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。若设置不当，内存使用可能浪费或者使用过多。对于繁忙的服务器，buffer pool将划分为多个实例以提高系统并发性，减少线程间读写缓存的争用。buffer pool的大小首先受innodb_buffer_pool_instances影响，当然影响较小。

sync_binlog: 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

• sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
• sync_binlog=1，每一次事务都提交，保证高一致性、安全性，但性能损耗也是最大的。(阿里云RDS默认值)
• sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统故障，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统故障，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响，就是对写入性能影响太大，binlog虽然是顺序IO，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。阿里云RDS默认sync_binlog为1，很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100、1000 或者是0。这样牺牲一定的一致性，可以获得更高的并发和吞吐量！

innodb_log_file_size: 日志文件大小，即：ib_logfile0
与ib_logfile1
大小，如果你的innodb_log_files_in_group
值为默认，就只会存在两个log file文件。如果参数innodb_log_file_size设置太小，就会导致MySQL的日志文件(redo log)频繁切换，频繁的触发数据库的检查点(Checkpoint)，导致刷新脏页(dirty page)到磁盘的次数增加。从而影响IO性能。另外，如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换(因为实例恢复还需要，不能被循环复写，好比Oracle中的redo log无法循环覆盖)这样MySQL就Hang住了。如果参数innodb_log_file_size设置太大的话，虽然大大提升了IO性能，但是当MySQL由于意外(断电，OOM-Kill等)宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响，所以必须权衡二者进行综合考虑。

innodb_log_buffer_size: 控制日志缓冲区的大小，通常不需要把日志缓冲区设置得非常大。推荐的范围是1MB~8MB，一般来说是足够了，MySQL默认是8MB。

innodb_write_io_threads、innodb_read_io_threads: InnoDB使用后台线程处理数据页上读写IO请求的数量，这个值与服务器的CPU相关，因为木子这里是16C，而且我的大部份是写入，所以将innodb_write_io_threads
设置成12
,innodb_read_io_threads
设置成4
，以提高对应写入能力。

innodb_flush_log_at_trx_commit: MySQL支持用户自定义在commit时如何将log buffer中的日志刷到log file中。这种控制通过变量：innodb_flush_log_at_trx_commit 来决定，该变量有：0、1、2三种值，默认值为1。注意，这个变量只是控制commit动作是否刷新log buffer到磁盘中。

• 设置为0，将日志缓冲写入到日志文件，并且每秒钟写盘一次，但是事务提交时不做任何事。在这种情况下，MySQL性能最好，但如果mysqld进程崩溃，通常会导致最后1s的日志丢失。[性能最好，最不安全]
• 设置为1，将日志缓冲写入到日志文件，并且每次事务提交都进行写盘操作。这是默认的设置，该设置能保证不会丢失任何已经提交的事务。(阿里云RDS默认值) [性能最差，最安全]
• 设置为2，每次事务提交时把日志缓冲写到日志文件，但不写盘，由存储引擎的main_thread每秒将日志写入磁盘。这时如果mysqld进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后1s的日志丢失。[中合]

上面说到的 [最后 1s] 并不是绝对的，有的时候会丢失更多数据。有时候由于调度的问题，每秒刷写(once-per-second flushing)并不能保证100%执行。对于一些数据一致性和完整性要求不高的应用，配置为2就足够了；如果为了最高性能，可以设置为0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为1。目前所说的双一模式，实际就是innodb_flush_log_at_trx_commit
和sync_binlog
都设置为1
，这也是最安全的，在mysqld服务崩溃或者服务器主机故障的情况下，binary log只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双一模式由于会导致频繁的IO操作，因此该模式也是最慢的一种方式。

innodb_thread_concurrency: 默认是0，则表示没有并发线程数限制，所有请求都会直接请求线程执行。注意：当innodb_thread_concurrency设置为0时，则innodb_thread_sleep_delay的设置将会被忽略不起作用。如果数据库没出现性能问题时，使用默认值即可。

innodb_log_files_in_group: 控制日志文件数量，默认为2个，mysql事务日志文件是循环覆写的，当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库的checkpoint，这回导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果innodb_log_file_size设置太小，就会导致innodb频繁地checkpoint，导致性能降低。而如果设置较大，由于事务日志是顺序IO，大大提高了IO性能，但是在崩溃恢复InnoDB时，会导致恢复时间变长。如果InnoDB数据表有频繁的写操作，那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。

max_allowed_packet: MySQL根据配置文件会限制Server接受的数据包大小，大的插入和更新会受max_allowed_packet参数限制，导致大数据写入或者更新失败。

开始导入数据

根据上面的这些参数说明，木子在恢复数据库的时候，采用了以下参数设置：

vi /etc/my.cnf
innodb_buffer_pool_size = 48G
innodb_log_buffer_size = 32M
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_write_io_threads = 12
innodb_read_io_threads = 4
innodb_log_files_in_group = 3
max_allowed_packet = 10M

复制

数据恢复完成，转测试环境后(测试环境大部份数据为读操作)，修改参数配置如下：

vi /etc/my.cnf
innodb_buffer_pool_size = 48G
innodb_log_buffer_size = 8M
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 2
sync_binlog = 100
innodb_write_io_threads = 4
innodb_read_io_threads = 12
innodb_log_files_in_group = 2
max_allowed_packet = 10M

复制

注: MySQL 5.7版本以后可以动态修改参数，但是也要修改配置文件参数，防止重启之后，参数又变成配置文件内的参数，MySQL 5.7以下的版本为静态参数，需要修改配置文件，并重新启动MySQL服务生效配置。

进入数据库，关闭日志、关闭自动提交、关闭主键和唯一键检查，开始导入，需要注意的是对应动态修改只对当前会话生效。

# 创建数据库
create database db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
# 进入数据库
use db_name;
# 关闭日志
set sql_log_bin=off;
# 关闭自动提交
set autocommit=0;
# 关闭主键和唯一键检查
set unique_checks=0;

# 确保已经关闭
show VARIABLES like 'sql_log_bin';
show VARIABLES like 'autocommit';
show VARIABLES like 'UNIQUE_CHECKS';

# 开启事务
start transaciton;

# 引入SQL文件
source xxx.sql

# 成功后提交事务
commit;

# 完成恢复，退出
exit

复制

参考文献

[1] MySQL官网资料: https://dev.mysql.com/doc/refman/5.7/en/
[2] 强烈推荐，阿里云的数据库内核月报: http://mysql.taobao.org/monthly/
[3] 华为云最佳实践: https://support.huaweicloud.com/bestpractice-rds/rds_02_0010.html

三平台同步更新：
博客： https://www.oubayun.com
知乎： 欧巴云
微信公众号： 欧巴云