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验证测试数据
至此恢复完成
