Detailed steps for restoring a mysql database through the binlog file on Linux: binlogmysql

Detailed steps for restoring a mysql database through the binlog file on Linux: binlogmysql

I. binlog Introduction

The server's binary log records all the operation logs for adding, deleting, and modifying the database (provided that binlog is enabled on the server), and the execution time of these operations is also included. To display the binary content, run the mysqlbinlog command.

Purpose 1: master-slave Synchronization

Purpose 2: recover the database (this is also learned only when the database file is lost online)

Mysqlbinlog command usage: shell> mysqlbinlog [options] log_file...

<! -- [If! SupportLists] --> 1) Example of mysqlbinlog options

The following are common options:

-- Start-datetime

Read a specified timestamp or time later than the local computer from a binary log. Valid values: = "1470733768" OR = "5:09:28"

Example:

[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001 --stop-datetime

Read the specified time smaller than the timestamp or equal to the time value of the Local Computer From the binary log.

-- Start-position

Read the specified position event from the binary log as the start point. Value: = "2698"

Example:

[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001 --stop-position

Read the specified position event location from the binary log as the event deadline. Value: = "2698"

II. Environment preparation and Backup Recovery

1) Check that binlog is enabled after mysql is installed.

Mysql> show binary logs;

ERROR 1381 (HY000): You are not using binary logging: The preceding message indicates that binlog is not enabled on the server.

Modify/etc/my. cnf

Add a line in the mysqld option as follows:

Log-bin = mysql-bin

By default, if no value is given, log-bin will index mysqld-bin, create mysqld-bin.00001, etc.

Restart mysqld.

2) Check binlog

mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 106 |+------------------+-----------+1 row in set (0.00 sec)

3) create raw data first.

mysql> create database Test_DB;Query OK, 1 row affected (0.00 sec)mysql> use Test_DB;Database changedmysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10));Query OK, 0 rows affected (0.00 sec)mysql> insert into OneTb values (1,'user1',18);mysql> insert into OneTb values (2,'user2',19);insert into OneTb values (3,'user3',20);

Check the data:

mysql> select * from OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 |+----+-------+------+3 rows in set (0.00 sec)

4) Backup Recovery (full backup and restoration)

Here we simulate the complete daily backup database task.

