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

Today, I will talk to you about how to understand the GTID and self-adding data tests in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

In an article yesterday, many netizens confirmed some details to me today. I think I happened to be reading GTID recently. We can rub it together and talk about it.

The concept of GTID seems simple, but in fact, there are still many ways.

Let's look at the scenarios that need to be considered for GTID changes from an architectural design point of view.

The change of GTID under the architecture mode of "one master and two slaves"

We will elaborate on the basis of the framework of one master and two slaves. We will use MHA's solution in this architectural mode.

If the Master node goes down at this time, MHA will enable the check mechanism.

At this point, the Slave 1 node will become the new Master,Slave 2 will reapply the data changes from Slave 1, how does the GTID change, and whether the Executed GTID Set of the library is one or two.

The scene continues to extend. If after the master library is started, it is assumed that it is a hardware problem, such as the power failure light, and the Master node is started, then how does the GTID change when the Master node is rejoined in the master-slave environment. This is the architecture diagram below.

And we continue to refine this problem, which is combined with the problem of self-increasing values. See if the data is inconsistent and cannot be copied in the way MySQL is implemented in this scenario. The combination of the two is a relatively complete test scenario. Of course I'm going to mark it as the first one, because there will be a second one.

Let's see how it works.

The practice of GTID under the architecture mode of "one master and two slaves"

One master and two slaves are identified as master (Master node), slave library 1 (Slave 1), and slave library 2 (Slave 2). The general test steps are as follows:

Initialize one master and two slaves

Master node initializes data and tests self-incrementing values

Configure MHA,Master node downtime

MHA switch, Slave 1 node is upgraded to master library, Slave 2 node is slave library

Master node startup

Master nodes join the master-slave replication environment

Step 1: initialize and get the GTID case of one master and two slaves

Step 1 is relatively simple and can be built using sandbox or quick scripting.

After the construction is completed, let's take a look at Gtid.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000001

Position: 1475

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7

The view of server_uuid is as follows:

Mysql > show global variables like 'server_uuid%'

+-- +

| | Variable_name | Value |

+-- +

| | server_uuid | 4f7b0b93-2400-11e7-99cb-782bcb377193 |

+-- +

3 rows in set (0. 01 sec) our subsequent tests will refer to this value.

The case of the Slave 1 node is as follows, which is significantly different from the server_uuid of the Master node. This information is available in the initialized directory auto.cnf.

Mysql > show global variables like 'server%'

+-- +

| | Variable_name | Value |

+-- +

| | server_id | 24802 | |

| | server_id_bits | 32 | |

| | server_uuid | 5433468e-2400-11e7-a834-782bcb377193 |

+-+-- + check the information of master status as follows, which shows that it is the same as the Gtid set value of Master node, which proves that the value of this Gtid is a unique identifier. Of course, from the full name of GTID, it is the global transaction ID.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000001

Position: 438

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7

1 row in set (0.00 sec) Let's take a look at the results of show slave status.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.127.128.78

Master_User: rpl_user

. . .

Retrieved_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:6-7

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7

. . .

1 row in set (0.00 sec)

Step 2: initialize the Master node and test the self-increment problem

Step 2 Let's initialize the Master node. Just create a database test

In the case of the create database test;Slave 1 node, you can see that the transaction ID sequence number at the end begins to increase.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000001

Position: 589

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: the initialization below 4f7b0b93-2400-11e7-99cb-782bcb377193:1-8 is the key. We will test the self-increment case to reproduce a classic problem. Create a table T1 and insert 3 records.

Mysql > drop table if exists T1

Mysql > create table T1 (id int auto_increment, an int, primary key (id)) engine=innodb

Mysql > insert into T1 values (1BI 2)

Mysql > insert into T1 values (null,2)

Mysql > insert into T1 values (null,2)

Mysql > select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

| | 2 | 2 |

| | 3 | 2 |

There is no doubt that the value of the self-increasing column at this time is 4.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) the data obtained by ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 on Slave 1 node and Slave 2 node are consistent, both 4

