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

The following mainly brings you the gtid foundation of the newly added features of MySQL5.7. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the newly added features of MySQL5.7, gtid Foundation. All right, don't talk too much nonsense, let's just read the following.

1. New feature of MySQL5.7: gtid replication

①: what is GTID

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

It is recorded into the Binary Log along with the transaction to identify the transaction.

There is one Gtid_log_event per transaction.

②: the composition of GTID

UUID + Sequence Number

Note: Sequence Number is a transaction sequence number inside the MySQL CVM. Transactions on a MySQL CVM will not have duplicate sequence numbers (to ensure uniqueness within the CVM).

Each MySQL CVM has a globally unique UUID.

③: the purpose of GTID

Simplify the use of replication and reduce the difficulty of replication cluster maintenance: no longer rely on Master's binlog file name and location in the file.

From CHANGE MASTER TO MASTER_LOG_FILE='Master-bin.000010', MASTER_LOG_POS='214'; to: CHANGE MASTER TO AUTO_POSITION=1

④: the principle of AUTO_POSITION

*: MySQL Server records the GTID of all transactions that have been executed, including replicated ones.

You can view it with the system variable Gtid_executed.

*: Slave records the GTID of all transactions received from master.

Can be viewed through Retrieve_gtid_set

*: when Slave connects to Master, it sends the gtid in gtid_executed to master. Master automatically skips these transactions and sends only things that are not copied to Slave.

⑤: advantages of MySQL5.7GTID enabling GTID online

There is no need to restart the MySQL CVM.

The configuration process is online, and the whole replication cluster still provides read and write services.

There is no need to change the replication topology.

The GTID function can be enabled online in any structural replication cluster.

Easier to build master-slave replication.

It is more secure than traditional replication.

GTID is continuous without emptiness, ensuring data consistency and zero loss.

⑥: the opening scene of GTID

Turn on the GTID function directly when the new system is built.

The GTID function is enabled after the old system is upgraded to MySQL5.7

The complexity of opening GTID in the old system

Some of the replication clusters have GTID turned on or not.

There is no GTID for the transaction in BINLOG before opening, but GTID after opening.

Mode during GTID startup:

Global system variable GTID_MODE

OFF does not generate GTID, and Slave only accepts transactions without GTID

OFF_PERMISSIVE does not generate GTID. Slave accepts transactions without GTID as well as transactions with GTID

ON_PERMISSIVE generates GTID. Slave accepts transactions without GTID as well as transactions with GTID

ON generates GTID, and Slave only accepts transactions with GTID

⑦: a simplified configuration process

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE; (set on each CVM)

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE; (set on each CVM)

Wait a while, and let the binlog events without GTID be executed on all CVMs.

SET @ @ GLOBAL.GTID_MODE = ON; (set on each CVM)

The purpose of storing the GTID into the table:

Support scenarios where Slave does not enable Binlog

Some Slave will never be switched to Master. Do not open binlog, you can save space and improve performance.

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

MySQL5.7 does not need to open binlog.

How to record GTID into a table

If binlog is enabled, all the GTID of the current binlog is inserted into the gtid_ executed table when switching between binlog. The insert operation is equivalent to one or more INSERT statements.

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

If binlog is not enabled, each transaction performs an equivalent INSERT operation before committing. This operation is part of the transaction and remains atomic with other operations of the transaction as a whole.

BEGIN

...

INSERT INTO mysql.gtid_executed (UUID, 101,101)

COMMIT

Compression of 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:

Statements / transactions not supported by GTID:

CREATE TABLE... SELECT

An engine that supports transactions and does not support transactions is used in the transaction.

BEGIN

INSERT INTO innodb_tbl (…)

INSERT INTO myisam_tbl (…)

COMMIT

Using CREATE/DROP TEMPORARY TABLE in a transaction

BEGIN

INSERT INTO innodb_tbl (…)

CREATE TEMPORARY TABLE temp1

...

COMMIT

Before enabling GTID, detect if there are any statements / transactions in the system that are not supported by GTID, and process them in advance.

Global system variable enforce-gtid-consistency

OFF: does not detect whether 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 logged.

ON: returns an error when a statement / transaction does not support GTID.

+-- +

| | Level | Code | Message | |

+-- +

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

+-- +

In the online database cloud server or test environment, enable WARN mode.

After processing statements that are not supported by GTID, enable GTID.

For the above about the new features of MySQL5.7 gtid foundation, you do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.