Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the basic knowledge of GTID". In daily operation, I believe many people have doubts about the basic knowledge of GTID. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the basic knowledge of GTID?" Next, please follow the editor to study!

1. Basic knowledge of GTID

The concept of GTID:

Each transaction committed on the source (primary) server creates a global transaction identifier for the unique identifier associated with it.

This identifier is not only unique, but also unique in all replications from the library. There is an one-to-one mapping between everything and all GTID.

Basic description of GTID:

GTID is represented as a pair of coordinates, separated by a colon (:), as follows:

GTID = source_id:transaction_id.

Source_id identifies the source server. Typically, the server's server_uuid is used for this purpose.

Transaction_id is a sequence number determined by the order in which transactions are committed on the server

For example, the first transaction to commit has 1 as its transaction_id, and the tenth transaction to be committed on the same source server is assigned 10 as transaction_id.

A transaction cannot have 0 as the sequence number in a GTID.

For example, the twenty-third transaction that was originally committed on the server where server_uuid equals 3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

This format is used to represent the GTID in the output of the statement, such as SHOW SLAVE STATUS and binary logs

You can use statements to view UUID

Show variables like'% UUID%'

As written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, GTID sequences originating from the same server may be merged into a single expression, as follows:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

The example shown above represents the first to fifth transactions originating from the MySQL server, whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562.

This format is also used to provide the parameters required for the START SLAVE options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS.

The GTID set is used in MySQL servers in several ways. For example, the values stored by the gtid_executed and gtid_purged system variables are represented as GTID collections.

In addition, the functions GTID_SUBSET () and GTID_SUBTRACT () require the GTID set as input. When the GTID collection is returned from the library variable, the UUID is arranged alphabetically and the number intervals are arranged in ascending order.

GTID is always saved between master and slave. This means that the source of any transaction applied to any slave library can be determined by examining the binary log. In addition, once a transaction with a given GTID is committed on a given server, any subsequent transactions with the same GTID are ignored by the server. Therefore, transactions committed on the master station cannot be applied once on the slave station, which helps to ensure consistency.

When using GTID, the slave library does not need any non-local data, such as the name of the file on the master library and the location in the file. All the necessary information for synchronizing with the main library is obtained directly from the replication data stream. GTID replaces the previously required file offset pairs to determine the point at which the data flow is started, stopped, or restored between the master and slave libraries. Therefore, do not include the MASTER_LOG_FILE or MASTER_LOG_POS option in the CHANGE MASTER TO statement, which is used to guide replication from a given master library; instead, when gtid is turned on, you only need to enable the MASTER_AUTO_POSITION option. The exact steps required to configure and launch the master and slave libraries using GTID-based replication. The generation and lifecycle of GTID consists of the following steps:

1) the transaction is executed and committed on the primary server.

Assign a GTID; GTID to the transaction using the UUID of the main library and the smallest non-zero transaction sequence number not used on this library to be written to the binary log of master (directly before the transaction itself in the log).

2) binary log data is transferred to the slave library and stored in the secondary log of the slave library, read GTID from the library and set the value of its gtid_next system variable to this GTID. This tells the standby library that the next transaction must use this GTID record. It should be noted that slave sets gtid_next in the context of the session.

3) verify from the library that this GTID has been used to record transactions in your own binary log. If the GTID is not used, the GTID is written from the device, the transaction is applied, and the transaction is written to its binary log.

By first reading and checking the transaction's GTID, before processing the transaction itself, the slave library not only ensures that no previous transaction with this GTID is applied on the slave library, but also ensures that no other session has read the GTID but has not committed the related transaction. In other words, multiple clients are not allowed to apply the same transaction at the same time.

4) because gtid_next is not empty, the slave library does not generate a GTID for this transaction, but writes the GTID stored in this variable, that is, the GTID obtained from the host immediately before the transaction in its binary log.

Mysql.gtid_ executed table

Starting with MySQL 5.7.5, GTID is stored in a table called gtid_executed in the mysql database.

For each GTID or a set of GTID it represents, a row in the table contains the UUID of the source server and the start and end transaction ID; of the group, both values are the same for rows that reference only a single GTID.

When you install or upgrade a MySQL server, the mysql.gtid_ execute table is created using the CREATE TABLE statement (if it does not already exist)

WARNING: as with other MySQL system tables, don't try to create or modify this table yourself.

The mysql.gtid_executed table allows you to use GTID when binary logging is disabled from the library, and to retain GTID history when binary logs are lost.

The GTID is stored in the mysql.gtid_ executed table only if gtid_mode is ON or ON_PERMISSIVE. The save point of GTID depends on whether binary logging is enabled:

1) if binary logging is disabled (log_bin is OFF), or if log_slave_updates is disabled, the server stores the GTID that belongs to each transaction in the table together with the transaction.

In addition, tables are periodically compressed at a user-configurable rate; this applies only to disabling binary logging or replicating slave libraries that update log records from the library.

