centos7 mysql mha_centos7 mysql+MHA高可用安装

一、配置主从同步

1.1 本例中主从ip及端口

Master：192.168.20.50:3306

Slave1：192.168.31.3:3306 (候选master)

Slave2：192.168.31.2:3306

1.2、安装mysql

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql-community-server

systemctl start mysqld.service

grep "password" /var/log/mysqld.log

mysql -uroot -p #登录

ALTER USER 'root'@'localhost' IDENTIFIED BY 'My2019..';

#因为安装了Yum Repository，以后每次yum操作都会自动更新，需要把这个卸载掉

yum -y remove mysql57-community-release-el7-10.noarch

1.3.Master配置参数

[mysqld]

user=mysql

pid_file= /var/lib/mysql/mysqld.pid

socket= /var/lib/mysql/mysql.sock

port= 3306basedir= /usr

datadir= /var/lib/mysql

tmpdir= /tmp

log-bin                 = master-bin

log-bin-index           = master-bin.index

server_id= 1innodb_log_file_size=256M

expire-logs-days = 1

validate_password=off #取消密码验证

#mysql5.6已上的特性，开启gtid，必须主从全开

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates = 1

#开启半同步复制 否则自动切换主从的时候会报主键错误

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

1.4.在master创建一个复制用户

主节点：

mysql>create user repl_user;

所有主机上授权：

mysql> grant replication slave on *.* to repl_user identified by 'tt123456';

mysql>flush privileges;

mysql>grant all on *.* to root identified by 'my123456'; #很重要

1.5. Slave1 配置参数

[mysqld]

pid_file = /var/lib/mysql/mysqld.pid

socket = /var/lib/mysql/mysql.sock

basedir=/usr

port=3306

user=mysql

tmpdir= /tmp

server_id= 2

relay_log_index = slave_relay_bin.index

relay_log= slave_relay_bin

innodb_log_file_size= 256M

expire-logs-days = 1

log-bin = mysql-bin (候选需要配)

log-bin-index = mysql-bin.index (候选需要配)

read_only=1

relay_log_purge=0   #(一主一丛不需要此项，两从及以上建议开次参数，防止切换为成主库的从库自动删除中继日志后，无法给其他从库应用这部分日志)

validate_password=off #取消密码验证

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates = 1

#开启半同步复制 否则自动切换主从的时候会报主键错误

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"(候选需要配)

loose_rpl_semi_sync_master_enabled = 1(候选需要配)

loose_rpl_semi_sync_slave_enabled = 1(候选需要配)

loose_rpl_semi_sync_master_timeout = 5000(候选需要配)

1.6.连接Master和Slave

mysql>CHANGE MASTER TO

MASTER_HOST='192.168.31.3',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1,

MASTER_USER='repl_user',

MASTER_PASSWORD='tt123456';

#master_log_file='master-bin.000007',#5.6后不需要指定

#master_log_pos=194;

mysql> start slave;

mysql> show slave status\G;

1.7查看半同步是否开启

master：

mysql> show status like 'Rpl_semi_sync_master_status';+-----------------------------+-------+

| Variable_name | Value |

+-----------------------------+-------+

| Rpl_semi_sync_master_status | ON |

+-----------------------------+-------+slave：

mysql> show status like 'Rpl_semi_sync_slave_status';+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

二、配置ssh公钥互信

2.1. 本例中manager节点和node节点ip

manager：192.168.20.50

node1：192.168.20.50

node2：192.168.31.3

node3：192.168.31.2

注：manager节点可以安装独立的服务器上，本例为了节省机器，manager安装在了主库(192.168.20.50)上.

2.2. 配置manager和node各节点间的root用户的ssh公钥互信

在三个 mysql 节点分别执行如下操作：(三个都有，包括自己ssh自己)

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.20.50ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.31.2ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.31.3

三、安装 MHA 包

3.1. MHA安装

manager和node节点安装：

#先安装依赖

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

rpm -ivh epel-release-latest-7.noarch.rpm

yum install -y perl-DBD-MySQLperl-Config-Tinyperl-Log-Dispatchperl-Parallel-ForkManager

wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm-ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpmmanager上安装：

wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3.2、MHA Manager 端配置

配置主配置文件

vi /etc/mha/app1.cnf

[server default]

manager_workdir=/var/log/mha/app1

manager_log=/var/log/mha/app1/manager.log

user=root #mysql用户

password=my123456 #mysql密码

ssh_user=root

repl_user=repl_user

