参考资料：
http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities
http://heylinux.com/archives/2776.html
环境介绍:
master: 192.168.56.101
slaves: 192.168.56.102, 192.168.56.103
2.安装mysql & utilities
rpm -aq|grep -i mysql
MySQL-client-5.6.14-1.el6.x86_64
MySQL-shared-5.6.14-1.el6.x86_64
MySQL-server-5.6.14-1.el6.x86_64
MySQL-devel-5.6.14-1.el6.x86_64
安装connector-python
rpm -ivh mysql-connector-python-1.1.4-1.el6.noarch.rpm
安装mysql utilities
$ tar -xvf mysql-utilities-1.3.6.tar.gz $ cd mysql-utilities-1.3.6/ $ python ./setup.py build $ sudo python ./setup.py install
3. 配置 /etc/my.cnf
master-192.168.56.101
[mysqld]
skip-host-cache
skip-name-resolve
# basic setting
datadir = /var/lib/mysql/
#tmpdir = /var/lib/mysql/tmp
socket = /var/lib/mysql/mysqld.sock
port = 3306
pid-file = /var/lib/mysql/mysqld.pid
# innodb setting
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /var/lib/mysql/bin-log-mysqld
log-bin-index = /var/lib/mysql/bin-log-mysqld.index
innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
binlog-do-db = testdb
replicate-do-db = testdb
# server id
server-id=1
# gtids setting
binlog-format = ROW
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
report-host = 192.168.56.101
report-port = 3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
# other settings
[mysqld_safe]
#log-error = /var/lib/mysql/mysqld.log
pid-file = /var/lib/mysql/mysqld.pid
open-files-limit = 8192
[mysqlhotcopy]
interactive-timeout
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
default-character-set = utf8
slave1: 192.168.56.102
[mysqld]
skip-host-cache
skip-name-resolve
# basic setting
datadir = /var/lib/mysql/
#tmpdir = /var/lib/mysql/tmp
socket = /var/lib/mysql/mysqld.sock
port = 3306
pid-file = /var/lib/mysql/mysqld.pid
# innodb setting
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /var/lib/mysql/bin-log-mysqld
log-bin-index = /var/lib/mysql/bin-log-mysqld.index
innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
binlog-do-db = testdb
replicate-do-db = testdb
# server id
server-id=3
# gtids setting
binlog-format = ROW
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
report-host = 192.168.56.102
report-port = 3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
# other settings
[mysqld_safe]
#log-error = /var/lib/mysql/mysqld.log
pid-file = /var/lib/mysql/mysqld.pid
open-files-limit = 8192
[mysqlhotcopy]
interactive-timeout
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
default-character-set = utf8
slave2: 192.168.56.103
[mysqld]
skip-host-cache
skip-name-resolve
# basic setting
datadir = /var/lib/mysql/
#tmpdir = /var/lib/mysql/tmp
socket = /var/lib/mysql/mysqld.sock
port = 3306
pid-file = /var/lib/mysql/mysqld.pid
# innodb setting
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /var/lib/mysql/bin-log-mysqld
log-bin-index = /var/lib/mysql/bin-log-mysqld.index
innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
binlog-do-db = testdb
replicate-do-db = testdb
# server id
server-id=2
# gtids setting
binlog-format = ROW
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
report-host = 192.168.56.103
report-port = 3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
# other settings
[mysqld_safe]
#log-error = /var/lib/mysql/mysqld.log
pid-file = /var/lib/mysql/mysqld.pid
open-files-limit = 8192
[mysqlhotcopy]
interactive-timeout
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
default-character-set = utf8
4.创建授权用户 root@'%' 以便通过 mysqlreplicate 来进行主从复制的配置，在所有服务器上：
$ mysql -uroot
mysql> grant all on *.* to root@'%' identified by 'pass' with grant option;
mysql> quit;
5. 创建复制所需的用户，在所有服务器上：
mysql -uroot
mysql> grant replication slave on *.* to 'rpl'@'%' identified by 'rpl';
mysql> quit;
6.配置主从复制，可以从何意一台服务器 mysqlreplicate --master=root:pass@'192.168.56.101':3306 --slave=root:pass@'192.168.56.102':3306 --rpl-user=rpl:rpl
mysqlreplicate --master=root:pass@'192.168.56.101':3306 --slave=root:pass@'192.168.56.103':3306 --rpl-user=rpl:rpl
7. 验证主从复制的数据完整性
说明，my.cnf内配置了如下两个参数，这样所有的复制只会针对testdb
binlog-do-db = testdb
replicate-do-db = testdb
[root@master-mysql56 ~]$ mysql -uroot
1 mysql> create database testdb;
2 mysql> quit;
[root@master-mysql56 ~]$ mysql -uroot -ppass -h'192.168.56.102' -e 'show databases;'
1 +-------------------+
2 | Database |
3 +-------------------+
4 | information_schema|
5 | mysql |
6 | performance_schema|
7 | test |
8 | testdb |
9 +-------------------+
[root@master-mysql56 ~]$ mysql -uroot -ppass -h'192.168.56.103' -e 'show databases;'
1 +-------------------+
2 | Database |
3 +-------------------+
4 | information_schema|
5 | mysql |
6 | performance_schema|
7 | test |
8 | testdb |
9 +-------------------+
[root@master-mysql56 ~]# mysqlrplshow --master=rpl:rpl@'192.168.56.101':3306 --discover-slaves-login=root:pass
# master on 192.168.56.101: ... connected.
# Finding slaves for master: 192.168.56.101:3306
# Replication Topology Graph
192.168.56.101:3306 (MASTER)
|
+--- 192.168.56.103:3306 - (SLAVE)
|
+--- 192.168.56.102:3306 - (SLAVE)
8.关闭master,测试autofailover
[root@master-mysql56 ~]# mysqlfailover --master=root:pass@'192.168.56.101':3306 --discover-slaves-login=root:pass --rediscover
# Discovering slaves for master at 192.168.56.101:3306
# Discovering slave at 192.168.56.103:3306
# Found slave: 192.168.56.103:3306
# Discovering slave at 192.168.56.102:3306
# Found slave: 192.168.56.102:3306
# Checking privileges.
# WARNING: Errant transaction(s) found on slave(s).
# - For slave '192.168.56.103@3306': 82133808-711e-11e3-a47d-080027fd7bfb:1-2
# - For slave '192.168.56.102@3306': 3a2cb8a9-711e-11e3-a47b-0800277f572f:1-2
# Discovering slaves for master at 192.168.56.101:3306
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Tue Dec 31 02:23:18 2013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
bin-log-mysqld.00000 191 testdb
GTID Executed Set
6feccbf3-711d-11e3-a476-08002798240e:1-6
WARNING: Errant transaction(s) found on slave(s).
Replication Health Status
+-----------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.56.101 | 3306 | MASTER | UP | ON | OK |
| 192.168.56.102 | 3306 | SLAVE | UP | ON | OK |
| 192.168.56.103 | 3306 | SLAVE | UP | ON | OK |
+-----------------+-------+---------+--------+------------+---------+
然后，执行命令的终端就挂起了，需要另外开启一个终端来关闭master：
[dong.guo@demoenv-trial-1 ~]$ mysqladmin -uroot -ppass shutdown
在刚挂起的终端可以看到
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 192.168.56.103:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at 192.168.56.103:3306
Failover console will restart in 5 seconds.
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Tue Dec 31 02:31:34 2013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
bin-log-mysqld.00000 231 testdb
GTID Executed Set
6feccbf3-711d-11e3-a476-08002798240e:1-6 [...]
Replication Health Status
+-----------------+-------+---------+--------+------------+------------------------------------------+
| host | port | role | state | gtid_mode | health |
+-----------------+-------+---------+--------+------------+------------------------------------------+
| 192.168.56.103 | 3306 | MASTER | UP | ON | OK |
| 192.168.56.102 | 3306 | SLAVE | UP | ON | Slave has 2 transactions behind master. |
+-----------------+-------+---------+--------+------------+------------------------------------------+
新的结构图如下：
[root@master-mysql56 ~]# mysqlrplshow --master=rpl:rpl@'192.168.56.103':3306 --discover-slaves-login=root:pass
# master on 192.168.56.103: ... connected.
# Finding slaves for master: 192.168.56.103:3306
# Replication Topology Graph
192.168.56.103:3306 (MASTER)
|
+--- 192.168.56.102:3306 - (SLAVE)
对于 auto-failover 的简短总结：
它包含在mysql-utilities这个软件包中；
它只能工作在MySQL 5.6 版本上，因为需要GITDs的支持；
它可以自动选取一个slave作为新的master，当现有的master死掉以后；
但是它不能自动将旧的master恢复，并且在手动恢复了旧的master之后也会停止工作。
可以结合mysqlfailover与半同步，能带来服务器的高可用性。而且这种方式，是一种配置简单，成本低廉的工作方式。半同步的配置，可参见如下资料。
http://dev.mysql.com/doc/refman/5.6/en/replication-semisync-installation.html
