单台数据库服务器无法满足性能和冗余性，现在我们需要添加2台新的服务器，并组建复制结构。
场景如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
现有的服务器server1，IP：192.168.1.1 port：13001。新增的服务器server2，IP：192.168.1.2 port:13001，server3，IP：192.168.1.3，port:3306，并使server2为新的主。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
实例
shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
--slave=slave_acc1@192.168.1.2:13001 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.
shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
--slave=slave_acc2@192.168.1.3:3306 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.3: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.
shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
--slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 health
# Checking privileges.
#
# Replication Topology Health:
+--------------+--------+---------+--------+------------+------------------------------+
| host | port | role | state | gtid_mode | health |
+--------------+--------+---------+--------+------------+------------------------------+
| 192.168.1.1 | 13001 | MASTER | UP | ON | OK |
| 192.168.1.2 | 13001 | SLAVE | UP | ON | Slave delay is NNN seconds |
| 192.168.1.3 | 3306 | SLAVE | UP | ON | Slave delay is NNN seconds |
+--------------+--------+---------+--------+------------+------------------------------+
# ...done.
shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
--slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 health
# Checking privileges.
#
# Replication Topology Health:
+--------------+--------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+--------------+--------+---------+--------+------------+---------+
| 192.168.1.1 | 13001 | MASTER | UP | ON | OK |
| 192.168.1.2 | 13001 | SLAVE | UP | ON | OK |
| 192.168.1.3 | 3306 | SLAVE | UP | ON | OK |
+--------------+--------+---------+--------+------------+---------+
# ...done.
shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
--slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 \
--new-master=slave_acc1@localhost:13002 --demote-master switchover
# Checking privileges.
# Performing switchover from master at 192.168.1.1:13001 to slave at 192.168.1.2:13001.
# 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 |
+--------------+--------+---------+--------+------------+---------+
| 192.168.1.2 | 13001 | MASTER | UP | ON | OK |
| 192.168.1.1 | 13001 | SLAVE | UP | ON | OK |
| 192.168.1.3 | 3306 | SLAVE | UP | ON | OK |
+--------------+--------+---------+--------+------------+---------+
在上面的例子中，使用mysqlreplicate 工具来设置现有的服务器和新增的服务器之间的复制拓扑结构。使用-b标记从主二进制日志的第一个事件开始复制。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
然后，使用mysqlrpladmin 工具来指定主从服务器和使用health命令来检查复制的状态。由于有大量的二进制日志需要同步，新的从需要一段时间才赶得上主，因此会有延迟的健康信息。一段时间后，health列都是OK的状态。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
同步完成后，再次使用mysqlrpladmin工具来切换到新主，使用 --new-master指定到server2，并且使用--demote-master选项将server1降级为slave，将server3成为server2的从。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
权限
m_account 用户需要的权限有：对mysql数据库的SELECT 和 INSERT 权限，以及REPLICATION SLAVE, REPLICATION CLIENT ，GRANT OPTION权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
slave_acc 用户需要SUPER 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
对于复制用户， --rpl-user选项使用的。要么自动创建要么以存在，需要有 REPLICATION SLAVE权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
为了成功运行mysqlrpladmin 工具的health命令，m_account 需要额外的SUPER权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
对于switchover 命令，所有的用户需要有SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE 和 REPLICATION SLAVE 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
小技巧
mysqlrpladmin 工具可以使用 --discover-slaves-login选项自动检测从，而不是手动指定从。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
mysqlrpladmin 工具可以指定外部脚本在故障转移和切换操作前后执行，分别使用 --exec-before 和 --exec-after 选项。注意，在故障转移和切换操作后执行的脚本，也就是 --exec-after选项指定的脚本，执行的前提条件是故障转移和切换操作是成功的。文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/add-new-server-and-change-master-role/
评论