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

This article mainly introduces Mysql full backup, binlog how to restore data, I hope you can supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

One day during the working time, a second-class goods made a mistake in production when the test environment, directly drop a database, need emergency recovery! Backup data files and incremental binlog files can be used for data recovery.

The specific ideas are summarized as follows:

1. The recovery condition is that MySQL should enable the binlog log feature, and all data should be fully prepared and incremented.

2. It is recommended to stop updating the database during recovery, that is, it is prohibited to update the database. (this is important.)

3. Restore the full amount first, and then restore the incremental logs after the full time point into SQL files in order.

4. Then delete the problematic SQL statements in the file (also through time and location point), and then restore to the database.

Specific examples demonstrate:

1. First of all, make sure that MySQL enables binlog logging, and check the following results

Mysql > show variables like'% log_bin%' +-- +-- + | Variable_name | Value | +- -+-+ | log_bin | ON | | log_bin_basename | / mysql_data/mysql-bin | | log_bin_index | / mysql_data/mysql-bin.index | | log_bin_ Trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +-- -+ 6 rows in set (0.01 sec)

2. Check the data information in the current test table for comparison.

Mysql > select * from Student +-+ | Sno | Sname | Ssex | Sage | Sdept | +-+ | 200215121 | Li Yong | male | 20 | CS | | 200215122 | Liu Chen | female | 19 | CS | 200215123 | Wang Min | female | 18 | MA | 200215125 | Zhang Li | female | 19 | IS | | 200215126 | Tiger | male | 25 | CS | | 200215127 | Master Wei | male | 35 | IS | 200215128 | Lao Xie | male | 33 | MA | | 200215129 | Xiao Jia | male | 30 | | | CS | | 200215130 | Huimin | male | 23 | CS | | 200215131 | Chen Xing | male | 33 | MA | 200215132 | A Fan | male | 36 | IS | | 200215133 | Guoliang | male | 40 | IS | | 200215134 | Lao Song | male | 40 | IS | | 200215135 | male | 35 | IS | | 200215136 | Boss Wang | female | 27 | | IS | +-+ 15 rows in set (0.00 sec)

3. Make a full backup now

Mysqldump-u root-p-B-F-R-x student | gzip > / mysql_backup/student_$ (date +% Y%m%d_%H%M%S) .sql.gz parameter description:-B: specify database-F: refresh log-R: backup stored procedure, etc.-x: lock table

4. Insert new data again

INSERT INTO Student VALUES ('200215137', 'Cheng Cheng', 'female', 30 'recorder'); INSERT INTO Student VALUES ('200215138' Qiqi', 'male', 29'); INSERT INTO Student VALUES ('200215139'' Longlong', 'male', 27'))

5. Check whether the insertion is successful. You can see that the insertion is successful as shown below.

Mysql > select * from Student +-+ | Sno | Sname | Ssex | Sage | Sdept | +-+ | 200215121 | Li Yong | male | 20 | CS | | 200215122 | Liu Chen | female | 19 | CS | 200215123 | Wang Min | female | 18 | MA | 200215125 | Zhang Li | female | 19 | IS | | 200215126 | Tiger | male | 25 | CS | | 200215127 | Master Wei | male | 35 | IS | 200215128 | Lao Xie | male | 33 | MA | | 200215129 | Xiao Jia | male | 30 | | | CS | | 200215130 | Huimin | male | 23 | CS | | 200215131 | Chen Xing | male | 33 | MA | 200215132 | A Fan | male | 36 | IS | | 200215133 | Guoliang | male | 40 | IS | | 200215134 | Lao Song | male | 40 | IS | | 200215135 | male | 35 | IS | | 200215136 | Boss Wang | female | 27 | | IS | | 200215137 | Program | female | 30 | IS | | 200215138 | Gigi | male | 29 | MA | 200215139 | Longlong | male | 27 | IS | +-+ 18 rows in set (sec)

6. Misoperation at this time, student database deleted

Mysql > drop database student;Query OK, 3 rows affected (0.11 sec) mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec)

7. Back up the files after viewing the full

[root@ocbsdb01 mysql_backup] # cd / mysql_ backup [root @ ocbsdb01 mysql_backup] # gzip-d student_20170829_090319.sql.gz [root@ocbsdb01 mysql_backup] # lsstudent_20170829_090319.sql [root@ocbsdb01 mysql_backup] # vim student_20170829_090319.sql

8. Check and move the binlog file, export it as a SQL file, remove the drop statements from it, and view the MySQL data storage directory

As you can see below, export the binlog file to the SQL file under / mysql_data, and vim edit it to delete the drop statement in it.

/ home/mysql/mysql5/bin/mysqlbinlog-- no-defaults / tmp/mysql-bin.000004 > / tmp/04.sql

Note: the binlog file must be removed before restoring full data, otherwise statements will continue to be written to binlog during the recovery process, resulting in confusion in the incremental recovery part of the data.

An error may be reported when converting sql. The details are as follows:

