mysql高可用主从MHA

淡淡的烟草味﹌ 2022-09-23 08:09 109阅读 0赞

MHA里有两个角色一个是node节点 一个是manager节点，要实现这个MHA，必须最少要三台数据库服务器，一主多备，即一台充当master，一台充当master的备份机，另外一台是从属机（淘宝也在该基础上进行了改造，目前淘宝TMHA已经支持一主一从），这里实验为了实现更好的效果使用四台机器，需要说明的是一旦主服务器宕机，备份机即开始充当master提供服务，如果主服务器上线也不会再成为master了，因为如果这样数据库的一致性就被改变了。

172.16.57.55      主机

172.16.57.56       从1

172.16.57.57       从2

172.16.57.88       manager

配置 ssh无密码登录

\#主机:master执行命令

[root@bd-qa-mongodb-55][root_bd-qa-mongodb-55]:~\#ssh-keygen -t rsa

[root@bd-qa-mongodb-55][root_bd-qa-mongodb-55]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.56][root_172.16.57.56]

[root@bd-qa-mongodb-55][root_bd-qa-mongodb-55]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.57][root_172.16.57.57]

[root@bd-qa-mongodb-55][root_bd-qa-mongodb-55]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.58][root_172.16.57.58]

从1

[root@bd-qa-oracle-56][root_bd-qa-oracle-56]:~\#ssh-keygen -t rsa

[root@bd-qa-oracle-56][root_bd-qa-oracle-56]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.56][root_172.16.57.56]

[root@bd-qa-oracle-56][root_bd-qa-oracle-56]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.57][root_172.16.57.57]

[root@bd-qa-oracle-56][root_bd-qa-oracle-56]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.58][root_172.16.57.58]

从2

[root@bd-qa-redis-57][root_bd-qa-redis-57]:~\#ssh-keygen -t rsa

[root@bd-qa-redis-57][root_bd-qa-redis-57]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.56][root_172.16.57.56]

[root@bd-qa-redis-57][root_bd-qa-redis-57]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.57][root_172.16.57.57]

[root@bd-qa-redis-57][root_bd-qa-redis-57]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.58][root_172.16.57.58]

manager

[root@bd-qa-vertica-58][root_bd-qa-vertica-58]:~\#ssh-keygen -t rsa

[root@bd-qa-vertica-58][root_bd-qa-vertica-58]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.56][root_172.16.57.56]

[root@bd-qa-vertica-58][root_bd-qa-vertica-58]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.57][root_172.16.57.57]

[root@bd-qa-vertica-58][root_bd-qa-vertica-58]:~\#ssh-copy-id -i ~/.ssh/id\_rsa.pub  [root@172.16.57.58][root_172.16.57.58]

二、规划mysql

1、安装mysql

\#在master、slave01和slave02上安装mysql服务

主库配置

\[ [root@T2][root_T2] mysql-5.7.9\]\# cat /etc/my.cnf

\[client\]

port = 3306

socket = /tmp/mysql.sock

\[mysqld\]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

server-id = 1

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

\#skip-name-resolve

\#skip-networking

back\_log = 300

max\_connections = 1000

max\_connect\_errors = 6000

open\_files\_limit = 65535

table\_open\_cache = 128

max\_allowed\_packet = 4M

binlog\_cache\_size = 1M

max\_heap\_table\_size = 8M

tmp\_table\_size = 16M

read\_buffer\_size = 2M

read\_rnd\_buffer\_size = 8M

sort\_buffer\_size = 8M

join\_buffer\_size = 8M

key\_buffer\_size = 4M

thread\_cache\_size = 8

query\_cache\_type = 1

query\_cache\_size = 8M

query\_cache\_limit = 2M

ft\_min\_word\_len = 4

log\_bin = mysql-bin

binlog\_format = ROW

log-slave-updates

expire\_logs\_days = 30

log\_error = /data/mysql/mysql-error.log

slow\_query\_log = 1

long\_query\_time = 1

slow\_query\_log\_file = /data/mysql/mysql-slow.log

performance\_schema = 0

explicit\_defaults\_for\_timestamp

\#lower\_case\_table\_names = 1

skip-external-locking

default\_storage\_engine = InnoDB

\#default-storage-engine = MyISAM

innodb\_file\_per\_table = 1

innodb\_open\_files = 500

innodb\_buffer\_pool\_size = 64M

innodb\_write\_io\_threads = 4

innodb\_read\_io\_threads = 4

innodb\_thread\_concurrency = 0

innodb\_purge\_threads = 1

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_log\_buffer\_size = 2M

innodb\_log\_file\_size = 32M

innodb\_log\_files\_in\_group = 3

innodb\_max\_dirty\_pages\_pct = 90

innodb\_lock\_wait\_timeout = 120

bulk\_insert\_buffer\_size = 8M

myisam\_sort\_buffer\_size = 8M

myisam\_max\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

interactive\_timeout = 28800

wait\_timeout = 28800

\[mysqldump\]

quick

max\_allowed\_packet = 16M

\[myisamchk\]

key\_buffer\_size = 8M

sort\_buffer\_size = 8M

read\_buffer = 4M

write\_buffer = 4M

\[ [root@T2][root_T2] mysql-5.7.9\]\# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

\[ [root@T2][root_T2] mysql-5.7.9\]\# service mysqld start

Starting MySQL. SUCCESS!

\[ [root@T2][root_T2] mysql-5.7.9\]\# export  MYSQL\_HOME=/usr/local/mysql

\[ [root@T2][root_T2] mysql-5.7.9\]\# export PATH=$PATH:$MYSQL\_HOME/bin

