DevOps文化 & SRE实战分享平台

0%

MySQL 5.7 快速导入导出大SQL文件及简单参数调优


文章声明:此文基于木子实操撰写
生产环境: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)来强化数据库的主备一致性、故障恢复、以及容错能力。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 未开启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_logfile0ib_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_commitsync_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参数限制,导致大数据写入或者更新失败。

开始导入数据

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

1
2
3
4
5
6
7
8
9
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

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

1
2
3
4
5
6
7
8
9
10
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服务生效配置。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 创建数据库
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 transaction;

# 引入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
知乎: 欧巴云
微信公众号: 欧巴云

坚持原创技术分享,您的支持与鼓励,是我持续创作的动力!