Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL how to quickly view the original SQL statement", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "MySQL how to quickly view the original SQL statement" bar!

Preparatory work

Enable binlog

The first step is to make sure that the MySQL server has binlog enabled, which is configured in the my.cnf file.

Cat / etc/my.cnf# can uncomment the log_bin. You can provide a specific path here, otherwise the default address log_bin# high version MySQL requires the parameter server-id, and provide an id value that is not repeated in the cluster to server-id=1# to restart the server service mysqld restart.

Record the current log location

In order to facilitate subsequent testing, let's first record some information about the MySQL log.

-- make sure that the log is enabled correctly. You can also view the specific path of the log file here.

Mysql > show variables like'% log_bin%' +-- +-- + | Variable_name | Value | +-- -+ | log_bin | ON | | log_bin_basename | / var/lib/mysql/mysqld-bin | | log_bin_index | / Var/lib/mysql/mysqld-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +- -+

-record the file name and offset location of the current log, which can be accurately located in the subsequent process of viewing the log

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysqld-bin.000001 | 2425 | +- -+

You can also view the log by specifying the start time, so record the current time as well

Mysql > select now (); +-+ | now () | +-+ | 2018-08-02 09:59:43 | +-+

Execute DDL

Execute the following statement in the test library, and the final goal is to restore this part of the statement by looking at the binlog.

-- create table create table aaa (id int, title varchar);-- insert initialization data insert into aaa (id, title) values (1, 'test 1'); insert into aaa (id, title) values (2, 'test 2'); insert into aaa (id, title) values (3, 'test 3');-- update / delete update aaa set title=' modification 'where id=2;delete from aaa where id=1

View binlog

Official tool mysqlbinlog

The parsing tool provided by the government is easy to use, but the result of parsing is not readable. We used the no-defaults parameter during the call to avoid unknown variable 'default-character-set=utf8' errors.

Mysqlbinlog-no-defaults / var/lib/mysql/mysqld-bin.000001-start-position=2425

The display result of mysqlbinlog is not readable. An excerpt here shows that in addition to the DDL statement we executed, other contextual information is also recorded.

# at 2425 session 180802 10:05:32 server id 1 end_log_pos 2553 CRC32 0x77e80f22 Querythread_id=70exec_time=0error_code=0use `aaaaa` / *! * /; SET timestamp 1533175532, SET timestamp @ @ session.pseudothread readreadable license idwise / session @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autothread @ @ session.unique_checks=1, @ @ session.session .sqlcards modeword @ session. SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment off set, @ @ session.autoincrement increment off set, @ @ session.session. Session database database for session. Database database where there is no sign of create table aaa (id int, title varchar (100)) / *! # at 2553 "180802 10:05:32 server id 1 end_log_pos 2632 CRC32 0x2bcb9bbd Querythread_id=70exec_time=0error_code=0SET timestamp 1533175532) / * / insert initialization data insert into aaa (id, title) values (1, 'test 1') / *! /

Third-party tool binlog2sql

There are a lot of open source tools for binlog, and we found binlog2sql after Google. It provides more functions than the official, depending on the options, you can get the original SQL, rollback SQL, remove the primary key INSERT SQL, and so on. This time we are only used to view the executed DDL. For more advanced usage, please refer to the official Chinese documentation.

This tool is developed on the basis of Python (2.7 minutes and 3.4 +), so install related dependencies first according to the manual.

Git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sqlpip install-r requirements.txt

The method is similar to the official tool, providing the offset position or start time for accurate positioning. If you do not provide location information, all information will be displayed from scratch.

# use offset position python binlog2sql.py-h227.0.0.1-P3306-uroot-paired root'-- start-file='mysqld-bin.000001'-- start-pos=2425-d aaaa# with the same function, and use timestamp python binlog2sql.py-h227.0.0.1-P3306-uroot-paired root'-- start-file='mysqld-bin.000001'-- start-datetime='2018-08-02 10ve0000'-d aaaa

The display result of this tool is very concise, as follows

USE aaaa;-- create table create table aaa (id int, title varchar); USE aaaa;-- insert initialization data insert into aaa (id, title) values (1, 'test 1'); USE aaaa;insert into aaa (id, title) values (2, 'test 2'); USE aaaa;insert into aaa (id, title) values (3, 'test 3'); USE aaaa;-- update / delete update aaa set title=' modification 'where id=2;USE aaaa;delete from aaa where id=1 Thank you for your reading, the above is the content of "MySQL how to quickly view the original SQL statement". After the study of this article, I believe you have a deeper understanding of how MySQL quickly view the original SQL statement, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.