You obviously need Python on your server (python-2.6.6-37.el6_4.x86_64 in mycase) and the Python MySQL library (mysql-connector-python-1.0.8-1.el6.noarch)
Master：db01
Slave： db02
[root@db01 software]# rpm -ivh mysql-connector-python-1.0.12-1.el6.noarch.rpm Preparing... ########################################### [100%] 1:mysql-connector-python ########################################### [100%] [root@db01 software]# [root@db01 software]# rpm -qa python mysql-connector-python python-2.6.6-37.el6_4.x86_64 mysql-connector-python-1.0.12-1.el6.noarch
installation MySQL Utilities
[root@db01 software]# tar zxvf mysql-utilities-1.3.5.tar.gz [root@db01 software]# cd mysql-utilities-1.3.5 [root@db01 mysql-utilities-1.3.5]# python setup.py build [root@db01 mysql-utilities-1.3.5]# python setup.py install
Configuration replication
[root@db01 ~]# mysqlreplicate --master=root:idontcare@db01:30306 --slave=root:idontcare@db02:30306 --rpl-user=rep:rep # master on db01: ... connected. # slave on db02: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. [root@db01 ~]#
Checks replication prerequisites
[root@db01 ~]# mysqlrplcheck --master=root:idontcare@db01:30306 --slave=root:idontcare@db02:30306 # master on db01: ... connected. # slave on db02: ... connected. Test Description Status --------------------------------------------------------------------------- Checking for binary logging on master [pass] Are there binlog exceptions? [pass] Replication user exists? [pass] Checking server_id values [pass] Checking server_uuid values [pass] Is slave connected to master? [pass] Check master information file [pass] Checking InnoDB compatibility [pass] Checking storage engines compatibility [pass] Checking lower_case_table_names settings [pass] Checking slave delay (seconds behind master) [pass] # ...done.
Display replication topology
[root@db01 ~]# mysqlrplshow --master=root:idontcare@db01:30306 --discover-slaves-login=root:idontcare -v # master on db01: ... connected. # Finding slaves for master: db01:30306 # Replication Topology Graph db01:30306 (MASTER) | +--- db02:30306 [IO: Yes, SQL: Yes] - (SLAVE) [root@db01 ~]#
Display replication health of the topology
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 health # Checking privileges. # # Replication Topology Health: +-------+--------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+--------+---------+--------+------------+---------+ | db01 | 30306 | MASTER | UP | ON | OK | | db02 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+ # ...done.
Stop Slave
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 stop # Checking privileges. # Performing STOP on all slaves. # Executing stop on slave db02:30306 Ok # ...done.
Start Slave
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 start # Checking privileges. # Performing START on all slaves. # Executing start on slave db02:30306 Ok # ...done.
Interactive health monitoring and failover
[root@db01 ~]# mysqlfailover --master=root:idontcare@db01:30306 --discover-slaves-login=root:idontcare --rediscover # Discovering slaves for master at db01:30306 # Discovering slave at db02:30306 # Found slave: db02:30306 # Checking privileges. # Discovering slaves for master at db01:30306 MySQL Replication Failover Utility Failover Mode = auto Next Interval = Wed Nov 6 16:25:07 2013 Master Information ------------------ Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB binlog-master.000006 231 GTID Executed Set 25d63f15-46b8-11e3-9002-080027e2e935:1-3 [...] Replication Health Status +-------+--------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+--------+---------+--------+------------+---------+ | db01 | 30306 | MASTER | UP | ON | OK | | db02 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+
Switchover select
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 elect # Checking privileges. # Electing candidate slave from known slaves. # Best slave found is located on db02:30306. # ...done.
To perform a switchover
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 --new-master=root:idontcare@db02:30306 --demote-master switchover # Checking privileges. # Performing switchover from master at db01:30306 to slave at db02:30306. # 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 | +-------+--------+---------+--------+------------+---------+ | db02 | 30306 | MASTER | UP | ON | OK | | db01 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+ # ...done.
Review display replication topology
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db02:30306 --slaves=root:idontcare@db01:30306 health # Checking privileges. # # Replication Topology Health: +-------+--------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+--------+---------+--------+------------+---------+ | db02 | 30306 | MASTER | UP | ON | OK | | db01 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+ # ...done.
[root@db01 ~]# mysqlrplshow --master=root:idontcare@db02:30306 --discover-slaves-login=root:idontcare -v # master on db02: ... connected. # Finding slaves for master: db02:30306 # Replication Topology Graph db02:30306 (MASTER) | +--- db01:30306 [IO: Yes, SQL: Yes] - (SLAVE)
To perform a switchover to be back to initial configuration use
[root@db01 ~]# mysqlrpladmin --master=root:idontcare@db02:30306 --slaves=root:idontcare@db01:30306 --new-master=root:idontcare@db01:30306 --demote-master switchover # Checking privileges. # Performing switchover from master at db02:30306 to slave at db01:30306. # 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 | +-------+--------+---------+--------+------------+---------+ | db01 | 30306 | MASTER | UP | ON | OK | | db02 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+ # ...done.
[root@db01 ~]# mysqlrplshow --master=root:idontcare@db01:30306 --discover-slaves-login=root:idontcare -v # master on db01: ... connected. # Finding slaves for master: db01:30306 # Replication Topology Graph db01:30306 (MASTER) | +--- db02:30306 [IO: Yes, SQL: Yes] - (SLAVE) [root@db01 ~]# [root@db01 ~]# [root@db01 ~]# mysqlrpladmin --master=root:idontcare@db01:30306 --slaves=root:idontcare@db02:30306 health # Checking privileges. # # Replication Topology Health: +-------+--------+---------+--------+------------+---------+ | host | port | role | state | gtid_mode | health | +-------+--------+---------+--------+------------+---------+ | db01 | 30306 | MASTER | UP | ON | OK | | db02 | 30306 | SLAVE | UP | ON | OK | +-------+--------+---------+--------+------------+---------+ # ...done.
Note:
The Master-Slave switchover can only be used in MySQL5.6 version.
You need add parameters:
gtid_mode=on
log_slave_updates=on
enforce_gtid_consistency=on
report_host=hostname
master-info-repository=TABLE
relay-log-info-repository=TABLE