从1库配置

\[ [root@T3][root_T3] mysql-5.7.9\]\# cat /etc/my.cnf

\[client\]

port = 3306

socket = /tmp/mysql.sock

\[mysqld\]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

server-id = 11

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

\#skip-name-resolve

\#skip-networking

back\_log = 300

max\_connections = 1000

max\_connect\_errors = 6000

open\_files\_limit = 65535

table\_open\_cache = 128

max\_allowed\_packet = 4M

binlog\_cache\_size = 1M

max\_heap\_table\_size = 8M

tmp\_table\_size = 16M

read\_buffer\_size = 2M

read\_rnd\_buffer\_size = 8M

sort\_buffer\_size = 8M

join\_buffer\_size = 8M

key\_buffer\_size = 4M

thread\_cache\_size = 8

query\_cache\_type = 1

query\_cache\_size = 8M

query\_cache\_limit = 2M

ft\_min\_word\_len = 4

log\_bin = mysql-bin

binlog\_format = ROW

log-slave-updates

expire\_logs\_days = 30

log\_error = /data/mysql/mysql-error.log

slow\_query\_log = 1

long\_query\_time = 1

slow\_query\_log\_file = /data/mysql/mysql-slow.log

performance\_schema = 0

explicit\_defaults\_for\_timestamp

\#lower\_case\_table\_names = 1

skip-external-locking

default\_storage\_engine = InnoDB

\#default-storage-engine = MyISAM

innodb\_file\_per\_table = 1

innodb\_open\_files = 500

innodb\_buffer\_pool\_size = 64M

innodb\_write\_io\_threads = 4

innodb\_read\_io\_threads = 4

innodb\_thread\_concurrency = 0

innodb\_purge\_threads = 1

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_log\_buffer\_size = 2M

innodb\_log\_file\_size = 32M

innodb\_log\_files\_in\_group = 3

innodb\_max\_dirty\_pages\_pct = 90

innodb\_lock\_wait\_timeout = 120

bulk\_insert\_buffer\_size = 8M

myisam\_sort\_buffer\_size = 8M

myisam\_max\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

interactive\_timeout = 28800

wait\_timeout = 28800

\[mysqldump\]

quick

max\_allowed\_packet = 16M

\[myisamchk\]

key\_buffer\_size = 8M

sort\_buffer\_size = 8M

read\_buffer = 4M

write\_buffer = 4M

\[ [root@T3][root_T3] mysql-5.7.9\]\# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

\[ [root@T3][root_T3] mysql-5.7.9\]\# service mysqld start

Starting MySQL. SUCCESS!

\[ [root@T3][root_T3] mysql-5.7.9\]\# export  MYSQL\_HOME=/usr/local/mysql

\[ [root@T3][root_T3] mysql-5.7.9\]\# export PATH=$PATH:$MYSQL\_HOME/bin

从2配置

\[ [root@T4][root_T4] mysql-5.7.9\]\# cat /etc/my.cnf

\[client\]

port = 3306

socket = /tmp/mysql.sock

\[mysqld\]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

server-id = 111

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

\#skip-name-resolve

\#skip-networking

back\_log = 300

max\_connections = 1000

max\_connect\_errors = 6000

open\_files\_limit = 65535

table\_open\_cache = 128

max\_allowed\_packet = 4M

binlog\_cache\_size = 1M

max\_heap\_table\_size = 8M

tmp\_table\_size = 16M

read\_buffer\_size = 2M

read\_rnd\_buffer\_size = 8M

sort\_buffer\_size = 8M

join\_buffer\_size = 8M

key\_buffer\_size = 4M

thread\_cache\_size = 8

query\_cache\_type = 1

query\_cache\_size = 8M

query\_cache\_limit = 2M

ft\_min\_word\_len = 4

log\_bin = mysql-bin

binlog\_format = ROW

log-slave-updates

expire\_logs\_days = 30

log\_error = /data/mysql/mysql-error.log

slow\_query\_log = 1

long\_query\_time = 1

slow\_query\_log\_file = /data/mysql/mysql-slow.log

performance\_schema = 0

explicit\_defaults\_for\_timestamp

\#lower\_case\_table\_names = 1

skip-external-locking

default\_storage\_engine = InnoDB

\#default-storage-engine = MyISAM

innodb\_file\_per\_table = 1

innodb\_open\_files = 500

innodb\_buffer\_pool\_size = 64M

innodb\_write\_io\_threads = 4

innodb\_read\_io\_threads = 4

innodb\_thread\_concurrency = 0

innodb\_purge\_threads = 1

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_log\_buffer\_size = 2M

innodb\_log\_file\_size = 32M

innodb\_log\_files\_in\_group = 3

innodb\_max\_dirty\_pages\_pct = 90

innodb\_lock\_wait\_timeout = 120

bulk\_insert\_buffer\_size = 8M

myisam\_sort\_buffer\_size = 8M

myisam\_max\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

interactive\_timeout = 28800

wait\_timeout = 28800

\[mysqldump\]

quick

max\_allowed\_packet = 16M

\[myisamchk\]

key\_buffer\_size = 8M

sort\_buffer\_size = 8M

read\_buffer = 4M

write\_buffer = 4M

注意这里必须都设置，log-slave-updates 以便切换

\[ [root@T4][root_T4] mysql-5.7.9\]\# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

\[ [root@T4][root_T4] mysql-5.7.9\]\# service mysqld start

Starting MySQL. SUCCESS!

\[ [root@T4][root_T4] mysql-5.7.9\]\# export  MYSQL\_HOME=/usr/local/mysql