[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sqlEnter password:

Simulate an operation error and modify the data incorrectly.

mysql> update OneTb set age = 15;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 15 || 2 | user2 | 15 || 3 | user3 | 15 |+----+-------+------+3 rows in set (0.00 sec)

Now we use the traditional method for restoration.

[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql 

Query again:

mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 |+----+-------+------+3 rows in set (0.00 sec)

We can see that all data has been restored.

5) simulate restoration using binlog

Create several data entries based on the original table.

mysql> insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 || 4 | user4 | 21 || 5 | user5 | 22 || 6 | user6 | 23 |+----+-------+------+6 rows in set (0.00 sec)

If we accidentally modify the data at this time or delete the database, resulting in all the data loss, this time if you use the latest backup file Test_DB_0809-16: 50. SQL, to restore the data, the newly inserted data after the backup will be lost.

Note: If the most recent backup file is used, it must be unavoidable (for example, the binlog is deleted, the entire hard disk crashes, and, think about it, is terrible ...).

Simulate misoperations and change user names in batches.

mysql> update Test_DB.OneTb set name='user10';Query OK, 6 rows affected (0.00 sec)Rows matched: 6 Changed: 6 Warnings: 0

No. The previous step is not harsh enough. Here, I will try again to delete all the tables.

mysql> drop table Test_DB.OneTb;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 3Current database: *** NONE ***Query OK, 0 rows affected (0.00 sec)

Since the binlog option was enabled at the beginning, we used binlog to restore the database. Starting with binlog, check the binlog file first. Currently, my mysql service has restarted binlog twice since binlog is enabled. Therefore, there are two binlog files (every restart, A new binlog file will be generated, and another case is that the flush logs command will be run again );

The mysql-bin.index file records the list of all binary logs recorded after the log-bin option is enabled.

Note: In the actual production environment, if you need to recover the database, do not allow users to access the database to avoid new data insertion, and in the master-slave environment, disable Master/Slave.

Use the mysqlbinlog command to view the binlog file. Let's take a look at the latest file mysql-bin.00002

Finally, we can see that there are delete operations. However, we cannot completely recover it because there are still delete operations.

Now, my idea is, export the first binlog file and the second binlog file to use the specified position (filter out the delete table operation and update Test_DB.OneTb set name = 'user10 ), export two SQL statements. Finally, combine the two SQL statements into one and import them to the database.

We first use the mysqlbinlog command to find the position of the update statement, and then specify the position to export the mysql-bin.00001.

[root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001….#160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0SET TIMESTAMP=1470733768/*!*/;SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;SET @@session.sql_mode=0/*!*/;/*!\C latin1 *//*!*/;SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23)/*!*/;# at 2698#160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0SET TIMESTAMP=1470734389/*!*/;update Test_DB.OneTb set name='user10'/*!*/;# at 2795#160809 5:30:38 server id 1 end_log_pos 2814 StopDELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

We can see from the above that the position after inserting normal data is 2698, then use the following command to export the SQL

[root@hcloud ~]# mysqlbinlog --stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql 

Then export the mysql-bin.00002 SQL statement (Note: because of the demo operation, the file only has one drop table operation, so do not handle, but in the actual environment, because there may be a restart of the database operation in the middle, at that time, you need to check whether the latest binlog has the statement required by the Business .)

The SQL statement has been exported. We can use this statement to directly restore all normal data.

Note: The previous full backup was not used for this recovery because I started binlog before creating all the databases and tables, all database operations have been recorded in the first binlog file, so you do not need to use the complete backup (In addition, you still need to use the full backup in the actual production environment, because there may be N binlog files in the online environment, you need to use the full backup and the latest binlog files for recovery)

Before starting the recovery, we should also kill the original Test_DB database. After all, we have created operations in binlog.

mysql> DROP DATABASE Test_DB;Query OK, 0 rows affected (0.03 sec)

You can also use the source command to import SQL statements after logging on to mysql to restore the database.

[root@hcloud ~]# mysql -uroot -p < Backup_1.sql 

Enter password:

After the restoration is complete, check whether the data in the table below is complete.

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || Test_DB || mysql |+--------------------+3 rows in set (0.00 sec)mysql> select * from Test_DB.OneTb;+----+-------+------+| id | name | age |+----+-------+------+| 1 | user1 | 18 || 2 | user2 | 19 || 3 | user3 | 20 || 4 | user4 | 21 || 5 | user5 | 22 || 6 | user6 | 23 |+----+-------+------+6 rows in set (0.00 sec)

OK, all of them are restored.

Iii. Summary

1) Recovery Method

A) use the latest full backup and binlog to specify the event start time and end time or position to restore the database.

B) Use all binlogs to specify the event start position and End Time to merge SQL files to restore the database (this method must ensure the integrity of binlog files)

C) use mysqldump for full recovery. (The data after the latest full backup is not important and can be recovered directly if it is lost. This method is the simplest and most efficient)

2) Appendix: officially recommended backup principles (in order to get a good night's sleep .... Well, yes)

A) when mysql is installed and running, the log-bin option is always enabled. The log file is located in the datadir directory, and the storage media of the directory must be secure.

B) regularly complete mysql backup.

C) regularly use FlUSH LOGS or mysqladmin flush-logs. This operation closes the current binary log file and creates a new binlog log file. (Same as the binlog operation created after mysql is restarted ). To back up binlog logs, you can also use binlog for Incremental backup.

The preceding section describes how to restore the mysql database through the binlog file on Linux. I hope this will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!