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

The architecture of MYSQL is generally one-master, multi-slave or two-master high availability mode, and physical failure does not need incremental recovery.

What circumstances require incremental recovery?

It is generally caused by human misoperation that requires incremental recovery.

Conditions that must be met for incremental recovery

1) enable MYSQL log-bin log function

2) there is a full and complete backup of all incremental binlog files from the moment after the full time to the time of the problem.

The idea of incremental recovery:

First restore the full amount, and then restore the incremental logs after the full time point into SQL files in order, and then delete the problematic SQL statements in the file (or through time and location points), and then restore to the database.

The following simulates the process of incremental recovery after the database is deleted by misoperation

Create a test library WWW and test test table

Create database www character set utf8 collate utf8_general_ci;use www;CREATE TABLE `test` (`id` int (4) NOT NULL, `name` varchar (16) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data

Insert test values (1Jing Xixiaoming`), (2JEI Xixiaozhang'), (3GiveryWW'), (4JEH Koala'); select * from test;+----+-+ | id | name | +-+ | 1 | xiaoming | | 2 | xiaozhang | | 3 | www | 4 | koala | +-+-- + 4 rows in set (0.00 sec)

This is the data in the complete time table.

Modify the system time to refresh the BINLOG at 0: 00 and then complete the www library.

Mysqldump-uroot-p123456-S / data/3306/mysql.sock-B-F-x-R-- master-data=2 www | gzip > / server/backup/www_$ (date +% F) .sql.gz

Check to see if complete documents are produced

[root@db03 backup] # lltotal 8drwxr-xr-x 2 root root 4096 Jul 13 02:18 tp-rw-r--r-- 1 root root 878 Jul 15 00:01 www_2016-07-15.sql.gz

Adjust the system time to 10:00 and insert a few pieces of data.

Mysql > insert test values (5 Duplicates Duplicates: 0 Warnings: 0), (6 Records Duplicates: 0 Warnings: 0)

At this point, the test data is

Mysql > select * from test;+----+-+ | id | name | 1 | xiaoming | | 2 | xiaozhang | | 3 | www | 4 | koala | | 5 | | | 6 | changsha | 7 | bbs | +-- +-- + 7 rows in set (0.00 sec)

Start to simulate human error to delete the www library

Mysql > show databases +-+ | Database | +-+ | information_schema | | blog | | mysql | | oldboy | | oldboy_gbk | | oldgril | | performance_schema | | xiaowan | +- -+ 8 rows in set (0.00 sec)

Perform a restore operation

1. First check the BINLOG location and back up all the binlog files after completion.

If you do not back up the full restore first, you will write to the LOG BIN file.

-rw-r--r-- 1 root root 878 Jul 15 00:01 www_2016-07-15.sql.gz [root@db03 backup] # gzip-d www_2016-07-15.sql.gz [root@db03 backup] # grep CHANGE www_2016-07-15.sql-CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000137', MASTER_LOG_POS=107 -rw-r- 1 root root 150 Jul 15 00:01 mysql-bin.000135-rw-r- 1 root root 150 Jul 15 10:10 mysql-bin.000136-rw-r- 1 root root 397 Jul 15 10:09 mysql-bin.000137

2. Delete the statement that parses the bin-log and finds the misoperation.

Mysqlbinlog-d www mysql-bin.000135 mysql-bin.000136 mysql-bin.000137 > > wwwbin.sql

[root@db03 backup] # vim wwwbin.sql

# at 318

# 160715 10:06:12 server id 1 end_log_pos 397 Query thread_id=17 exec_time=0 error_code=0

SET timestamp 1468548372

Drop database www # if you don't delete and restore, the misoperation will be restored to the database together, then you will go back to the origin in vain!

/ *! * /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

3. Restore complete data

[root@db03 backup] # mysql-uroot-p123456-S / data/3306/mysql.sock wwwbin.sql

2. If there is a problem with an important library, it is best to stop the library or prevent the library from being written by the application service, and then restore it (iptables processing). If parsed through host, comments parse the file record, user center (interface is down).

3. Multiple binlog files should be restored sequentially.

Mysqlbinlog-d www 01 02 03 04 > bin.sql or for loop reading order is important.

4. If it is not drop but updata that destroys the data, it will be complicated to solve. It is generally necessary to stop or disable the library from being written by the application service, and then restore it.

If you delete a table in a library, you need to separate the SQL statement of the table from bin-log:

a. Export the table structure of the original specified oldboy library, restore the test library, then restore the oldboy_bin.sql statements to the test library, and then export the required single table with mysqkdump to restore to the fully restored formal library.

B.www_bin.sql is divided at least by database, then grep "table name" can filter out all the records of old_bin.sql for that table. Restore to an official library that has been fully restored

After mysqlbinlog-d oldboy > bin.sql

Table bin.sql > a.sql to be restored by grep

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.