[MySQL] MySQL 5.6 Replication and Failover

Preparation :
1.準備四台CentOS 6.x 64bit的VM
2.在上面先裝好MySQL在每個VM裡

1.安裝MySQL Utilities 及 MySQL Connector/Python
// for utilties
#python setup.py install
// for connector/python
#rpm -ivh mysql-connector-python-1.0.12-1.el6.noarch.rpm

2.設定my.cnf在每台MySQL上
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
server-id=1  #每台機器都要不一樣


log-bin=util1-bin.log  #每台機器可以有自己的與下一行都是一定要的，這樣才能failover
log-slave-updates=true
report-host=172.16.1.66  #用該台機器的ip
report-port=3306
socket=/tmp/mysql.sock
pid-file=/tmp/mysql.pid
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3.Grant privilege to root on each node
#/usr/local/mysql/bin/mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;"

4.Setup replication
#mysqlreplicate --master=root@172.16.1.66:3306 --slave=root@172.16.1.67:3306
#mysqlreplicate --master=root@172.16.1.66:3306 --slave=root@172.16.1.68:3306
#mysqlreplicate --master=root@172.16.1.66:3306 --slave=root@172.16.1.69:3306

5.Show replication
#mysqlrplshow --master=root@172.16.1.66 --discover-slaves-login=root

6.Check replication status
#mysqlrplcheck --master=root@172.16.1.66 --slave=root@172.16.1.67
#mysqlrplcheck --master=root@172.16.1.66 --slave=root@172.16.1.68
#mysqlrplcheck --master=root@172.16.1.66 --slave=root@172.16.1.69

7.Set auto-failover
#mysqlfailover --master=root@172.16.1.66 --discover-slaves-login=root --rediscover

8.Switch over (Optional)
#mysqlrpladmin --master=root@172.16.1.67:3306 --new-master=root@172.16.1.66:3306 --demote-master --discover-slaves-login=root switchover