MYSQL高可用简介

在具体实现上,数据库高可用方案大体可分为基于共享存储的多实例模式-业界常见的商业化数据库集群如oracle、db2等均采用这种模式,以及无共享(shared-nothing)模式,本文重点介绍的MySQL Innodb cluster就属于这种模式。

基于分布式系统的一致性算法,MySQL Innodb cluster能够保证一组不那么靠谱的服务器在一个不那么靠谱的环境条件下也总能在决策时达成共识,以确保事务以及数据的强一致性。同时,它具备故障自动检测、自动隔离能力,只要集群中有效节点超过半数,数据库就能够正常提供服务。集群的建设成本和运维成本都大大降低。

MySQL Innodb cluster支持集群的在线动态伸缩。当有计划地增加或者删除节点,以及通过正常操作停掉某些节点时,集群的节点总数都会相应发生变化,供后续的事务决策、选举算法使用。

以五个节点的集群为例,按照简单多数原则,该集群最多可以容忍两个节点同时发生故障,剩下的三个节点照常可以提供服务。如果故障范围进一步扩大,该集群(或者称之为孤岛)失去多数优势,所有事务都会挂起,直到再次获得多数优势,挂起的事务才能提交。

了解了MySQL Innodb cluster的概况,下面我们介绍它的安装、配置过程,最后通过几个典型案例,测试验证其高可用性表现。

MYSQL集群安装配置

MySQL官网()提供多个操作系统多种格式的发布包,可根据具体需求下载。需要注意的是,配置Innodb cluster需要使用MySQL Shell,因此需要下载的发布包如下:

mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar

mysql-shell-1.0.11-1.el7.x86_64.rpm

1、 安装

为方便安装,建议配置YUM源,指向安装介质所在的目录,之后通过以下命令安装MySQL Server和MySQL Shell。

# yum install mysql-community-server

# yum install mysql-shell

2、接下来执行以下步骤,完成数据库初始化工作。

# groupadd mysql

# adduser -d /home/db/mysql -g mysql mysql

# sudo -u mysql mkdir -p /home/db/mysql/data /home/db/mysql/log /home/db/mysql/run

3、将所有节点的信息(含DB公网/私网,以及部署mysqlrouter的AP)都登记到/etc/hosts

# vi /etc/my.cnf

socket=/home/db/mysql/run/mysqld.sock

# server configuration

datadir=/home/db/mysql/data

socket=/home/db/mysql/run/mysqld.sock

log_error=/home/db/mysql/log/mysqld.log

# Replication configuration parameters

server_id = 1

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

log_bin=binlog

binlog_format=ROW

# Group Replication configuration

transaction_write_set_extraction=XXHASH64

loose_group_replication_allow_local_disjoint_gtids_join=ON

4、初始化及服务启动

# mysqld --initialize

# service mysqld start

5、集群配置前准备(新建用户、授权,用户名随意)

mysql>SET SQL_LOG_BIN=0;

mysql>create user ic@'%' identified by '...';

mysql>GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO ic@'%' WITH GRANT OPTION;

mysql>GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO ic@'%' WITH GRANT OPTION;

mysql>GRANT SELECT ON *.* TO ic@'%' WITH GRANT OPTION;

mysql>GRANT DELETE ON mysql.* to ic with grant option;

mysql>GRANT INSERT ON mysql.* to ic with grant option;

mysql>GRANT UPDATE ON mysql.* to ic with grant option;

mysql>FLUSH PRIVILEGES;

mysql>SET SQL_LOG_BIN=1;

6、集群配置(主节点配置)

#mysqlsh ic@localhost:3306

mysql-js> dba.createCluster('myCluster', );

新增节点:

mysql-js> dba.getCluster().addInstance('ic@linux:3306', );

mysql-js> dba.getCluster().addInstance('ic@linux:3306', );

集群信息存储(!!!!!!集群成员数量变动--如有新成员加入--时,集群内每个节点都要执行本步骤!!!!!!)

mysqlsh-js> dba.configureLocalInstance('root@localhost:3306');

高可用配置

因为MySQL Innodb cluster原生版本对心跳丢失导致的集群故障未作自动处置,因此需要增加以下代码:

drop procedure mysql.block_service;

delimiter |

create procedure mysql.block_service(in p_switch int)

if p_switch then

select if(sum(if(member_state = 'ONLINE', 1, 0)) >= count(*)/2 + 1, ':', concat('sudo iptables -A INPUT -p tcp -j DROP -d ', @@hostname, ' --dport ', @@port)) as TO_DO

