mysql迁移：xtrabackup迁移mysql5.7.32

问题描述：利用外部xtrabackup工具来做迁移mysql数据库，或者恢复数据库

xtrabackup迁移mysql

1.环境

	mysql源库	mysql目标迁移库
IP	192.168.163.3	196.168.163.16
操作系统	Ceontos7.8	Ceontos7.8
数据库版本	mysql-5.7.32	mysql-5.7.32
mysql实例	db_hzh03	db_hzh03
my.cnf路径	/data/mysql-5.7.32/db_hzh03/conf/hzh03.cnf	/data/mysql-5.7.32/db_hzh03/conf/hzh03.cnf
socket路径	/data/mysql-5.7.32/db_hzh03/mysql.sock	/data/mysql-5.7.32/db_hzh03/mysql.sock
xtrabackup版本	xtrabackup-24	xtrabackup-24

2.Xtrabackup安装

在源库和目标库都安装percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

Xtrabackup下载路径

2.1软件安装

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

链接：https://pan.baidu.com/s/1mPIcKyleAVKTWk_chPFzZw
提取码：htmy

2.2安装依赖包

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

2.3安装软件

rpm -ivh  percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm --nodeps --force

2.4测试xtrabackup是否安装成功

[root@localhost ~]# xtrabackup -version

两个服务器同样操作

3.源端备份

3.1源端制造测试验证数据

用户创建

数据库创建

mysql> create database test01;

Query OK, 1 row affected (0.01 sec)

3.2备份

创建备份目录

[root@localhost ~]# mkdir -p /mysqlbackup/

[root@localhost ~]# chown -R mysql.mysql /mysqlbackup/

[root@localhost ~]# innobackupex --defaults-file=/data/mysql-5.7.32/db_hzh03/conf/hzh03.cnf --socket=/data/mysql-5.7.32/db_hzh03/mysql.sock --user=root --password=zabbix.9.31   /mysqlbackup

查看备份出来的文件

3.3传输文件到目标服务器

[root@localhost /mysqlbackup]# scp -r 2021-01-18_11-22-21 mysql@192.168.163.16:/data/mysql-5.7.32/

4.目标端恢复

在线恢复数据库备份时，注意一些参数的含义，下面所用到的参数

--defaults-file：目标端数据库的.cnf配置文件

--datadir ：重新规划的恢复数据文件位置

--socket ：目标端数据库的socket文件位置

4.1导入报错

Can't locate Digest/MD5.pm

[root@localhost /data/mysql-5.7.32]# innobackupex --defaults-file=/data/mysql-5.7.32/db_hzh03/conf/hzh03.cnf --socket=/data/mysql-5.7.32/db_hzh03/mysql.sock --user=root --password=zabbix.9.31   /data/mysql-5.7.32/2021-01-18_11-22-21/

xtrabackup: recognized server arguments: --datadir=/data/mysql-5.7.32/db_hzh03/data --tmpdir=/data/mysql-5.7.32/db_hzh03/tmp --log_bin=/data/mysql-5.7.32/db_hzh03/binlog/hzh03-bin --server-id=3330621 --open_files_limit=65000 --innodb_open_files=63000 --innodb_data_home_dir=/data/mysql-5.7.32/db_hzh03/data --innodb_log_group_home_dir=/data/mysql-5.7.32/db_hzh03/data --innodb_log_file_size=500M --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql-5.7.32/db_hzh03/data --innodb_undo_tablespaces=3 --innodb_flush_log_at_trx_commit=2 --innodb_flush_method=O_DIRECT --innodb_io_capacity=2000 --innodb_buffer_pool_size=4G --innodb_log_buffer_size=32M --innodb_max_dirty_pages_pct=85 --innodb_adaptive_hash_index=0

xtrabackup: recognized client arguments:

210118 14:34:05 innobackupex: Starting the backup operation

 

IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".

 

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.

BEGIN failed--compilation aborted at - line 693.

210118 14:34:06 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /data/mysql-5.7.32/db_hzh03/mysql.sock

Using server version 5.7.32-log

innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /data/mysql-5.7.32/db_hzh03/data

xtrabackup: open files limit requested 65000, set to 65000

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = /data/mysql-5.7.32/db_hzh03/data

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = /data/mysql-5.7.32/db_hzh03/data

xtrabackup:   innodb_log_files_in_group = 3

xtrabackup:   innodb_log_file_size = 524288000

xtrabackup: using O_DIRECT

InnoDB: Number of pools: 1

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: File /data/mysql-5.7.32/db_hzh03/data/ib_logfile0: 'open' returned OS error 71. Cannot continue operation

InnoDB: Cannot continue operation.

系统缺少支持perl-Digest-MD5组件，如果联网的话，可以直接yum安装

[root@localhost /etc/yum.repos.d]# yum -y install perl perl-devel perl-Time-HiRes perl-DBD-MySQL

[root@localhost /etc/yum.repos.d]# yum -y install perl-Digest-MD5

4.2导入恢复

[mysql@localhost ~]$ innobackupex --defaults-file=/data/mysql-5.7.32/db_hzh03/conf/hzh03.cnf --socket=/data/mysql-5.7.32/db_hzh03/mysql.sock --user=root --password=zabbix.9.31  --copy-back /data/mysql-5.7.32/2021-01-18_11-22-21/

4.3重启数据库

[mysql@localhost /data/mysql-5.7.32]$ sh /data/mysql-5.7.32/db_hzh03/bin/shutdown.sh

Enter password:

[mysql@localhost /data/mysql-5.7.32]$ sh /data/mysql-5.7.32/db_hzh03/bin/startup.sh

[mysql@localhost /data/mysql-5.7.32]$ sh /data/mysql-5.7.32/db_hzh03/bin/login.sh

4.4验证测试数据

至此恢复完成