3 common errors in reading MySQL Binlog logs

1. mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'

When we add the default-character-set=utf8mb4 option in my.cnf, an error will be reported when viewing binlog in mysqlbinlog.

Solution: Add the --no-defaults option after .mysqlbinlog

For example:

mysql bin executable file path /bin/mysqlbinlog --no-defaults binlog directory /mysql_bin.000005

2.ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

Use mysqldump to export data and migrate the database. When migrating in, the above error was reported.

The reason for the query is: the current GTID_EXECUTED parameter already has a value, and the dump file from the source database contains the SET @@GLOBAL.GTID_PURGED operation

Solution:

Method 1: reset mater

This operation can clear the GTID_EXECUTED value of the current library

Method 2: --set-gtid-purged=off

When dumping, add the --set-gtid-purged=off parameter to avoid exporting gtid information.

mysqldump --set-gtid-purged=off -d dbtest > dbtest.sql

3.ERROR 1782 (HY000) at line 23: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

When we use the mysqlbinlog tool to specify --stop-position and --start-position for recovery (partial recovery), an error is reported.

For example, the data in binlog is as follows:

When we select the starting point corresponding to Query (713) instead of the Pos corresponding to Gtid (648), an error will be reported.

The following writing error:

Installation path/ bin/mysqlbinlog --no-defaults --start-position=713 --stop-position=1646

The following writing does not report an error:

Installation path /bin/mysqlbinlog --no-defaults --start-position=648 --stop-position=1646 .........

Summarize

The above are the three common errors in reading MySQL Binlog logs that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!