Detailed steps to restore mysql database through binlog file on Linux

1. Binlog introduction

　The server’s binary log records all additions, deletions, and modifications of the database (provided that binlog is enabled on your own server), and also includes the execution time of these operations. In order to display these binary contents, we can use the mysqlbinlog command to view it.

Usage 1: Master-slave synchronization

Usage 2: Restoring the database (I only learned about this after a database file was lost online)

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

1) mysqlbinlog option example

Common options include the following:

--start-datetime

Read from the binary log a specified time equal to the timestamp 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

Copy after login

Read the specified time from the binary log that is less than the timestamp or equal to the local computer. The value is the same as above

--start-position

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

Example:

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

Copy after login

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

2. Environment preparation and backup and recovery

　1) After installing mysql, check to enable binlog

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging: The above prompt indicates that there is no server to enable binlog

　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 use mysqld-bin as the index and create mysqld-bin.00001, etc.

　Just restart mysqld.

　2) Check the binlog

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

Copy after login

　3) First create some raw data.

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(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);

Copy after login

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)

Copy after login

　4) Backup and restore (full backup and restore)

Here we simulate the daily complete backup database task.

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

Copy after login

　An operation error occurred during simulation and the data was modified incorrectly.

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

Copy after login

　Now we use traditional methods to restore and restore.

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

Copy after login

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

Copy after login

　You can see that the data has been restored.

　5) Use binlog to simulate restoration

　Create several pieces of data 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: 0
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)

Copy after login

　If we accidentally modify the data or delete the library at this time, causing all data to be lost, if we use the latest backup file Test_DB_0809-16:50.sql at this time to restore the data, it will be lost. Newly inserted data after backup.

Note: If you really use the most recent backup file, it must be a last resort (for example, the binlog is deleted, the entire hard disk hangs... It's scary to think about it...).

　Simulate misoperation 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

Copy after login

No, the previous step was not ruthless enough. Let’s be more ruthless here and delete all the tables

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)

Copy after login

Since we turned on the binlog option at the beginning, we used binlog to restore the database. Let's start with the binlog. First check the binlog file. Currently, my mysql service has been restarted twice since binlog was turned on, so there are 2 binlog files (each time it is restarted, a binlog file will be regenerated. Another situation is to run The FLUSH LOGS command will also rebuild one);

　What is recorded in the mysql-bin.index file is: a list of all binary logs recorded since the log-bin option is turned on.

Note: In an actual production environment, if you encounter a situation where you need to restore the database, do not allow users to access the database to avoid new data being inserted, and in a master-slave environment, shut down the master-slave.

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

　You can see from the end that there is a deletion operation. But we can't completely restore it because there is still a deletion operation at the end.

　　现在我的思路就是，先将第一个binlog 和第二个binlog 文件导出来à利用指定的position位置的方式（过滤掉删除表操作和update Test_DB.OneTb set name='user10';这条语句），导出2个sql 语句，最后我们将2个sql 合成一个sql，导入到数据库中即可。

　　我们先用mysqlbinlog命令找到update 那条语句的位置，然后指定position 将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=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',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=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*/;

Copy after login

　　从上面可以看到我们在做插入正常数据后的position 是2698，那么使用下面的命令导出sql

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

Copy after login

　　然后导出mysql-bin.00002的sql 语句（注：由于演示操作，该文件只有一个drop 表操作，所以不做处理，但是在实际环境中，由于中途可能会有重启数据库操作，那时就需要检测最新的binlog有没有业务需要的语句。）

　　sql 语句已经导出来了。我们可以利用该语句直接恢复所有正常的数据。

　　注：本次恢复没有利用到之前的完整备份，因为我是开启binlog后，然后才做的所有建库建表操作，第一个binlog文件里已经记录了所有的数据库操作，所以不需要使用之前的完整备份（另外：实际的生产环境，还是需要利用到完整备份的，因为线上环境可能会有N多个binlog文件，所以需要利用到完整备份和最新的binlog文件来结合恢复）

　　开始恢复前，我们将原有的Test_DB数据库也给干掉吧。毕竟我们的binlog中有创建操作

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

Copy after login

　　恢复数据库时还可以利用在登陆mysql 后，用source 命令导入sql语句，这里暂不介绍

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

Copy after login

Enter password:

　　恢复完成后，我们检查下表的数据是否完整

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)

Copy after login

　　Ok完整的都恢复过来了。

三、总结

　　1) 恢复方式

　　　　a) 利用最新一次的完整备份加binlog 指定事件起始时间和终止时间或者position恢复数据库

　　　　b) 利用所有binlog指定事件起始位置和终止时间来合并sql文件恢复数据库（此方法要确保binlog文件的完整）

　　　　c) 利用mysqldump 使用完整恢复。（在确保最新一次的完整备份后的数据不重要，允许丢掉的情况下，直接恢复。该方法最简单、效率最高）

　　2) 附:官方建议的备份原则（为了能睡个好觉….嗯，是的）

　　　　a) 在mysql安装好并运行时，就始终开启 log-bin选项，该日志文件位于datadir目录下，也要确保该目录所在存储介质是安全的。

　　　　b) 定期做完整的mysql 备份。

　　　　c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs ，该操作会关闭当前的二进制日志文件，并新建一个binlog日志文件。(和重启mysql后新建的binlog操作一样)。以备份binlog日志，利用binlog日志也可以做增量备份。

以上所述是小编给大家介绍的Linux上通过binlog文件恢复mysql数据库详细步骤，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！