MySQL从服务器升为主服务器_mysql5.6主从复制第三部分[把从服务器提升为主服务器](2)...-CSDN博客

本文链接：https://blog.csdn.net/weixin_30950075/article/details/113680921

*************************** 1. ROW ***************************

Slave_IO_State: Connecting TO master

Master_Host: 127.0.0.1

Master_User: repl_user

Master_Port: 5610

Connect_Retry: 60

Master_Log_File: black-bin.000001

Read_Master_Log_Pos: 1809

Relay_Log_File: mysql_sandbox5613-relay-bin.000002

Relay_Log_Pos: 1382

Relay_Master_Log_File: black-bin.000001

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

......

Last_IO_Errno: 2003

Last_IO_Error: error connecting TO master 'repl_user@127.0.0.1:5610' - retry-TIME: 60 retries: 1

Last_SQL_Errno: 0

Last_SQL_Error:

场景构造完毕。

主服务器black已经挂掉，从服务器blue上面缺少gtid 31和32， green上面缺少gtid 32， red与主服务器数据相同。

准备把green(端口为5612)提升为主服务器。

新主green:

mysql [localhost] {msandbox} (test) > stop slave;

Query OK, 0 ROWS affected (0.00 sec)

从blue:

mysql [localhost] {msandbox} (test) > stop slave;

Query OK, 0 ROWS affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=5612, MASTER_USER='repl_user',MASTER_PASSWORD='billy', master_auto_position=1;

Query OK, 0 ROWS affected, 2 warnings (0.01 sec)

mysql [localhost] {msandbox} (test) >

mysql [localhost] {msandbox} (test) > START slave;

Query OK, 0 ROWS affected, 1 warning (0.01 sec)

mysql [localhost] {msandbox} (test) > SHOW slave STATUS \G

*************************** 1. ROW ***************************

Slave_IO_State: Waiting FOR master TO send event

Master_Host: 127.0.0.1

Master_User: repl_user

Master_Port: 5612

Connect_Retry: 60

Master_Log_File: green-bin.000004

Read_Master_Log_Pos: 5939

Relay_Log_File: mysql_sandbox5611-relay-bin.000006

Relay_Log_Pos: 769

Relay_Master_Log_File: green-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.......

Master_Server_Id: 3

Master_UUID: 02b2ff66-7bd3-11e2-a4f6-00241db92e69

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

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: 05b47d41-7b10-11e2-9fff-00241db92e69:31

Executed_Gtid_Set: 05b47d41-7b10-11e2-9fff-00241db92e69:1-31

Auto_Position: 1

1 ROW IN SET (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT * FROM t;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 ROWS IN SET (0.00 sec)

看起来不错，新主green把gtid31对应的TRANSACTION发送给了从blue。

从red:

mysql [localhost] {msandbox} (test) > stop slave;

Query OK, 0 ROWS affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=5612, MASTER_USER='repl_user',MASTER_PASSWORD='billy', master_auto_position=1;

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

mysql [localhost] {msandbox} (test) > START slave;

Query OK, 0 ROWS affected, 1 warning (0.01 sec)

mysql [localhost] {msandbox} (test) > SHOW slave STATUS \G

*************************** 1. ROW ***************************

Slave_IO_State: Waiting FOR master TO send event

Master_Host: 127.0.0.1

Master_User: repl_user

Master_Port: 5612

Connect_Retry: 60

Master_Log_File: green-bin.000004

Read_Master_Log_Pos: 5939