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

This article mainly introduces the importance of binlog logs for mysql database, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated articles in the industry information.

It is well known that binlog logs are very important for mysql databases. In the emergency of data loss, we often think of using the binlog log function for data recovery (scheduled full backup + binlog log recovery incremental data part) to avoid the danger!

Needless to say, the following is a combed explanation of binlog log operation:

First, a 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 the data query statement select), records in the form of events, and also contains the time consumed by the statement execution. MySQL's binary log is transaction-safe.

DDL

-Data Definition Language database definition language

The main commands are CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the structure of the table (TABLE), data types, links and constraints between tables and other initialization work, they are mostly used in the establishment of tables.

DML

-Data Manipulation Language data manipulation language

The main commands are SELECT, UPDATE, INSERT and DELETE, which, as its name suggests, are the language used to manipulate the data in the database.

The common options for mysqlbinlog are as follows:

-- start-datetime: reads from the binary log a specified time equal to the timestamp or later than the local computer

-- stop-datetime: read the specified time value less than the timestamp or equal to the local computer from the binary log as above

-- start-position: reads the specified position event location from the binary log as a start.

-- stop-position: reads the specified position event location from the binary log as the end of the event

Generally speaking, there is a 1% performance loss when you turn on binlog logging.

There are two most important usage scenarios for binlog logs:

1) MySQL master-slave replication: MySQL Replication opens binlog,Master on the Master side and passes its binary log to slaves to achieve

The purpose of master-slave data consistency.

2) naturally, the data is recovered, and the data is recovered by using the mysqlbinlog tool.

Binlog logs include two types of files:

1) binary log index file (file name suffix .index) is used to record all binary files

2) the binary log file (file name suffix .00000 *) records all DDL and DML statement events in the database (except for the data query statement select).

2. Enable binlog log:

1) Edit and open mysql configuration file / etc/mys.cnf

[root@vm-002 ~] # vim / etc/my.cnf

Add in the [mysqld] block

Log-bin=mysql-bin confirms that it is open (mysql-bin is the base name or prefix name of the log)

2) restart the mysqld service to make the configuration effective

[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 log 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)

Third, commonly used binlog log operation commands

1) View a list of all binlog logs

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 numbered name of the last (latest) binlog log and the pos end point (Position) value of the last action 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 log and generates a newly numbered binlog log file from now 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)

Note:

Whenever the mysqld service is restarted, this command is automatically executed to refresh the binlog log; adding the-F option to mysqldump backup data will also refresh the binlog log.

4) reset (empty) 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 the contents of binlog logs:

1) use the mysqlbinlog built-in view command method:

Note:

-- > binlog is a binary file. Ordinary file viewers such as cat, more, vim, etc., cannot be opened. You must use the built-in mysqlbinlog command to view it.

-- > binlog log and database file are in the same directory

-- > if an error is reported when using the mysqlbinlog command in versions below MySQL5.5, add the "--no-defaults" option

Looking at the data storage directory of mysql, we can see from the following result that it is / var/lib//mysql

[root@vm-002 ~] # ps-ef | grep mysql

Root 9791 10 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, and 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

Sql statement executed by insert into member (name,sex,age,classid) values ('wangshibo','m',27,'cls1'), (' guohuihui','w',27,'cls2') #

/! /

# at 796

# 160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 # time of execution

.

Explanation:

Server id 1: service number of the database host

End_log_pos 796: pos node at the end of sql

Thread_id=11: thread number

2) there are many full-text contents of the binlog log read out in the above method, so 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 name of the binlog file to be queried (the first binlog file if not specified)

FROM pos: specify the starting point of pos (if not specified, it will start from the first pos point of the whole file)

LIMIT [offset,]: offset (0 if not specified)

Row_count: total number of queries (all rows are 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 ('masking, recording, w') NOT NULL DEFAULT'

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 divide the specified binlog log file into valid event lines and use limit to specify the starting offset of the pos point and query the number of entries!

Examples are as follows:

A) query the first (earliest) binlog log:

Mysql > show binlog events\ G

B) specify the file to query mysql-bin.000002:

Mysql > show binlog events in 'mysql-bin.000002'\ G

C) specify the file mysql-bin.000002 to be queried, starting from pos point: 624:

Mysql > show binlog events in 'mysql-bin.000002' from 624\ G

D) specify to query the mysql-bin.000002 file, starting from pos point: 624, query 10 items (that is, 10 statements)

Mysql > show binlog events in 'mysql-bin.000002' from 624 limit 10\ G

E) specify to query the mysql-bin.000002 file, starting from pos point: 624, offset 2 lines (that is, skip 2 in the middle), and query 10 items

Mysql > show binlog events in 'mysql-bin.000002' from 624 limit 2pm 10\ G

Using binlog logs to recover 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 ('masking NOT NULL DEFAULT')

-> 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 ('moulding dint') | 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 schedules a full backup every day at 4 a.m., 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

Back up the ops database to the / opt/backup/ops$ (date +% F) .sql.gz file manually here:

[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 the log

-R: backup stored procedures, etc.

-x: lock the table

-- master-data: add CHANGE MASTER statement, binlog file and location information to the backup statement

When the database backup is complete, you don't have to worry about data loss, because there is full backup data!

Because the-F option is used in the full backup above, the log will be refreshed automatically at the beginning of the data backup operation, which will be generated automatically

A new binlog log, which will be used to record database "add, delete and modify" operations after 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)