Then we make the following changes to delete the value of id=3 in the table. This situation is also easy to understand, that is, the self-increasing column will not change.

Mysql > delete from T1 where id=3

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

The same is true for Slave 1 nodes and Slave 2 nodes, with self-increment values of 4

Step 3: configure MHA,Master node downtime

For this step, please refer to the sandbox and MHA Quick Test (R12 Note, Day 32). There is a basic introduction to the configuration of MHA. You can use the following two scripts to do basic verification. App1.cnf is the basic configuration file. The content is roughly as follows:

[server default]

Manager_workdir=/home/mha/manager

Manager_log=/home/mha/manager/app1/manager.log

Port=24801-specified port

User=mha_test

Password=mha_test-need to be created in advance

Repl_user=rpl_user

Repl_password=rpl_pass

Master_ip_failover_script= / home/mha/conf/master_ip_failover2

# shutdown_script= / script/masterha/power_manager

# report_script= / script/masterha/send_report

# master_ip_online_change_script= / script/masterha/master_ip_online_change

[server1]

Hostname=10.127.128.78

Port=24801

Candidate_master=1

[server2]

Hostname=10.127.128.78

Candidate_master=1

Port=24802

[server3]

Hostname=10.127.128.78

Candidate_master=1

Mutual trust check of port=24803ssh.

# masterha_check_ssh-check of conf=app1.cnf master-slave replication.

# masterha_check_repl-conf=app1.cnf

After checking it correctly, we start the MHA manager service.

Nohup masterha_manager-- conf=/home/mha/conf/app1.cnf > / tmp/mha_manager.log 2 > & 1 &

Then we can find the corresponding process number and Kill it directly.

[root@grtest app1] # ps-ef | grep 24801

Mysql 2168 1918 0 14:29 pts/7 00:00:00 / usr/local/mysql/bin/mysqld-defaults-file=/home/data/s1/s1.cnf-basedir=/usr/local/mysql_5.7.17-datadir=/home/data/s1-plugin-dir=/usr/local/mysql_5.7.17/lib/plugin-user=mysql-log-error=/home/data/s1/grtest.err-pid-file=/home/data/s1/grtest.pid-- Socket=/home/data/s1/s1.sock-port=24801

Root 3623 12108 0 14:40 pts/7 00:00:00 grep 24801

[root@grtest app1] # kill-9 1918 2168 Let's briefly describe that after the Master node is killed, the increment value of table T1 of the main library will be 3 after startup, that is, the last max (id) + 1 starts calculation. And the self-increment value of the library is 4, how to balance this?

Step 4:MHA switch, Slave1 node is the main library

The whole switching process is done automatically, and MHA detects the heartbeat and then automatically starts switching the master-slave replication relationship. GTID is one thing to pay attention to in the whole process.

Started automated (non-interactive) failover.

Invalidated master IP address on 10.127.128.78 (10.127.128.78 virtual 24801)

Selected 10.127.128.78 (10.127.128.78 24802) as a new master.

10.127.128.78 (10.127.128.78 OK 24802): Applying all logs succeeded.

10.127.128.78 (10.127.128.78 OK 24802): Activated master IP address.

10.127.128.78 (10.127.128.78): OK: Slave started, replicating from 10.127.128.78 (10.127.128.78)

10.127.128.78 (10.127.128.78 virtual 24802): Resetting slave info succeeded.

Master failover to 10.127.128.78 (10.127.128.78 24802) completed successfully. So the Slave 1 node officially takes over the environment.

Check the information of the new main library Slave 1 node, which is the same GTID as the original Master node.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000001

Position: 1895

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: the part of 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14server_uuid is still the same.

Mysql > show global variables like 'server%'

+-- +

| | Variable_name | Value |

+-- +

| | server_id | 24802 | |

| | server_id_bits | 32 | |

| | server_uuid | 5433468e-2400-11e7-a834-782bcb377193 |