from performance_schema.replication_group_members;

select concat('sudo iptables -D INPUT -p tcp -j DROP -d ', @@hostname, ' --dport ', @@port);

delimiter ;

drop procedure mysql.kill_threads_from_mysqlrouter;

delimiter |

create procedure mysql.kill_threads_from_mysqlrouter()

declare v_done int default false;

declare v_pid bigint unsigned;

declare v_host varchar(60);

declare v_user varchar(32);

declare v_command varchar(16);

declare v_state varchar(64);

declare v_group_has_quorum varchar(8);

declare c_processlist cursor for

select processlist_host, processlist_user, processlist_command, processlist_state, processlist_id

from performance_schema.threads t, mysql_innodb_cluster_metadata.hosts h, mysql_innodb_cluster_metadata.routers r

where h.host_id = r.host_id

and t.processlist_host = h.host_name

and t.processlist_id is not null;

declare continue handler for not found set v_done = true;

select if(sum(if(member_state = 'ONLINE', 1, 0)) >= count(*)/2 + 1, 'YES', 'NO') into v_group_has_quorum

from performance_schema.replication_group_members;

if v_group_has_quorum = 'NO' then

open c_processlist;

fetch c_processlist into v_host, v_user, v_command, v_state, v_pid;

if v_done then

kill v_pid;

close c_processlist;

delimiter ;

drop procedure mysql.resume_local_group_replication;

delimiter |

create procedure mysql.resume_local_group_replication()

declare v_state varchar(16);

declare v_mode varchar(8);

select m.member_state

into v_state

from performance_schema.replication_group_members m

where m.member_host = @@hostname;

if v_state = 'error' then

stop group_replication;

start group_replication;

delimiter ;

创建密码文件

# mysql_config_editor set --login-path=client --host=localhost --user=root --password

# mv ~/.mylogin.cnf $(grep ^mysql: /etc/passwd | awk 'BEGIN')

# chown mysql:mysql $(grep ^mysql: /etc/passwd | awk 'BEGIN')/.mylogin.cnf

# chmod 400 $(grep ^mysql: /etc/passwd | awk 'BEGIN')/.mylogin.cnf

脚本部署

/usr/bin/resume_local_gr

/usr/bin/resume_local_gr.awk

/var/lib/systemd/system/resume_local_gr.service

启动服务

service resume_local_gr start

---停服务:service resume_local_gr stop

---重启服务:service resume_local_gr restart

---查看状态: service resume_local_gr status

AP端myrouter配置

# adduser -d /home/ap/myrouter -g myrouter myrouter

# 将所有节点的信息(本机及 DB公网/私网)都登记到/etc/hosts。若不做mysqlrouter负载均衡,则其它部署mysqlrouter的AP无需登记到本机

# mysqlrouter --bootstrap ic@128.64.108.107:3306 --user myrouter --directory /home/ap/myrouter --conf-skip-tcp --conf-use-sockets --force

创建密码文件

# mysql_config_editor set --login-path=primary --user=ic --host=localhost --password --socket=/home/ap/myrouter/mysql.sock

# mysql_config_editor set --login-path=secondary --user=ic --host=localhost --password --socket=/home/ap/myrouter/mysqlro.sock

高可用性测试

初始状态,三个节点状态均为ONLINE,主节点为linux,集群状态一切正常,并且最多可以容忍一个节点发生故障。

主节点(Linux)心跳故障,从其自身视角(见下图)看,只有自己状态为ONLINE,但是由于无法跟其它节点通信,因此其它节点状态都为“UNREACHABLE”;另外,其所在孤岛只有一个节点,已失去多数优势,因此所有事务都会挂起。

另外一个孤岛由于取得多数优势,并且当前孤岛没有主节点,因此linux被自动选为主节点。从它的视角(见下图)看,主节点已变为linux108120,原主节点linux108120的状态已被置为MISSING,整个集群有两个活动节点,事务可以正常处理,但是已经无法容忍故障扩散。

从节点(Linux)心跳故障排除后,客户化定制的自动拉起服务被触发,重新回到集群中。

关于mysql高可用的案例还有很多,篇幅有限只能介绍到这里啦,若需要文中材料的,欢迎留言联系作者索取哟。

< END >

感谢您的阅读!

厦门开发中心测试与推广支持处

系统与非功能小组出品

作者&编辑:方妍

关注我们

专注,所以专业