mysql 配置高可用集群示例-原创手记-慕课网

准备6台服务器或虚拟机，这里以六台虚拟机为例：主机名和ip地址如下

hostname	ip地址
host11	192.168.4.11
host12	192.168.4.12
host13	192.168.4.13
host14	192.168.4.14
host15	192.168.4.15
host16	192.168.4.16
VIP	192.168.4.100

部署mysql高可用集群（主从同步+MHA软件）

集群时使用多台服务器提供的相同mysql服务示例中用host11-host15，mysql监控服务器用host11

高可用集群主备模式：当主角色的主机宕机后，备用主机自动接替主角色的主机提供服务服务给客户端。

#cilent mysql -h192.168.4.12 -uadmin -p123456

监控软件用MHA软件

host16 监控服务

vip地址：192.168.4.100

主备用主备用主备用备用

mysql11 mysql12 mysql13 mysql14 mysql15

slave slave slave slave

第一步：准备MHA运行环境

一主多从

安装依赖的软件包

ssh root用户无密码登陆

拓扑结构

master11

______________________________________________________

| | | | |

slave12 slave13 slave14 slave15 mgm16

Manager

公共配置：

在所有主机上安装软件软件包（11~16）

[root@host11～]# cd mha-soft-student

[root@host11～]# ls perl-*.rpm

perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm

[root@host11～]# yum -y install perl-*.rpm

在所有主机上安装mha_node软件包（11~16）

yum -y install perl-DBD-mysql

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

一、配置所有数据节点主机之间可以互相以ssh密钥对方式认证登陆

[root@host11～]# ssh-keygen -t rsa

二、配置manager16主机无密码ssh登录所有数据节点主机

三、配置主从同步,要求如下：

11 主库开半同步复制
12 从库（备用主库）开半同步复制
13 从库（备用主库）开半同步复制
14 从库不做备用主库所以不用开半同步复制
15 从库不做备用主库所以不用开半同步复制

配置一主多从

3.1、master11配置：

vim /etc/my.cnf

[mysqld]

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled = 1

rpl-semi-sync-slave-enabled = 1

server_id=11

log-bin=master11

binlog-format="mixed"

:wq

[root@host11 ~]# systemctl restart mysqld

[root@host11 ~]# ls /var/lib/mysql/master11.*

/var/lib/mysql/master11.000001 /var/lib/mysql/master11.index

[root@host11 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";

mysql> set global relay_log_purge=off;

mysql> show master status;

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

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

| master11.000001 | 441 | | | |

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

mysql> quit;

3.2、备用master12的配置

vim /etc/my.cnf

[mysqld]

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled = 1

rpl-semi-sync-slave-enabled = 1

server_id=12

log-bin=master12

binlog-format="mixed"

[root@host12 ~]# systemctl restart mysqld

[root@host12 ~]# ls /var/lib/mysql/master12.*

/var/lib/mysql/master12.000001 /var/lib/mysql/master12.index

[root@host12 ~]# mysql -uroot -p123456

mysql> set global relay_log_purge=off; #// 不自动删除本机的中继日志文件

mysql> change master to

-> master_host="192.168.4.11",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="master11.000001",

-> master_log_pos=441;

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

mysql> start slave;

[root@host12 ~]# mysql -uroot -p123456 -e "show slave status\G" | grep -i YES

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

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host12 ~]#

3.3、备用master13的配置

[root@host13 ~]# vim /etc/my.cnf

[mysqld]

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled = 1

rpl-semi-sync-slave-enabled = 1

server_id=13

log-bin=master13

binlog-format="mixed"

:wq

[root@host13 ~]# systemctl restart mysqld

[root@host13 ~]# ls /var/lib/mysql/master13.*

/var/lib/mysql/master13.000001 /var/lib/mysql/master13.index

[root@host13 ~]# mysql -uroot -p123456

mysql> set global relay_log_purge=off;

mysql> change master to master_host="192.168.4.11",master_user="repluser",master_password="123456",master_log_file="master11.000001",master_log_pos=441;

