mysql utilities 中文乱码_MySQL管理工具MySQL Utilities — 恢复有故障的主(50)

在故障成功转移后，往往需要恢复到最初的复制拓扑结构，并且使有故障的主重新再次为主。

假设故障转移后的拓扑结构是这样的，主server2:3312，从server3:3313, server4:3314, server:3315，现在需要将原来的主server1:3311再次成为主。手工执行这个过程的话，是非常繁琐的甚至还会导致复制失败，使用MySQL Utilities工具只需三个简单的步骤来搞定。

实例

解决上述问题需要考虑下面几个问题。首先必须停止运行mysqlfailover 实例，启动恢复旧的主 server1:3311。接着，设置旧的主为新主server2:3312的从。

shell> mysqlreplicate --master=root@server2:3312 --slave=root@server1:3311 -rpl-user=rpl:rpl

# master on localhost: ... connected.

# slave on localhost: ... connected.

# Checking for binary logging on master...

# Setting up replication...

# ...done.

1

2

3

4

5

6

shell>mysqlreplicate--master=root@server2:3312--slave=root@server1:3311-rpl-user=rpl:rpl

# master on localhost: ... connected.

# slave on localhost: ... connected.

# Checking for binary logging on master...

# Setting up replication...

# ...done.

接下来，切换到之前的主

shell> mysqlrpladmin --master=root@server2:3312 \

--slaves=root@server2:3313,root@server4:3314,root@server5:3315 \

--rpl-user=rpl:rpl --new-master=root@server1:3311 --demote-master switchover

# Checking privileges.

# Performing switchover from master at server2:3312 to slave at server1:3311.

# Checking candidate slave prerequisites.

# Checking slaves configuration to master.

# Waiting for slaves to catch up to old master.

# Stopping slaves.

# Performing STOP on all slaves.

# Demoting old master to be a slave to the new master.

# Switching slaves to new master.

# Starting all slaves.

# Performing START on all slaves.

# Checking slaves for errors.

# Switchover complete.

#

# Replication Topology Health:

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

| host | port | role | state | gtid_mode | health |

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

| server1 | 3311 | MASTER | UP | ON | OK |

| server2 | 3312 | SLAVE | UP | ON | OK |

| server3 | 3313 | SLAVE | UP | ON | OK |

| server4 | 3314 | SLAVE | UP | ON | OK |

| server5 | 3315 | SLAVE | UP | ON | OK |

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

# ...done.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

shell>mysqlrpladmin--master=root@server2:3312\

--slaves=root@server2:3313,root@server4:3314,root@server5:3315\

--rpl-user=rpl:rpl--new-master=root@server1:3311--demote-masterswitchover

# Checking privileges.

# Performing switchover from master at server2:3312 to slave at server1:3311.

# Checking candidate slave prerequisites.

# Checking slaves configuration to master.

# Waiting for slaves to catch up to old master.

# Stopping slaves.

# Performing STOP on all slaves.

# Demoting old master to be a slave to the new master.

# Switching slaves to new master.

# Starting all slaves.

# Performing START on all slaves.

# Checking slaves for errors.

# Switchover complete.

#

# Replication Topology Health:

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

|host|port|role|state|gtid_mode|health|

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

|server1|3311|MASTER|UP|ON|OK|

|server2|3312|SLAVE|UP|ON|OK|

|server3|3313|SLAVE|UP|ON|OK|

|server4|3314|SLAVE|UP|ON|OK|

|server5|3315|SLAVE|UP|ON|OK|

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

# ...done.

恢复到初始的复制拓扑，并重新启动mysqlfailover (需要使用--force选项)。

shell> mysqlfailover --master=root@server1:3311 \

--slaves=root@server2:3312,root@server3:3313,root@server4:3314,server5:3315 \

--log=log.txt --rpl-user=rpl:rpl --force

# Checking privileges.

MySQL Replication Failover Utility

Failover Mode = auto Next Interval = Sat Jul 27 02:17:12 2013

Master Information

------------------

Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB

master-bin.000002 151

GTID Executed Set

None

Replication Health Status

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

| host | port | role | state | gtid_mode | health |

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

| server1 | 3311 | MASTER | UP | ON | OK |

| server2 | 3312 | SLAVE | UP | ON | OK |

| server3 | 3313 | SLAVE | UP | ON | OK |

| server4 | 3314 | SLAVE | UP | ON | OK |

| server5 | 3315 | SLAVE | UP | ON | OK |

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

Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

shell>mysqlfailover--master=root@server1:3311\

--slaves=root@server2:3312,root@server3:3313,root@server4:3314,server5:3315\

--log=log.txt--rpl-user=rpl:rpl--force

# Checking privileges.

MySQLReplicationFailoverUtility

FailoverMode=autoNextInterval=SatJul2702:17:122013

MasterInformation

------------------

BinaryLogFilePositionBinlog_Do_DBBinlog_Ignore_DB

master-bin.000002151

GTIDExecutedSet

None

ReplicationHealthStatus

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

|host|port|role|state|gtid_mode|health|

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

|server1|3311|MASTER|UP|ON|OK|

|server2|3312|SLAVE|UP|ON|OK|

|server3|3313|SLAVE|UP|ON|OK|

|server4|3314|SLAVE|UP|ON|OK|

|server5|3315|SLAVE|UP|ON|OK|

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

Q-quitR-refreshH-healthG-GTIDListsU-UUIDsL-logentries

权限

用户需要有配置复制的权限。

小技巧

一定要等待失败的主赶上新的主，以免丢失数据，可以使用mysqlrpladmin 工具的health命令查看。