troubleshooting MySQL binary log recovery data error: @ @GLOBAL. Gtid_mode = OFF.

Troubleshoot MySQL using binary logs to recover data error: Error 1781 (HY000) at line: @ @SESSION. Gtid_next cannot is set to Uuid:number when @ @GLOBAL. Gtid_mode = OFF.

Work, the use of binary logs, to help develop colleagues to recover data, there has been an error alarm:

[Email protected] tmp]# mysqlbinlog--no-defaults mysql-bin.000614|mysql-uroot-penter password:error 1781 (HY000) at Li NE: @ @SESSION. Gtid_next cannot is set to Uuid:number when @ @GLOBAL. Gtid_mode = OFF. [Email protected] tmp]# mysqlbinlog--no-defaults mysql-bin.000614|mysql-uroot-penter password:[[email protected] tmp ]# Echo $?0

Server-related environment parameters:

Server System: CentOS Linux release 7.3.1611 (Core)

MySQL version:

Mysql> select version (); +-----------+| Version () |+-----------+| 5.7.13 |+-----------+1 row in Set (0.00 sec)

Workaround:

Configure Gtid Options

Before configuration:

Mysql> show global variables like ' gtid_mode '; ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection id:24current database:gold+---------------+-------+| variable_name | Value |+---------------+-------+| Gtid_mode | OFF |+---------------+-------+1 row in Set (0.10 sec)

After configuration:

mysql> set @ @GLOBAL. gtid_mode = on; error 1788  (HY000):  the value of @ @GLOBAL. gtid_mode can only be changed one step at a time: off  <-> off_permissive <-> on_permissive <-> on. also note  that this value must be stepped up or down simultaneously  on all servers. see the manual for instructions.mysql> set  @ @GLOBAL. gtid_mode = off_permissive;mysql> show global variables like  ' GTID_MODE ' ; +---------------+----------------+| variable_name | value           |+---------------+----------------+| gtid_mode     |  off_permissive |+---------------+----------------+1 row in set  (0.00&NBSP;SEC) 

Gtid Related knowledge:

GTID (globaltransaction ID) is the number of a committed transaction and is a globally unique number. Gtid is actually made up of Uuid+tid. Where the UUID is the unique identity of a MySQL instance. The TID represents the number of transactions that have been committed on the instance and is monotonically incremented as the transaction commits.

MySQL5.6 increased Gtid replication.

A transaction corresponds to a unique ID.

A gtid is executed only once on a server.

Gtid is used to replace the previous classic copy method.

Advantages:

Data security is higher relative to row replication;

easier failover;

Limitations of use of Gtid:

Non-transactional engine not supported (error from library, Stopslave; Start slave; ignored);

Do not support CREATE TABLE ... SELECT statement Copy (Main library direct error); Sql_slave_skip_counter is not supported;

Not supported for Createtemporary table and drop temporary table statements;

A table that updates both the transaction engine and the non-transactional engine in one SQL is not allowed;

In a replication group, the Ctid must be turned on or off Gtid;

Reboot required to open Dtid (5.7 may not be required);

When the Dtid is turned on, it is not using the original traditional copy mode;

Gtid and non-gtid MySQL instances can not replicate data, either are gtid, or are common;

Updating a non-transactional engine table, updating a transaction table with a non-transactional table in the same transaction causes multiple Gtids to be assigned to the same transaction;

Temporary tables, within which transactions cannot be performed to create a delete temporary table statement, but can be executed outside the transaction, but must be set autocommit = 1;

CREATE TABLE ... Selectstatements

Unsafe statement-based replication, which is actually two separate events, one for building tables and one for inserting source table data into a new table.

Do not execute unsupported statements

Enabling the--enforce-gtid-consistency option starts the Gtid mode, and the unsupported statements above will return an error.

troubleshooting MySQL binary log recovery data error: @ @GLOBAL. Gtid_mode = OFF.