mysql5.6.27怎么使用_mysql5.6.27主从从复制及切换

实验目的：M1->M2->S1级联复制结构更改为M1->S1->M2

M1:10.50.12.33

M2:10.50.12.34

S1:10.50.12.35

主从从级联环境搭建：

mysql安装略，需要注意的是要区别server-id，命名最好是ip尾号+端口号

以下操作默认mysql已经正常启动，但是还没有做主从级联复制

主从级联复制操作：

M1：

mysql> grant replication slave  on *.* to 'repl'@'10.50.12.%' identified by 'slavepass';

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 327

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

M2:

mysql> change master to master_host='10.50.12.33',master_user='repl',master_password='slavepass',master_port=3336,master_log_file='mysql-bin.000003',master_log_pos=327;

mysql> start slave;

mysql> show slave status\G

.....................................

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.......................................

至此，M1到M2主从复制搭建完成，接下来做M2->S1主从复制

M2:

mysql> grant replication slave  on *.* to 'repl'@'10.50.12.%' identified by 'slavepass';

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 327

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

S1:

mysql> change master to master_host='10.50.12.34',master_user='repl',master_password='slavepass',master_port=3336,master_log_file='mysql-bin.000003',master_log_pos=327;

mysql> start slave;

mysql> show slave status\G

.....................................

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.......................................

到现在为止，M1->M2->S1主从从级联复制环境已经搭建完毕，开始做切换

认为使复制报错，使M2和S1复制停止

M1：

create database gaoquan;

S1:

drop database gaoquan;

M2:

drop database gaoquan;

M1:

drop database gaoquan;

M2:

mysql> show slave status\G

Slave_IO_State: Waiting for master to send event

Master_Host: 10.50.12.33

Master_User: repl

Master_Port: 3336

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 521

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 386

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: No

mysql> stop slave;

S1:

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.50.12.34

Master_User: repl

Master_Port: 3336

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 521

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 386

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: No

mysql> stop slave;

到此时，从的复制已经停止，开始切换：

M1:

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 521

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

S1:

mysql> change master to master_host='10.50.12.33',master_user='repl',master_password='slavepass',master_port=3336,master_log_file='mysql-bin.000003',master_log_pos=521;

mysql> start slave;

mysql> grant replication slave  on *.* to 'repl'@'10.50.12.%' identified by 'slavepass';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 521

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

M2：

mysql> change master to master_host='10.50.12.35',master_user='repl',master_password='slavepass',master_port=3336,master_log_file='mysql-bin.000003',master_log_pos=521;

mysql> start slave;

切换完成，此时使用show slave status\G在M2和S1上查看，发现复制状态已经正常，且级联关系已经变为M1->S1->M2