【MySQL 8.0】物理备份与恢复验证

[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