Shulou(Shulou.com)06/01 Report--

Introduction to the experiment

Scenarios where incremental recovery is generally applicable:

1. Artificial sql statement destroys the database.

2. A system failure before the next full backup results in the loss of database data

3. In the master-slave architecture, the master database data failed.

Recovery steps for data changed after losing a full backup

1. First make a full backup to ensure that a fully backed up sql file is generated.

Mysql > select * from yx # Database +-+-+ | name | score | +-+-+ | zhangsan | 100.00 | lisi | 90.00 | wangwu | 80.00 | | zhaoliu | zhaoliu | +-+-+ 4 rows in set (0.00 sec) [root@promote data] # mysqldump-u Root-p test > / opt/test.sql # full backup of the database

2. Use flush-logs to generate a new binary log file to save the database operation statements.

[root@promote data] # mysqladmin-u root-p flush-logs # generates binaries

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. Insert a record in the database, and then perform the flush-logs operation to generate a new binary incremental backup file.

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 # generates binaries

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. Use delete to delete the data you just inserted. Data is lost after simulating a full backup.

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. Use binaries for recovery operation

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

6. Check the contents of the database and find the deleted data. Indicates that the data was recovered successfully.

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)

Recovery steps for losing all data after a full backup

1. Delete the table yx using drop, and the simulated data is completely lost.

Mysql > drop table yx

Query OK, 0 rows affected (0.01 sec)

Mysql > show tables

Empty set (0.00 sec)

2. First use the mysql command to perform a full backup and restore operation.

[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. Use binaries for incremental backup operations.

[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)

Recovery based on time and location

Use binary logs to achieve local point-in-time and location recovery. If you need to insert two pieces of data into the database, but due to misoperation, delete a piece of data between the two insert statements, and this data should not be deleted. Recovery needs to be based on point-in-time and location.

-start-datetime=datetime

Start reading from the first event in the binary log whose date time is equal to or later than the datetime parameter.

-stop-datetime=datetime

Stop reading from the first event in the binary log whose date time is equal to or later than the datetime parameter.

-start-position=N

Start reading from the event when the first position in the binary log is equal to the N parameter.

-stop-position=N

Stop reading from the event when the first position in the binary log is equal to and greater than the N parameter.

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. Recovery based on point in time. 18-07-03 21:56:04 is the error statement node, 18-07-03 21:56:11 the second correct statement node

[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

Com _ MIT _ blank /

# 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.com

# 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

Com _ MIT _ blank /

# 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.com

# 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 no-defaults 56 stop-datetime='18 04' mysql-bin.000003 | mysql- u root-p # end node

Enter password:

[root@promote data] # mysqlbinlog-- no-defaults-- start-datetime='18-07-03 21 no-defaults 56 start-datetime='18 11' mysql-bin.000003 | mysql- u root-p # restart node

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. Location-based recovery, where 581 is the node of the error statement and 866 is the node of the second correct statement

[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.01sec)

[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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.