Detailed steps for restoring Mysql database via Binlog files on Linux _mysql

First, Binlog Introduction

The server's binary log records all of the database's operations log (provided that Binlog is opened on its own server) and the execution time of these operations. In order to display these binary content, we can use the Mysqlbinlog command to view it.

Usage 1: master-Slave synchronization

Use 2: Restore the database (also on the line appears once the database file is lost, only to understand this and learn)

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

<!--[if!supportlists]-->1) mysqlbinlog option Example

Common options include the following:

--start-datetime

Reads from the binary log that the specified time stamp is equal to or later than the local computer. Values such as: = "1470733768" or = "2016-08-09 5:09:28"

Example:

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

Reads from the binary log specifying a time value less than or equal to the local computer is the same as above

--start-position

Reads the specified position event location from the binary log as the start. Take value: = "2698"

Example:

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

Reads the specified position event location from the binary log as the event ended. Take value: = "2698"

II. Environmental preparedness and backup recovery

1 after the installation of MySQL, check open binlog

Mysql> show BINARY LOGS;

ERROR 1381 (HY000): You are the not using binary logging: The instructions above indicate that no server is open Binlog

Modify/ETC/MY.CNF

Add one line to the MYSQLD option as follows:

Log-bin=mysql-bin

Default if not given value, Log-bin will be indexed by Mysqld-bin, create mysqld-bin.00001, etc.

Restart the mysqld.

2) Check the Binlog

Mysql> show binary logs;
+------------------+-----------+
| Log_name | file_size |
+------------------+-----------+
| mysql-bin.000001
| +------------------+-----------+
1 row in Set (0.00 sec)

3 Create some raw data first.

mysql> CREATE database test_db;
Query OK, 1 row Affected (0.00 sec)
mysql> use test_db;
Database changed
mysql> CREATE TABLE onetb (id int (a) not null,name varchar (a), age INT);
Query OK, 0 rows Affected (0.00 sec)
mysql> insert INTO ONETB values (1, ' User1 ', k);
mysql> INSERT INTO ONETB values (2, ' user2 ',);
INSERT into ONETB values (3, ' User3 ', 20);

Check the data:

Mysql> select * from Onetb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | |
2 | User2 | |
| 3 | User3 | |
+----+-------+------+
3 rows in Set (0.00 sec)

4 Backup Restore (full backup and restore)

Here we simulate doing a daily full backup database task.

[Root@hcloud ~]# mysqldump-uroot-p test_db >/data/mysqlbackup/test_db_0809-16:50.sql
Enter Password:

Simulated operation error, the data modification is wrong.

mysql> Update ONETB set age =;
Query OK, 3 Rows Affected (0.00 sec)
rows matched:3 changed:3 warnings:0 mysql>
select * from Onetb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | |
2 | User2 | |
| 3 | User3 | |
+----+-------+------+
3 rows in Set (0.00 sec)

Now we use the traditional way to restore restore.

Check again:

Mysql> SELECT * from test_db. ONETB;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | |
2 | User2 | |
| 3 | User3 | |
+----+-------+------+
3 rows in Set (0.00 sec)

You can see that the data has been restored back.

5) using Binlog simulation to restore

Create a few data on the base of the original table.

mysql> INSERT INTO test_db. ONETB VALUES (4, ' user4 ',), (5, ' User5 ',), (6, ' User6 ',);
Query OK, 3 Rows Affected (0.00 sec)
records:3 duplicates:0 warnings:0 mysql>
select * from test_db. ONETB;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | |
2 | User2 | |
| 3 | User3 | |
| 4 | User4 | |
| 5 | User5 | |
| 6 | User6 | |
+----+-------+------+
6 rows in Set (0.00 sec)

If we accidentally modify the data or delete the library, resulting in all the loss of data, this time if the latest backup files Test_db_0809-16:50.sql, to restore the data, then the backup will be lost after the new inserted data.