mysql> start slave;

mysql>

[root@host13 ~]# mysql -uroot -p123456 -e "show slave status\G" | grep -i yes

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

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host13 ~]#

3.4、配置从服务器14

[root@host14 ~]# vim /etc/my.cnf

[mysqld]

server_id=14

:wq

[root@host14 ~]# systemctl restart mysqld

[root@host14 ~]# mysql -uroot -p123456

mysql> change master to master_host="192.168.4.11",master_user="repluser",master_password="123456",master_log_file="master11.000001",master_log_pos=441;

mysql> start slave;

mysql> quit;

[root@host14 ~]#

[root@host14 ~]# mysql -uroot -p123456 -e "show slave status\G" | grep -i yes

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

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host14 ~]#

3.5、配置从服务器15

[root@host15 ~]# vim /etc/my.cnf

[mysqld]

server_id=15

:wq

[root@host15 ~]# systemctl restart mysqld

[root@host15 ~]# mysql -uroot -p123456

mysql> change master to master_host="192.168.4.11",master_user="repluser",master_password="123456",master_log_file="master11.000001",master_log_pos=441;

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

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

Bye

[root@host15 ~]#

[root@host15 ~]# mysql -uroot -p123456 -e "show slave status\G" | grep -i yes

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

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@host15 ~]#

3.6、在客户端测试主从同步配置(验证)

3.6.1 在主库11上添加访问数据的授权用户

[root@host11 ~]# mysql -uroot -p123456

mysql> grant all on gamedb.* to admin@"%" identified by "123456";

3.6.2 在客户端主机连接主库11 建库表记录

]# mysql -h192.1168.4.11 -uadmin -p123456

mysql> create database gamedb;

mysql> create table gamedb.t1 (id int);

mysql> insert into gamedb.t1 values(999);

mysql> select * from gamedb.t1;

mysql>

3.6.3 在客户端使用授权用户连接从库12-15,也能看到同样的库表及记录

[root@host12 ~]# mysql -h从库IP地址 -uadmin -p123456

mysql> select * from gamedb.t1;

第二步：配置MHA

2.1配置数据主机（一主到从安装依赖的软件包彼此之间可以ssh root 无密码登陆） 192.168.4.100

2.1.1 一主多从

2.1.2 安装依赖的perl软件包

2.1.3安装软件mha-node

[root@host11~]# cd 数据库软件包/mha-soft-student/

[root@host11 mha-soft-student]# yum -y install perl-DBD-mysql

