mysql fileover_mysql utilities (1) 使用mysqlfailover配置自动master slave倒换

参考资料：http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilitieshttp://heylinux.com/archives/2776.html环境介绍:ma

参考资料：

环境介绍:

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