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

What is the main Mysql binlog log that I will bring you below? How do I recover a database using binlog? What do you want the Mysql binlog log to be? How do I recover a database using binlog? Can bring you practical use, this is also my main purpose of editing this article. All right, don't talk too much nonsense, let's just read the following.

First acquaintance of MySQL log binlog

MySQL important log, binary log files, record all DDL and DML statements (except select), record in the form of events, including the time spent on statement execution, transaction security.

DDL (database definition language), the main commands are create, alter, drop and so on. DDL mainly defines or changes the structure and data type of the table table. Use it when building a meter.

MDL (data manipulation language), the main commands are select, update, insert, delete.

Common options for mysqlbinlog:

-- start-datetime: reads the specified timestamp from the binary.

-- stop-datetime: reads the specified timestamp from the binary.

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

-- stop-position: reads the specified position event location from the binary.

Binlog log usage scenarios:

1) MySQL master-slave replication: MySQL enables binlog,master on the Master side of replication to pass binaries to slaves to achieve master-slave data consistency.

2) data recovery: data recovery through mysqlbinlog tool

Binlog logs include two types of files:

1) the binary log index file (file suffix .index) is used to record all binaries

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

Enable binlog logging and basic operations

Enable binlog:

/ etc/my.cnf file

Log-bin=mysql-bin

Mysql command action:

Check the log to open.

Mysql > show variables like 'log_%'

View all binlog lists

Mysql > show master logs

Check the master status, the numbered name of the last binlog log, and the last action event pos end point (position) value.

Mysql > show master status

Refresh the log log and start to generate a new numbered binlog log file

Mysql > flush logs

Note: when the mysqld service is restarted, this command is also executed to refresh the binlog log.

Reset (empty) all binlog

Mysql > reset master

View the contents of MySQL binlog log

Binlog logs are binary files, which cannot be opened by cat, vim and other tools. Use the built-in mysqlbinlog command to view them.

Binlog content Analysis:

# at 28226679 "181116 0:20:01 server id 776499703 end_log_pos 28226710 CRC32 0xb12373d2 Xid = 54012151COMMITAccording to SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; # at 28226710 * 181116 0:20:35 server id 3095565156 end_log_pos 28226757 CRC32 0x05f2d78a Rotate to mysql-bin.001121 pos: 4DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/ / *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Server id: database host service number

End_log_pos: pos node at the end of the database SQL

Binlog log view command:

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

Binlog starts from pos point 100. query 10 items.

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

Using binlog Log to restore Database

1) restore the nearest full backup library

Mysql-uusername-p-v db_test-h 192.168.1.100 <. / db_test .sql

2) find the start-position point and-- stop-position to restore through binlog

Binlog:mysql-bin.001118 、 mysql-bin.001119 、 mysql-bin.001120

Mysqlbinlog-v-- base64-output=DECODE-ROWS / data/binlog/mysql-bin.001118 | head-10mysqlbinlog-v-- base64-output=DECODE-ROWS / data/binlog/mysql-bin.001120 | grep-C 30-I "22:05:01"

3) binlog recovery

/ bin/mysqlbinlog-- start-position=120-- stop-position=2174941 / data/binlog/mysql-bin.001118 / data/binlog/mysql-bin.001119 / data/binlog/mysql-bin.001120 | / bin/mysql-uusername-p-v db_test-h 192.168.1.100

-- start-position is the pos point recorded after backup.-- stop-position is the pos point before misoperation. If you batch multiple binlog files, then start-position is the pos point of the first binlog file, and stop-position is the pos point of the last binlog file.

Summary: the so-called recovery is to re-execute it with the sql statement of the interval specified in the binlog log saved by mysql.

What is the above about the Mysql binlog log? How do I recover a database using binlog? Do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.