【MySQL 8.0】增量备份与恢复验证

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --target-dir=/home/mysql/backup/base 

[mysql@node01 ~]$ ./tpcc-mysql/tpcc_start -h 192.168.1.101 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 120

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --incremental-basedir=/home/mysql/backup/base --target-dir=/home/mysql/backup/incr1

[mysql@node01 ~]$ ./tpcc-mysql/tpcc_start -h 192.168.1.101 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 120

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --incremental-basedir=/home/mysql/backup/incr1 --target-dir=/home/mysql/backup/incr2 

[mysql@node01 ~]$ scp -r backup/* node02:/home/mysql/backup/

[mysql@node02 ~]$ xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/backup/base 
[mysql@node02 ~]$ xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/backup/base --incremental-dir=/home/mysql/backup/incr1 
[mysql@node02 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup/base --incremental-dir=/home/mysql/backup/incr2 
[mysql@node02 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup/base

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


[mysql@node02 ~]$ cat backup/incr2/xtrabackup_binlog_info
mysql-bin.000019        349566  

[mysql@node02 ~]$ cd binlog
[mysql@node02 binlog]$ mysqlbinlog --start-position=349566 mysql-bin.000019 | mysql -uroot -pabcd.1234

Page Tracking
(root@node01) > install component "file://component_mysqlbackup";
Query OK, 0 rows affected (0.59 sec)

(root@node01) > select count(*) from mysql.component where component_urn='file://component_mysqlbackup';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

(root@node01) > grant system_variables_admin on *.* to xtrabackup@'localhost'; 
Query OK, 0 rows affected (0.35 sec)

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --target-dir=/home/mysql/backup/base --page-tracking

[mysql@node01 ~]$ ./tpcc-mysql/tpcc_start -h 192.168.1.101 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 120

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --incremental-basedir=/home/mysql/backup/base --target-dir=/home/mysql/backup/incr1 --page-tracking

[mysql@node01 ~]$ ./tpcc-mysql/tpcc_start -h 192.168.1.101 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 120

[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --incremental-basedir=/home/mysql/backup/incr1 --target-dir=/home/mysql/backup/incr2 --page-tracking

[mysql@node01 ~]$ vim full_backup.sh 
#!/bin/bash
user='xtrabackup'
passwd='xtrabackup'
backup_dir='/home/mysql/backup/base'
/usr/bin/xtrabackup --backup --user=$user --password=$passwd --target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`

[mysql@node01 ~]$ vi incr_backup.sh 
#!/bin/bash  
user='xtrabackup'
passwd='xtrabackup'
backup_dir='/home/mysql/backup'
last_day=$(date -d "1 days ago" +%Y-%m-%d)
filename=$(find $backup_dir -name "$last_day*" -print|awk -F / '{print $NF}')
/usr/bin/xtrabackup --backup --user=$user --password=$passwd --incremental-basedir=$backup_dir/$filename 
--target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`

[mysql@node01 ~]$ chmod a+x *.sh  
[mysql@wallet01 ~]$ crontab -e
00 2 * * 0 /home/mysql/full_backup.sh>/home/mysql/backup/full_backup.log 2>&1
00 2 * * 1-6 /home/mysql/incr_backup.sh>/home/mysql/backup/incr_backup.log 2>&1