mysql从服务器成为主_MySQL 5.6主从复制第三部分[把从服务器提升为主服务器]

主服务器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

Relay_Log_File: mysql_sandbox5613-relay-bin.000006

Relay_Log_Pos: 408

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:

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

Auto_Position: 1

1 ROW IN SET (0.00 sec)

再看一下新主green有没有获取到gtid 32

mysql [localhost] {msandbox} (test) > SHOW master STATUS;

+------------------+----------+--------------+------------------+-------------------------------------------+

| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------------------------------+

| green-bin.000004 | 5939 | | | 05b47d41-7b10-11e2-9fff-00241db92e69:1-31 |

+------------------+----------+--------------+------------------+-------------------------------------------+

1 ROW IN SET (0.00 sec)

嗯，失败了，从red并没有把gtid32对应的transaction发给新主green。 gtid也不是万能的，看来我对它期望过高了。

不过与传统的master binlog file , master binlog pos比起来，还是要智能一些的。

找出同步最成功的那台从服务器，直接把它提升为主即可。

如果硬要指定某一台从服务器提升为主， 先change它的master到同步最成功的那台从服务器， 等把gtid全部自动补全了，就可以把它提升为主了。

ps:

pt-table-checksum 不能用于row-based replication。

ps2:

为了避免新主green在重启之后，又继续自动成为black的slave, 需要在green的配置文件里面加上 skip-slave-start