[root@node01 ~]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@node01 ~]# rpm -ivh percona-release-latest.noarch.rpm 
[root@node01 ~]# yum install -y percona-xtrabackup-80

用户与权限
(root@node01) > create user xtrabackup@'localhost' identified with mysql_native_password by 'xtrabackup';  
Query OK, 0 rows affected (0.01 sec)

(root@node01) > grant backup_admin,reload,process,lock tables,replication client on *.* to xtrabackup@'localhost'; 
Query OK, 0 rows affected (0.04 sec)

(root@node01) > grant select on performance_schema.log_status to xtrabackup@'localhost';
Query OK, 0 rows affected (0.04 sec)

(root@node01) > grant select on performance_schema.keyring_component_status to xtrabackup@'localhost';
Query OK, 0 rows affected (0.05 sec)

(root@node01) > grant select on performance_schema.replication_group_members to xtrabackup@'localhost';
Query OK, 0 rows affected (0.01 sec)

(root@node01) > grant select on mysql.component to xtrabackup@'localhost';
Query OK, 0 rows affected (0.01 sec)

(root@node01) > flush privileges;
Query OK, 0 rows affected (0.01 sec)

(root@node01) > show grants for 'xtrabackup'@'localhost';
+----------------------------------------------------------------------------------------------+
| Grants for xtrabackup@localhost                                                              |
+----------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO `xtrabackup`@`localhost`    |
| GRANT BACKUP_ADMIN ON *.* TO `xtrabackup`@`localhost`                                        |
| GRANT SELECT ON `mysql`.`component` TO `xtrabackup`@`localhost`                              |
| GRANT SELECT ON `performance_schema`.`keyring_component_status` TO `xtrabackup`@`localhost`  |
| GRANT SELECT ON `performance_schema`.`log_status` TO `xtrabackup`@`localhost`                |
| GRANT SELECT ON `performance_schema`.`replication_group_members` TO `xtrabackup`@`localhost` |
+----------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

[mysql@node01 ~]$ ssh-keygen
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node02
[mysql@node01 ~]$ ssh node02 date
Sun Apr  9 15:12:54 CST 2023

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --target-dir=/home/mysql/backup/full  
[mysql@node01 ~]$ scp -r backup/full/* node02:/home/mysql/backup/full
[mysql@node02 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup/full
[mysql@node02 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup/full

[mysql@node02 ~]$ /etc/init.d/mysql.server start
[mysql@node02 ~]$ /etc/init.d/mysql.server status

[mysql@node02 ~]$ cat backup/xtrabackup_binlog_info
mysql-bin.000016        95923365

[mysql@node02 ~]$ cd binlog
[mysql@node02 binlog]$ mysqlbinlog --start-position=95923365 mysql-bin.000016 mysql-bin.000017 | mysql -uroot -pabcd.1234
[mysql@node01 ~]$ cat full_backup.sh 
#!/bin/bash
user='root'
passwd='abcd.1234'
backup_dir='/home/mysql/backup'
/bin/xtrabackup --backup --user=$user --password=$passwd --target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`
last_day=$(date -d "7 days ago" +%Y-%m-%d)
filename=$(find $backup_dir -name "$last_day*" -print|awk -F / '{print $NF}')
if [ ! -n "$filename" ]; then
  echo "null"
else
rm -rf $backup_dir/$filename
fi

[mysql@node01 ~]$ chmod a+x full_backup.sh 

[mysql@node01 ~]$ crontab -l
0 2 * * * /home/mysql/full_backup.sh > /home/mysql/backup/full_backup.log 2>&1
压缩与解压备份
[root@node01 ~]# tar xvf qpress-11-linux-x64.tar
[root@node01 ~]# mv qpress /usr/bin/

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --compress --target-dir=/home/mysql/backup/compress
[mysql@node01 ~]$ xtrabackup --decompress --remove-original --target-dir=/home/mysql/backup/compress

加密与解密备份
[mysql@node01 ~]$ openssl rand -base64 24
fwjrZQgcAwAg3TD+lcgrNJuPCIYfdfGq

[mysql@node01 ~]$ echo -n "fwjrZQgcAwAg3TD+lcgrNJuPCIYfdfGq" > /home/mysql/backup/keyfile

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --encrypt=AES256 --encrypt-key-file=/home/mysql/backup/keyfile --target-dir=/home/mysql/backup/encrypt
[mysql@node01 ~]$ xtrabackup --decrypt=AES256 --encrypt-key-file=/home/mysql/backup/keyfile  --remove-original --target-dir=/home/mysql/backup/encrypt

流式备份
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream > /home/mysql/backup/stream/backup.xbstream
[mysql@node01 ~]$ xbstream -x -C /home/mysql/backup/stream < /home/mysql/backup/stream/backup.xbstream 

压缩
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 > /home/mysql/backup/stream/backup.lz4
[mysql@node01 ~]$ lz4 -d /home/mysql/backup/stream/backup.lz4 | xbstream -x -C /home/mysql/backup/stream

远程
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | ssh mysql@node02 "cat - > /home/mysql/backup/stream/backup.lz4 "
[mysql@node02 ~]$ lz4 -d /home/mysql/backup/stream/backup.lz4 | xbstream -x -C /home/mysql/backup/stream

限速
[root@node01 ~]# yum install -y pv
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | pv -q -L 20m | ssh mysql@node02 "cat - > /home/mysql/backup/stream/backup.lz4 "

校验
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | tee >(sha1sum > /tmp/source_checksum) | ssh mysql@node02 \
"cat - | tee >(sha1sum > /tmp/destination_checksum) > /home/mysql/backup/stream/backup.lz4"

[mysql@node01 ~]$ cat /tmp/source_checksum
f4ff3f5af3fb865bdcf9a1e7de9113781097892f  -

[mysql@node02 ~]$ cat /tmp/destination_checksum
f4ff3f5af3fb865bdcf9a1e7de9113781097892f  -