mysql高可用 jdbc配置要求_配置MySQL高可用集群MHA(示例代码)

配置MySQL高可用集群+++++++++++++++++++

主机角色 ：

客户端 client50

数据库服务器 mysql51 到 mysql55

管理主机 mgm56

VIP地址 192.168.4.100

拓扑结构：

client50

|

mysql51主

|

| | | | |

mysql52 mysql53 mysql54 mysql55 mgm56

从 从 从 从 管理集群

备用主 备用主

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

配置MySQL主从同步结构：一主多从

配置主库51

配置备用从库52

配置备用从库53

配置纯从库54

配置纯从库55

51-55都要进行

安装mysql【yum -y install mysql-community-*】

mysql-5.7.17.tartar -xf

mysql-community-client-5.7.17-1.el7.x86_64.rpm

mysql-community-common-5.7.17-1.el7.x86_64.rpm

mysql-community-devel-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm

mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm

mysql-community-libs-5.7.17-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm

mysql-community-server-5.7.17-1.el7.x86_64.rpm

mysql-community-test-5.7.17-1.el7.x86_64.rpm

在所有服务器51-56上安装mha_node软件[mha4mysql-node-0.56-0.el6.noarch.rpm]

]#yum -y install perl-DBD-mysql

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

一、配置所有数据库主机之间可以互相以ssh密钥对方式认证登陆[免密码]

二、配置manager56主机 无密码ssh登录 所有数据库主机【免密码】

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

51 主库 开半同步复制

52 从库(备用主库) 开半同步复制

53 从库(备用主库) 开半同步复制

54 从库 不做备用主库所以不用开半同步复制

55 从库 不做备用主库所以不用开半同步复制

配置半同步复制模式

查看当前的数据库服务是否支持动态加载模块

mysql>show variables like ‘have_dynamic_loading‘;

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

| Variable_name | Value |

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

| have_dynamic_loading | YES |

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

使用命令安装模块

mysql>

mysql> INSTALL PLUGIN rpl_semi_sync_master

-> SONAME ‘semisync_master.so‘;

mysql> INSTALL PLUGIN rpl_semi_sync_slave

-> SONAME ‘semisync_slave.so‘;

查看模块状态

mysql>

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_NAME LIKE ‘%semi%‘;

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

| PLUGIN_NAME | PLUGIN_STATUS |

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

| rpl_semi_sync_master | ACTIVE |

| rpl_semi_sync_slave | ACTIVE |

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

启用半同步复制

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

查看是否启用成功

mysql> show variables like ‘rpl_semisync%_enabled‘;

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_slave_enabled | ON |

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

把配置写进配置文件，使其永久生效

3.1、master51配置：

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

relay_log_purge=off; //不允许自动删除中继日志文件【mysql> set global relay_log_purge=off;】

server_id=51

log-bin=master51

binlog-format="mixed"

:wq

#systemctl restart mysqld

#mysql -uroot -p123abcxyz

mysql> grant replication slave on . to [email protected]"%" identified by "123abcxyz";

mysql> show master status;

注意：备用52-53上也要有repluser用户

3.2、备用master52的配置

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

relay_log_purge=off; //不允许自动删除中继日志文件【mysql> set global relay_log_purge=off;】

server_id=52

log-bin=master52

binlog-format="mixed"

:wq

#systemctl restart mysqld

]# ls /var/lib/mysql/master52.*

/var/lib/mysql/master52.000001 /var/lib/mysql/master52.index

#mysql -uroot -p123abcxyz

mysql> change master to

-> master_host="192.168.4.51",

-> master_user="repluser",

-> master_password="123abcxyz",

-> master_log_file="master51.000001",

-> master_log_pos=441;

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

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.3、备用master53的配置

]# 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

relay_log_purge=off; //不允许自动删除中继日志文件【mysql> set global relay_log_purge=off;】

server_id=53

log-bin=master53

binlog-format="mixed"

:wq

]# systemctl restart mysqld

]# ls /var/lib/mysql/master53.*

]# mysql -uroot -p123abcxyz

mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.000001",master_log_pos=441;

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

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.4、配置从服务器54

]# vim /etc/my.cnf

[mysqld]

server_id=54

:wq

]# systemctl restart mysqld

]# mysql -uroot -p123abcxyz

mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.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> show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.5、配置从服务器55

]# vim /etc/my.cnf

[mysqld]

server_id=55

:wq

]# systemctl restart mysqld

]# mysql -uroot -p123abcxyz

mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123abcxyz",master_log_file="master51.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> show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[配置51-55主从同步完成]

阿保存在所有数据库服务器51-55上授权监控用户

mysql>

grant all on . to [email protected]‘%‘ identified by ‘123abcxyz‘;

++++++++++++++++++++++++++++++++++

准备集群环境：

1 在所有主机上安装共享目录里的perl软件包 (51-56)

公共配置：在所有主机上安装软件软件包

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

]# yum -y install perl-.rpm

