Mysql5.6.21-gtid Master-Slave switching

Lab Environment: 3 Servers, a,b,c

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/87/wKiom1UviUWy0ytBAADedPcz6W4313.jpg "title=" 01.png "alt=" Wkiom1uviuwy0ytbaadedpcz6w4313.jpg "/>

a-master:192.168.112.131

b-slave:192.168.112.132

c-slave:192.168.112.129

One: View the current replication status.

1: View A,b,c server Tid.

A-master Server: 192.168.112.131

Sql>show Master status;
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/83/wKioL1Uvis7Cgn0SAAEpv1-JusM486.jpg "title=" 02.png "alt=" Wkiol1uvis7cgn0saaepv1-jusm486.jpg "/>

B-slave Server: 192.168.112.132

Sql>show slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/87/wKiom1UviZPgcqu8AAGKkHqzw6o526.jpg "title=" 03.png "alt=" Wkiom1uvizpgcqu8aagkkhqzw6o526.jpg "/>

C-slave Server: 192.168.112.132

Sql>show slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/83/wKioL1UvivuBDkepAAGKkHqzw6o186.jpg "title=" 03.png "alt=" Wkiol1uvivubdkepaagkkhqzw6o186.jpg "/>

Second: Analog data inconsistency, a master server down.

1:c-salve disconnects the master and slave.

Sql>stop slave;

2:a-master Insert new data.

Sql>create table t13 (id int);

Sql>create table t14 (id int);

Sql>create table t15 (id int);

At this point, we can see that the C server is missing 13-15 tables of data. b server is normal.

3: Simulate a server outage, directly shut down a server's MySQL service.

$sh: /scripts/mysql_shutdown.sh

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/87/wKiom1UviojAvAL1AACReuNhzGo125.jpg "title=" 05.png "alt=" Wkiom1uviojaval1aacreunhzgo125.jpg "/>

4: View B Server Status.

Sql>show slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/83/wKioL1Uvi_rT3InJAAKrE_IfITQ013.jpg "title=" 06.png "alt=" Wkiol1uvi_rt3injaakre_ifitq013.jpg "/>

Three: Promote B-slave as the main service.

1: Stop the slave service on the B server

Sql>stop slave;

2:C server changes the connection home server IP address

Sql>change Master to master_host= ' 192.168.112.132 ', master_user= ' ruser ', master_password= ' Rpass ', master_auto_ Position=1;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/87/wKiom1Uvir6ifjj2AACmNB2DIvs454.jpg "title=" 07.png "alt=" Wkiom1uvir6ifjj2aacmnb2divs454.jpg "/>

The 3:C server starts the slave service and views the status of the C server.

Sql>start slave;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/83/wKioL1UvjCqi8cDmAAA8o1tiqgs752.jpg "title=" 08.png "alt=" Wkiol1uvjcqi8cdmaaa8o1tiqgs752.jpg "/>

Sql>show slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/87/wKiom1UviumAqBMvAAL2IfH550Q592.jpg "title=" 09.png "alt=" Wkiom1uviumaqbmvaal2ifh550q592.jpg "/>

Here is a question, why the value of Executed_gtid_set on the C server is still: 7edc6fd5-e1bf-11e4-8842-000c29e512d6:1-16,

According to the theory, Gtid is not the only one? is b and a server gtid the same?? Remember the relay log that is executed here. The Gtid value of the relay log record must be on the original a server.

4: View the new data that the C server copied from the B server.

Sql>use Testhuang;

Sql>show tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/87/wKiom1UvixSSbUQ7AAC6H2peyBM323.jpg "title=" 10.png "alt=" Wkiom1uvixssbuq7aac6h2peybm323.jpg "/>

Additional Information Description:

The current environment is asynchronous architecture (non-semi-synchronous), the production situation may be the C server replicated more data, B has not been able to replicate, a server

It's down. We should choose the server with the highest TID value in the node as the primary server. If the mandatory B server to do the main, we should also let C first take over the main, B service connection C, the data are synchronized successfully, then switch.

Back to that question, why Executed_gtid_set is still the original value. Let's do an experiment first.

5:B new primary server to add new data.

Sql>create table t16 (id int);

Sql>create table t17 (id int);

Sql>create table t18 (id int);

6: At this time, we execute show Master status;

Sql>show Master status;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/83/wKioL1UvjKOwusoIAAFVfZKXfhw092.jpg "title=" 11.png "alt=" Wkiol1uvjkowusoiaafvfzkxfhw092.jpg "/>7d055966-e1bf-11e4-8842-000c2976947c:1-3

7edc6fd5-e1bf-11e4-8842-000c29e512d6:1-16

Have you found that the value of Gtid has changed, there are two lines, "," separated by the gtid of the only theory? Because the previous t13-t15 was the original Gtid transaction. So when the C server is connected to Server B, the information that is synchronized is the Gtid value of the a server before any data is added. We can synchronize relay log view.

View relay logs on the 7:C server:

See the Binlog log file here, depending on the situation.

$/usr/local/mysql56/bin/mysqlbinlog mysql-relay-bin.000002

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/83/wKioL1UvjL7hsZj_AATucd4DpmE246.jpg "title=" 12.png "alt=" Wkiol1uvjl7hszj_aatucd4dpme246.jpg "/>

We can find the relay log used on the C server. It can be concluded that the Binlog log on the B server is the same.

Because the log is pulled down from the B server, do not believe can go to see, so it is consistent with the same firm has nodes

The Gtid values are consistent. We will also prove it more fully in the following case.

Four: A server recovery, join from node.

1: Start the a server MySQL service.

$sh: /scripts/mysql_startup.sh

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/83/wKioL1UvjOeSSj98AADBRcnE1PU383.jpg "title=" 13.png "alt=" Wkiol1uvjoessj98aadbrcne1pu383.jpg "/>

2: View current status

Sql>show Master status;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/87/wKiom1Uvi5_iVcOaAAFQe1K2ZrQ213.jpg "title=" 14.png "alt=" wkiom1uvi5_ivcoaaafqe1k2zrq213.jpg "/> can see the Gtid value of a server, is 1-16. No new primary server is currently connected.

3: Connect the new database B.

sql> changeMaster to master_host= ' 192.168.112.132 ', master_user= ' ruser ', master_password= ' Rpass ', Master_auto _position=1;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/87/wKiom1Uvi7SyecJbAACTtfA5spM917.jpg "title=" 15.png "alt=" Wkiom1uvi7syecjbaacttfa5spm917.jpg "/>

4: Start the slave service.

Sql>start slave;

5: View data Synchronization status.

Sql>use Testhuang;

Sql>start tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/83/wKioL1UvjSDRrePYAAD3_GPCDU0706.jpg "title=" 16.png "alt=" Wkiol1uvjsdrrepyaad3_gpcdu0706.jpg "/>

The data has been synchronized with t16-t18.

6: View slave status

Sql>show slave Status\g

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/83/wKioL1UvjTbyhM8VAANpN0yEIvo337.jpg "title=" 17.png "alt=" Wkiol1uvjtbyhm8vaanpn0yeivo337.jpg "/>

We can see that the executed_gtid_set here has only one value, which corresponds exactly to the theoretical value.

This article is from the "Lonely Ops" blog, so be sure to keep this source http://jiajinh.blog.51cto.com/2085098/1633589

Mysql5.6.21-gtid Master-Slave switching