10-Mysql-使用xtrabackup工具备份数据库安装xtrabackup工具执行备份命令 innobackup

安装xtrabackup工具


yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL -y

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm -y

执行备份命令

innobackupex --user=root --password=summer66 -S /tmp/mysql.sock /xtrabackup_data/

不带时间戳的备份信息

innobackupex --no-timestamp --user=root --password=linux3306 -S /tmp/mysql.sock /xtrabackup_data_no_time/full_3306_db_$(date +%F)/

模拟删除数据

挪走自带的数据，用mv替代rm

[root@tech-db-51 /linux0224/mysql_3306]#mv ./*  /opt/3306_db_backup/

基于xtraback命令的参数，对默认所有的未提交事务，确保数据一致性

innobackupex --apply-log   /xtrabackup_data_no_time/full_3306_db_2024-10-10/

此时就可以直接恢复数据了

innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync  /xtrabackup_data_no_time/full_3306_db_2024-10-10/

4 重新授权给mysql


[root@tech-db-51 /linux0224/mysql_3306]#chown -R mysql.mysql ./*

5.重启

[root@tech-db-51 /linux0224/mysql_3306]#systemctl restart mysqld

6.确保数据可以访问

mysql> select * from test_backup.test_table;
+------+
| id   |
+------+
|  777 |
|  888 |
|  999 |
+------+
3 rows in set (0.01 sec)