MySQL5.7 New features--gtid basics

1, MySQL5.7 new features: Gtid copy

What is ①:gtid?

Is the ID of the transaction, the unique identification number, and the globally unique.

The transaction is logged to binary log, which is used to identify the transaction.

Each transaction has a gtid_log_event.

The composition of the ②:gtid

UUID + Sequence Number

Note: Sequence number is a transaction order code inside the MySQL server. A transaction on a MySQL server does not have a duplicate sequential number (guaranteed to be unique within the server).

Each MySQL server has a globally unique UUID.

The purpose of ③:gtid

Simplifies the use of replication and reduces the difficulty of replication cluster maintenance: No longer relies on Master's Binlog file name and location in the file.

By change MASTER to master_log_file= ' master-bin.000010 ', master_log_pos= ' 214 '; Instead: Change MASTER to Auto_position=1;

The principle of ④:auto_position

*:mysql Server Records the Gtid of all the transactions that have been performed, including those copied.

Can be viewed with system variable gtid_executed.

*:slave records the Gtid of all transactions received from master.

Can be viewed via retrieve_gtid_set

When the *:slave is connected to master, the Gtid in gtid_executed is sent to master. Master automatically skips these transactions and sends only those things that are not copied to the slave.

⑤:mysql5.7gtid benefits of enabling the Gtid feature online

You do not need to restart the MySQL server.

The configuration process is online, and the entire replication cluster still provides read and write services to the outside world.

There is no need to change the replication topology.

The Gtid feature can be enabled online in a replication cluster of any structure.

More simple to build master-slave replication.

More secure than traditional replication.

Gtid is continuous, no voids, guaranteed data consistency, 0 loss.

⑥:gtid's opening scene

Gtid function is opened directly when the new system is built

The old system is upgraded to MySQL5.7 after the Gtid function is turned on

The complexity of the old system opening Gtid

Some of the replication clusters have Gtid turned on and some are not.

The transaction in the Binlog before opening has no Gtid, the Gtid after opening

Gtid Mode during startup:

Global System Variable Gtid_mode

OFF does not produce Gtid, slave only accept transactions without Gtid

off_permissive does not produce Gtid, slave accepts a transaction with Gtid without Gtid

on_permissive generates Gtid, slave accepts a transaction with Gtid without Gtid

on generates Gtid, slave only accepts transactions with Gtid

⑦: Simplified configuration process

SET @ @GLOBAL. Gtid_mode = off_permissive; (set on each server)

SET @ @GLOBAL. Gtid_mode = on_permissive; (set on each server)

For a while, let the Binlog events without gtid be executed on all servers.

SET @ @GLOBAL. Gtid_mode = on; (set on each server)

The purpose of storing gtid into a table:

Support Slave does not open the Binlog scene

Some slave will never be switched to master. Without opening the Binlog, you can save space and improve performance.

MySQL5.6 on Gtid can only be stored in Binlog, so binlog must be turned on to use the Gtid feature.

MySQL5.7 does not need to open the Binlog.

How to record gtid in a table

If Binlog is turned on, all gtid of the current binlog are inserted into the gtid_executed table when the Binlog is toggled. The insert operation is equivalent to one or more insert statements.

INSERT into mysql.gtid_executed (UUID, 1, 100)

If Binlog is not turned on, each transaction performs an equivalent insert operation before committing. This operation is part of the transaction, and the other operations of the transaction remain atomic as a whole.

BEGIN;

...

INSERT into mysql.gtid_executed (UUID, 101, 101);

COMMIT;

Compression of the gtid_executed table:

Before compression

+--------------------------------------+----------------+--------------+

| Source_uuid | Interval_start | Interval_end |

+--------------------------------------+----------------+--------------+

|              xxxxxxxx-4733-11e6-91fe-507b9d0eac6d |            1 | 1 |

+--------------------------------------+----------------+--------------+

|              xxxxxxxx-4733-11e6-91fe-507b9d0eac6d |            2 | 2 |

+--------------------------------------+----------------+--------------+

|              xxxxxxxx-4733-11e6-91fe-507b9d0eac6d |            3 | 3 |

+--------------------------------------+----------------+--------------+

After compression

+--------------------------------------+----------------+--------------+

| Source_uuid | Interval_start | Interval_end |

+--------------------------------------+----------------+--------------+

|              xxxxxxxx-4733-11e6-91fe-507b9d0eac6d |            1 | 3 |

+--------------------------------------+----------------+--------------+

Control compression Frequency

mysql> SET GLOBAL gtid_executed_compression_period = N; (number of n – Transactions)

Restricted statement detection for Gtid:

Gtid statements/Transactions not supported:

CREATE TABLE ... SELECT

Both support transactions and the engine that do not support transactions are used in the transaction.

BEGIN;

INSERT into Innodb_tbl (...);

INSERT into Myisam_tbl (...);

COMMIT;

Using Create/drop temporary TABLE in transactions

BEGIN;

INSERT into Innodb_tbl (...);

CREATE temporary TABLE Temp1;

．．．

COMMIT;

Before enabling Gtid, detect if there are statements/transactions that are not supported by Gtid in the system and are processed in advance.

Global System Variable Enforce-gtid-consistency

OFF: does not detect if there are statements/transactions that are not supported by Gtid

WARN: When an unsupported statement/transaction is found, a warning is returned and a warning message is recorded in the log.

On: Error returned when Discovery statement/transaction does not support Gtid.

+---------+------+---------------------------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------------------------+

| Warning | 1786 | Statement violates GTID consistency:create TABLE ... SELECT. |

+---------+------+---------------------------------------------------------------+

In the database server or test environment on-line, turn on Warn mode.

After you finish processing the statements that are not supported by Gtid, enable Gtid.

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1924994

MySQL5.7 New features--gtid basics