准备测试数据

MariaDB [(none)]> use cndba
Database changed
MariaDB [cndba]> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

MariaDB [cndba]> insert into t1 values(10);
Query OK, 1 row affected (0.03 sec)

MariaDB [cndba]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [cndba]> select * from t1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

MariaDB [cndba]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000033 |       745 |
| mysql-bin.000034 |       705 |
| mysql-bin.000035 |     12706 |
+------------------+-----------+

全备数据库cndba

[root@www.cndba.cn ~]# mysqldump -u root -proot cndba > /backup/cndba.sql

```## 刷新日志flush logs 
```sql
MariaDB [cndba]> flush logs;
生成一个新的二进制日志，记录备份后的DML操作，方便后续恢复
Query OK, 0 rows affected (0.02 sec)

MariaDB [cndba]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000033 |       745 |
| mysql-bin.000034 |       705 |
| mysql-bin.000035 |     12753 |
| mysql-bin.000036 |       401 |
+------------------+-----------+

模拟在全备后，进行新的DML操作

MariaDB [cndba]>  insert into t1(id) values(20);
Query OK, 1 row affected (0.63 sec)

用全备恢复数据库

恢复前，先flush logs，将利用全备恢复的信息记录到新的日志中。假如不进行该步骤，利用全备恢复的信息（删除t1表，并重建，插入id为10）会记录到当前的二进制日志（mysql-bin.000036）中，在后续恢复二进制日志mysql-bin.000036时，会执行全备恢复的步骤，相当于没有恢复该二进制日志。
MariaDB [cndba]>  flush logs;
Query OK, 0 rows affected (0.03 sec)

MariaDB [cndba]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000033 |       745 |
| mysql-bin.000034 |       705 |
| mysql-bin.000035 |     12753 |
| mysql-bin.000036 |       618 |
| mysql-bin.000037 |       401 |
+------------------+-----------+
恢复数据库
[root@www.cndba.cn ~]#  mysql -u root -proot cndba < /backup/cndba.sql

MariaDB [cndba]> select * from t1;
+------+
| id   |
+------+
|   10 |
+------+

利用二进制日志恢复

[root@www.cndba.cn ~]#  mysqlbinlog /data/mysql/mysql-bin.000036 | mysql -u root -proot

数据被恢复

MariaDB [cndba]> select * from t1;
+------+
| id   |
+------+
|   10 |
|   20 |
+------+

版权声明：本文为博主原创文章，未经博主允许不得转载。