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

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

When MySQL Recovers Data using binary logs, the following ERROR occurs: ERROR 1781 (HY000) at line 16: @ SESSION. GTID_NEXT cannot be set to UUID: NUMBER when @ GLOBAL. GTID_MODE = OFF.

At work, when using binary logs to help developers recover data, an error is reported:

[root@localhost tmp] # mysqlbinlog --no-defaults mysql-bin.000614|mysql -uroot -p Enter password: ERROR 1781 (HY000) at line 16: @@SESSION.GTID_NEXT cannot be  set  to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF. [root@localhost tmp] # mysqlbinlog --no-defaults mysql-bin.000614|mysql -uroot -p Enter password: [root@localhost tmp] # echo $? 0

Server environment parameters:

Server: CentOS Linux release 7.3.1611 (Core)

MySQL version:

mysql>  select  version(); + -----------+ | version() | + -----------+ | 5.7.13    | + -----------+ 1 row  in  set  (0.00 sec) 

Solution:

Configure gtid options

Before Configuration:

mysql> show  global  variables  like  'gtid_mode' ; ERROR 2006 (HY000): MySQL server has gone away No  connection . Trying  to  reconnect... Connection  id:    24 Current  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 sec)

GTID knowledge:

GTID (GlobalTransaction ID) is the ID of a committed transaction and a globally unique ID. GTID is actually composed of UUID + TID. UUID is the unique identifier of a MySQL instance. TID indicates the number of transactions committed on the instance, and increases monotonically as the transaction is committed.

GTID replication is added to MySQL5.6.

A transaction corresponds to a unique ID.

A gtid can only be executed once on a server.

GTID is used to replace the previous classic replication method.

Advantages:

Data is more secure than row replication;

Failover is simpler;

Limitations of GTID:

Non-transaction engines are not supported (slave database error, stopslave; start slave; ignore );

Create table… is not supported... Select statement replication (primary database directly reports an error); SQL _slave_skip_counter is not supported;

The createtemporary table and drop temporary table statements are not supported;

Updating a transaction engine and a non-transaction engine table at the same time in an SQL statement is not allowed;

In a replication group, you must enable CTID or GTID;

To enable DTID, You need to restart it (which may not be required in 5.7 );

After DTID is enabled, the original traditional replication method is not used;

Gtid and non-gtid mysql instances cannot copy data, either gtid or common;

Update a non-transaction engine table. Updating a transaction table and a non-transaction table in the same transaction will cause multiple GTIDs to be allocated to the same transaction;

A temporary table cannot be created or deleted inside the transaction, but can be executed outside the transaction, but set autocommit = 1 must be set;

Create table... SELECTstatements

Unsafe statement-based replication is actually two independent events, one for table creation and the other for inserting source table data into the new table.

Do not execute unsupported statements

Enable the -- enforce-gtid-consistency option to start GTID mode. Otherwise, an error is returned.