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

This article introduces the relevant knowledge of "how to understand mysql GTID master-slave replication". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, an overview of GTID:

1. Global transaction identity: global transaction identifieds.

2. GTID things are globally unique, and a transaction corresponds to a GTID.

3. A GTID is executed only once on a server to avoid data confusion or inconsistency caused by repeated execution.

4. The replication method used by GTID instead of classic is no longer enabled by binlog+pos. Instead, master_auto_postion=1 is used to automatically match GTID breakpoints for replication.

5. It is supported by MySQL-5.6.5 and improved after MySQL-5.6.10.

6. In the traditional slave terminal, the binlog does not need to be turned on, but in the GTID, the binlog of the slave terminal must be turned on in order to record the GTID (coercion) that has been executed.

Second, the components of GTID:

Preceded by server_uuid: followed by a serial number

For example: server_uuid:sequence number

7800a22c-95ae-11e4-983d-080027de205a:10

UUID: the unique ID of each mysql instance, which can also be understood as the source ID because it is passed to the slave.

Sequence number: on each MySQL server, there is a sequence that grows from 1 to 1, with a number corresponding to a transaction.

Third, the advantages of GTID over traditional replication:

1. It's easier to implement failover, instead of looking for log_file and log_Pos as before.

2. Easier to build master-slave replication.

3. It is more secure than traditional replication.

4. GTID is continuous without holes, so when there is a data conflict in the master-slave database, it can be skipped by adding empty things.

4. The working principle of GTID

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log.

2. The iUnip thread on the slave side writes the changed binlog to the local relay log.

3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record.

4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it.

5. If there is no record, slave will execute the transaction of the GTID from relay log and record it to binlog.

6. During the parsing process, it will determine whether there is a primary key, if not, use a secondary index, and if not, use a full scan.

Main points:

1. When slave accepts the binlog of master, it verifies whether the GTID of master has been executed (a server can only execute it once).

2. In order to ensure the consistency of master-slave data, multithreading can only execute one GTID at the same time.

6. Use GTID to build the main parameters of master-slave replication of mysql:

[mysqld]

# GTID:

Gtid_mode=on

Enforce_gtid_consistency=on

Server_id=2003306 # the server_id of the instance should be different every day

# binlog

Log-bin=mysqlbin

Log-slave-updates=1 # allows the lower end to access slave

Binlog_format=row # strongly recommends that other formats may cause data inconsistencies

# relay log

Skip_slave_start=1

Note: it is recommended to use the latest version above mysql-5.6.5.

(2) two ways to start GTID:

Method 1.

1. If you are on a running server, you need to restart mysql server.

2. Before starting, be sure to turn off the writing of master to ensure that all slave ends have been synchronized with the master data.

3. All slave need to add the configuration parameters of skip_slave_start=1 to avoid using the old replication protocol after startup.

Method 2.

1. If it is a newly built server, just start it directly.

7. Matters needing attention in building master-slave:

(1) Mount the salve end to the master end by using GTID:

1. It is best not to execute the transaction immediately after startup, but on change master first.

2. Then execute the transaction, of course, it is not necessary.

3. Use the sql below to switch slave to the new master.

Stop slave

Change master to

Master_host = 192.168.100.200

Master_port = 3306

Master_user = abobo

Master_password=123

Master_auto_position = 1

(2) if you add a new slave to the master side of the already running GTID

There are two ways:

Method one, suitable for master is also a new situation.

1. If all the binlog of your master is still there. You can choose a method similar to the one above to install slave and change master to directly to the master side.

2. The principle is to directly get all the GTID of master and execute it.

3. Advantages: simple and convenient.

4. Disadvantages: if there is too much binlog, it takes a long time for data to be fully synchronized, and master starts with GTUD enabled.

Second, it is suitable for those who have more than big data. (recommended)

1. Build a new slave through the backup of slave or other slave. (see part 3)

2. Principle: get the data of master and the corresponding GTID range of these data, and then skip the gtid contained in the backup through the @ @ global.gtid_purged setting of slave.

3. Advantage: the deficiency of the first method can be avoided.

4. Disadvantages: relatively speaking, it is a little complicated.

(3) build a new slave through backup: (extension of method 2)

There are two ways:

Method 1. Mysqldump method:

1. Specify-- master-data=2 at the time of backup (command to save the file number and location of the binlog).

2. Using the command mysqldump, you can see the following two messages in the dump file:

SET @ @ SESSION.SQL_LOG_BIN=0

SET @ @ GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8'

3. After restoring the backup to slave, mount the master using the change master to command.

Note: this feature is not supported until later commands in mysql5.6.9.

Method 2. Percona Xtrabackup

1. The Xtrabackup_binlog_info file contains the information of global.gtid_purged='XXXXXX:XXXX'.

2. Then go to slave to make manual SET GLOBAL.GTID_PURGED='XXXXXX:XXXX'.

3. Restore the backup and open the change master to command.

Note: if the system has been running for a long time and cannot find the GTID number, you can find it in the above way.

8. How does GTID skip transaction conflicts:

1. This function mainly skips transactions instead of the original set global sql_slave_skip_counter = 1.

2. Because the GTID must be continuous, normally there will be no vacancy in the GTID generated by the same server. So you can't simply skip a transaction, you can only replace an actual transaction by injecting empty things.

