Two problems with MySQL Q&a parsing binlog

Two problems with MySQL Q&a parsing binlog Blog Category:

• Mysql

Mysqlbinlog Character Set parsing binlog format

Two students in a row asked a similar question, must be recorded.

Problem:

an analytic binlog application of the students found no matter with UTF8 or GBK to parse Binlog can encounter unresolved statements, because some users will use UTF8, some with GBK. In particular, the Chinese characters in the SQL statements that handle the query type, such as those in the build table statement.

So he thought of using mysqlbinlog to look at the contents of Binlog.

Mysqlbinlog The results of this tool brings new questions.

To open a client, the execution sequence is as follows:

Set names UTF8;

CREATE Table A (c int);

CREATE table B (c int);

Set names GBK;

drop table A;

CREATE Table A (c int) comment= ' test character set ';

Mysqlbinlog parsing results are as follows

The results of the Binlog are the same as expected. From the results, you can see

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=28/*!*/;

The original information in this sentence gets the character set currently in use, then parsing the subsequent Binlog statements is no problem. You can see that we have modified the character set before drop table A, and we can see a statement that sets the character set in the Binlog parsing result.

question: But the parsing tool can specify the starting position. If you specify to start parsing after the position of the drop table A statement, is it impossible to know the current character set information (GBK)? Is it only possible to get the correct table statement for this table in MySQL server?

Obviously not so complicated, otherwise MySQL from the library to perform a change master changes the synchronization location, from the library should use what character set? The problems encountered are the same.

In fact, for Binlog of the statement format (type query_event), the character set number that is currently used is recorded in each EVENT . Regardless of the main library, the library, or the Mysqlbinlog, the same number is saved to the corresponding character set, which determines what character set to use to parse the string in Binlog.

There is also a reason why the authorities cannot guarantee the correctness of the master-slave structure from the library version to the main library version.

why does the Mysqlbinlog tool not output the currently used character set for each line? in fact, a bit of a neat programmer will do this, the current statement and the previous statement using the same environment variables, will not repeat the output.

Question 2 :Mysqlbinlog The result has a use of one DB, executes multiple statements, and at this point the results of Mysqlbinlog see that only one use DB is displayed when switching. So what if you start parsing binlog from the second statement that follows, will it cause these statements to be executed to another library?

Similarly.