repl_password=tt123456

ping_interval=1

#master_binlog_dir= /var/lib/mysql,/var/log/mysql

#secondary_check_script=masterha_secondary_check -s 192.168.20.50 -s 192.168.31.3 -s 192.168.31.2

#master_ip_failover_script="/etc/mha/scripts/master_ip_failover"

#master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"

#report_script="/etc/mha/scripts/send_report"

[server1]#这里模块不注释，ssh测试会报错，不过没影响

hostname=192.168.20.50port=3306master_binlog_dir=/var/lib/mysql

candidate_master=1check_repl_delay=0[server2]

hostname=192.168.31.3port=3306master_binlog_dir=/var/lib/mysql

candidate_master=1#如果候选master有延迟的话，relay日志超过100m，failover切换不能成功，加上此参数后会忽略延迟日志大小。

check_repl_delay=0[server3]

hostname=192.168.31.2port=3306master_binlog_dir=/var/lib/mysql

ignore_fail=1#如果这个节点挂了，mha将不可用，加上这个参数，slave挂了一样可以用

no_master=1  #从不将这台主机转换为master

PS:以上注释不能写在配置文件里，不然会报错

四、运行

4.1. 检查SSH配置

masterha_check_ssh --conf=/etc/masterha/app1/app1.cnf

4.2检查MHA当前配置

masterha_check_repl --conf=/etc/masterha/app1/app1.cnf

4.3启动mha

nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf   --remove_dead_master_conf --ignore_last_failover < /dev/null >  /var/log/mha/app1/manager.log  2>&1 &

4.4查看状态

masterha_check_status --conf=/etc/masterha/app1/app1.cnf

4.5关闭mha

masterha_stop  --conf=/etc/masterha/app1/app1.cnf

4.6查看日志

tail -f  /etc/masterha/app1/mha_manager.log

4.7从库从新加入新主

grep "CHANGE MASTER TO MASTER"  /var/log/mha/app1/manager.log | tail -1

五、Failover应用场景测试

自动failover测试

应用场景1：master dead后，MHA当时已经开启，候选Master库(Slave)会自动failover为Master.

后面我的主库改为192.168.31.3，在这机器执行：systemctl stop mysqld.service

然后192.168.20.50执行：mysql> show master status\G;

从库机器192.168.31.2执行：mysql> show slave status\G;

最后把原主库192.168.31.3修复成一个新的slave：

#查看具体修复语句

# grep "CHANGE MASTER TO MASTER"  /etc/masterha/app1/manager.log| tail -1CHANGE MASTER TO MASTER_HOST='192.168.20.50',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000009', MASTER_AUTO_POSITION=1,MASTER_USER='repl_user', MASTER_PASSWORD='xxx';

mysql> CHANGE MASTER TO MASTER_HOST='192.168.20.50',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000009', MASTER_AUTO_POSITION=1,MASTER_USER='repl_user', MASTER_PASSWORD='tt123456';

mysql>start slave;

mysql>show slave status\G;

验证。

报错

1.replicates is not defined in the configuration file!

masterha_check_repl --conf=/etc/masterha/app1/app1.cnf

Thu Jan31 11:36:27 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jan31 11:36:27 2019 - [info] Reading application default configuration from /etc/masterha/app1/app1.cnf..

Thu Jan31 11:36:27 2019 - [info] Reading server configuration from /etc/masterha/app1/app1.cnf..

Thu Jan31 11:36:27 2019 - [info] MHA::MasterMonitor version 0.58.

Thu Jan31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 192.168.20.50:3306 fromw

hich slave192.168.31.2(192.168.31.2:3306) replicates is not defined in the configuration file!Thu Jan31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking confi

gurations. at/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.

Thu Jan31 11:36:28 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring ser

vers.

Thu Jan31 11:36:28 2019 - [info] Got exit code 1 (Not master dead).

解决：配置文件里面没有manager的主机信息，添加上去。

[server1]

hostname=192.168.20.50port=3306master_binlog_dir=/var/lib/mysql

candidate_master=1check_repl_delay=0

2、[error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm

[error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking confi

gurations. Argument"1 #M-dM-;M-^NM-dM-8M-\rM-eM-0M-^FM-hM-?M-^YM-eM-^OM-0M-d..." isn't numeric in numeric ge (>=) at /usr/s

hare/perl5/vendor_perl/MHA/ServerManager.pm line 1157.

Thu Jan31 14:39:52 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring ser

vers.

解决：

配置文件不能有中文注释。。