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

Background:

MySQL5.6 has added some improvements on the basis of 5.5. this article first explains one of the major improvements, "GTID".

Concept:

GTID is the global transaction ID (global transaction identifier), and 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, so GTID can guarantee the execution of each MySQL instance transaction (the same transaction will not be repeated and transactions that have not been executed will be completed). Here is a concrete form of GTID:

4e659069-3cd8-11e5-9a49-001c4270714e:1-77

For more specific instructions, see the official statement.

The meaning of GTID:

What is the significance of introducing GTID? 1) because the format of GTID is clear, you can know the instance on which the transaction was committed through UUID. 2) through GTID, it is very convenient to fail over the replication structure and the new master setting. The problem in the following diagram (from Chapter 10 of High performance MySQL) is well solved.

The image above means that Server1 (Master) crashes, and according to the value of Master_log_File/Read_Master_Log_Pos obtained from the upper show slave status, Server2 (Slave) has followed the Lord, while Server3 (Slave) has not. At this time, if you promote Server2 as the master, Server3 will become the follower of Server2. At this time, when performing change on Server3, you need to do some calculations, which will not be explained here. For specific instructions, see Chapter 10 of High performance MySQL, which is relatively troublesome.

This problem has been very simple since the appearance of GTID in 5. 6. Because the GTID of the same transaction has the same value on all nodes, the GTID of the current stop point of the Server3 can be located to the GTID on the Server2. Even due to the emergence of the MASTER_AUTO_POSITION function, we do not need to know the specific value of GTID, we can directly use the CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION command to complete the work of failover.

Principle:

After connecting from the slave server to the master server, send the executed GTID (Executed_Gtid_Set) and the acquired GTID (Retrieved_Gtid_Set) to the master server, and the master server can send the missing GTID and the corresponding transactions from the slave server to make up. When the master server hangs up, find the slave server that synchronizes most successfully and directly promote it to the master server. If you have to specify a slave server that is not the latest slave server, first change to the slave server that is the most successful synchronization, and so on, after all the GTID is completed, you can upgrade it to the master server.

Test:

1) Construction of replication environment: specific steps of replication construction can be searched on the Internet.

Because GTID is supported, several parameters have been added to 5.6:

Copy the code

Mysql > show variables like'% gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_gtid_simple_recovery | OFF |

| | enforce_gtid_consistency | OFF |

| | gtid_deployment_step | OFF |

| | gtid_executed |

| | gtid_mode | OFF |

| | gtid_next | AUTOMATIC |

| | gtid_owned |

| | gtid_purged |

| | simplified_binlog_gtid_recovery | OFF |

+-+ +

Copy the code

There is no difference between the construction of master-slave environment and 5.5. the only thing to note is that you need to enable these three parameters to enable GTID:

# GTID

Gtid_mode = on

Enforce_gtid_consistency = 1

Log_slave_updates = 1

If any parameter is not enabled, an error will be reported:

2015-08-09 02:33:57 6512 [ERROR]-gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires-log-bin and-log-slave-updates

2015-08-09 02:33:57 6512 [ERROR] Aborting

2015-08-09 02:39:58 9860 [ERROR]-- gtid-mode=ON or UPGRADE_STEP_1 requires-- enforce-gtid-consistency

2015-08-09 02:39:58 9860 [ERROR] Aborting

For specific methods, please refer to the official documentation.

After the three instances are enabled (3306, 3307, 3308), you should also pay attention to the execution of change:

Uuid of each instance:

Copy the code

3306:

Mysql > select @ @ server_uuid

+-+

| | @ @ server_uuid |

+-+

| | 4e659069-3cd8-11e5-9a49-001c4270714e |

+-+

3307:

Mysql > select @ @ server_uuid

+-+

| | @ @ server_uuid |

+-+

| | 041d0e65-3cde-11e5-9a6e-001c4270714e |

+-+

3308:

Mysql > select @ @ server_uuid

+-+

| | @ @ server_uuid |

+-+

| | 081ccacf-3ce4-11e5-9a95-001c4270714e | |

+-+

Copy the code

Use the master-slave change before 5.6:

Mysql > change master to master_host='127.0.0.1',master_user='rep',master_password='rep',master_log_file='mysql-bin3306.000001',master_log_pos=151,/master_auto_position=1/

Error report:

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.

The MASTER_LOG_FILE,MASTER_LOG_POS parameter cannot be used when using the MASTER_AUTO_POSITION parameter.

Use the master-slave change after 5.6:

Mysql > change master to master_host='127.0.0.1',master_user='rep',master_password='rep',master_port=3306,master_auto_position=1

Two warnings errors will be reported when executing the above command, mainly due to the security of the replication account. For more information, please see here.

In general, because GTID is supported, there is no need to manually determine the MASTER_LOG_FILE and MASTER_LOG_POS of the primary server. You need to specify FILE and POS if you do not need GTID. Execute the above command on the 2 slaves until the master-slave environment is built. After the master / slave of GTID is completed, you can view it through show processlist:

