安装

安装依赖

2.X.X相关依赖下载地址

8.X.X相关依赖下载地址

2.X.X -> mysql5.X
  1. yum -y install perl-Digest-MD5
  2. yum -y install rsync
  3. rpm -ivh libev-4.15-7.el7.x86_64.rpm
  4. rpm -ivh percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
  5. rpm -ivh qpress-11-1.el7.x86_64.rpm

8.X.X -> mysql8.X.X
yum -y install perl-Digest-MD5
yum -y install rsync
rpm -ivh libev-4.15-7.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-80-8.0.26-18.1.el7.x86_64.rpm
rpm -ivh qpress-11-1.el7.x86_64.rpm

备份恢复

备份用户创建

# 创建用户（若用户没有）
CREATE USER 'dbbackup'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'dbbackup';

# 授权
# 8.X.X需要 BACKUP_ADMIN 权限
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.*  TO "dbbackup"@'127.0.0.1';

# 8.X.X需要
GRANT SELECT ON performance_schema.log_status TO "dbbackup"@'127.0.0.1';

FLUSH PRIVILEGES;

备份

# 创建存放目录的文件夹
mkdir /data/dbbackup

# 备份1 打包到一个文件中
# --stream=xbstream 流式导出
xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=dbbackup --password=dbbackup --port=源库port --backup --target-dir=/data/dbbackup --apply-log-only=true --stream=xbstream --compress --compress-threads=8 --parallel=4 > /data/dbbackup/dbbackup-10.55.192.17-3310-`date +%Y-%m-%d-%H-%M-%S`.xbstream

# 备份2 直接备份到文件夹
xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=dbbackup --password=dbbackup --port=xxxx --backup --apply-log-only=true --compress --compress-threads=5 --parallel=3  --target-dir=/data/dbbackup/10.123.99.41_3311

恢复

# 传输备份文件
scp /data/dbbackup/dbbackup-10.55.192.17-3310-2021-11-03-14-45-56.xbstream root@目标端:/data/dbbackup

# 关闭目标端数据库实例
mysqld_multi report
mysqld_multi stop 3310

# 备份目标端数据库目录
mv /data/mysql/mysqldataXXXX /data/mysql/mysqldataXXXX_back

# 创建 MySQL 文件夹
mkdir -p /data/mysql/mysqldataXXXX/{binlog,innodb_log,innodb_ts,log,mydata,relaylog,sock,tmpdir} 

# 取出备份文件，若使用了打包方式的备份，则需要取出
xbstream -C /data/mysql/mysqldataXXXX/mydata/ -x < dbbackup-10.55.192.17-3310-2021-11-09-19-30-52.xbstream

# 解压备份文件
# 删除原文件
xtrabackup --decompress --remove-original --target-dir=/data/mysql/mysqldataXXXX/mydata

# 生成表空间文件
#1. 如果是部署单独的新的服务，或者服务整个还原，全备是不加--apply-log-only的，目的是回滚所有事务，保证没有悬挂事务需要处理
#2. 如果是需要后续的增量或者像我们这种搭建复制的情况，全备和增备都是一定要加--apply-log-only的
xtrabackup --prepare --apply-log-only=true --target-dir=/data/mysql/mysqldataXXXX/mydata

xtrabackup --prepare --target-dir=/data/mysql/mysqldata3313/mydata


# 根据 MySQL 配置文件修改 MySQL 日志文件的路径
# 1. 不加--apply-log-only=true
mv /data/mysql/mysqldataXXXX/mydata/ib_logfile0 /data/mysql/mysqldataXXXX/innodb_log
mv /data/mysql/mysqldataXXXX/mydata/ib_logfile1 /data/mysql/mysqldataXXXX/innodb_log
mv /data/mysql/mysqldataXXXX/mydata/ibdata1 /data/mysql/mysqldataXXXX/innodb_ts
mv /data/mysql/mysqldataXXXX/mydata/ibtmp1 /data/mysql/mysqldataXXXX/innodb_ts
touch /data/mysql/mysqldataXXX/log/error.log
# 2. 加--apply-log-only=true
mv /data/mysql/mysqldataXXXX/mydata/ibdata1 /data/mysql/mysqldataXXXX/innodb_ts
touch /data/mysql/mysqldataXXX/log/error.log


# 修改目录用户组
chown -R mysql.mysql /data/mysql/mysqldataXXXX

# 启动mysql实例，需要观察第二次启动后会不会自动开启复制
mysqld_multi start XXX
mysqld_multi stop XXX
mysqld_multi start XXX
ss -anp| grep XXX

搭建复制

搭建

# 查看备份 GTID 的复制位点
cat /data/mysql/mysqldataXXXX/mydata/xtrabackup_info
# binlog_pos = filename 'mysql-bin.000012', position '240659374', GTID of the last change '64a69b77-3564-11ec-8ce4-58c7acc856d2:1-8176010'

# 搭建复制
reset slave all;
reset master;
set global gtid_purged='7193a492-eb97-11eb-8c55-0cda411d5d4f:1-9212848213';
change master to master_host='10.123.36.11',master_user='repl',master_port=3316,master_password='repl',master_auto_position=1;
start slave;
show slave status\G