一、全备操作
通过先进行一次全量备份
[root@mha ~]# xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 --backup --target-dir=/backup/mysql/full_`date +%Y-%m-%d-%H-%M-%S`
增量备份操作
[root@mha ~]# xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 --backup --target-dir=/backup/mysql/full_2022-03-09-10-42-52/incr_back/ --incremental-dir=/backup/incremental/incremental_bak_20220309/
二、误删除操作drop 库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sfqd |
| students_info |
| sys |
| zabbix |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database sfqd;
Query OK, 1 row affected (0.12 sec)
mysql> drop database students_info;
Query OK, 1 row affected (0.03 sec)
1、目前数据库状态
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
三、恢复操作
1、mysql服务停止
[root@mha ~]# etc/init.d/mysqld stop
Shutting down MySQL............. SUCCESS!
2、mysql data文件拷贝
mv /data/mysql/mysql /data/mysql/ mysql20220309
3、还原
[root@mha ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_2022-03-09-10-42-52
[root@mha ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/mysql/full_2022-03-09-10-42-52
4、加权限启动mysql
[root@mha ~]# ll /data/mysql/
total 8
drwxr-xr-x 2 mysql mysql 51 Aug 31 2021 logs
drwxr-x--- 8 root root 4096 Mar 9 14:37 mysql
drwxr-x--- 7 mysql mysql 4096 Mar 9 14:24 mysql20220309
[root@mha ~]# chown -R mysql.mysql /data/mysql/
[root@mha ~]# ll /data/mysql/
total 8
drwxr-xr-x 2 mysql mysql 51 Aug 31 2021 logs
drwxr-x--- 8 mysql mysql 4096 Mar 9 14:37 mysql
drwxr-x--- 7 mysql mysql 4096 Mar 9 14:24 mysql20220309
5、启动mysql
[root@mha ~]# /etc/init.d/mysqld start
Starting MySQL...... SUCCESS!
6、检查数据
恢复完成
