Shulou(Shulou.com)06/01 Report--

Solve the problem that mysql uses binary log to recover data report error: 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 colleagues recover data, there is an error alarm:

[root@localhost tmp] # mysqlbinlog-- no-defaults mysql-bin.000614 | mysql- uroot-pEnter 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-pEnter password: [root@localhost 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)

Solution:

Configure gtid option

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 sec)

GTID related knowledge:

GTID (GlobalTransaction ID) is the number for a committed transaction and is a globally unique number. GTID is actually made up of UUID+TID. Where UUID is the unique identity of an MySQL instance. TID represents the number of transactions that have been committed on this instance and increases monotonously as the transaction commits.

MySQL5.6 added GTID replication.

A transaction corresponds to a unique ID.

A GTID is executed only once on a server.

GTID is a replication method that replaces the previous classic.

Advantages:

Data security is higher than row replication

Failover is easier

Limitations of GTID usage:

Non-transactional engine is not supported (error reported from library, ignored by stopslave; start slave;)

Create table is not supported... Select statement replication (error report directly from main library); sql_slave_skip_counter is not supported

Not supported for createtemporary table and drop temporary table statements

It is not allowed to update tables of both transaction engine and non-transaction engine in a SQL.

In a replication group, you must require CTID to be enabled or GTID to be turned off

Restart is required to enable DTID (may not be required in 5.7)

When DTID is enabled, the original traditional replication method is no longer used.

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

Updating non-transactional engine tables, updating transactional and non-transactional tables in the same transaction will result in multiple GTIDs assigned to the same transaction

Temporary table. Create delete temporary table statements cannot be executed within a transaction, but can be executed outside a transaction, but must be set to set autocommit = 1

CREATE TABLE... SELECTstatements

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

Do not execute unsupported statements

Enable the-- enforce-gtid-consistency option to start GTID mode, and the above unsupported statements will return an error.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.