|
As we all know, binlog logs are very important for MySQL database. In the event of an emergency of data loss, we often think of using the binlog log function to recover data (scheduled full backup + binlog log recovery of incremental data) to avoid danger! Without further ado, here is the explanation of the binlog log operations: 1. Preliminary understanding of binlog
MySQL's binary log binlog can be said to be the most important log of MySQL. It records all DDL and DML statements (except data query statements select) in the form of events, and also includes the time consumed by the execution of the statements. MySQL's binary log is transaction-safe.
------------------------------------------------------------------------------------------------------------------------------------------------------------
DDL
----Data Definition Language The main commands of database definition language are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of the table (TABLE), data type, links and constraints between tables and other initialization work. They are mostly used when creating tables. DML
----Data Manipulation Language The main commands of data manipulation language are SELECT, UPDATE, INSERT, and DELETE. As its name suggests, these four commands are used to operate the data in the database.
------------------------------------------------------------------------------------------------------------------------------------------------------------ The common options for mysqlbinlog are as follows:
--start-datetime: Read from the binary log a time equal to or later than the local computer's timestamp
--stop-datetime: Read from the binary log a time value that is less than the timestamp or equal to the time of 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.
--stop-position: Read the specified position event position from the binary log as the event end ********************************************************************* Generally speaking, turning on binlog will result in about 1% performance loss.
Binlog logs have two most important usage scenarios:
1) MySQL master-slave replication : MySQL Replication starts binlog on the Master side, and the Master passes its binary log to the slaves to achieve
The purpose of master-slave data consistency.
2) Naturally, data is recovered by using the mysqlbinlog tool.
The binlog log includes two types of files:
1) Binary log index file ( file name suffix is .index ) is used to record all binary files
2) Binary log files ( file name suffix .00000* ) record all DDL and DML (except data query statements select) statement events in the database. 2. Open binlog log:
1) Edit and open the MySQL configuration file /etc/mys.cnf
[root@vm-002 ~]# vim /etc/my.cnf
Add in the [mysqld] section
log-bin=mysql-bin confirms that it is turned on (mysql-bin is the base name or prefix name of the log); 2) Restart the mysqld service to make the configuration take effect
[root@vm-002 ~]# /etc/init.d/mysqld stop
[root@vm-002 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ] 3) Check whether binlog is enabled
mysql> show variables like 'log_%';
+---------------------------------+---------------------+
| Variable_name | Value |
+---------------------------------+---------------------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+---------------------------------+---------------------+
9 rows in set (0.00 sec) 3. Commonly used binlog log operation commands
1) View all binlog log lists
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 149 |
|mysql-bin.000002 | 4102 |
+------------------+-----------+
2 rows in set (0.00 sec) 2) Check the master status, that is, the number name of the last (latest) binlog log, and the pos end point value of its last operation event
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 4102 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 3) flush refreshes the log and generates a new numbered binlog log file from this point on
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec) mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 149 |
|mysql-bin.000002 | 4145 |
|mysql-bin.000003 | 106 |
+------------------+-----------+
3 rows in set (0.00 sec) Notice:
Whenever the mysqld service is restarted, this command will be automatically executed to refresh the binlog log. Adding the -F option when backing up data with mysqldump will also refresh the binlog log. 4) Reset (clear) all binlog logs
mysql> reset master;
Query OK, 0 rows affected (0.12 sec) mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec) 4. There are two common ways to view binlog log content:
1) Use mysqlbinlog's built-in viewing command method:
Notice:
-->binlog is a binary file. Ordinary file viewers such as cat, more, and vim cannot open it. You must use the built-in mysqlbinlog command to view it.
-->binlog log and database files are in the same directory
-->If an error occurs when using the mysqlbinlog command in MySQL versions below 5.5, add the "--no-defaults" option Check the mysql data storage directory. From the following results, we can see that it is /var/lib//mysql
[root@vm-002 ~]# ps -ef|grep mysql
root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/ lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx
root 9919 9715 0 21:23 pts/1 00:00:00 grep --color mysql [root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test Use the mysqlbinlog command to view the contents of the binlog log. The following is a snippet analysis:
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002
..............
# at 624
#160925 21:29:53 server id 1 end_log_pos 796 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474810193/*!*/;
insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #Executed SQL statement
/*!*/;
# at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 #Execution time
............. explain:
server id 1: the service number of the database host;
end_log_pos 796: POS node at the end of sql
thread_id=11: thread number 2) The above method reads more full text of binlog logs, and it is not easy to distinguish and view the POS point information
Here is a more convenient query command:
Command format:
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; Parameter explanation:
IN 'log_name': Specify the binlog file name to be queried (if not specified, it will be the first binlog file)
FROM pos: Specify the starting point of the search (if not specified, it will start from the first pos point of the entire file)
LIMIT [offset,] ：Offset (0 if not specified)
row_count : query the total number of rows (all rows if not specified) mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 125 |
|mysql-bin.000002 | 823 |
+------------------+-----------+
2 rows in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000002'\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.73-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000002
Pos: 106
Event_type: Query
Server_id: 1
End_log_pos: 188
Info: use `ops`; drop table customers
*************************** 3. row ***************************
Log_name: mysql-bin.000002
Pos: 188
Event_type: Query
Server_id: 1
End_log_pos: 529
Info: use `ops`; CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`sex` enum('m','w') NOT NULL DEFAULT 'm',
`age` tinyint(3) unsigned NOT NULL,
`classid` char(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 4. row ***************************
Log_name: mysql-bin.000002
Pos: 529
Event_type: Query
Server_id: 1
End_log_pos: 596
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000002
Pos: 596
Event_type: Intvar
Server_id: 1
End_log_pos: 624
Info: INSERT_ID=1
*************************** 6. row ***************************
Log_name: mysql-bin.000002
Pos: 624
Event_type: Query
Server_id: 1
End_log_pos: 796
Info: use `ops`; insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')
*************************** 7. row ***************************
Log_name: mysql-bin.000002
Pos: 796
Event_type: Xid
Server_id: 1
End_log_pos: 823
Info: COMMIT /* xid=17 */
7 rows in set (0.00 sec) ERROR:
No query specified mysql> The above statement can return the specified binlog log file in the form of valid event lines, and use limit to specify the starting offset of the pos point to query the number of entries!
The following is an example of operation:
a) Query the first (earliest) binlog log:
mysql> show binlog events\G; b) Query the file mysql-bin.000002:
mysql> show binlog events in 'mysql-bin.000002'\G; c) Specify the query file mysql-bin.000002, starting from pos point: 624:
mysql> show binlog events in 'mysql-bin.000002' from 624\G; d) Specify to query the file mysql-bin.000002, starting from pos point: 624, and query 10 records (i.e. 10 statements)
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G; e) Specify the query file mysql-bin.000002, start the query from pos point: 624, offset 2 lines (i.e. skip 2 in the middle), and query 10 records
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G; 5. Use binlog log to restore mysql data The following operations are performed on the member table of the ops library
mysql> use ops;
mysql> CREATE TABLE IF NOT EXISTS `member` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(16) NOT NULL,
-> `sex` enum('m','w') NOT NULL DEFAULT 'm',
-> `age` tinyint(3) unsigned NOT NULL,
-> `classid` char(6) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec) mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member |
+---------------+
1 row in set (0.00 sec) mysql> desc member;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| sex | enum('m','w') | NO | | m | |
| age | tinyint(3) unsigned | NO | | NULL | |
| classid | char(6) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec) Insert two pieces of data in advance
mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec) Let’s start the scene simulation:
1)
The ops library will perform a scheduled full backup task at 4 a.m. every day, as follows:
[root@vm-002 ~]# crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz Here is a manual execution to back up the ops database to the /opt/backup/ops_$(date +%F).sql.gz file:
[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Enter password:
[root@vm-002 ~]# ls /opt/backup/
ops_2016-09-25.sql.gz
-----------------
Parameter Description:
-B: Specify the database
-F: refresh log
-R: backup storage process, etc.
-x: lock table
--master-data: Add the CHANGE MASTER statement and binlog file and location information to the backup statement
-----------------
Once the database backup is completed, you don’t have to worry about data loss because there is a complete backup data! ! Since the -F option is used in the full backup above, the system will automatically refresh the log when the data backup operation starts, so a new binlog log will be automatically generated. This new binlog log will be used to record the database "add, delete, and modify" operations after the backup.
Check it out:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) That is to say, mysql-bin.000003 is used to record all "add, delete, and modify" operations on the database after 4:00. 2)
I go to work at 9 o'clock in the morning. Due to business needs, various "add, delete and modify" operations will be performed on the database.
For example, data is inserted or modified in the member table under the ops database: First, insert data in the morning:
mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec) 3) At noon, the data modification operation was performed again:
mysql> update ops.member set name='李四' where id=4;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ops.member set name='小二' where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec) 4)
At 18:00 in the afternoon, tragedy happened inexplicably!
I accidentally executed the drop statement and directly deleted the ops library! Scared to death!
mysql> drop database ops;
Query OK, 1 row affected (0.02 sec) 5)
At times like this, don't panic! ! !
First, carefully check the last binlog log and record the key POS points. Which POS point operation caused the database to be damaged (usually in the last few steps); Back up the last binlog log file first:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/
`mysql-bin.000003' -> `/opt/backup/mysql-bin.000003'
[root@vm-002 mysql]# ls /opt/backup/
mysql-bin.000003 ops_2016-09-25.sql.gz Then execute a refresh log index operation and restart a new binlog log file. It stands to reason that mysql-bin.000003
This file will not be written to again, because it is convenient for us to analyze the cause and find the ops node. All database operations will be written to the next log file in the future.
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec) mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 6)
Read the binlog log and analyze the problem.
The method of reading binlog logs has been mentioned above.
Method 1: Use mysqlbinlog to read binlog logs:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003 Method 2: Log in to the server and view (recommended)
mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN |
| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID=3 |
| mysql-bin.000003 | 201 | Query | 1 | 444 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`gsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') |
| mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ |
| mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN |
| mysql-bin.000003 | 538 | Query | 1 | 646 | use `ops`; update ops.member set name='李四' where id= |
| mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ |
| mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN |
| mysql-bin.000003 | 740 | Query | 1 | 848 | use `ops`; update ops.member set name='小等' where id= |
| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid=69 */ |
| mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops |
| mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos=4 |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec) or: mysql> show binlog events in 'mysql-bin.000003'\G;
.........
.........
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos: 875
Event_type: Query
Server_id: 1
End_log_pos: 954
Info: drop database ops
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 954
Event_type: Rotate
Server_id: 1
End_log_pos: 997
Info: mysql-bin.000004;pos=4
13 rows in set (0.00 sec) Through analysis, the POS point interval that caused the database damage was between 875 and 954 (calculated according to the POS nodes in the log interval) , and it only needs to be restored to before 875 . 7)
First restore the full backup data at 4 am:
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# ls
mysql-bin.000003 ops_2016-09-25.sql.gz
[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql
Enter password:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
-------------- --------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
-------------- .............
............. --------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
-------------- In this way, all the backup data before the early morning of the same day (4:00) is restored. mysql> show databases; #Found that the ops database has been restored
mysql> use ops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member |
+---------------+
1 row in set (0.00 sec) mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec) mysql>
However, this only restored the data before 4 a.m. that day, and the data between 4:00 and 18:00 has not been restored! !
What should I do?
Don't panic! This can be recovered based on the new binlog log of mysql-bin.000003 mentioned earlier.
8)
Recovering data from binlog
The syntax format of the restore command is:
mysqlbinlog mysql-bin.0000xx | mysql -u username -p password database name --------------------------------------------------------
Explanation of common parameter options:
--start-position=875 starting pos point
--stop-position=954 End pos point
--start-datetime="2016-9-25 22:01:08" starting time
--stop-datetime="2019-9-25 22:09:46" End time
--database=zyyshop specifies to restore only the zyyshop database (there are often multiple databases on a host, only local logs are limited)
--------------------------------------------------------
Less common options:
-u --user=name Username to connect to the remote host
-p --password[=name] Password to connect to the remote host
-h --host=name Get binlog logs from the remote host
--read-from-remote-server Read binlog logs from a MySQL server
--------------------------------------------------------
Summary: Actually, the read binlog log content is passed to the mysql command through the pipe character. These commands and files should be written as absolute paths as much as possible; a) Complete recovery (you need to manually edit mysql-bin.000003 with vim and remove the drop statement)
[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops b) Specify pos end point recovery (partial recovery):
--stop-position=471 pos end node (471 according to the transaction interval) Notice: This pos end node is between the "original data of the member table" and the data before updating "name='李四'", so the data before changing "name='李四'" can be restored.
The operation is as follows:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec) Restore the data up to the time when "name='李四'" was changed (673 in terms of transaction interval)
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec) c) Specify PSO point interval recovery (partial recovery):
Update the data of name='李四'. The log interval is Pos[538] --> End_log_pos[646]. The transaction interval is: Pos[471] --> End_log_pos[673] Update the data of name='小等'. The log interval is Pos[740] --> End_log_pos[848], and the transaction interval is: Pos[673] --> End_log_pos[875] c1)
To restore name='李四' separately, you can do this:
Restore separately according to the binlog log interval:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops Recover separately by transaction interval
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c2)
To restore name='小二' separately, you can do this:
Restore separately according to the binlog log interval:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=740 --stop-position=848 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops Recover separately by transaction interval
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=673 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c3)
To restore the name='李四' and name='小二' multi-step operations together, you need to follow the transaction interval, which can be done like this:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops View the database:
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec) In this way, the data status before deletion is restored! ! in addition: You can also specify a time node interval for recovery (partial recovery):
In addition to using the POS node method for recovery, you can also restore by specifying a time node interval. To restore by time, you need to use the mysqlbinlog command to read the binlog log content and find the time node. As above, after accidentally deleting the ops library:
Perform a full backup restore first
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql View the ops database
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec) mysql>
Check the mysq-bin00003 log to find the time node
[root@vm-002 ~]# cd /var/lib/mysql
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003
.............
.............
BEGIN
/*!*/;
# at 173
#160925 21:57:19 server id 1 end_log_pos 201 Intvar
SET INSERT_ID=3/*!*/;
# at 201
#160925 21:57:19 server id 1 end_log_pos 444 Query thread_id=3 exec_time=0 error_code=0
use `ops`/*!*/;
SET TIMESTAMP=1474811839/*!*/;
insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') #Executed sql statement
/*!*/;
# at 444
#160925 21:57:19 server id 1 end_log_pos 471 Xid = 66 #Start execution time
COMMIT /*!*/;
# at 471
#160925 21:58:41 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0 #End time
SET TIMESTAMP=1474811921/*!*/;
BEGIN
/*!*/;
# at 538
#160925 21:58:41 server id 1 end_log_pos 646 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474811921/*!*/;
update ops.member set name='李四' where id=4 #Executed SQL statement
/*!*/;
# at 646
#160925 21:58:41 server id 1 end_log_pos 673 Xid = 68 #Start execution time
COMMIT /*!*/;
# at 673
#160925 21:58:56 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0 #End time
SET TIMESTAMP=1474811936/*!*/;
BEGIN
/*!*/;
# at 740
#160925 21:58:56 server id 1 end_log_pos 848 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474811936/*!*/;
update ops.member set name='小二' where id=2 #Executed SQL statement
/*!*/;
# at 848
#160925 21:58:56 server id 1 end_log_pos 875 Xid = 69 #Start execution time
COMMIT /*!*/;
# at 875
#160925 22:01:08 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0 #End time
SET TIMESTAMP=1474812068/*!*/;
drop database ops
/*!*/;
# at 954
#160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; Restore to the data before changing "name='李四'"
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:57:19" --stop-datetime="2016-09-25 21:58:41" -- database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:41" --stop-datetime="2016-09-25 21:58:56" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:56" --stop-datetime="2016-09-25 22:01:08" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+-----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)
In this way, the state before deletion is restored!
Summarize:
The so-called recovery is to let MySQL re-execute the SQL statements saved in the specified paragraph interval in the binlog log one by one. The above explanation of MySQL binlog log and the method of using binlog log to recover data is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. You may also be interested in:- mysql binary log file restore database
- Teach you to automatically restore the log file (binlog) of the MySQL database
- Detailed explanation of how to restore database data through MySQL binary log
- MySQL uses binlog logs to implement data recovery
- Teach you how to restore MySQL data through log files
| 
