通过xtrabackup工具实现mysql大量数据迁移

通过xtrabackup工具实现mysql大量数据迁移

迁移前请确保数据库版本一致，具体至小版本

Mysql Version：8.0.19

主：192.168.118.21:3306 从：192.168.118.31:3306

1、安装XtraBackup工具 （主从服务器均需要安装）

1）下载工具

wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.34-29/binary/tarball/percona-xtrabackup-8.0.34-29-Linux-x86_64.glibc2.17.tar.gz

tar zxvf percona-xtrabackup-8.0.34-29-Linux-x86_64.glibc2.17.tar.gz

2、迁移数据

1）使用 XtraBackup 备份主库（在主库中执行）

cd /root/percona-xtrabackup-8.0.34-29-Linux-x86_64.glibc2.17/bin

./xtrabackup --defaults-file=/etc/my.cnf --backup --user=repl --password=jjxt@123 --target-dir=/opt/app/backup/3306 --host 192.168.118.21 --port=3306

指定数据目录：--datadir=/data/mysql/mysql_3306/data

提示如下则完成导出：

2）报错解决

①权限报错

报错：2025-07-02T16:15:52.597028+08:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query 'LOCK INSTANCE FOR BACKUP' : 1227 (42000) Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation

MySQL 数据库授权用户没有 BACKUP_ADMIN 权限（尽量使用root用户，避免出现其他权限问题）。为了解决该问题，需要在 MySQL 数据库中授权 root 用户 BACKUP_ADMIN 权限，具体操作步骤如下：

mysql -uroot -h192.168.118.21 -P3306 -p

GRANT BACKUP_ADMIN ON *.* TO 'root'@'%';

mysql> show grants for 'root'@'%';

mysql> FLUSH PRIVILEGES;

②目录报错

2025-07-02T16:38:16.564363+08:00 0 [ERROR] [MY-013862] [InnoDB] Neither found #innodb_redo subdirectory, nor ib_logfile* files in ./

默认程序会找/etc/my.cnf配置文件，若搭建时不是此目录需要单独指定 --defaults-file=/etc/my.cnf

3）将备份传输到从库

cd /opt/app/backup

tar zcvf 3306.tar.gz 3306/

scp 3306.tar.gz root@192.168.118.31:/opt/app/backup

4）对原数据进行备份

mysqld_multi stop 3306 #停止数据库

mv /data/mysql/mysql_3306/data /opt/app/backup/data_3306 #将数据目录备份

5）恢复备份到从库数据目录（从库执行）

cd /opt/app/backup && tar zxvf 3306.tar.gz

6）准备备份（在从库中执行）

./xtrabackup --prepare --target-dir=/opt/app/backup/3306

–target-dir：备份文件目录

7）恢复备份到从库的数据目录（在从库中执行）

#将备份文件拷贝至数据目录

./xtrabackup --datadir=/data/mysql/mysql_3306/data --copy-back --target-dir=/opt/app/backup/3306

–target-dir：备份文件目录

--datadir=数据目录

#授权

chown -R mysql:mysql /data/mysql/mysql_3306/

#启动数据库

mysqld_multi start 3306

3、修复主从

1）获取 binlog 文件名和位置（查看 xtrabackup_binlog_info）（可以从备份文件内获取，也可以登录主库获取）

[root@node-2 log]# cat /opt/app/backup/3306/xtrabackup_binlog_info

binlog.000013 157

2）配置从库连接主库并启动复制（在从库中执行）

#连接主库

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.118.31',

-> MASTER_USER='repl', ## 之前在主库中设置的主从复制用户

-> MASTER_PASSWORD='jjxt@123',

-> MASTER_LOG_FILE='mysql3306_bin.000013',

-> MASTER_LOG_POS=157;

Query OK, 0 rows affected, 1 warning (0.02 sec)

#启动slave

START SLAVE;

3）检查主从复制状态

SHOW SLAVE STATUS \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes