mysql gtid 增量备份_Mysql增量备份与恢复

Mysql增量备份与恢复

实验介绍

增量恢复一般适用的场景：

1、人为的sql语句破坏了数据库

2、在进行下一次完全备份之前发生系统故障导致数据库数据丢失

3、在主从架构中，主库数据发生了故障

丢失完全备份之后更改的数据的恢复步骤

1、首先做一个完全备份，确保生成完全备份的sql文件。

mysql> select * from yx; #完全备份前数据库

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

4 rows in set (0.00 sec)

[root@promote data]# mysqldump -u root -p test > /opt/test.sql #对数据库完全备份

2、使用flush-logs生成新的二进制日志文件，用以保存之后的数据库操作语句。

[root@promote data]# mysqladmin -u root -p flush-logs #生成二进制文件

Enter password:

[root@promote data]# ls

auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.index sys

ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 performance_schema test

3、在数据库中插入一条记录，再执行flush-logs操作，生成新的二进制增量备份文件。

mysql> insert into yx(name,score) values('tom',87);

Query OK, 1 row affected (0.00 sec)

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| tom | 87.00 |

+----------+--------+

5 rows in set (0.00 sec)

[root@promote data]# mysqladmin -u root -p flush-logs #生成二进制文件

Enter password:

[root@promote data]# ls

auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 performance_schema test

ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.index sys

4、用delete删除刚才插入的数据。模拟完全备份后数据丢失。

mysql> delete from yx where name='tom';

Query OK, 1 row affected (0.00 sec)

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

4 rows in set (0.00 sec)

5、使用二进制文件进行恢复操作

[root@promote data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p

6、查看数据库内容，删除的数据有了。说明数据恢复成功。

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| tom | 87.00 |

+----------+--------+

5 rows in set (0.00 sec)

完全备份之后丢失所有数据的恢复步骤

1、使用drop删除表yx，模拟数据完全丢失

mysql> drop table yx;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

Empty set (0.00 sec)

2、先使用mysql命令进行完全备份恢复操作。

[root@promote data]# mysql -u root -p test < /opt/test.sql

mysql> use test;

Database changed

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

4 rows in set (0.00 sec)

3、使用二进制文件进行增量备份操作。

[root@promote data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| tom | 87.00 |

+----------+--------+

5 rows in set (0.00 sec)

基于时间点与位置的恢复

利用二进制日志实现局域时间点与位置的恢复，假如需要往数据库中插入两条数据，但是由于误操作，两条插入语句中间删除一条数据，而这条数据不应该删除，这时候，需要基于时间点与位置进行恢复。

–start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读。

–stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。

–start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

–stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

5 rows in set (0.00 sec)

mysql> insert into yx values('test01',87);

Query OK, 1 row affected (0.00 sec)

mysql> delete from yx where name='zhangsan';

Query OK, 1 row affected (0.00 sec)

mysql> insert into yx values('test02',99);

Query OK, 1 row affected (0.17 sec)

mysql> select * from yx;

+---------+-------+

| name | score |

+---------+-------+

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| test01 | 87.00 |

| test02 | 99.00 |

+---------+-------+

6 rows in set (0.00 sec)

1、基于时间点的恢复。18-07-03 21:56:04是错误语句节点，18-07-03 21:56:11第二句正确语句节点

[root@promote data]# mysqlbinlog --no-defaults --base64-output=decode-rows mysql-bin.000003

# at 298

#180703 21:55:35 server id 1 end_log_pos 406 CRC32 0x257c67ab Query thread_id=46 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1530626135/*!*/;

insert into yx values('test01',87)

/*!*/;

# at 406

#180703 21:55:35 server id 1 end_log_pos 437 CRC32 0xdd7913a3 Xid = 392

COMMIT/*!*/;

# at 437

#180703 21:56:04 server id 1 end_log_pos 502 CRC32 0x0d09bd0b Anonymous_GTID last_committed=1 sequence_number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 502

#180703 21:56:04 server id 1 end_log_pos 581 CRC32 0xe6040c79 Query thread_id=46 exec_time=0 error_code=0

SET TIMESTAMP=1530626164/*!*/;

BEGIN

/*!*/;

# at 581

#180703 21:56:04 server id 1 end_log_pos 691 CRC32 0x2d99f699 Query thread_id=46 exec_time=0 error_code=0

SET TIMESTAMP=1530626164/*!*/;

delete from yx where name='zhangsan'

/*!*/;

# at 691

#180703 21:56:04 server id 1 end_log_pos 722 CRC32 0x4a742173 Xid = 393

COMMIT/*!*/;

# at 722

#180703 21:56:11 server id 1 end_log_pos 787 CRC32 0x6d0b47d8 Anonymous_GTID last_committed=2 sequence_number=3

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 787

#180703 21:56:11 server id 1 end_log_pos 866 CRC32 0x97e2deb7 Query thread_id=46 exec_time=0 error_code=0

SET TIMESTAMP=1530626171/*!*/;

BEGIN

/*!*/;

# at 866

#180703 21:56:11 server id 1 end_log_pos 974 CRC32 0x9e24e8af Query thread_id=46 exec_time=0 error_code=0

SET TIMESTAMP=1530626171/*!*/;

insert into yx values('test02',99)

[root@promote data]# mysql -u root -p test < /opt/test.sql #先进行完全恢复

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

4 rows in set (0.00 sec)

[root@promote data]# mysqlbinlog --no-defaults --stop-datetime='18-07-03 21:56:04' mysql-bin.000003 | mysql -u root -p #结束节点

Enter password:

[root@promote data]# mysqlbinlog --no-defaults --start-datetime='18-07-03 21:56:11' mysql-bin.000003 | mysql -u root -p #重新开始节点

Enter password:

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| test01 | 87.00 |

| test02 | 99.00 |

+----------+--------+

6 rows in set (0.00 sec)

2、基于位置恢复，其中581是错误语句的节点，866是第二句正确语句的节点

[root@promote data]# mysql -u root -p test < /opt/test.sql

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

+----------+--------+

4 rows in set (0.01 sec)

[root@promote data]# mysqlbinlog --no-defaults --stop-position='581' mysql-bin.000003 | mysql -u root -p

Enter password:

[root@promote data]# mysqlbinlog --no-defaults --start-position='866' mysql-bin.000003 | mysql -u root -p

Enter password:

mysql> select * from yx;

+----------+--------+

| name | score |

+----------+--------+

| zhangsan | 100.00 |

| lisi | 90.00 |

| wangwu | 80.00 |

| zhaoliu | 99.00 |

| test01 | 87.00 |

| test02 | 99.00 |

+----------+--------+

6 rows in set (0.00 sec)