Copy the code

Mysql > show processlist\ G

1. Row

Id: 38

User: rep

Host: localhost:52321

Db: NULL

Command: Binlog Dump GTID # copy via GTID

Time: 48

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Rows_sent: 0

Rows_examined: 0

Copy the code

2) Test the failover of replication

Server1 (3306) is down and the server can't get up. You need to set one of them as the slave master and the other as its slave library:

Server2 (3307):

Master_Log_File: mysql-bin3306.000002 Read_Master_Log_Pos: 4156773 Exec_Master_Log_Pos: 4156773

Server3 (3308):

Master_Log_File: mysql-bin3306.000001 Read_Master_Log_Pos: 83795320 Exec_Master_Log_Pos: 83795320

In contrast, the transaction completed by server2 is closer to or equal to server1 than server3, and now you need to set server3 as the slave library of server2.

Before MySQL5.6, the calculation here will be very troublesome, and it is likely to make an error to calculate the log_pos of the previous main library and the log_pos that is currently set as the main library. So there are some high-availability tools such as MHA,MMM to solve the problem.

After MySQL5.6, this problem was easily solved. Because the GTID of the same transaction has the same value on all nodes, the GTID of the current stop point of the server3 can be located to the GTID on the server2, so you can execute change directly on the server3:

Copy the code

Mysql > stop slave

Query OK, 0 rows affected (0.02 sec)

# never execute reset master, otherwise it will start on the first GTID.

Mysql > change master to master_host='127.0.0.1',master_user='rep',master_password='rep',master_port=3307,master_auto_position=1; # specifies another slave that is closer to the master.

Query OK, 0 rows affected, 2 warnings (0.02 sec)

Mysql > start slave; # successfully switched to the new master

Query OK, 0 rows affected (0.03 sec)

Copy the code

The master-slave structure has changed, and server2 is Master,server3 and Slave. Because there is no need to calculate the value of pos, this problem is easily solved through GTID.

3) Skip replication errors: gtid_next, gtid_purged

① skipped an incorrect transaction from the server:

Copy the code

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin3306.000001

Read_Master_Log_Pos: 38260944

Relay_Log_File: mysqld-relay-bin3307.000002

Relay_Log_Pos: 369

Relay_Master_Log_File: mysql-bin3306.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1008

Last_Error: Error 'Can't drop database' mablevi'; database doesn't exist' on query. Default database: 'mablevi'. Query: 'drop database mablevi'

Skip_Counter: 0

Exec_Master_Log_Pos: 151

Relay_Log_Space: 38261371

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1008

Last_SQL_Error: Error 'Can't drop database' mablevi'; database doesn't exist' on query. Default database: 'mablevi'. Query: 'drop database mablevi'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714e

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0 # delay synchronization by 10 minutes by specifying change, such as change master to master_delay=600.

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 150810 23:38:39

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48

Executed_Gtid_Set:

Auto_Position: 1

Copy the code

Before MySQL5.6, you only need to execute:

Mysql > set global sql_slave_skip_counter=1

Skip an erroneous transaction and continue with replication. But not after MySQL5.6:

Mysql > set global sql_slave_skip_counter=1

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @ @ GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

Analysis: because it is replicated through GTID, you also need to skip this transaction to continue replication, this transaction can be checked in the binlog on the master: because you don't know which GTID is wrong, you don't know how to skip which GTID. But the POS:151 in the execution Master can be found in the information in show slave status.

Exec_Master_Log_Pos: 151

An error was reported, so GTID was found through mysqlbinlog:

At 151

# 150810 22:57:45 server id 1 end_log_pos 199 CRC32 0x5e14d88f GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '4e659069-3cd8-11e5-9a49-001c4270714e bank /

Execute when the GTID is found: it must be executed in the following order

Copy the code

Mysql > stop slave

Query OK, 0 rows affected (0.01 sec)

Mysql > set session gtid_next='4e659069-3cd8-11e5-9a49-001c4270714emysql; # set gtid_next in session to skip this GTID

Query OK, 0 rows affected (0.01 sec)

Mysql > begin; # start a transaction

Query OK, 0 rows affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Mysql > SET SESSION GTID_NEXT = AUTOMATIC; # set gtid_next back

Query OK, 0 rows affected (0.00 sec)

Mysql > start slave; # enable replication

Query OK, 0 rows affected (0.01 sec)

Copy the code

View replication status:

Copy the code

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin3306.000001

Read_Master_Log_Pos: 38260944

Relay_Log_File: mysqld-relay-bin3307.000003

Relay_Log_Pos: 716

Relay_Master_Log_File: mysql-bin3306.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 38260944

Relay_Log_Space: 38261936

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714e

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0 # delay synchronization

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48

Executed_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-48

Auto_Position: 1

Copy the code

