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

环境介绍:

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与半同步，能带来服务器的高可用性。而且这种方式，是一种配置简单，成本低廉的工作方式。半同步的配置，可参见如下资料。