Note: If you really use the most recent backup file to do it, it must be in the last resort (such as Binlog is deleted, the whole hard drive off 、、、 think all terrible ... ）。

Simulate misoperation, change the user's name in bulk.

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 last step is not ruthless enough, here a little more ruthless, the table to delete

mysql> drop table test_db. ONETB;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect
... Connection id:3 Current
database: * * NONE * * *
Query OK, 0 rows Affected (0.00 sec)

Since we started the Binlog logging option, we used Binlog to recover the database. The following from the Binlog start, first check the Binlog file, the current MySQL service I opened binlog after the restart two times, so there are 2 binlog files (each restart, will regenerate a binlog file, there is a situation is the operation of the flush The logs command will also reconstruct one);

The Mysql-bin.index file records the list of all binary log lists that have been recorded since the Log-bin option was turned on.

Note: In the actual production environment, if you encounter the need to restore the database, do not allow users to access the database to avoid the new data inserted, as well as in the master-slave environment, the shutdown master.

Use the Mysqlbinlog command to view the Binlog file. Let's look at the latest files mysql-bin.00002

You can see from the end that there is a delete operation. But we can not fully recover, because the last there is the operation of the deletion.

Now my idea is to export the first Binlog and the second binlog file to à use the specified position location (filter out the Delete table operation and update test_db. Onetb set name= ' user10 '; this statement, Export 2 SQL statements, and finally we will combine 2 SQL into a SQL, import into the database.

We first use the Mysqlbinlog command to find the location of the UPDATE statement, and then specify that position will mysql-bin.00001 out.

[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=0
SET 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 ',), (5, ' User5 ',), (6, ' User6 ',)
/*!*/;
# at 2698
#160809 5:19:49 server ID 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP =1470734389/*!*/;
Update test_db. Onetb set name= ' User10 '
/*!*/;
# at 2795
#160809 5:30:38 server ID 1 end_log_pos 2814 Stop
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

From the above we can see that after we do insert normal data position is 2698, then use the following command to export SQL

The MYSQL-BIN.00002 SQL statement is then exported (note: The file has only one drop table operation because of the demo operation, so it does not process it, but in the real world, because there is a possibility of restarting the database operation in the middle of the day, it is necessary to detect the latest Binlog there are no business needs statements. ）

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

Note: This recovery does not take advantage of the previous full backup, because I was open binlog, and then do all the build table operations, the first Binlog file has recorded all the database operations, so do not need to use the previous full backup (plus: The actual production environment, Still need to take advantage of the full backup, because the online environment may have n multiple binlog files, so you need to use the full backup and the latest Binlog files to combine the recovery.

Before we begin to recover, we will also kill the original test_db database. After all, we have create operations in our Binlog.

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

Restore the database can also be used after landing MySQL, with the source command to import SQL statements, here is not introduced

Enter Password:

After the recovery is complete, we check that the data in the following table 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 | |
2 | User2 | |
| 3 | User3 | |
| 4 | User4 | |
| 5 | User5 | |
| 6 | User6 | |
+----+-------+------+
6 rows in Set (0.00 sec)

OK, the whole has come back.

Third, summary

1) Recovery mode

(a) Use the latest full backup plus Binlog to specify event start and end times or position recovery database

(b) Merge the SQL file Recovery database with all Binlog specify the start and end times of the event (this method ensures the integrity of the Binlog file)

(c) Use of mysqldump for full recovery. (It is not important to ensure that the data after the latest full backup is not significant and is allowed to be discarded directly.) The method is the simplest and most efficient.

2) Attached: The official recommendation of the backup principle (in order to get a good night's sleep ... well, yes)

A when MySQL is installed and running, always turn on the Log-bin option, which is located in the DataDir directory, and to ensure that the storage media is safe for that directory.

b do a full MySQL backup on a regular basis.

C Use FlUSH LOGS or Mysqladmin flush-logs on a regular basis, which closes the current binary log file and creates a new Binlog log file. (like the new binlog operation after restarting MySQL). To back up the Binlog log, you can also make incremental backups using the Binlog log.

The above is a small set of Linux to introduce you to the Binlog file to restore the MySQL database of the detailed steps, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!