2 在所有服务器51-56上安装mha_node软件包

]#cd mha-soft-student/

]#yum -y install perl-DBD-mysql

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

4 在管理主机56上安装mha_manager软件包

]# yum -y install perl-ExtUtils- perl-CPAN-*

]# tar -zxf mha4mysql-manager-0.56.tar.gz

]# cd mha4mysql-manager-0.56

]# perl Makefile.pl //[需要的依赖包可通过yum list | grep -i 关键字 获取yum -y install perl-ExtUtils-perl-CPAN-]

]# make

]# make install

配置MHA:

在管理主机56上创建管理命令

]# mkdir /root/bin

]#cd mha4mysql-manager-0.56/bin

]#cp * /root/bin/

创建主配置 并编辑

]# mkdir /etc/mha

]#cp mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha/

]#vim /etc/mha/app1.cnf

[server default] #服务默认配置

manager_workdir=/etc/mha #工作目录

manager_log=/etc/mha/manager.log #日志文件

master_ip_failover_script=/etc/mha/master_ip_failover #故障切换脚本

#ssh服务用户名及端口

ssh_user=root

ssh_port=22

#主从复制时，从库连接主库的用户名及密码

repl_user=repluser

repl_password=123abcxyz

#监控数据库时，连接服务器的用户及密码

user=root

password=123abcxyz

#指定数据库服务器ip及角色

[server1]

hostname=192.168.4.51

candidate_master=1

port=3306

[server2]

hostname=192.168.4.52

candidate_master=1

port=3306

[server3]

hostname=192.168.4.53

candidate_master=1

port=3306

[server4]

hostname=192.168.4.54

no_master=1

port=3306

[server5]

hostname=192.168.4.55

no_master=1

port=3306

:wq

创建故障切换脚本并编辑[正常在mha4mysql-manager-0.56目录下]

]# cd mha-soft-student

]# cp master_ip_failover /etc/mha/

]# chmod +x /etc/mha/master_ip_failover

]# vim /etc/mha/master_ip_failover

35 my $vip = ‘192.168.4.100/24‘; # Virtual IP

36 my $key = "1";

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

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

:wq

把脚本中指定的ip地址手动部署在当前主库51 上。

mysql51]# ifconfig eth0:1 192.168.4.100/24

mysql51]# ifconfig eth0:1

启动服务

1 检查ssh连接

mgm56]# masterha_check_ssh --conf=/etc/mha/app1.cnf

2检查主从同步配置

mgm56]# masterha_check_repl --conf=/etc/mha/app1.cnf

3 启动管理服务

mgm56]#

masterha_manager --conf=/etc/mha/app1.cnf

--remove_dead_master_conf --ignore_last_failover

4查看状态

]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:9541) is running(0:PING_OK), master:192.168.4.51

测试配置

在客户端50主机上连接vip 192.168.4.100 访问数据库服务

1主库做用户授权

mysql51>create database db3;create table db3.a(id int);

mysql51>grant select,insert on db3.to [email protected]"%" identified

by "123abcxyz";

2客户端使用授权用户连接

client50]# mysql -h192.168.4.100 -utest -p123abcxyz db3

mysql>

mysql> insert into db3.a values(100);

Query OK, 1 row affected (0.07 sec)

mysql> select from db3.a;

测试高可用配置

把主库51的数据库服务停止

客户端仍然可以连接VIP地址192.168.4.100 访问数据库服务

mysql51]# systemctl stop mysqld

client50]# ping -c 2 192.168.4.100

client50]# mysql -h192.168.4.100 -uplj -p123abcxyz db3

mysql> select @@hostname;

mysql> select * from db3.a;

mysql> insert into db3.a values(101);

在/etc/mha/app1.cnf文件没有了主库51的配置

数据库53 - 55 把 主库地址指向 新选举出的主库ip地址

把坏掉的数据库服务器51 再添加到集群里。

MySQL51]# systemctl start mysqld

MySQL51]# mysql -uroot -p123abcxyz

mysql> change master to master_host="192.168.4.52",

-> master_user="repluser",

-> master_password="123abcxyz",

-> master_log_file="master52.000002", 当前主库日志

-> master_log_pos=1237; 对应的pos点

mysql> start slave;

mysql> show slave statusG; IO 和SQL 进程都是Yes 主机ip 52

mgm56]# vim /etc/mha/app1.cnf

[server1]

candidate_master=1

hostname=192.168.4.51

:wq

mgm56]#

masterha_manager --conf=/etc/mha/app1.cnf

--remove_dead_master_conf --ignore_last_failover

查看状态

mgm56]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:9541) is running(0:PING_OK), master:192.168.4.52

把主库52的数据库服务停止后，客户端仍然可以连接

vip地址192.168.4.100 访问MySQL数据库服务.

在/etc/mha/app1.cnf文件没有了主库52的配置

数据库53 - 55 把 主库地址指向 新选举出的主库ip地址[日志最新的51/53]