+-+-- + this place needs attention, that is to check the situation of self-incrementing column, because it is originally from the library, so the latest value is 4.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 in this case, the Slave 2 node will readjust the replication relationship

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.127.128.78

Master_User: rpl_user

Master_Port: 24802

. . .

Retrieved_Gtid_Set:

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

Auto_Position: 1

. . .

The view of the self-increment column is as follows:

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

There may be some confusion here, and there will be some errors in the understanding of GTID, we insert a row of data on the Slave 1 node.

Mysql > insert into T1 values (null,2); at this point, check the self-incrementing column as follows, incrementing gradually.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 needs to re-examine the situation of Gtid at this time. You can see that there is the server_uuid of the original Master node as well as the server_ UUID value of the current new main library.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000001

Position: 2133

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

5433468e-2400-11e7-a834-782bcb377193:1

The information of the new slave Slave 2 node is as follows:

Node 3:

Mysql >

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.127.128.78

Master_User: rpl_user

Master_Port: 24802

. . .

Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

5433468e-2400-11e7-a834-782bcb377193:1

Auto_Position: 1

. . .

So you can find that the future self-increment values of failover will not be affected, and GTID set will contain the current main library and the original main library information.

Step 5:Master node startup

The steps to start the Master node are relatively simple.

# / usr/local/mysql_5.7.17/bin/mysqld_safe-- defaults-file=/home/data/s1/s1.cnf & there are a lot of details to confirm after startup, one of which is information about master status.

Mysql > show slave status\ G

Empty set (0.00 sec)

Mysql > show master status\ G

* * 1. Row *

File: binlog.000002

Position: 190

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14 is obviously wrong because the master-slave replication relationship has not been adjusted.

Let's take a look at the self-increment value at this time. The tangled problem is that the self-increment is 3, while the self-increment of Slave 1 node and Slave 2 node is 5. 5.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Step: 6:Master nodes join the master-slave replication environment

Reconfigure the master-slave replication relationship:

CHANGE MASTER TO MASTER_HOST='10.127.128.78', MASTER_PORT=24802, MASTER_AUTO_POSITION=1, MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'; start the new slave library, and you will find that there will be two GTID after startup.

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.127.128.78

Master_User: rpl_user

Master_Port: 24802

. . .

Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1

Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

5433468e-2400-11e7-a834-782bcb377193:1

Auto_Position: 1

. . . At this time, check the self-increment column again. This step seems a little more complicated, but in fact, the new slave library will receive the data changes applied on the Slave 1 node, which is equivalent to inserting a record in the Master node, causing the self-increment column to continue to increase.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1-- id value restored

1 row in set (0. 00 sec) We can view the information of binlog for basic verification.

[root@grtest app1] # / usr/local/mysql_5.7.17/bin/mysqlbinlog-vv / home/data/s1/binlog.000002

...

BEGIN

/ *! * /

# at 310

# 170418 14:44:01 server id 24802 end_log_pos 352 Table_map: `test`.`t1` mapped to number 219

# at 352

# 170418 14:44:01 server id 24802 end_log_pos 392 Write_rows: table id 219 flags: STMT_END_F

BINLOG'

SbX1WBPiYAAAKgAAAGABAAAAANsAAAAAAAEABHRlc3QAAnQxAAIDAwAC

SbX1WB7iYAAAKAAAAIgBAAAAANsAAAAAAAEAAgAC//wEAAAAAgAAAA==

'/ *! * /

# INSERT INTO `test`.`t1`

# SET

# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2room2 / * INT meta=0 nullable=1 is_null=0 * /

# at 392

# 170418 14:44:01 server id 24802 end_log_pos 419 Xid = 19

Com _ MIT _ blank /

SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * /

DELIMITER

# End of log file

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/; so that the understanding of GTID will be clearer. The problem of self-increment will also be more clear, and it is true that data inconsistencies can be resolved at present.

After reading the above, do you have any further understanding of how to understand GTID and self-adding data tests in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.