mysqldump全库备份和binlog增量备份的配合

mysqldump全库备份

[root@haha ~]# mkdir /opt/mysql_backup
[root@haha ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs -B haha > /opt/mysql_backup/haha_db_$(date +%Y-%m-%d_%H:%M:%S).sql

增量备份

通过mysql的二进制日志间接实现增量备份

mysql> insert into test values(101,"zz");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(102,"ee");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(103,"xx");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

[root@haha ~]# cp mysql_bin.00000{3..4} /opt/mysql_backup/

数据恢复

mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

[root@haha ~]# mysql -uroot -p123456 haha < haha_db_2021-03-09_17\:15\:04.sql

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang  |
|    2 | heihei |
+------+--------+
2 rows in set (0.00 sec)

[root@haha ~]# mysqlbinlog mysql_bin.000003 | mysql -uroot -p123456

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang  |
|    2 | heihei |
|  101 | zz     |
|  102 | ee     |
+------+--------+
4 rows in set (0.00 sec)

[root@haha ~]# mysqlbinlog mysql_bin.000004 | mysql -uroot -p123456

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang  |
|    2 | heihei |
|  101 | zz     |
|  102 | ee     |
|  103 | xx     |
+------+--------+
5 rows in set (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

二进制日志实现，基于时间点或者位置的恢复

[root@haha ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs -B hehe > hehe_db_$(date +%Y-%m-%d_%H:%M:%S).sql

mysql> insert into test values(100,"zz");
Query OK, 1 row affected (0.01 sec)

mysql> delete from test where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(101,"cc");
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|  100 | zz   |
|  101 | cc   |
+------+------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
##切割日志，使得--start-position可以恢复数据

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
##临时关闭日志

[root@haha ~]# mysql -uroot -p123456 hehe < hehe_db_2021-03-09_19\:26\:38.sql 

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
+------+------+
1 row in set (0.00 sec)

[root@haha ~]# mysqlbinlog --stop-position=501 /data/mysql/log/mysql_bin.000002 | mysql -uroot -p123456

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|  100 | zz   |
+------+------+
2 rows in set (0.00 sec)

[root@haha ~]# mysqlbinlog --start-position=681 /data/mysql/log/mysql_bin.000002 | mysql -uroot -p123456

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|  100 | zz   |
|  101 | cc   |
+------+------+
3 rows in set (0.00 sec)

（1）基于时间恢复
使用mysqlbinlog加上–stop-datetime选项，表示从二进制日志中读取指定时间之前的日志事件，后面误操作的语句不执行，–start-datetime选项表示从二进制日志中读取指定时间之后的日志事件。
需要注意的是，二进制文件中保存的日期格式需要调整为用”-”和”:”分隔。
使用基于时间点的恢复，可能会出现在一个时间点里同时存在正确的操作和存在错误操作。所以基于位置是一种更为精确的恢复方式

（2）基于位置恢复
–start-position 从二进制日志中读取指定position 事件位置作为开始。commit前的at

–stop-position 从二进制日志中读取指定position 事件位置作为事件截至。begin前的at

恢复数据的日志文件不能是正在使用的，需要切割