In other words, mysql-bin.000003 is used to record all "additions, deletions and changes" to the database after 4:00.

2)

I went to work at 9: 00 in the morning, and all kinds of "add, delete and change" operations will be carried out on the database due to the needs of the business.

For example, insert and modify data in the member table under the ops library, and so on:

First, insert the 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)

Another data modification operation was performed at noon:

Mysql > update ops.member set name=' Li Si 'where id=4

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > update ops.member set name=' waiter '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 | waiter | 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, the tragedy appeared inexplicably!

Cheap hand executed the drop statement and deleted the ops library directly! Scared to pee!

Mysql > drop database ops

Query OK, 1 row affected (0.02 sec)

5)

Don't panic at a time like this!

First take a closer look at the last binlog log and record the key pos points, which pos operation caused the database corruption (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 perform a refresh log index operation to restart the new binlog logging file. In theory, mysql-bin.000003

There will be no further writes to this file, because it is easy for us to analyze the cause and find the ops node, and all database operations will be written to the next log file.

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 the binlog log:

[root@vm-002 ~] # cd / var/lib/mysql/

[root@vm-002 mysql] # mysqlbinlog mysql-bin.000003

Method 2: log in to the CVM 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 | Query | 1 | 444 | useops; 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 | useops | Update ops.member set name=' Li Si 'where id= | | mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT / * xid=68 * / | mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN | | mysql-bin.000003 | Query | 1 | 848 | useops`; update ops.member set name=' waiter' 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 the analysis, the pos point interval that caused the database damage is between 875 pos 954 (this is calculated according to the pos node of the log interval), as long as it is restored to 875.

7)

First restore the fully backed up data at 4: 00 in the morning:

[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 / 这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。 mysql>

Show databases; # found that the ops library 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 >

But this is only to restore the data before 4: 00 in the morning, and the data between 4: 00, 4: 00, and 18: 00 has not been recovered!

What should I do?

Don't panic! This can be restored based on the new binlog log of mysql-bin.000003 mentioned earlier.

8)

Recover data from binlog logs

Restore the syntax format of the command:

Mysqlbinlog mysql-bin.0000xx | mysql- u username-p password database name

Common parameter options explain:

-- start-position=875 starting pos point

-- stop-position=954 end pos point

-- start-datetime= "2016-9-25 22:01:08" start time

-- end time of stop-datetime= "2019-9-25 22:09:46"

-- database=zyyshop specifies that only zyyshop databases are restored (there are often multiple databases on a host, local log logs only)

Seldom used options:

-u-- the user name of the user=name connected to the remote host

-p-- password [= name] password to connect to the remote host

-h-- host=name obtains binlog logs from a remote host

-- read-from-remote-server reads binlog logs from a MySQL server

Summary: in fact, the contents of the read binlog log are passed to the mysql command through the pipe character. These commands and files are written as absolute paths as possible.

A) full recovery (manual vim editing mysql-bin.000003 is required to remove that drop statement)

[root@vm-002 backup] # / usr/bin/mysqlbinlog / var/lib/mysql/mysql-bin.000003 | / usr/bin/mysql-uroot-p123456-v ops

B) specify the pos end point restore (partial restore):

-- stop-position=471 pos end node (calculated according to transaction interval, it is 471)

Note:

This pos end node is between the data before the "member table raw data" and the update "name=' Li Si'", so that you can restore the data before changing "name=' Li Si'".

Do the following:

[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 until the change of "name=' Li Si'" (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):

To update the data of name=' Li Si', the log interval is Pos-- > End_log_pos-- according to transaction interval: Pos-- > End_log_pos

Update the data of 'name=' waiter'. The log interval is Pos-> End_log_pos. By transaction interval: Pos-- > End_log_pos.

C1)

To resume the operation of name=' Li Si 'alone, 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

Restore separately according to 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 resume the 'name=' waiter' operation alone, 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

Restore separately according to 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 multi-step operations of name=' Li Si 'and name=' waiter' together, you need to press the transaction interval, but you can do 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 | waiter | 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 state before deletion is restored!

In addition:

You can also specify time node interval recovery (partial recovery):

In addition to using the method of pos node for recovery, it can also be recovered by specifying the time node interval. According to the time recovery need, use the mysqlbinlog command to read the binlog log content and find the time node.

As above, after deleting the ops library by mistake:

Full backup recovery first

[root@vm-002 backup] # mysql-uroot-p-v

< ops_2016-09-25.sql 查看ops数据库 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 out 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 INSERTHERTIZED IDC 3Universe /

# 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') # sql statement executed

/! /

# at 444

# 160925 21:57:19 server id 1 end_log_pos 471 Xid = 66 # time to start execution

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

Sql statement executed by update ops.member set name=' Li Si 'where id=4 #

/! /

# at 646

# 160925 21:58:41 server id 1 end_log_pos 673 Xid = 68 # time when execution starts

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 Universe /

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 Universe /

Sql statement executed by update ops.member set name=' waiter 'where id=2 #

/! /

# at 848

# 160925 21:58:56 server id 1 end_log_pos 875 Xid = 69 # time to start execution

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/

Revert to the data before changing "name=' Li Si'"

[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 | waiter | 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!

Summary:

The so-called recovery is to ask mysql to re-execute the sql statements saved in the specified paragraph in the binlog log one by one.

Read the above about the importance of binlog logs for mysql database, hoping to give you some help 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.