mysql迁移：xtrabackup迁移mysql5.7.32

概述问题描述：利用外部xtrabackup工具来做迁移mysql数据库，或者恢复数据库 xtrabackup迁移mysql 1.环境 mysql源库 mysql目标迁移库 IP 192.168.163.3

问题描述：利用外部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-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-32/

 

 

 

 

4.目标端恢复

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

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

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

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

4.1导入报错

Can't locate Digest/MD5.pm

[root@localhost /data/MysqL-32]# innobackupex --defaults-file=/data/MysqL-9.31   /data/MysqL-32/21/xtrabackup: recognized server arguments: --datadir=/data/MysqL-32/db_hzh03/data --tmpdir=/data/MysqL-32/db_hzh03/tmp --log_bin=/data/MysqL-32/db_hzh03/binlog/hzh03-bin --server-ID=3330621 --open_files_limit=65000 --innodb_open_files=63000 --innodb_data_home_dir=/data/MysqL-32/db_hzh03/data --innodb_log_group_home_dir=/data/MysqL-32/db_hzh03/data --innodb_log_file_size=500M --innodb_log_files_in_group=3 --innodb_undo_directory=/data/MysqL-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=0xtrabackup: 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.06 Connecting to MysqL server host: localhost,user: root,password: set,port: not 32/db_hzh03/MysqL.sockUsing server version 32-loginnobackupex version 21 based on MysqL server 32 linux (x86_64) (revision ID: 5988af5)xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/MysqL-dataxtrabackup: open files limit requested 65000,set to 65000xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = /data/MysqL-dataxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextendxtrabackup:   innodb_log_group_home_dir = /data/MysqL-dataxtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 524288000 O_DIRECTInnoDB: Number of pools: 1InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specifIEd.InnoDB: file /data/MysqL-32/db_hzh03/data/ib_logfile0: open' returned OS error 71. Cannot continue operationInnoDB: 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-9.31  --copy-back /data/MysqL-21/

 

 

 

 

4.3重启数据库

[MysqL@localhost /data/MysqL-32]$ sh /data/MysqL-32/db_hzh03/bin/shutdown.shEnter password:[MysqL@localhost /data/MysqL-startup.sh[MysqL@localhost /data/MysqL-32/db_hzh03/bin/login.sh

　　

4.4验证测试数据

 

 

 

至此恢复完成

 

 

 

总结

以上是内存溢出为你收集整理的mysql迁移：xtrabackup迁移mysql5.7.32全部内容，希望文章能够帮你解决mysql迁移：xtrabackup迁移mysql5.7.32所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。