Two problems related to binlog parsing by MySQLQ & amp;

Two questions of MySQLQA parsing binlog have been repeatedly encountered by two students asking similar questions. You must record them .? Question :??? A user who parses binlog applications finds that, whether utf8 or gbk is used to parse binlog, they may encounter unresolvable statements, because some users use utf8 and some use gbk. Especially in Query Processing

Two questions about MySQL QA parsing binlog must be recorded. ? Question :??? A user who parses binlog applications finds that, whether utf8 or gbk is used to parse binlog, they may encounter unresolvable statements, because some users use utf8 and some use gbk. Especially in Query Processing

Two Problems of MySQL Q & A parsing binlog

When two students continuously ask similar questions, you must record them.

?

Problem:

???A user who parses binlog applications finds that, whether utf8 or gbk is used to parse binlog, they may encounter unresolvable statements, because some users use utf8 and some use gbk. It is especially used to process Chinese Characters in Query-type SQL statements, such as Chinese comments in Table creation statements.

???? So he thought of using mysqlbinlog to look at the content in binlog.

???? The result of Mysqlbinlog brings new questions.

?????? Open a client and execute the following sequence:

????? Set names utf8;

????? Create table a (c int );

????? Create table B (c int );

????? Set names gbk;

????? Drop table;

????? Create table a (c int) comment = 'test character set ';

? The mysqlbinlog parsing result is as follows:

??? The binlog results are the same as expected. From the results, you can use

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

?

??? The current character set is obtained from the original information of this sentence, so there is no problem in parsing the subsequent binlog statements. We can see that we modified the character set before drop table a, and we can also see a statement for setting the character set in the binlog parsing result.

?

????Question:However, the parsing tool can specify the starting position. If the resolution starts after the position of the statement drop table a, is it impossible to know the current character set information (gbk )? Is the correct table creation statement for this table only available in MySQL server?

?

??? Obviously, it won't be so complicated. Otherwise, when a change master is executed on the slave database of MySQL to change the synchronization location, what character set should the slave database use? The problem is the same.

??? In fact, for the statement format binlog (type: QUERY_EVENT ),EachEventThe currently used character set numbers are recorded.. Both the master database, slave database, and mysqlbinlog store the same serial number to the corresponding character set. You can decide which character set to use to parse the strings in binlog.

??? Here we can also figure out why the Master/Slave structure of the slave database version is less correct than that of the master database version.

???? AsWhy?MysqlbinlogDoes the Tool Output the current character set on each line?In fact, programmers who are a bit clean will do this. The current statement uses the same environment variable as the previous statement and will not output it repeatedly.

?

Question 2:After a database is used in the mysqlbinlog result, multiple statements are executed. As shown in the mysqlbinlog result, only the use db is displayed once during the switchover. If the binlog is parsed from the second statement, will these statements be executed in another database?

Similarly.

?

?