解决 Mysqldump不能导出大表的一种有效办法

原创已于 2025-05-24 10:18:29 修改 · 777 阅读

14 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#linux #开源软件

于 2025-05-18 21:12:41 首次发布

作者：田逸（formyz）

问题描述

项目里有一套MySQL 主从库集群，一主两从，操作系统为Centos 7.9，MySQL版本为8.0.28.在运行过程中，有一个从库经常性的同步失败，采取的措施执行”reset slave”,然后再从头开始同步，由于同步的基准位置比较大（偏移量数值），同步会耗费很长的时间，甚至有时候会失败。

为解决这个问题，初步的计划是—登录主库，在不影响业务的情况下，执行指令“ reset master”，清理掉binlog文件，然后用Mysqldump重新导出主库的全库数据，将导出的文件复制到从库进行主从同步，这样可以大大减少重新同步的时间，执行的命令如下：

mysqldump -h 10.122.200.240 --all-databases --opt --single-transaction > /data/db_all_20250518.sql

整个库大约50G，按以往的经验，大概个把小时就能完成。为了避免SSH远程连接中断，将上述指令运行在“screen”中。

大概运行20几分钟，屏幕报错，不能继续执行数据库导出操作，报错信息如下：

“mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `b_standard_apply` at row: 13042347”。通过调整MySQL选项文件“/etc/my.cnf ”的某些选项值，比如“net_read_timeout”等，没有效果。尝试执行“mysqldump”命令以各种选项进行导入，搞了两天，还是这个问题。查询表“b_standard_apply”,一共1400多万条记录，也不算很大。耽误太多时间，压力很大，放弃“Mysqldump”另起炉灶，用“Xtrabackup”试试。

部署Xtrabackup

主库系统命令行，执行下列命令安装Xtrabackup：

yum install https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.28-20/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.28-20.1.el7.x86_64.rpm

安装过程会有报错，那是Centos 7不再维护，Centos 7相关镜像站点的Yum源被清理掉，但不影响Xtrabackp的正常安装。要验证Xtrabackup是否被正确，系统命令行任意路径执行指令“xtrabackup --version”，版本号被正确的显示（如下图所示），基本可以认为安装是正确的。

主库数据全库导出

为方便管理，将导出的数据指定专门的目录，因为Xtrabackup导出的数据有大量的数据和目录。这里我将目录创建在大分区“/data”下，操作命令如下：

mkdir /data/bk

注意：备份/导出数据的磁盘空间一定要足够大。

登录MySQL主库，系统命令行执行下列指令导出数据库的数据。

xtrabackup --backup --target-dir=/data/bk -u root -H 10.122.200.240

执行导出数据库失败，报错信息如下图所示。

根据提示，是账号“root”权限问题。Mysql客户端以“root”账号登录Mysql服务器，提示符“mysql >”执行下列命令给“root”授权。

mysql> grant BACKUP_ADMIN on *.* to 'root'@'10.122.200.%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

切换到系统命令行，继续执行前述导出数据操作，正常执行完毕会有如下的屏幕输出。

[Xtrabackup] completed OK!

导出数据复制到从库系统

用Scp或者Rsync将Xtrabackup导出的数据（/data/bk）远程复制到从数据库系统，命令如下。

# scp -r /data/bk 10.122.200.241:/data

备份数据恢复到从数据库

准备一个同版本的数据库系统，编辑MySQL选项文件“/etc/my.cnf”,设置“server_id=10240”、“gtid-mode = on”等，完整的文件内容如下（读者可根据自己的环境自行修改）。

[mysqld_safe]

log-error=/data/mysql_db/error.log

pid-file=/data/mysql_db/mysqld.pid

[mysqld]

basedir = /usr/local/mysql

datadir = /data/mysql_db

port = 3306

socket = /tmp/mysql.sock

authentication_policy=mysql_native_password

explicit_defaults_for_timestamp=true

slow_query_log=on

slow_query_log_file = /data/mysql_db/slow.log

long_query_time = 2

#skip-grant-tables

#replica_skip_errors=1236,1032,1062

skip-name_resolve

max-connect-errors=100000

max-connections=2000

sql_mode=''

max_allowed_packet=12G

net_write_timeout=1200

net_read_timeout=1200

# need for slave

server-id = 10241

binlog-format = ROW

gtid-mode = on

enforce-gtid-consistency = true

log-bin = mysql-bin

relay-log = mysql-relay-bin

log_replica_updates=1

innodb_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_buffer_size=8388608

innodb_page_size=16384

#innodb_undo_directory=.

#innodb_undo_tablespaces=0

innodb_buffer_pool_size=50G

innodb_log_file_size=2G

innodb_flush_method=O_DIRECT

innodb_io_capacity=2000

innodb_io_capacity_max=6000

innodb_lru_scan_depth=2000

[client]

default-character-set = utf8

socket = /tmp/mysql.sock

user=root

password=HqxP6XCJgYflQU

[mysql]

default-character-set = utf8

socket = /tmp/mysql.sock

在从数据库选项文件”/etc/my.cnf”中，我们指定数据库数据的存储路径“/data/mysql_db”,当我们执行“Xtrabackup”导入数据, 存储位置就是该路径，因此需要保证目录“/data/mysql_db”为空。并且要求从数据库不要初始化，更不要预先启动从库MySQL服务。

从库系统命令行执行下列指令，进行数据恢复。

xtrabackup --prepare --target-dir=/data/bk_dir

xtrabackup --copy-back --target-dir=/data/bk_dir

正确执行完毕，查看目录“/data/mysql_db”,是否存在文件和目录（未执行数据恢复前是空），如果不空，将目录“/data/mysql_db”属主及属主修改成“mysql”，命令为“chown mysql:mysql /data/mysql_db”.

MySQL主从同步

从数据库启动MySQL服务，客户端登录MySQL服务器，执行数据库主从同步，此处不再赘述。