\[ [root@T4][root_T4] mysql-5.7.9\]\# export PATH=$PATH:$MYSQL\_HOME/bin

2、配置master、slave01和slave02之间的主从复制

在MySQL5.6 的Replication配置中，master端同样要开启两个重要的选项，server-id和log-bin,并且选项server-id在全局架构中并且唯一，不能被其它主机使用，这里采用主机ip地址的最后一位充当server-id的值；slave端要开启relay-log，这里我们已经在配置文件配置 ;

3、在master、slave01上创建主从同步的账号。slave01是备用master，这个也需要建立授权用户。

mysql> set password=password('fafa123');

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on \*.\* to dba\_user@'%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

\[ [root@T2][root_T2] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant replication slave on \*.\* to 'backup'@'172.16.57.%' identified by 'backup';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

从1库设置

mysql> set password=password('fafa123');

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on \*.\* to dba\_user@'%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

\[ [root@T3][root_T3] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant replication slave on \*.\* to 'backup'@'172.16.57.%' identified by 'backup';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

4、在master上执行命令，查看master状态信息

\[root@T2 mysql-5.7.9\]\# mysql -uroot -pfafa123 -e 'show master status;'

Warning: Using a password on the command line interface can be insecure.

\+------------------+----------+--------------+------------------+-------------------+

\+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     1900 |              |                  |                   |

\+------------------+----------+--------------+------------------+-------------------+

5、在slave01和slave02上执行主从同步

\#slave01配置主从

mysql> change master to master\_host='172.16.57.55',master\_user='backup',master\_password='backup',master\_port=3306,master\_log\_file='mysql-bin.000002',master\_log\_pos=1900;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 172.16.57.55

Master\_User: backup

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 1900

Relay\_Log\_File: T3-relay-bin.000002

Relay\_Log\_Pos: 320

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 1900

Relay\_Log\_Space: 524

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 1

Master\_UUID: 8ae37b24-3c1f-11e6-bc4e-5254008cd584

Master\_Info\_File: /data/mysql/master.info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set:

Executed\_Gtid\_Set:

Auto\_Position: 0

Replicate\_Rewrite\_DB:

Channel\_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

出现此错误是sql不合法原因：

如：select \* from abc\\G;

或者

select \* from abc;;

\\G后面不需要再加分号;

正确写法是

mysql> show slave status\\G

\#slave02配置主从

mysql> set password=password('fafa123');

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on \*.\* to dba\_user@'%' identified by 'fafa123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

\[ [root@T4][root_T4] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant replication slave on \*.\* to 'backup'@'172.16.57.%' identified by 'backup';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

\[ [root@T4][root_T4] mysql-5.7.9\]\# mysql -uroot -pfafa123

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \\g.

Your MySQL connection id is 3

Server version: 5.7.9-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> change master to master\_host='172.16.57.55',master\_user='backup',master\_password='backup',master\_port=3306,master\_log\_file='mysql-bin.000002',master\_log\_pos=1900;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 172.16.57.55

Master\_User: backup

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000002

Read\_Master\_Log\_Pos: 1900

Relay\_Log\_File: T4-relay-bin.000002

Relay\_Log\_Pos: 320

Relay\_Master\_Log\_File: mysql-bin.000002

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB:

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 1900

Relay\_Log\_Space: 524

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 1

Master\_UUID: 8ae37b24-3c1f-11e6-bc4e-5254008cd584

Master\_Info\_File: /data/mysql/master.info

SQL\_Delay: 0

SQL\_Remaining\_Delay: NULL

Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates

Master\_Retry\_Count: 86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set:

Executed\_Gtid\_Set:

Auto\_Position: 0

Replicate\_Rewrite\_DB:

Channel\_Name:

1 row in set (0.00 sec)

\#实验到这里表示主从已经配置完成！

三、规划mha

1、创建mha管理用的复制账号，每台数据库(master、slave01、slave02)上都要创建账号，在这里以其中master为例.。

主

\[ [root@T2][root_T2] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant all privileges on \*.\* to 'mha\_rep'@'172.16.57.%' identified by 'fafa123';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

从1

\[ [root@T3][root_T3] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant all privileges on \*.\* to 'mha\_rep'@'172.16.57.%' identified by 'fafa123';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

从2

\[ [root@T4][root_T4] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e "grant all privileges on \*.\* to 'mha\_rep'@'172.16.57.%' identified by 'fafa123';flush privileges;"

Warning: Using a password on the command line interface can be insecure.

主库检查

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select host,user from user;

\+-------------+-----------+

| host        | user      |

\+-------------+-----------+

| %           | dba\_user  |

| 172.16.57.% | backup    |

| 172.16.57.% | mha\_rep   |

| localhost   | mysql.sys |

| localhost   | root      |

\+-------------+-----------+

5 rows in set (0.00 sec)

2、在3台主机上(master、slave01和slave02)上分别安装mha4mysql-node包，这里以master为例，其它主机同理。

主

\[ [root@T2][root_T2] mysql\]\# yum install perl-DBD-MySQL -y

出现冲突的报错

file /usr/share/mysql/ukrainian/errmsg.sys from install of mysql-libs-5.1.73-7.el6.x86\_64 conflicts with file from package MySQL-Cluster-server-gpl-7.4.11-1.el6.x86\_64

\[ [root@T2][root_T2] mysql\]\# yum remove MySQL-Cluster-server-\*

\[ [root@T2][root_T2] mysql-5.7.9\]\# rpm -ivh  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Retrieving  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Preparing...                \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

1:mha4mysql-node         \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

从1

\[ [root@T3][root_T3] mysql-5.7.9\]\# yum install perl-DBD-MySQL -y

\[ [root@T3][root_T3] mysql-5.7.9\]\# rpm -ivh  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Retrieving  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Preparing...                \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

1:mha4mysql-node         \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

从2

\[ [root@T4][root_T4] mysql-5.7.9\]\#  yum install perl-DBD-MySQL -y

\[ [root@T4][root_T4] mysql-5.7.9\]\# rpm -ivh  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Retrieving  [https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm][https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]

Preparing...                \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

1:mha4mysql-node         \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

3、在manager上安装mha4mysql-manager和mha4mysql-node包

这里是172.16.57.58这台机器

\[root@oracle mysql\]\# yum install perl cpan perl-DBD-MySQL perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Net-Telnet -y

\[root@oracle mysql\]\# rpm -ivh https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm

下载不到，直接安装

\[ [root@oracle][root_oracle] mysql\]\# rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm

Preparing...                \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

1:mha4mysql-node         \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

\[root@manager ~\]\# wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.56.tar.gz

\[ [root@oracle][root_oracle] mysql\]\# ls

boost\_1\_59\_0         config  mha4mysql-manager-0.56.tar.gz         mysql-5.7.9

boost\_1\_59\_0.tar.gz  data    mha4mysql-node-0.54-0.el6.noarch.rpm  mysql-5.7.9.tar.gz

\[ [root@oracle][root_oracle] mysql\]\#  tar zvxf mha4mysql-manager-0.56.tar.gz

\[root@oracle mysql\]\# cd mha4mysql-manager-0.56

\[root@oracle mha4mysql-manager-0.56\]\# perl Makefile.PL

\*\*\* Module::AutoInstall version 1.03

\*\*\* Checking for Perl dependencies...

\[Core Features\]

\- DBI                   ...loaded. (1.609)

\- DBD::mysql            ...loaded. (4.013)

\- Time::HiRes           ...missing.

\- Config::Tiny          ...loaded. (2.12)

\- Log::Dispatch         ...loaded. (2.26)

\- Parallel::ForkManager ...loaded. (0.7.9)

\- MHA::NodeConst        ...loaded. (0.54)

==> Auto-install the 1 mandatory module(s) from CPAN?   \[y\] y

CPAN is the world-wide archive of perl resources. It consists of about

300 sites that all replicate the same contents around the globe. Many

countries have at least one CPAN site already. The resources found on

CPAN are easily accessible with the CPAN.pm module. If you want to use

CPAN.pm, lots of things have to be configured. Fortunately, most of

them can be determined automatically. If you prefer the automatic

configuration, answer 'yes' below.

If you prefer to enter a dialog instead, you can answer 'no' to this

question and I'll let you configure in small steps one thing after the

other. (Note: you can revisit this dialog anytime later by typing 'o

conf init' at the cpan prompt.)

Would you like me to configure as much as possible automatically?   \[yes\] yes

后面会自动创建

注意此时需要有网安装

\[root@oracle mha4mysql-manager-0.56\]\# make && make install

\[root@oracle mha4mysql-manager-0.56\]\# mkdir -p /usr/local/mha/scripts

\[root@oracle mha4mysql-manager-0.56\]\# cp samples/conf/app1.cnf /usr/local/mha/mha.cnf

\[root@oracle mha4mysql-manager-0.56\]\# cp samples/scripts/\* //usr/local/mha/scripts/

4、修改manager端mha的配置文件，如下

解释

user=mha\_rep                                    \#MHA管理mysql的用户名

password=fafa123                                 \#MHA管理mysql的密码

manager\_workdir=/usr/local/mha                  \#MHA的工作目录

manager\_log=/usr/local/mha/manager.log          \#MHA的日志路径

ssh\_user=root                                   \#免秘钥登陆的用户名

repl\_user=backup                                \#主从复制账号，用来在主从之间同步数据

ping\_interval=1                                 \#ping间隔时间，用来检查master是否正常

no\_master=1                                     \#设置na\_master=1，使服务器不能成为master

\[ [root@oracle][root_oracle] ~\]\# cat /usr/local/mha/mha.cnf

\[server default\]

user=mha\_rep

password=fafa123

manager\_workdir=/usr/local/mha

manager\_log=/usr/local/mha/manager.log

ssh\_user=root

repl\_user=backup

ping\_interval=1

\[server1\]

hostname=172.16.57.55

master\_binlog\_dir=/data/mysql/

candidate\_master=1                        \#master宕机后，优先启用这台作为master

\[server2\]

hostname=172.16.57.56

master\_binlog\_dir=/data/mysql/

candidate\_master=1

\[server3\]

hostname=172.16.57.57

master\_binlog\_dir=/data/mysql/

candidate\_master=1

5、检查ssh是否畅通

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# pwd

/opt/app/mysql/mha4mysql-manager-0.56

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# masterha\_check\_ssh --conf=/usr/local/mha/mha.cnf

Mon Jun 27 15:16:19 2016 - \[warning\] Global configuration file /etc/masterha\_default.cnf not found. Skipping.

Mon Jun 27 15:16:19 2016 - \[info\] Reading application default configurations from /usr/local/mha/mha.cnf..

Mon Jun 27 15:16:19 2016 - \[info\] Reading server configurations from /usr/local/mha/mha.cnf..

Mon Jun 27 15:16:19 2016 - \[info\] Starting SSH connection tests..

Mon Jun 27 15:16:20 2016 - \[debug\]

Mon Jun 27 15:16:19 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.55][root_172.16.57.55](172.16.57.55:22) to  [root@172.16.57.56][root_172.16.57.56](172.16.57.56:22)..

Mon Jun 27 15:16:19 2016 - \[debug\]   ok.

Mon Jun 27 15:16:19 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.55][root_172.16.57.55](172.16.57.55:22) to  [root@172.16.57.57][root_172.16.57.57](172.16.57.57:22)..

Mon Jun 27 15:16:19 2016 - \[debug\]   ok.

Mon Jun 27 15:16:20 2016 - \[debug\]

Mon Jun 27 15:16:19 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.56][root_172.16.57.56](172.16.57.56:22) to  [root@172.16.57.55][root_172.16.57.55](172.16.57.55:22)..

Mon Jun 27 15:16:20 2016 - \[debug\]   ok.

Mon Jun 27 15:16:20 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.56][root_172.16.57.56](172.16.57.56:22) to  [root@172.16.57.57][root_172.16.57.57](172.16.57.57:22)..

Mon Jun 27 15:16:20 2016 - \[debug\]   ok.

Mon Jun 27 15:16:21 2016 - \[debug\]

Mon Jun 27 15:16:20 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.57][root_172.16.57.57](172.16.57.57:22) to  [root@172.16.57.55][root_172.16.57.55](172.16.57.55:22)..

Mon Jun 27 15:16:20 2016 - \[debug\]   ok.

Mon Jun 27 15:16:20 2016 - \[debug\]  Connecting via SSH from  [root@172.16.57.57][root_172.16.57.57](172.16.57.57:22) to  [root@172.16.57.56][root_172.16.57.56](172.16.57.56:22)..

Mon Jun 27 15:16:21 2016 - \[debug\]   ok.

Mon Jun 27 15:16:21 2016 - \[info\] All SSH connection tests passed successfully.

\#如果得到以上结果，表明主机之间ssh互信是畅通的

6、masterha\_check\_repl工具检查mysql主从复制是否成功，masterha\_check\_repl是一个很好的工具，以后可以用在监控脚本中

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# masterha\_check\_repl --conf=/usr/local/mha/mha.cnf

Mon Jun 27 15:52:31 2016 - \[warning\] Global configuration file /etc/masterha\_default.cnf not found. Skipping.

Mon Jun 27 15:52:31 2016 - \[info\] Reading application default configurations from /usr/local/mha/mha.cnf..

Mon Jun 27 15:52:31 2016 - \[info\] Reading server configurations from /usr/local/mha/mha.cnf..

Mon Jun 27 15:52:31 2016 - \[info\] MHA::MasterMonitor version 0.56.

Mon Jun 27 15:52:31 2016 - \[info\] Dead Servers:

Mon Jun 27 15:52:31 2016 - \[info\] Alive Servers:

Mon Jun 27 15:52:31 2016 - \[info\]   172.16.57.55(172.16.57.55:3306)

Mon Jun 27 15:52:31 2016 - \[info\]   172.16.57.56(172.16.57.56:3306)

Mon Jun 27 15:52:31 2016 - \[info\]   172.16.57.57(172.16.57.57:3306)

Mon Jun 27 15:52:31 2016 - \[info\] Alive Slaves:

Mon Jun 27 15:52:31 2016 - \[info\]   172.16.57.56(172.16.57.56:3306)  Version=5.7.9-log (oldest major version between slaves) log-bin:enabled

Mon Jun 27 15:52:31 2016 - \[info\]     Replicating from 172.16.57.55(172.16.57.55:3306)

Mon Jun 27 15:52:31 2016 - \[info\]     Primary candidate for the new Master (candidate\_master is set)

Mon Jun 27 15:52:31 2016 - \[info\]   172.16.57.57(172.16.57.57:3306)  Version=5.7.9-log (oldest major version between slaves) log-bin:enabled

Mon Jun 27 15:52:31 2016 - \[info\]     Replicating from 172.16.57.55(172.16.57.55:3306)

Mon Jun 27 15:52:31 2016 - \[info\]     Primary candidate for the new Master (candidate\_master is set)

Mon Jun 27 15:52:31 2016 - \[info\] Current Alive Master: 172.16.57.55(172.16.57.55:3306)

Mon Jun 27 15:52:31 2016 - \[info\] Checking slave configurations..

Mon Jun 27 15:52:31 2016 - \[info\]  read\_only=1 is not set on slave 172.16.57.56(172.16.57.56:3306).

Mon Jun 27 15:52:31 2016 - \[warning\]  relay\_log\_purge=0 is not set on slave 172.16.57.56(172.16.57.56:3306).

Mon Jun 27 15:52:31 2016 - \[info\]  read\_only=1 is not set on slave 172.16.57.57(172.16.57.57:3306).

Mon Jun 27 15:52:31 2016 - \[warning\]  relay\_log\_purge=0 is not set on slave 172.16.57.57(172.16.57.57:3306).

Mon Jun 27 15:52:31 2016 - \[info\] Checking replication filtering settings..

Mon Jun 27 15:52:31 2016 - \[info\]  binlog\_do\_db= , binlog\_ignore\_db=

Mon Jun 27 15:52:31 2016 - \[info\]  Replication filtering check ok.

Mon Jun 27 15:52:31 2016 - \[info\] Starting SSH connection tests..

Mon Jun 27 15:52:33 2016 - \[info\] All SSH connection tests passed successfully.

Mon Jun 27 15:52:33 2016 - \[info\] Checking MHA Node version..

Mon Jun 27 15:52:34 2016 - \[info\]  Version check ok.

Mon Jun 27 15:52:34 2016 - \[info\] Checking SSH publickey authentication settings on the current master..

Mon Jun 27 15:52:34 2016 - \[info\] HealthCheck: SSH to 172.16.57.55 is reachable.

Mon Jun 27 15:52:34 2016 - \[info\] Master MHA Node version is 0.54.

Mon Jun 27 15:52:34 2016 - \[info\] Checking recovery script configurations on the current master..

Mon Jun 27 15:52:34 2016 - \[info\]   Executing command: save\_binary\_logs --command=test --start\_pos=4 --binlog\_dir=/data/mysql/ --output\_file=/var/tmp/save\_binary\_logs\_test --manager\_version=0.56 --start\_file=mysql-bin.000003

Mon Jun 27 15:52:34 2016 - \[info\]   Connecting to  [root@172.16.57.55][root_172.16.57.55](172.16.57.55)..

Creating /var/tmp if not exists..    ok.

Checking output directory is accessible or not..

ok.

Binlog found at /data/mysql/, up to mysql-bin.000003

Mon Jun 27 15:52:34 2016 - \[info\] Master setting check done.

Mon Jun 27 15:52:34 2016 - \[info\] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Mon Jun 27 15:52:34 2016 - \[info\]   Executing command : apply\_diff\_relay\_logs --command=test --slave\_user='mha\_rep' --slave\_host=172.16.57.56 --slave\_ip=172.16.57.56 --slave\_port=3306 --workdir=/var/tmp --target\_version=5.7.9-log --manager\_version=0.56 --relay\_log\_info=/data/mysql/relay-log.info  --relay\_dir=/data/mysql/  --slave\_pass=xxx

Mon Jun 27 15:52:34 2016 - \[info\]   Connecting to  [root@172.16.57.56][root_172.16.57.56](172.16.57.56:22)..

Checking slave recovery environment settings..

Opening /data/mysql/relay-log.info ... ok.

Relay log found at /data/mysql, up to T3-relay-bin.000007

Temporary relay log file is /data/mysql/T3-relay-bin.000007

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Mon Jun 27 15:52:34 2016 - \[info\]   Executing command : apply\_diff\_relay\_logs --command=test --slave\_user='mha\_rep' --slave\_host=172.16.57.57 --slave\_ip=172.16.57.57 --slave\_port=3306 --workdir=/var/tmp --target\_version=5.7.9-log --manager\_version=0.56 --relay\_log\_info=/data/mysql/relay-log.info  --relay\_dir=/data/mysql/  --slave\_pass=xxx

Mon Jun 27 15:52:34 2016 - \[info\]   Connecting to  [root@172.16.57.57][root_172.16.57.57](172.16.57.57:22)..

Checking slave recovery environment settings..

Opening /data/mysql/relay-log.info ... ok.

Relay log found at /data/mysql, up to T4-relay-bin.000012

Temporary relay log file is /data/mysql/T4-relay-bin.000012

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Mon Jun 27 15:52:35 2016 - \[info\] Slaves settings check done.

Mon Jun 27 15:52:35 2016 - \[info\]

172.16.57.55 (current master)

+--172.16.57.56

+--172.16.57.57

Mon Jun 27 15:52:35 2016 - \[info\] Checking replication health on 172.16.57.56..

Mon Jun 27 15:52:35 2016 - \[info\]  ok.

Mon Jun 27 15:52:35 2016 - \[info\] Checking replication health on 172.16.57.57..

Mon Jun 27 15:52:35 2016 - \[info\]  ok.

Mon Jun 27 15:52:35 2016 - \[warning\] master\_ip\_failover\_script is not defined.

Mon Jun 27 15:52:35 2016 - \[warning\] shutdown\_script is not defined.

Mon Jun 27 15:52:35 2016 - \[info\] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

四、mha实验模拟

1、在每次做mha实验的时候，我们都最好先执行如下命令做检测

\[root@manager ~\]\# masterha\_check\_ssh --conf=/usr/local/mha/mha.cnf

\[root@manager ~\]\# masterha\_check\_repl --conf=/usr/local/mha/mha.cnf

\#确定两条命令的返回结果都是无异常的，然后启动mha服务

2、在manager端启动mha服务并时刻监控日志文件的输出变化

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# nohup masterha\_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha\_manager.log 2>&1 &

\[1\] 17213

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# ps -ef |grep masterha |grep -v 'grep'

root     17213 24725  0 15:56 pts/3    00:00:00 perl /usr/local/bin/masterha\_manager --conf=/usr/local/mha/mha.cnf

3、测试master宕机后，时候会自动切换

\#测试前查看slave01,slave02的主从同步情况

\#slave01

\[ [root@T3][root_T3] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

\#slave02

\[ [root@T4][root_T4] network-scripts\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

\#停止master的mysql服务

\[ [root@T2][root_T2] mysql-5.7.9\]\# service mysqld stop

Shutting down MySQL............ SUCCESS!

\#我们查看slave02的主从同步信息

\[ [root@T4][root_T4] network-scripts\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.56

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

4、恢复master服务

\#删除故障转移文件，此步以后才能重新转换回来，即172.16.57.58节点

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# rm -rf /usr/local/mha/mha.failover.complete

\#重启mysql服务，即172.16.57.55节点

\[ [root@T2][root_T2] mysql-5.7.9\]\# service mysqld start

Starting MySQL. SUCCESS!

此时启动它不在集群里面

\#在manager的日子文件中找到主从同步的sql语句，即172.16.57.58节点

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# grep MASTER\_HOST /usr/local/mha/manager.log

Mon Jun 27 16:00:12 2016 - \[info\]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER\_HOST='172.16.57.56', MASTER\_PORT=3306, MASTER\_LOG\_FILE='mysql-bin.000004', MASTER\_LOG\_POS=154, MASTER\_USER='backup', MASTER\_PASSWORD='xxx';

\#在master上启动主从同步，密码为backup，即172.16.57.56节点，依据上面日志记录的检查点

mysql> change master to master\_host='172.16.57.56',master\_user='backup',master\_password='backup',master\_port=3306,master\_log\_file='mysql-bin.000004', MASTER\_LOG\_POS=154;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

\#在master和slave02上执行，检查主从同步是否都正常，这里以master为例，slave02同理

\[ [root@T4][root_T4] network-scripts\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.56

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

然而在老主库报错

\[ [root@T2][root_T2] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.55

Slave\_IO\_Running: No

Slave\_SQL\_Running: Yes

2016-06-27T08:23:15.600018Z 8 \[ERROR\] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error\_code: 1593

检查

mysql> show variables like 'server\_id';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| server\_id     | 1     |

\+---------------+-------+

1 row in set (0.00 sec)

没发现问题

新主库查看是否是 /data/mysql/auto.cnf，相同导致，即172.16.57.56机器上

mysql> show slave hosts;

\+-----------+------+------+-----------+--------------------------------------+

\+-----------+------+------+-----------+--------------------------------------+

|       111 |      | 3306 |        11 | e34ec4dd-3c25-11e6-ba9a-52540034e4ef |

\+-----------+------+------+-----------+--------------------------------------+

1 row in set (0.00 sec)

mysql> show variables like 'server\_uuid';

\+---------------+--------------------------------------+

| Variable\_name | Value                                |

\+---------------+--------------------------------------+

| server\_uuid   | 69577b5a-3c25-11e6-ae06-5254006b272f |

\+---------------+--------------------------------------+

1 row in set (0.00 sec)

发现不能识别172.16.57.55的从机器

原理是上面改变主的时候，指定旧的主了，要指定新的主即172.16.57.56

\[ [root@T2][root_T2] mysql\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.56

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

最后正确

5、再次启动MHA的manager服务，并停止slave01

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# nohup masterha\_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha\_manager.log 2>&1 &

\[1\] 17593

\#关闭slave01的mysql服务

\[ [root@T3][root_T3] mysql-5.7.9\]\# service mysqld stop

Shutting down MySQL............ SUCCESS!

manager节点查看情况

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# tail -f /usr/local/mha/manager.log

Started automated(non-interactive) failover.

The latest slave 172.16.57.55(172.16.57.55:3306) has all relay logs for recovery.

Selected 172.16.57.55 as a new master.

172.16.57.55: OK: Applying all logs succeeded.

172.16.57.57: This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

172.16.57.57: OK: Applying all logs succeeded. Slave started, replicating from 172.16.57.55.

172.16.57.55: Resetting slave info succeeded.

Master failover to 172.16.57.55(172.16.57.55:3306) completed successfully.

\#在slave02上查看主从同步情况

\[ [root@T4][root_T4] network-scripts\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.55

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

可以看到主节点成功切换到172.16.57.55节点上了

下面需要对slave01服务进行恢复，这种切换是一个曲折的过程

6、恢复slave01服务

\#删除故障转移文件，manager节点进行

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# rm -rf /usr/local/mha/mha.failover.complete

从1节点重启服务

\[ [root@T3][root_T3] mysql-5.7.9\]\# service mysqld start

Starting MySQL. SUCCESS!

\#在manager的日子文件中找到主从同步的sql语句，即172.16.57.58

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# grep MASTER\_HOST /usr/local/mha/manager.log

Mon Jun 27 16:42:48 2016 - \[info\]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER\_HOST='172.16.57.55', MASTER\_PORT=3306, MASTER\_LOG\_FILE='mysql-bin.000007', MASTER\_LOG\_POS=154, MASTER\_USER='backup', MASTER\_PASSWORD='xxx';

\#在slave01上启动主从同步，密码为backup，注意此时的主节点是172.16.57.55

mysql> change master to master\_host='172.16.57.55',master\_user='backup',master\_password='backup',master\_port=3306,master\_log\_file='mysql-bin.000007', MASTER\_LOG\_POS=154;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

\#在slave01和slave02上执行，检查主从同步是否都正常，这里以slave01为例，slave02同理。

从1

\[ [root@T3][root_T3] mysql-5.7.9\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.55

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

从2

\[ [root@T4][root_T4] network-scripts\]\# mysql -uroot -pfafa123 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 172.16.57.55

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

7、再次启动MHA的manager服务，在172.16.57.58

\[ [root@oracle][root_oracle] mha4mysql-manager-0.56\]\# nohup masterha\_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha\_manager.log 2>&1 &

\[1\] 17662

五、通过vip实现mysql的高可用

1、修改/usr/local/mha/mha.cnf

\[server default\]

user=mha\_rep

password=123456

manager\_workdir=/usr/local/mha

manager\_log=/usr/local/mha/manager.log

master\_ip\_failover\_script=/usr/local/mha/scripts/master\_ip\_failover    \#添加管理vip的脚本

ssh\_user=root

repl\_user=backup

repl\_password=backup

ping\_interval=1

\[server1\]

hostname=192.168.253.241

master\_binlog\_dir=/data/mysql/

candidate\_master=1

\[server2\]

hostname=192.168.253.242

master\_binlog\_dir=/data/mysql/

candidate\_master=1

\[server3\]

hostname=192.168.253.243

master\_binlog\_dir=/data/mysql/

no\_master=1

2、修改脚本/usr/local/mha/scripts/master\_ip\_failover

\#!/usr/bin/env perl

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

my (

$command,          $ssh\_user,        $orig\_master\_host, $orig\_master\_ip,

$orig\_master\_port, $new\_master\_host, $new\_master\_ip,    $new\_master\_port

);

my $vip = '192.168.253.240';            \#vip地址

my $key = '1';

my $ssh\_start\_vip = "/sbin/ifconfig eth0:$key $vip";        \#绑定在指定的网卡上面

my $ssh\_stop\_vip = "/sbin/ifconfig eth0:$key down";

GetOptions(

'command=s'          => \\$command,

'ssh\_user=s'         => \\$ssh\_user,

'orig\_master\_host=s' => \\$orig\_master\_host,

'orig\_master\_ip=s'   => \\$orig\_master\_ip,

'orig\_master\_port=i' => \\$orig\_master\_port,

'new\_master\_host=s'  => \\$new\_master\_host,

'new\_master\_ip=s'    => \\$new\_master\_ip,

'new\_master\_port=i'  => \\$new\_master\_port,

);

exit &main();

sub main \{

print "\\n\\nIN SCRIPT TEST====$ssh\_stop\_vip==$ssh\_start\_vip===\\n\\n";

if ( $command eq "stop" || $command eq "stopssh" ) \{

my $exit\_code = 1;

eval \{

print "Disabling the VIP on old master: $orig\_master\_host \\n";

&stop\_vip();

$exit\_code = 0;

\};

if ($@) \{

warn "Got Error: $@\\n";

exit $exit\_code;

elsif ( $command eq "start" ) \{

my $exit\_code = 10;

eval \{

print "Enabling the VIP - $vip on the new master - $new\_master\_host \\n";

&start\_vip();

$exit\_code = 0;

\};

if ($@) \{

warn $@;

exit $exit\_code;

elsif ( $command eq "status" ) \{

print "Checking the Status of the script.. OK \\n";

exit 0;

else \{

&usage();

exit 1;

sub start\_vip() \{

\`ssh $ssh\_user\\@$new\_master\_host \\" $ssh\_start\_vip \\"\`;

\# A simple system call that disable the VIP on the old\_master

sub stop\_vip() \{

\`ssh $ssh\_user\\@$orig\_master\_host \\" $ssh\_stop\_vip \\"\`;

sub usage \{

"Usage: master\_ip\_failover --command=start|stop|stopssh|status --orig\_master\_host=host --orig\_master\_ip=ip --orig\_master\_port=port --new\_master\_host=host --new\_master\_ip=ip --new\_master\_port=port\\n";

3、模拟故障进行切换

\#停止master的mysql服务

\[root@master ~\]\# service mysqld stop

Shutting down MySQL... SUCCESS!

\#查看slave02的同步信息

\[root@slave02 ~\]\# mysql -uroot -p123456 -e 'show slave status\\G' |egrep 'Master\_Host|Slave\_IO\_Running:|Slave\_SQL\_Running:'

Warning: Using a password on the command line interface can be insecure.

Master\_Host: 192.168.253.242

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

\#查看slave01的IP信息

1: lo: <LOOPBACK,UP,LOWER\_UP> mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

2: eth0: <BROADCAST,MULTICAST,UP,LOWER\_UP> mtu 1500 qdisc pfifo\_fast state UP qlen 1000

link/ether a2:28:26:4d:72:f6 brd ff:ff:ff:ff:ff:ff

inet 192.168.253.242/24 brd 192.168.253.255 scope global eth0

\#这里能看到vip已自动添加

inet 192.168.253.240/24 brd 192.168.253.255 scope global secondary eth0:1

3: eth1: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000

link/ether 56:17:7e:16:50:3c brd ff:ff:ff:ff:ff:ff

4、恢复master的mysql服务同开始恢复方法一样。

六.MHA日常维护命令

1.查看ssh登陆是否成功

masterha\_check\_ssh --conf=/usr/local/mha/mha.cnf

2.查看复制是否建立好

masterha\_check\_repl --conf=/usr/local/mha/mha.cnf

3.启动mha

nohup masterha\_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha\_manager.log 2>&1 &

4.检查启动的状态

masterha\_check\_status --conf=/usr/local/mha/mha.cnf

5.停止mha

masterha\_stop masterha\_check\_status --conf=/usr/local/mha/mha.cnf

6.failover后下次重启

\#每次failover切换后会在管理目录生成文件app1.failover.complete ，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉。

rm -rf /usr/local/mha/mha.failover.complete

[root_bd-qa-mongodb-55]: mailto:root@bd-qa-mongodb-55
[root_172.16.57.56]: mailto:root@172.16.57.56
[root_172.16.57.57]: mailto:root@172.16.57.57
[root_172.16.57.58]: mailto:root@172.16.57.58
[root_bd-qa-oracle-56]: mailto:root@bd-qa-oracle-56
[root_bd-qa-redis-57]: mailto:root@bd-qa-redis-57
[root_bd-qa-vertica-58]: mailto:root@bd-qa-vertica-58
[root_T2]: mailto:root@T2
[root_T3]: mailto:root@T3
[root_T4]: mailto:root@T4
[https_downloads.mariadb.com_files_MHA_mha4mysql-node-0.54-0.el6.noarch.rpm]: https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm
[root_oracle]: mailto:root@oracle
[root_172.16.57.55]: mailto:root@172.16.57.55