It does not apply to replicating the primary library because binary logging must be enabled on the primary library to replicate.

2) if binary logging is enabled (log_bin is ON), whenever the binary log is rotated or the server shuts down, the server writes the GTID of all transactions written to the previous binary log to the mysql.gtid_ executed table.

This applies to replication master nodes or replication slave nodes with binary logging enabled.

In the event that the server stops unexpectedly, the GTID set in the current binary log is not saved in the mysql.gtid_ execute table. In this case, these GTID are added to the table during the restore process and to a set of GTID in the gtid_executed system variable.

When binary logging is enabled, the mysql.gtid_executed table does not provide complete GTID records for all transactions that have been executed. This information is provided by the global value of the gtid_executed system variable.

The mysql.gtid_ executed table is zeroed by RESET MASTER.

Mysql.gtid_ executed table compression

Over time, the mysql.gtid_ executed table may be populated with rows involving a single GTID from the same server, and its transaction ID forms a sequence.

If you compress the table periodically by replacing each such set of rows with a single row that spans the entire interval of the transaction identifier, you can save considerable space

When GTID is enabled, the server periodically performs this type of compression on the mysql.gtid_ executed table. You can control the compression ratio by setting the execution_gtids_compression_period system variable to control the number of transactions allowed before the table is compressed.

The default value for this variable is 1000; this means that, by default, table compression is performed after every 1000 transactions. Setting execution_gtid_compression_period to 0 will prevent full compression; however, if you do, you should be prepared for a significant increase in disk space that the gtid_ execute table may require.

Compression of the mysql.gtid_ executed table is performed by a dedicated foreground thread named thread/sql/compress_gtid_table. This thread is not listed in the output of SHOW PROCESSLIST, but you can view the row in the thread table, as follows:

SELECT * FROM performance_schema.threads WHERE NAME LIKE'% gtid%'\ G

The thread/sql/compress_gtid_table thread usually sleeps until the execution_gtids_compression_period transaction is executed, and then wakes up to perform the compression of the mysql.gtid_executed table described earlier. Then hibernate until another execution_gtids_compression_period transaction occurs, then wake up and perform compression again, repeating the cycle indefinitely. Setting this value to 0 when binary logging is disabled means that the thread is always asleep and never wakes up.

Ordinary replication mode is switched to gtid mode, which can only be operated cold in 5.6environment and online in 5.7environment.

II. 1. Use GTID in master-slave replication (5.6environment cold operation)

The key steps in the startup process of the simplest GTID replication topology (consisting of a master library and a slave library) are as follows:

1) if replication is already running, synchronize the two servers by making them read-only.

2) stop both servers.

3) restart both servers and enable GTID and configure the correct options.

4) instruct the slave server to use the master server as the replication data source and use automatic positioning. The SQL statements required to complete this step are described in the examples later in this section.

5) take a new backup. Binary logs containing transactions without GTID cannot be used on GTID-enabled servers, so backups made before this point cannot be used for new configurations.

6) start the slave server, and then disable read-only mode again on both servers so that they can accept the update.

Specific process:

1. The master library is set to read_only to make the slave library catch up with the master library.

2. Both master and slave shut down the MySQL service

3. Both master and slave configure the startup parameters of gtid in the my.cnf file

Gtid_mode=ON

Enforce-gtid-consistency=true

4, start the MySQL service using skip-slave-start from the library

Use MASTER_AUTO_POSITION = 1 from the library. Configure master and slave

CHANGE MASTER TO

MASTER_HOST = 'host'

MASTER_PORT = port

MASTER_USER = 'user'

MASTER_PASSWORD = 'password'

MASTER_AUTO_POSITION = 1

Neither the MASTER_LOG_FILE option nor the MASTER_LOG_POS option can be used with MASTER_AUTO_POSITION set to 1

5. After taking gtid, you need to make a new backup, because the backup made before opening gtid is not available.

For example, you can perform FLUSH LOGS on the server where the backup is performed. Then, make an explicit backup, or wait for the next iteration of any regular backup routine that you may have set up.

6. Open master and slave from the library.

The main library is closed

Using GTIDs for Failover and Scaleout

When using a global transaction identifier (GTID) for MySQL replication, there are many techniques for configuring a new slave, which can then be used for extension and promoted to a primary station as needed for failover. This section describes the following technologies:

A simple copy

Copy data and transactions to the slave station

Inject an empty transaction

Exclude transactions with gtid_purged

Restore GTID mode slave station

Global transaction identifiers are added to MySQL replication to simplify general management of replication data flows and, in particular, failover activities. Each identifier uniquely identifies a set of binary log events that make up a transaction. GTID plays an important role in making changes to the database: the server automatically skips any transaction with an identifier identified by the server as previously processed. This behavior is critical for automatic replication location and correct failover.