3. The method of injecting empty things:

Stop slave

Set gtid_next='xxxxxxx:N'

Begin;commit

Set gtid_next='AUTOMAIC'

Start slave

4. The xxxxx:N here is the GTID that your slave sql thread reported an error, or the GTID you want to skip.

9. Parameter notes for GTID:

[master] > show global variables like'% gtid%'

1. Enforce_gtid_consistency: some security restrictions for enabling gtid (mind enabling it).

2. Gtid_executed: both global and seeeion levels are available. Used to save the GTIDs that has been executed.

Tip: show master status\ G; the Executed_Gtid_Set and gitd_executed in the output are the same. When reset master, this value is cleared.

3. Gtid_owned: both global and session levels are available. Global means that all servers have GTIDs,session level, which means that the current client owns all GTIDs. (this feature is rarely used)

4. Gtid_mode: whether to enable the GTID feature.

5. Gtid_purged: global parameter, set in binlog, the GTIDs that has been purged, and the GTIDs dropped by purged will be included in the gtid_executed.

Tip: as a result, slave will no longer go to master to request these GTIDs, and this value can only be set if Executed_Gtid_Set is empty.

6. Gtid_next: the parameters of session level at this time:

[master] > show session variables like'% gtid_next%'

10. Some functional limitations of GTID:

(1) update the non-transaction engine:

1. Case reproduction:

Master: do a multi-sql update to an innodb table, and the effect is to produce a GTID.

Slave: the corresponding table is the MYISAM engine, and an error will be reported after the first statement of this GTID is executed, because a non-transaction engine a sql is a transaction.

2. Error number:

Last_Errno:1756

3. Exception recovery scheme:

(1) simple stop slave; start slave; can ignore errors. But at this time, there has been a problem with the consistency of the master and the subordinate. Need to manually make up the data of slave difference.

(2) first, the engine is adjusted to the same, and the slave is also changed to the transaction engine.

(2) create table.... select statements is not supported

1. Case reproduction:

Master: a sql that directly executes a create table select * from table;

2. Report an error:

Error 1786

3. Principle:

Because the create table... select statement generates two sql, one is the DDL creates the table SQL, and the other is the sql where the insert into inserts the data. Because DDL causes autocommit, this sql requires at least two GTID, but in GTID mode, only one GTID can be generated for this sql, and enforcement will result in the same result as the above update non-transaction engine.

(3) A sql colleague operates the innodb engine and the myisam engine:

Case reproduction: T1 table is innodb, T2 table is myisam

1. Update T1 set T2 set T1 1000 where t1.id=t2.id

2. Error report: 1785

3. The principle is the same as the second one.

(4) in a replication grouop, all mysql must turn the GTID function on or off.

1. Case reproduction:

Connect a slave that does not open gtid to the master that opens GTID through the original binlog and pos methods.

2. Report an error:

The slave IO thread stops because the master has @ @ GLOBAL.GTID_MODE ON and this server has @ @ GLOBAL.GTID_MODE OFF.

(5) in a replication group, if GTID is enabled, the replication method of classic is no longer allowed:

1. Case reproduction:

Connect a slave that turns on gtid to the master that opens GTID through the original binlog and pos methods.

2. Report an error:

ERROR 1776 (HY000): Parameters MASTER_LOG_FILE,MASTER_LOG_POS,RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

(6) GTID_MODE belongs to not online:

A reboot is required to take effect. Officials do not support smooth switching from classic replication to GTID replication.

Tips:

Since you need to restart the system to enable GTID, all instances in a replication group must enable or disable GTID. After enabling GTID, you cannot use classic to replicate.

Question:

In other words, the online business must be shut down and then started, which will lead to service interruption.

Solution:

1. In view of this situation, the community has two corresponding smooth upgrade solutions:

One is booking.com, which is described in Taobao's September database monthly report, adding a bridged server that can run either GTID mode or classic mode.

The other is produced by facebook.com. All slave can be connected to a master that does not have GTID mode enabled with GTID mode on.

2. You can close one part and stop writing, but if you don't need to read it, change the other part to GTID mode.

(7) Temporary tables.

1. Create temporary table and drop temporary table statements are not supported in GTID environment.

If the-- enforce_gtid_consistency parameter is on and autocommit=1, then you can use it.

(8) about Errant transaction

1. Errant transaction: the so-called errant transaction is a transaction that is executed from master without specification, but directly from slave.

2. Due to the GTID protocol, it has been mentioned at the beginning (see GTID architecture).

3. If errant transaction is generated in slave, it is easy to cause failover failure because of the rules in GTID protocol. There are two main situations:

A. A useless or temporary errant transaction operation has been done on slave, and if the slave is upgraded to master, all databases connected to it will get the transaction. If it is the same, there will be conflict.

B. After doing this errant transaction transaction, other slave has not yet obtained the GTID of this errant transaction, so it needs to send synchronization from the master to other slave, but the main binlog is deleted, and an error will be reported.

4. In short: avoid producing errant transaction as much as possible. You can execute sql in slave by: set sql_log_bin=off, but also with data consistency in mind.

This is the end of the content of "how to understand mysql GTID master-slave copy". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.