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

Editor to share with you what the binlog log of MySQL is. I hope you will gain a lot after reading this article. Let's discuss it together.

Basic understanding of binlog

MySQL's binary log can be said to be the most important log of MySQL. It records all DDL and DML (except data query statements) statements in the form of events, as well as the time spent on statement execution. MySQL's binary log is transaction-safe.

Generally speaking, there is a 1% performance loss when you turn on binary logging (see MySQL's official Chinese manual version 5.1.24). There are two most important usage scenarios for binary:

One: MySQL Replication opens binlog,Mster on the Master side and passes its binary log to slaves to achieve the purpose of master-slave data consistency.

Second: naturally, the data is restored, through the use of mysqlbinlog tools to restore the data.

Binary logs include two types of files: binary log index files (file name suffix .index) are used to record all binary files, and binary log files (file name suffix .00000 *) record all DDL and DML (except data query statements) statement events in the database.

1. Enable binlog log:

Vi editing opens the mysql configuration file

# vi / usr/local/mysql/etc/my.cnf

In the [mysqld] block

Set / add log-bin=mysql-bin to confirm that it is on (the value mysql-bin is the base or prefix name of the log)

Restart the mysqld service for the configuration to take effect

# pkill mysqld

# / usr/local/mysql/bin/mysqld_safe-- user=mysql &

Second, you can also log in to the mysql server and check whether the binary log is enabled through the variable configuration table of mysql. Word: variable [variables v "ri" b "l]

Log in to the server

# / usr/local/mysql/bin/mysql-uroot-p123456 mysql > show variables like 'log_%' +-- +-- + | Variable_name | Value | +-+ | log_bin | ON | |-> ON indicates that binlog log is enabled | log_bin_basename | / usr/local/mysql/data/mysql-bin | | log_bin_index | / usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | | log_bin_use_v1_row_events | OFF | | log_error | / usr/local/mysql/data/martin.err | | log_output | FILE | | log | _ queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +- -+

Third, common binlog log operation commands

1. View a list of all binlog logs

Mysql > show master logs

two。 View the master status, that is, the numbered name of the last (latest) binlog log and its last action event pos end point (Position) value

Mysql > show master status

3. Refresh the log log and generate a newly numbered binlog log file from now on

Mysql > flush logs

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

4. There are two common ways to view the contents of a binlog log:

1. Use the mysqlbinlog native view command method:

Note: binlog is a binary file, ordinary file viewer cat more vi and so on cannot be opened. You must use your own mysqlbinlog command to view it.

The binlog log is in the same directory as the database file (my environment configuration installation is selected in / usr/local/mysql/data)

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

# / usr/local/mysql/bin/mysqlbinlog / usr/local/mysql/data/mysql-bin.000013

Here is a snippet for analysis:

.... # at # 131128 17:50:46 server id 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0-> execution time: 17:50:46 Pos point: 665 SET timestamp 1385632246 pos; update zyyshop.stu set name=' Li 'where id=4-> SQL / *! * / execution; # at 665 # 131128 17:50:46 server id 1 end_log_pos 692 Xid = 1454-> execution time: 17:50:46 Pos point: 692....

Note: service number of server id 1 database host

End_log_pos 665 pos point

Thread_id=11 thread number

two。 The full text of the binlog log is read out in the above method, so it is not easy to distinguish and view the pospoint information. Here is a more convenient query command:

Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Option resolution:

IN 'log_name' specifies the name of the binlog file to query (if not specified is the first binlog file)

FROM pos specifies the starting point of pos (if not specified, it is calculated from the first pos point of the whole file)

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

Total number of row_count queries (all rows are not specified)

Intercept some query results:

* * 20. Row * Log_name: mysql-bin.000021- -> binlog log file name Pos: 11197-- > pos starting point: Event_type: Query- -- > event type: Query Server_id: 1-> Identify which server is executed by End_log_pos: 11308-> pos end point: 11308 (that is, the pos starting point of the downlink) Info: use `zyyshop` INSERT INTO `team2` VALUES (0df8er5')-> executed sql statement * * 21. Row * * Log_name: mysql-bin.000021 Pos: 11308-> pos start Point: 11308 (that is, the end point of the uplink pos) Event_type: Query Server_id: 1 End_log_pos: 11417 Info: use `zyyshop` / *! 40000 ALTER TABLE `team2` ENABLE KEYS * / * * 22. Row * * Log_name: mysql-bin.000021 Pos: 11417 Event_type: Query Server_id: 1 End_log_pos: 11510 Info: use `zyyshop`; DROP TABLE IF EXISTS `type`

This statement can return the specified binlog log file into valid event lines, and can use limit to specify the starting offset of the pos point and query the number of entries.

a. Query the first (earliest) binlog log:

Mysql > show binlog events\ G

b. Specify to query the mysql-bin.000021 file:

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

c. Specify to query the mysql-bin.000021 file, starting with pos point: 8224:

Mysql > show binlog events in 'mysql-bin.000021' from 8224\ G

d. Specify to query mysql-bin.000021 this file, starting from pos point: 8224, query 10 items

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

e. Specify to query the mysql-bin.000021 file, starting from pos point: 8224, offset by 2 lines, query 10 items

Mysql > show binlog events in 'mysql-bin.000021' from 8224 limit 2jol 10\ G

5. Restore binlog log experiment (zyyshop is a database)

1. Suppose it is 4:00 in the morning, and my scheduled task begins to perform a full database backup:

Back up the zyyshop database to the / root/BAK.zyyshop.sql file:

# / usr/local/mysql/bin/mysqldump-uroot-p123456-lF-- log-error=/root/myDump.err-B zyyshop > / root/BAK.zyyshop.sql

.

After about a few minutes, the backup is complete, I do not have to worry about data loss, because I have a backup, quack ~ ~

Because I use the-F option, the system will refresh the log log at the beginning of the backup and generate a new binlog log to record the database "add, delete and modify" operation after the backup. Take a look:

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+ | mysql-bin.000023 | 120 | +-- -+

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

two。 I went to work at 9:00 in the morning, and all kinds of "add, delete and modify" operations will be carried out on the database according to the business requirements.

@ for example: create a student table and insert and modify the data, and so on: CREATE TABLE IF NOT EXISTS `tt` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (16) NOT NULL, `sex` enum ('massively NOT NULL DEFAULT') NOT NULL DEFAULT 'masked, `age`tinyint (3) unsigned NOT NULL, `classid` char (6) DEFAULT NULL PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Import experimental data

Mysql > insert into zyyshop.tt (`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')

View data

Mysql > select * from zyyshop.tt +-+ | id | name | sex | age | classid | +-+ | 1 | yiyi | w | 20 | cls1 | | 2 | Xiaoer | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | | 4 | lisi | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | +-+

The operation of modifying the data was performed at noon.

Mysql > update zyyshop.tt set name=' Li Si 'where id=4; mysql > update zyyshop.tt set name=' waiter' where id=2

The modified result:

Mysql > select * from zyyshop.tt +-+ | id | name | sex | age | classid | +-+ | 1 | yiyi | w | 20 | cls1 | | 2 | Waiter | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | 4 | Li Si | m | 20 | cls4 | 5 | wangwu | w | 26 | cls6 | +-+

Suppose it is 18:00 in the afternoon, and inexplicably a miserable SQL statement is executed, and the entire database is gone:

Mysql > drop database zyyshop

3. It's too bad right now, don't panic! 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:

# ll / usr/local/mysql/data | grep mysql-bin # cp-v / usr/local/mysql/data/mysql-bin.000023 / root/

Perform a refresh log index operation at this time to restart the new binlog log file. Theoretically, there will be no subsequent writes to the mysql-bin.000023 file (it is convenient for us to analyze the cause and find the pos point), and all database operations will be written to the next log file.

Mysql > flush logs; mysql > show master status

4. Read binlog logs and analyze problems

Method 1: use mysqlbinlog to read binlog logs:

# / usr/local/mysql/bin/mysqlbinlog / usr/local/mysql/data/mysql-bin.000023

Method 2: log in to the server and view (recommended):

Mysql > show binlog events in 'mysql-bin.000023'

The following is the last clip:

+- -+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +- -+-- + | mysql-bin.000023 | 922 | Xid | 1 | 953 | COMMIT / * xid=3820 * / | | mysql-bin.000023 | 953 | Query | 1 | 1038 | BEGIN | | mysql-bin.000023 | 1038 | Query | 1 | 1164 | use `zyyshop` | Update zyyshop.tt set name=' Li Si 'where id=4 | | mysql-bin.000023 | 1164 | Xid | 1 | 1195 | COMMIT / * xid=3822 * / | | mysql-bin.000023 | 1195 | Query | 1 | 1280 | BEGIN | | mysql-bin.000023 | 1280 | Query | 1 | 1406 | use `zyyshop` | Update zyyshop.tt set name=' waiter 'where id=2 | | mysql-bin.000023 | 1406 | Xid | 1 | 1437 | COMMIT / * xid=3823 * / | mysql-bin.000023 | 1437 | Query | 1 | 1538 | drop database zyyshop | +- -+

Through the analysis, the range of pos points causing database damage is between 1437 and 1538, as long as it is restored to 1437.

5. Now restore the data that was backed up in the early morning:

# / usr/local/mysql/bin/mysql-uroot-p123456-v

< /root/BAK.zyyshop.sql; 注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。 但今天一整天(4:00--18:00)的数据肿么办呢？就得从前文提到的 mysql-bin.000023 新日志做文章了...... 6.从binlog日志恢复数据 恢复语法格式： # mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 --start-datetime="2013-11-29 13:18:54" 起始时间点 --stop-datetime="2013-11-29 13:21:53" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志) 不常用选项： -u --user=name Connect to the remote server as username.连接到远程主机的用户名 -p --password[=name] Password to connect to remote server.连接到远程主机的密码 -h --host=name Get the binlog from server.从远程主机上获取binlog日志 --read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志 小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径； A.完全恢复(本例不靠谱，因为最后那条 drop database zyyshop 也在日志里，必须想办法把这条破坏语句排除掉，做部分恢复) # /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop B.指定pos结束点恢复(部分恢复)： @ --stop-position=953 pos结束点 注：此pos结束点介于"导入实验数据"与更新"name='李四'"之间，这样可以恢复到更改"name='李四'"之前的"导入测试数据" # /usr/local/mysql/bin/mysqlbinlog --stop-position=953 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop 在另一终端登录查看结果(成功恢复了)： mysql>

Select * from zyyshop.tt +-+ | id | name | sex | age | classid | +-- + | 1 | yiyi | w | 20 | cls1 | | | 2 | xiaoer | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | 4 | lisi | m | 20 | cls4 | 5 | wangwu | w | 26 | cls6 | +-+ |

c. Specify pso point interval recovery (partial recovery):

To update the name=' Li Si 'data, the log interval is Pos [1038]-> End_log_pos [1164], and by transaction interval: Pos [953]-> End_log_pos [1195]

To update the data of name=' waiter', the log interval is Pos [1280]-> End_log_pos [1406], and by transaction interval: Pos [1195]-> End_log_pos [1437]

C1. To resume the operation of name=' Li Si 'alone, you can do this:

# / usr/local/mysql/bin/mysqlbinlog-start-position=1038-stop-position=1164-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

It can also be recovered separately by transaction interval, as follows:

# / usr/local/mysql/bin/mysqlbinlog-start-position=953-stop-position=1195-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

C2. To resume the 'name=' waiter' operation alone, you can do this:

# / usr/local/mysql/bin/mysqlbinlog-start-position=1280-stop-position=1406-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

It can also be recovered separately by transaction interval, as follows:

# / usr/local/mysql/bin/mysqlbinlog-start-position=1195-stop-position=1437-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

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:

# / usr/local/mysql/bin/mysqlbinlog-start-position=953-stop-position=1437-database=zyyshop / usr/local/mysql/data/mysql-bin.000023 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

d. Log in to another terminal to view the current results (the two names have also been restored):

Mysql > select * from zyyshop.tt +-+ | id | name | sex | age | classid | +-- + | 1 | yiyi | w | 20 | cls1 | | | 2 | waiter | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | 4 | Lisi | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | + |

e. You can also specify a time interval for recovery (partial recovery): in addition to using pos points for recovery, you can also use a specified time interval for recovery. According to the time recovery needs, you can use the mysqlbinlog command to read the binlog log contents and find the time node.

For example, I delete the tt table that I just recovered, and then use the time interval to restore it.

Mysql > drop table tt @-- start-datetime= "2013-11-29 13:18:54" start time @-- stop-datetime= "2013-11-29 13:21:53" end time # / usr/local/mysql/bin/mysqlbinlog-- start-datetime= "2013-11-29 13:18:54"-- stop-datetime= "2013-11-29 13:21:53"-- database=zyyshop / usr/local/mysql / data/mysql-bin.000021 | / usr/local/mysql/bin/mysql-uroot-p123456-v zyyshop

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

After reading this article, I believe you have a certain understanding of MySQL's binlog log, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.