Captures the mapping between identifiers and the set of events that contain a given transaction in the binary log. This presents some challenges when supplying new servers with data from other existing servers. In order to reproduce the identifier set on the new server, you need to copy the identifier from the old server to the new server and maintain the relationship between the identifier and the actual event. This is necessary to restore the slave station that is immediately available as a candidate to become a new master station for failover or switchover.

A simple copy. The easiest way to reproduce all identifiers and transactions on a new server is to make the new server a slave to a master server with full execution history and enable global transaction identifiers on both servers.

Once replication starts, the new server replicates the entire binary log from the primary server to get all the information about all GTID.

This method is simple and effective, but requires the slave to read the binary log from the host; sometimes it takes a long time for the new slave to keep up with the host, so this method is not suitable for fast failover or backup recovery. This section describes how to avoid getting all execution history from the primary server by copying the binary log files to the new server.

Copy data and transactions to the slave station. When the source server has previously processed a large number of transactions, executing the entire transaction history can be time-consuming, which can be the main bottleneck when setting up a new replication slave server. To eliminate this requirement, you can import datasets, binary logs, and snapshots of global transaction information contained by the source server into the new slave server. The source server can be either a master server or a slave server, and you must ensure that the source handles all necessary transactions before replicating the data.

II. 2 using GTID in master-slave replication (5.7 environment online operation)

1. Execute on each server:

SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN

two。 Execute on each server:

SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = ON

3. Execute on each server:

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

4. Execute on each server:

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

5. On each server, when the state variable ONGOING_ANONYMOUS_TRANSACTION_COUNT is zero, you can use the following statement to query:

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'

On a copy slave, it is theoretically possible that it appears to be zero and then non-zero again. This is not a problem, as long as zero is displayed at once.

6. When all the transactions generated in step 5 are replicated to all servers, you can do this without stopping the update: the only thing that matters is that all anonymous transactions are replicated.

7. If you use binary logs for anything other than replication (for example, instant backup and restore), wait until you don't need an old binary log with transactions without GTID.

For example, after step 6 is complete, you can perform FLUSH LOGS on the server where the backup is performed. Then, make an explicit backup, or wait for the next iteration of any regular backup routine you set.

Ideally, wait for the server to clear all binary logs that exist when step 6 is completed. Also wait for any backups made before step 6 to expire.

Important--, there's no turning back.

This is the second most important point. It is important to understand binary logs that contain anonymous transactions without using GTIDs that cannot be used after the next step. After completing this step, you must ensure that transactions without GTID do not exist anywhere in the topology.

8. Execute on each server:

SET @ @ GLOBAL.GTID_MODE = ON

9. On each server, add gtid-mode=ON to the my.cnf parameter file

Gtid_mode=ON

# log_slave_updates=1

Enforce-gtid-consistency=1

10.change master- slave library

STOP SLAVE [FOR CHANNEL 'channel']

CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel']

START SLAVE [FOR CHANNEL 'channel']

II. 3 turn off GTID in master-slave replication (MySQL version 5.7.6 or later online operation)

1. Do the following on each slave, or for each channel if you are using multi-source replication, and include the FOR CHANNEL channel clause

Syntax:

STOP SLAVE [FOR CHANNEL 'channel']

CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, MASTER_LOG_POS = position [FOR CHANNEL 'channel']

START SLAVE [FOR CHANNEL 'channel']

Use MASTER_LOG_FILE and MASTER_LOG_POS to turn on master and slave

CHANGE MASTER TO

MASTER_AUTO_POSITION = 0

MASTER_LOG_FILE = 'mysql-bin.000008'

MASTER_LOG_POS = 677786318

two。 Execute on each server:

SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE

3. Execute on each server:

SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE

4. On each server, the wait variable @ @ GLOBAL.GTID_OWNED equals an empty string. This can be checked using the following methods:

SELECT @ @ GLOBAL.GTID_OWNED

On the copy slave machine, it is theoretically possible that this is empty and then non-empty. This is not a problem, as long as it is empty.

5. Wait for all transactions that currently exist in any binary log to be copied to all slaves. For methods to check that all anonymous transactions have been replicated to all servers.

6. If you use binary logs for anything other than replication, such as performing a point-in-time backup or restore: wait until you don't need an old binary log with GTID transactions.

For example, after step 5 is complete, you can perform FLUSH LOGS on the server you want to back up. Then, make an explicit backup, or wait for the next iteration of any regular backup routine you set.

Ideally, wait for the server to clear all binary logs that exist when step 5 is completed. Wait for backups before step 5 to expire.

important

This is a key point in this program. It is important to understand that logs containing GTID transactions cannot be used after the next step. Before continuing, you must make sure that the GTID transaction does not exist anywhere in the topology.

7. Execute on each server:

SET @ @ GLOBAL.GTID_MODE = OFF

8. Comment out the parameters related to gtid-mode in the my.cnf parameter file on each server

# gtid_mode=ON

# log_slave_updates=1

# enforce-gtid-consistency=1

At this point, the study of "what are the basic knowledge of GTID" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.