The error was successfully skipped here, and synchronization continues. You can deal with replication failures in this way, and here is another example that you can take a look at (skip a statement / transaction from the server):

View Code

Note: replication via GTID does not specify MASTER_LOG_FILE and MASTER_LOG_POS, so replication through GTID starts with the first transaction, and subsequent execution will not continue unless there is a record of previous execution in your own binlog.

② if the transaction log is purge, then change:

View Code

Error report:

Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

What needs to be solved here is how Slave skips parts of purge instead of executing in the first transaction.

Copy the code

Execute on the master to view the GTID of the purge:

Mysql > show global variables like 'gtid_purged'

+-+

| | Variable_name | Value |

+-+

| | gtid_purged | 4e659069-3cd8-11e5-9a49-001c4270714e:1-50 |

+-+

1 row in set (0.00 sec)

Execute from above and skip the GTID:

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

Mysql > set global gtid_purged = '4e659069-3cd8-11e5-9a49-001c4270714e:1-50'

Query OK, 0 rows affected (0.02 sec)

Mysql > reset master

Query OK, 0 rows affected (0.04 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

If it appears:

ERROR 1840 (HY000): @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty

Then you need to perform:

Reset master

Copy the code

When you get here, the synchronization from here is normal.

View Code

③ recovers data from the library through another one.

For example, if a slave misoperates and the data is lost, it can be recovered through another slave:

Copy the code

Slave2 (3308):

Mysql > use mmm

Database changed

Mysql > show tables

+-+

| | Tables_in_mmm |

+-+

| | patent_family |

| | t |

| | tt |

+-+

3 rows in set (0.00 sec)

Mysql > truncate table tt; # misoperation, delete the record

Query OK, 0 rows affected (0.02 sec)

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin3306.000001

Read_Master_Log_Pos: 38260553

Relay_Log_File: mysqld-relay-bin3308.000002

Relay_Log_Pos: 38260771

Relay_Master_Log_File: mysql-bin3306.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 38260553

Relay_Log_Space: 38260980

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 4e659069-3cd8-11e5-9a49-001c4270714e

Master_Info_File: / var/lib/mysql3/master.info

SQL_Delay: 0 # delay synchronization

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 4e659069-3cd8-11e5-9a49-001c4270714e:1-46

Executed_Gtid_Set: 081ccacf-3ce4-11e5-9a95-001c4270714e:1, # with an extra GTID (transaction executed by its own instance)

4e659069-3cd8-11e5-9a49-001c4270714e:1-46

Auto_Position: 1

After the data has been deleted by mistake, it is best to stop copying: stop slave

Recovery data backs up data from slave1 (3307) and restores it to slave2 (3308).

Backup:

Mysqldump-uzjy-p123456-h227.0.0.1-P3307-- default-character-set=utf8-- set-gtid-purged=ON-B mmm > mmm1.sql

When restoring to slave2, you need to execute it on slave2: reset master;, otherwise an error will be reported:

ERROR 1840 (HY000) at line 24: @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty.

Restore:

Root@zjy:~# mysql-uzjy-p123456-h227.0.0.1-P3308-- default-character-set=utf8

< mmm.sql 开启同步： mysql>

Start slave

Query OK, 0 rows affected, 1 warning (0.03 sec)

At this point, you will find that the mistakenly deleted data has been restored and the replication is normal. Because according to the principle of GTID, the backup of slave1 can be synchronized with Master directly.

Copy the code

One thing to note in backup is that in the instance where GTID is enabled for backup, you need to specify the-- set-gtid-purged parameter, otherwise warning will be reported:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events

The backup file will appear:

SET @ @ GLOBAL.GTID_PURGED='4e659069-3cd8-11e5-9a49-001c4270714e:1-483'

When restoring, you will be required to reset master on the instance first, otherwise an error will be reported:

Warning: Using a password on the command line interface can be insecure.

ERROR 1840 (HY000) at line 24: @ @ GLOBAL.GTID_PURGED can only be set when @ @ GLOBAL.GTID_EXECUTED is empty.

Specify the-- set-gtid-purged=ON parameter, which will be executed when GTID_PURGED appears, and the slave library can be directly change to the master without other operations. More information about GTID can be found in the official documentation.

Summary:

GTID is the global transaction ID (global transaction identifier), which was originally implemented by google and was not added by the official MySQL until 5. 6. If there is only one Master and one Slave in the master-slave structure, there will be no advantage for the GTID, but for the structural advantages of more than two masters, the new master can be switched without data loss. When using GTID, you should pay attention to: before building master-slave replication, perform some operations (such as data cleaning, etc.) on an instance that will become a master. Through GTID replication, these operations before the master-slave is established will also be replicated to the slave server, causing replication failure. That is, replication via GTID starts with the transaction log that starts first, even if these operations are performed before replication. For example, performing some drop and delete cleanup operations on server1, and then performing change operations on server2 will make server2 also perform server1 cleanup operations.

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.