xtrabackup 的使用

xtrabackup 的使用

一、安装包准备

1. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm
2. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-5.7.30-1.el7.x86_64.rpm
3. wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-5.7.30-1.el7.x86_64.rpm
4. wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

二、安装

1. rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm
2. rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm
3. rpm -ivh mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm
4. yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

三、模拟备份恢复

1.模拟周一全量备份 quanbei_2024-02-23
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --host=***** --port=3306 --user=root --password=***** /data/backup/test_xtrabackup/xbk_quanbei/quanbei_$(date +%Y-%m-%d) --no-timestamp

2.模拟周二差异备份 chayibei_2024-02-23
innobackupex --defaults=/etc/my.cnf --host=***** --port=3306 --user=root --password=***** --socket=/tmp/mysql.sock --incremental /data/backup/test_xtrabackup/xbk_zengbei/chayibei_$(date+$(ls -tl /data/backup/test_xtrabackup/xbk_quanbei/ |sed -n ‘2p’| awk ‘{print $9}’) --no-timestamp

3.模拟周三增量备份 zengbei_2024-02-23_13-36
innobackupex --defaults=/etc/my.cnf --host=***** --port=3306 --user=root --password=***** --incremental /data/backup/test_xtrabackup/xbk_zengbei/zengbei_$(date+$(ls -tl /data/backup/test_xtrabackup/xbk_zengbei/ |sed -n ‘2p’| awk ‘{print $9}’) --no-timestamp

4.模拟周四增量备份 zengbei_2024-02-23_13-45
innobackupex --defaults=/etc/my.cnf --host=***** --port=3306 --user=root --password=***** --incremental /data/backup/test_xtrabackup/xbk_zengbei/zengbei_$(date+$(ls -tl /data/backup/test_xtrabackup/xbk_zengbei/ |sed -n ‘2p’| awk ‘{print $9}’) --no-timestamp

5.模拟周五增量备份 zengbei_2024-02-23_13-47
innobackupex --defaults=/etc/my.cnf --host=***** --port=3306 --user=root --password=***** --incremental /data/backup/test_xtrabackup/xbk_zengbei/zengbei_$(date+$(ls -tl /data/backup/test_xtrabackup/xbk_zengbei/ |sed -n ‘2p’| awk ‘{print $9}’) --no-timestamp

6.删除数据
6.1删除数据库
mysql -uroot -p***** -e ‘drop database ddd’
6.2关闭数据库
systemctl stop mysqld
6.3数据库目录备份并重新创建
mv data data.bak
mkdir data

7.进行恢复测试
7.1合并数据 （合并的次数要增量备份的次数一样 并且合并的顺序也有与增量备份的顺序一致）
innobackupex --defaults=/etc/my.cnf --host=***** --port=3306 --user=root --password=***** --apply-log --redo-only /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/
7.2合并周二的差异数据
innobackupex --apply-log --redo-only /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/ --incremental-dir=/data/backup/test_xtrabackup/xbk_zengbei/chayibei_2024-02-23/
7.3合并周三的增量数据
innobackupex --apply-log --redo-only /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/ --incremental-dir=/data/backup/test_xtrabackup/xbk_zengbei/zengbei_2024-02-23_13-36/
7.4合并周四的增量数据
innobackupex --apply-log --redo-only /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/ --incremental-dir=/data/backup/test_xtrabackup/xbk_zengbei/zengbei_2024-02-23_13-45/
7.5合并周五的增量数据
innobackupex --apply-log --redo-only /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/ --incremental-dir=/data/backup/test_xtrabackup/xbk_zengbei/zengbei_2024-02-23_13-47/
7.6恢复数据 (把合并后的数据拷贝到数据库目录下)
innobackupex --copy-back /data/backup/test_xtrabackup/xbk_quanbei/quanbei_2024-02-23/

8.启动数据库
chown -R mysql.mysql data
systemctl start mysqld