[root@ocbsdb01 tmp] # / home/mysql/mysql5/bin/mysqlbinlog / tmp/mysql-bin.000004 > / tmp/04.sql

Mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

The reason is that the tool mysqlbinlog does not recognize the default-character-set=utf8 directive in the configuration in binlog.

There are two ways to solve this problem

In the MySQL configuration / etc/my.cnf, change default-character-set=utf8 to character-set-server = utf8, but this requires restarting the MySQL service, which can be costly if your MySQL service is busy.

Open it with the command mysqlbinlog-- no-defaults mysql-bin.000004

9. Start restoring complete data

[root@ocbsdb01 tmp] # mysql-u root-p

Show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | student | | sys | +-+ 5 rows in set (0.00 sec)

11. Start recovering incremental data

Use the 04.sql file to recover the new data between the full time and the deletion of the database, and edit 04bin.sql # to delete the drop statement in it

[root@ocbsdb01 tmp] # vim 04.sql

Delete the following contents of the drop operation: set @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; / *! 50003 SET completion operations

Otherwise, the following error will be reported:

ERROR 1790 (HY000) at line 96: @ @ SESSION.GTID_NEXT cannot be changed by a client that owns a GTID.

The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

The details of my binlog file are as follows:

/ *! 50530 SET @ @ SESSION.PSEUDOSLAVE models 1 / *; # at 4 "170829 9:03:22 server id 201609 end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829 9 Freund 3 Warning: this binlog is either in use or was not closed properly.BINLOG 'Wr2kWQcodes JEwAdwAAAHsAAABAQANS43LjE4LxvZwZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 123 "170829 9:03:22 server id 201609 end_log_pos 154CRC32 0xf909764e Previous-GTIDs# [empty] # at 154" 170829 9:04:42 server id 201609 end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSURA "at 219" 170829 9:04:42 server id 201609 end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0SET TimESTAMP "15039682" SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlautomotive modeboxes 1344274432Universe set @ @ session.auto_increment_increment=1, @ @ session.autoincrement increments offsetbacks 1Cancurpact races!\ C utf8 * / / *! /; SET @ @ session.session. SET @ @ session.lctimekeeper namespace namespace at at 359 "170829 9:04:42 server id 201609 end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225flags: STMT_END_FBINLOG 'qr2kWROJEwQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 421 "170829 9:04:42 server id 201609 end_log_pos CRC32 0xfcd87186 Xid = 168COMMITGOMITGUA" server id "at 452" 170829 9:04:42 server id 201609 end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.GetWord ("at") 517 "170829 9:04:42 server id 201609 end_log_pos 592TimESTAMP" 15039682 # at 592 end_log_pos 170829 9:04:42 server id 201609 end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 22 seven at 657 170829 9:04:42 server id 201609 end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225flags: STMT_END_FBINLOG 'qr2kWROJEwMAQAAAAJECAAAOEAAAAAAEAB3dWRlbnQAB1dWRlbQAB1dWRlbQAB1N0dWRlbQABf7WAAAAAAM8CAAAAAOEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 750 "170829 9:04:42 server id 201609 end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to SESSION.GTID_NEXT=" at 815 "170829 9:04:42 server id 201609 end_log_pos 890 TIMESTAMPPOS 15039682 # at 890 170829 9:04:42 server id 201609 end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 22 percent at 955 170829 9:04:42 server id 201609 end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225flags: STMT_END_FBINLOG 'qr2kROJEwMAQAAALsDAAAAOEAAAAAEAB3dWRlbnQAB1N0dWRlbQAB1dWRlbQAB1dWRlbQAB1dWRlbnQABf7WR6JEwMAPgAAPkDAAAAAOEAAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 1048 million 170829 9:06:30 server id 201609 end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to set @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; 9:06:30 server id 201609 end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0SET timestamp 1503968790 / *! 50003 SET completion SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

After adjustment, start to recover incremental data.

[root@ocbsdb01 tmp] # mysql-u root-p

Select * from Student +-+ | Sno | Sname | Ssex | Sage | Sdept | +-+ | 200215121 | Li Yong | male | 20 | CS | | 200215122 | Liu Chen | female | 19 | CS | 200215123 | Wang Min | female | 18 | MA | 200215125 | Zhang Li | female | 19 | IS | | 200215126 | Tiger | male | 25 | CS | | 200215127 | Master Wei | male | 35 | IS | 200215128 | Lao Xie | male | 33 | MA | | 200215129 | Xiao Jia | male | 30 | | | CS | | 200215130 | Huimin | male | 23 | CS | | 200215131 | Chen Xing | male | 33 | MA | 200215132 | A Fan | male | 36 | IS | | 200215133 | Guoliang | male | 40 | IS | | 200215134 | Lao Song | male | 40 | IS | | 200215135 | male | 35 | IS | | 200215136 | Boss Wang | female | 27 | | IS | | 200215137 | Program | female | 30 | IS | | 200215138 | Gigi | male | 29 | MA | 200215139 | Longlong | male | 27 | IS | +-+ 18 rows in set (sec)

Read the above about Mysql full backup, binlog how to restore data, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.