MySQL物理备份-XtraBackup远程备份恢复

总括：

生产环境500G数据备份，8个压缩线程，约2小时，压缩后大小约130G，qp文件解压约1小时

模拟从库故障，从主库备份数据恢复从库，把主库数据远程备份到从库。

1.主从库安装xtrabackup工具
#安装依赖
sudo yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey qpressv sshpass

#依赖包下载地址
https://repo.percona.com/yum/release/7/RPMS/x86_64/

rpm -ivh percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm


2.备份并释放流文件:
若未配置ssh免登录：
nohup bash -c 'innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p'P@ssw0rd' -S /opt/mysql/data/3310/mysqld.sock  --stream=xbstream --compress --compress-threads=16  /opt/mysql/data/3310/ |sshpass -p'rootroot'  ssh root@10.186.57.34  "xbstream -x -C /data/xbstream"' > /soft/backup.log 2>&1 &


若配置ssh免密登录：
nohup innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p --socket=/opt/mysql/data/3310/mysqld.sock --stream=xbstream --compress --compress-threads=8 /opt/mysql/data/3310/ | ssh root@10.186.57.34 'xbstream -x -C /data/xbstream' > /soft/backup.log 2>&1 &

3.解压:
xtrabackup  --parallel=8 --decompress --remove-original --target-dir=/data/xbstream/

4.恢复:
#清理/opt/mysql/etc/3310/my.cnf内数据目录和redolog目录数据

apply-log
innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf   --parallel=4 --apply-log /data/xbstream/ 

##/opt/mysql/etc/3310/my.cnf 可以是故障从库的配置文件

copy-backup
xtrabackup --defaults-file=/opt/mysql/etc/3310/my.cnf  --copy-back --target-dir=/data/xbstream/ 

5.修改数据目录和redolog目录属主
chown -R mysql.mysql /data 
chown -R mysql.mysql /redolog

6.启动从库mysql服务
 /opt/mysql/base/8.0.28/bin/mysqld --defaults-file=/opt/mysql/etc/3310/my.cnf --daemonize --pid-file=/opt/mysql/data/3310/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3310/mysqld.sock --port=3310

7.建立复制
[root@localhost xbstream]# cat xtrabackup_binlog_info
mysql-bin.000010        184074483       304e4a27-33ed-11ef-91b7-02000aba3b26:1-25571

change replication source to 
source_host='10.186.59.38',
source_port=3310,
source_user='repl',
source_password='P@ssw0rd',
source_log_file='mysql-bin.000010',
source_log_pos=184074483;

start replica;
show replica status \G;

8.等待主从数据追平

1.安装xtrabackup工具

#安装依赖
sudo yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey qpress


#依赖包下载地址
https://repo.percona.com/yum/release/7/RPMS/x86_64/

[root@localhost soft]# rpm -ivh percona-xtrabackup-24-2.4.29-1.el7.x86_64\ \(1\).rpm
warning: percona-xtrabackup-24-2.4.29-1.el7.x86_64 (1).rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-24-2.4.29-1.el################################# [100%]
[root@localhost soft]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-3.el7.x86_64.rpm

2.备份并释放流文件

nohup bash -c 'innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p"P@ssw0rd" -S /opt/mysql/data/3310/mysqld.sock --stream=xbstream --compress --compress-threads=8 /opt/mysql/data/3310/ |sshpass -p'rootroot'  ssh root@10.186.57.34  "xbstream -x -C /data/xbstream"' > /soft/xtrabackup.log 2>&1 &

##备份命令说明
1.compress压缩，开启8个压缩线程（可视数据库负载大小调整），压缩率可达70%，500G压缩至130G.
2.|sshpass -p'rootroot' 可实现远程备份到目标服务器，若两台服务器配置了ssh免密登录，则可去除sshpass -p'rootroot'。
3.备份命令后台运行，可在/soft/xtrabackup.log查看备份进度和信息。

#远程服务器/data/xbstream目录
[root@localhost xbstream]# ip a |grep 10.186
    inet 10.186.57.34/24 brd 10.186.57.255 scope global eth0
[root@localhost xbstream]# ll
total 38980
-rw-r----- 1 root root      484 Jul 18 07:19 backup-my.cnf.qp
drwxr-x--- 2 root root       23 Jul 18 07:19 ccic_prod_mass0
drwxr-x--- 2 root root      311 Jul 18 07:19 ccic_prod_mass1
drwxr-x--- 2 root root      311 Jul 18 07:19 ccic_prod_mass2
drwxr-x--- 2 root root      311 Jul 18 07:19 ccic_prod_mass3
-rw-r----- 1 root root      372 Jul 18 07:19 ib_buffer_pool.qp
-rw-r----- 1 root root 35850662 Jul 18 07:19 ibdata1.qp
drwxr-x--- 2 root root     4096 Jul 18 07:19 mysql
drwxr-x--- 2 root root     8192 Jul 18 07:19 performance_schema
drwxr-x--- 2 root root     8192 Jul 18 07:19 sys
drwxr-x--- 2 root root      111 Jul 18 07:19 test
drwxr-x--- 2 root root       67 Jul 18 07:19 universe
-rw-r----- 1 root root      166 Jul 18 07:19 xtrabackup_binlog_info.qp
-rw-r----- 1 root root      147 Jul 18 07:19 xtrabackup_checkpoints
-rw-r----- 1 root root      619 Jul 18 07:19 xtrabackup_info.qp
-rw-r----- 1 root root  4012668 Jul 18 07:19 xtrabackup_logfile.qp
[root&#