[root@host11 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

2.1.4 配此之间可以ssh root用户无密码登陆

2.1.5 授权监控用户

mysql> grant all on *.* to root@"%" identified by "123456";

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";

2.1.6 所有数据库服务器启不删除本机的中继日志文件

mysql> set global relay_log_purge=off;// 不自动删除本机的中继日志文件

2.2 配置管理主机 192.168.4.16

2.2.1 安装依赖的perl软件包

2.2.2 安装软件mha-node

[root@host16~]# cd /root/桌面/数据库软件包/mha-soft-student/

[root@host16 mha-soft-student]# yum -y install perl-DBD-mysql

[root@host16 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

源码安装mha4mysql-manager

[root@host16 mha-soft-student]# yum -y install perl-ExtUtils-* perl-CPAN-*

[root@host16 mha-soft-student]#tar -zxf mha4mysql-manager-0.56.tar.gz

[root@host16 mha-soft-student]#cd mha4mysql-manager-0.56

[root@host16 mha4mysql-manager-0.56]# perl Makefile.pl

[root@host16 mha4mysql-manager-0.56]# make

[root@host16 mha4mysql-manager-0.56]# make install

2.2.3 指定命令所在的路径

[root@host16 ~]# cd /root/桌面/数据库软件包/mha-soft-student/mha4mysql-manager-0.56

[root@host16 mha4mysql-manager-0.56]# mkdir /root/bin

[root@host16 mha4mysql-manager-0.56]# cp bin/* /root/bin

[root@host16 mha4mysql-manager-0.56]# ls /root/bin

2.2.4 修改配置文件

[root@host16 ~]# mkdir /etc/mha_manager/

[root@host16 ~]# cd /root/桌面/数据库软件包/mha-soft-student/mha4mysql-manager-0.56/samples/conf

[root@host16 mha4mysql-manager-0.56]# cp app1.cnf /etc/mha_manager/app1.cnf

[root@host16 mha4mysql-manager-0.56]# cd ~

[root@host16 ~]# vim /etc/mha_mannger/app1.cnf

[server default]

manager_workdir=/etc/mha_manager

manager_log=/etc/mha_manager/manager.log

master_ip_failover_script=/etc/mha_manager/master_ip_failover

ssh_user=root

ssh_port=22

repl_user=repluser

repl_password=123456

user=root

password=123456

[server1]

hostname=192.168.4.11

candidate_master=1

[server2]

hostname=192.168.4.12

candidate_master=1

[server3]

hostname=192.168.4.13

candidate_master=1

[server4]

hostname=192.168.4.14

no_master=1

[server5]

hostname=192.168.4.15

no_master=1

:wq

[root@host16 ~]# cd mha4mysql-manager-0.56/samples/scripts #//

[root@host16 ~]# cp master_ip_failover /etc/mha_manager/ #//master_ip_failover 文件需要perl编译

[root@host16 ~]# rm -rf /etc/mha_manager/master_ip_failover

[root@host16 ~]# cp /root/桌面/数据库软件包/mha-soft-studentmaster_ip_failover /etc/mha_manager/ #//master_ip_failover正常文件需要perl编译，上课前已经被老师已经编译ok，只需进去修改vip地址

[root@host16 ~]# chmod +x /etc/mha_manager/master_ip_failover

[root@host16 ~]# vim /etc/mha_manager/master_ip_failover

my $vip = '192.168.4.100/24'; # Virtual IP

my $key = "1";

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

测试配置文件

[root@host16 ~]# vim /etc/mha_manager/app1.cnf

[server default]

#master_ip_failover_script=/etc/mha_manager/master_ip_failover

[root@host16 ~]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf #//测试ssh root用户22号是否可通过

[root@host16 ~]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf #//测试数据库sql repl用户是否可以连接

MySQL Replication Health is OK.

3、启动服务：

3.1 把vip 地址手动绑定在主库上

[root@host11 ~]# ifconfig eth0:1 192.168.4.100/24

[root@host11 ~]# ifconfig eth0:1

3.2 启动服务

[root@host16 ~]# vim /etc/mha_manager/app1.cnf

[server default]

master_ip_failover_script=/etc/mha_manager/master_ip_failover

[root@host16 ~]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover #//说明remove_dead_master_conf是说主库down后删除app1.cnf中的配置文件对应的信息，ignore_last_failover是指在8个小时内不能down多次

[root@host16 ~]#masterha_check_status --conf=/etc/mha_manager/app1.cnf #//查看mha运行状态

4、测试高可用集群配置

在数据库服务上添加访问数据连接用户 webuser 123456

[root@root9pc01 ~]# mysql -h192.68.4.11 -uroot -p123456

MySQL > create database db13;

mysql> grant all on db13.* to webuser@"%" identified by "123456"；

4.1 客户端连接VIP地址访问数据库

]# mysql -h192.168.4.100 -uwebuser -p123456

4.2 测试高用集群

把主机11上的数据库服务停止

]# systemctl stop mysqld

把宕机的数据库服务器11 在添加到当前集群里

]# mysql -h192.68.4.11 -uroot -p123456

mysql> change master to master_host="192.168.4.12", master_user="repluser",master_password="123456",master_log_file="master12.000001",master_log_pos=154;

mysql> start slave;

16：

]# vim /etc/mha_manager/app1.cnf

[server1]

candidate_master=1

hostname=192.168.4.11

:wq

]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf

MySQL Replication Health is OK.

]#masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --

ignore_last_failover