MySQL主从复制、读写分离、高可用集群搭建
一、服务介绍
1.1 Keepalived
Keepalived，见名知意，即保持存活，其目的是解决单点故障，当一台服务器宕机或者故障时自动切换到其他的服务器中。Keepalived是基于VRRP协议实现的。VRRP协议是用于实现路由器冗余的协议，VRRP协议将两台或多台路由器设备虚拟成虚拟设备，可以对外提供虚拟路由器IP(一个或多个)，即漂移IP（VIP）。
1.2 ProxySQL
ProxySQL是一个高性能，高可用性的MySQL代理服务，用于实现数据库的代理和读写分离的功能。
1.3 Mariadb
Mariadb是多用户，多线程的SQL数据库服务器。它是C/S架构，即client/server，客服端/服务端架构。MariaDB基于事务的Maria存储引擎，使用了Percona的 XtraDB，InnoDB的变体，性能十分的强大。mariadb由开源社区维护，采用GPL授权许可，完全兼容MySQL。
1.4 MHA
MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave节点成为新的master节点，在此期间，MHA会通过于其它从节点获取额外信息来避免一致性方面的问题。MHA 还提供了master节点的在线切换功能，即按需切换master/slave节点。
二、架构要求
2.1 架构要求
实现MsySQL数据库服务器主从复制、主从高可用、读写分离、ProxySQL高可用的功能。
2.2 系统版本选择
OS:centos7.3
Kernel：3.10.0-514.el7.x86_64
Archive：X86_64
2.3 部署环境
三、Linux系统环境查看
3.1 查看服务器硬件信息
|
1
|
dmidecode |
grep
"Product Name"
|
3.2 查看 CPU CPU型号
|
1
|
lscpu |
grep
"Model name"
|
3.3 查看CPU个数
|
1
|
lscpu |
grep
"^CPU(s)"
|
3.4 查看内存大小
|
1
|
free
-h |
grep
Mem|
awk
'{print $2}'
|
四、系统初始化
4.1 清空防火墙规则
|
1
|
iptables -F
|
4.2 关闭防火墙或者定义防火墙规则（这里为了实验方便、选择关闭）
|
1
2
|
systemctl stop firewalld.service
systemctl disable firewalld.service
|
4.3 关闭Selinux
|
1
2
|
sed
-i
's@SELINUX=enforcing@SELINUX=disabled@g'
/etc/selinux/config
setenforce 0
|
4.4 关闭NetworkManager
|
1
2
|
systemctl stop NetworkManager
systemctl disable NetworkManager
|
4.5 时间同步
|
1
|
ntpdate 172.16.0.1
|
4.6 hosts文件修改
|
1
2
3
4
5
6
|
192.168.0.51 node1
192.168.0.52 node2
192.168.0.53 node3
192.168.0.54 node4
192.168.0.55 node5
192.168.0.56 node6
|
4.7 设置主机名
|
1
2
3
4
5
6
|
hostnamectl
set
-
hostname
node1
hostnamectl
set
-
hostname
node2
hostnamectl
set
-
hostname
node3
hostnamectl
set
-
hostname
node4
hostnamectl
set
-
hostname
node5
hostnamectl
set
-
hostname
node6
|
4.8 分发sshkey（node3上操作）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
#创建密钥
[root@node3 ~]
# ssh-keygen -t rsa -P ''
#分发公钥到每一台主机
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node3
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node1
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node2
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node4
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node5
ssh
-copy-
id
-i .
ssh
/id_rsa
.pub root@node6
#将私钥和公钥发往其他六个节点，保证各节点基于密钥通信
[root@node3 ~]
# scp .ssh/id_rsa .ssh/id_rsa.pub root@node1:/root/.ssh/
id_rsa 100% 1675 1.6KB
/s
00:00
id_rsa.pub 100% 392 0.4KB
/s
00:00
[root@node3 ~]
# scp .ssh/id_rsa .ssh/id_rsa.pub root@node2:/root/.ssh/
id_rsa 100% 1675 1.6KB
/s
00:00
id_rsa.pub 100% 392 0.4KB
/s
00:00
[root@node3 ~]
# scp .ssh/id_rsa .ssh/id_rsa.pub root@node4:/root/.ssh/
id_rsa 100% 1675 1.6KB
/s
00:00
id_rsa.pub 100% 392 0.4KB
/s
00:00
[root@node3 ~]
# scp .ssh/id_rsa .ssh/id_rsa.pub root@node5:/root/.ssh/
id_rsa 100% 1675 1.6KB
/s
00:00
id_rsa.pub 100% 392 0.4KB
/s
00:00
[root@node3 ~]
# scp .ssh/id_rsa .ssh/id_rsa.pub root@node6:/root/.ssh/
id_rsa 100% 1675 1.6KB
/s
00:00
id_rsa.pub 100% 392 0.4KB
/s
00:00
|
五、环境部署
5.1 主从复制配置（node3、node4、node5、node6）
1 安装Mariadb（4个节点）
|
1
|
yum
install
mariadb mariadb-server -y
|
2 主节点Mariadb服务配置
|
1
2
3
4
5
6
|
vim
/etc/my
.cnf.d
/server
.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 1
log_bin = log-bin
|
3 启动服务
|
1
|
systemctl start mariadb.service
|
4 创建主从配置账号
|
1
2
3
|
grant all privileges on *.* to
'michael'
@
'192.168.0.%'
identified by
'password'
;
grant replication slave,replication client on *.* to
'repuser'
@
'192.168.0.%'
identified by
'repass'
;
flush privileges;
|
5 其他从节点Mariadb服务配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
#node3
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 11
relay_log=relay-log
read_only=ON
#node4
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 12
relay_log=relay-log
read_only=ON
#node5
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 13
relay_log=relay-log
read_only=ON
|
6 全部启动服务
|
1
|
systemctl start mariadb.service
|
7 主节点查看日志文件
|
1
2
3
4
5
6
7
8
9
|
MariaDB [(none)]> SHOW MASTER LOGS;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| log-bin.000001 | 30331 |
| log-bin.000002 | 1038814 |
| log-bin.000003 | 899 |
+----------------+-----------+
3 rows
in
set
(0.00 sec)
|
8 建立主从复制关系
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
#从节点建立主从关系
CHANGE MASTER TO MASTER_HOST=
'192.168.0.53'
,MASTER_USER=
'repuser'
,MASTER_PASSWORD=
'repass'
,MASTER_LOG_FILE=
'log-bin.000003'
,MASTER_LOG_POS=899;
#启动SLAVE
START SLAVE ;
MariaDB [(none)]> START SLAVE ;
Query OK, 0 rows affected (0.00 sec)
#查看slave状态，确认主从复制是否配置成功（从节点都需要配置）
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.0.53
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000003
Read_Master_Log_Pos: 899
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 527
Relay_Master_Log_File: log-bin.000003
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: 899
Relay_Log_Space: 815
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
|
9 测试主从复制
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
#主节点创建数据库hellodb
MariaDB [(none)]> CREATE DATABASE hellodb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql
| performance_schema |
|
test
|
+--------------------+
#各个从节点查看，显示hellodb数据库存在，主从配置成功
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
|
test
|
+--------------------+
5 rows
in
set
(0.00 sec)
|
5.2 读写分离配置
1 Keepalived、ProxySQL、Mariadb安装
|
1
2
3
|
yum
install
keepalived mariadb -y
wget -c
ftp
:
//172
.16.0.1
/pub/Sources/7
.x86_64
/proxysql/proxysql-1
.3.6-1-centos7.x86_64.rpm
yum
install
proxysql-1.3.6-1-centos7.x86_64.rpm -y
|
2 配置ProxySQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
[root@node1 keepalived]
# cat /etc/proxysql.cnf
datadir=
"/var/lib/proxysql"
#管理配置段
admin_variables=
{
admin_credentials=
"admin:admin"
mysql_ifaces=
"127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
#MySQL变量配置段
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=
true
poll_timeout=2000
interfaces=
"0.0.0.0:3306;/tmp/proxysql.sock"
default_schema=
"information_schema"
stacksize=1048576
server_version=
"5.5.30"
connect_timeout_server=3000
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=
true
sessions_sort=
true
connect_retries_on_failure=10
}
#MySQL服务配置段
mysql_servers =
(
{
address =
"192.168.0.53"
# no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306
# no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0
# master node
status =
"ONLINE"
# default: ONLINE
weight = 1
# default: 1
compression = 0
# default: 0
},
{
address=
"192.168.0.54"
port=3306
hostgroup=1
#slave node
status =
"ONLINE"
# default: ONLINE
weight = 1
# default: 1
compression = 0
# default: 0
},
{
address=
"192.168.0.55"
port=3306
hostgroup=1
#slave node
status =
"ONLINE"
# default: ONLINE
weight = 1
# default: 1
compression = 0
# default: 0
},
{
address=
"192.168.0.56"
port=3306
hostgroup=1
#slave node
status =
"ONLINE"
# default: ONLINE
weight = 1
# default: 1
compression = 0
# default: 0
}
)
#MySQL用户配置段
mysql_users:
(
{
username =
"michael"
# no default , required
password =
"password"
# default: ''
default_hostgroup = 0
# default: 0
max_connections=1000
defult_schema=
"test"
active = 1
# default: 1
}
)
mysql_query_rules:
(
)
scheduler=
(
)
#MySQL读写配置段
mysql_replication_hostgroups=
(
{
writer_hostgroup=0
reader_hostgroup=1
}
)
|
3 启动ProxySQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@node1]
# service proxysql start
Starting ProxySQL: DONE!
[root@node2 init.d]
# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 127.0.0.1:6032 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
|
4 node1连接测试，连接成功
|
1
2
3
4
5
6
|
[root@node1 ~]
# mysql -umichael -ppassword -h 192.168.0.153
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection
id
is 387
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
|
node2配置和node1配置如出一辙。
5.3 ProxySQL高可用配置
1 node1 keepalived.conf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
[root@node1 ~]
# cat /etc/keepalived/keepalived.conf
! Configuration File
for
keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1
vrrp_mcast_group4 224.1.101.114
}
#服务器宕机，既执行降级，切换服务器；否则退出。
vrrp_script chk_down{
script
"[[ -f /etc/keepalived/down ]] && exit 1 || exit 0"
interval 1
weight -10
fall 1
rize 1
}
#健康状态检测脚本，检测proxysql是否存活
vrrp_script chk_proxysql {
script
"killall -0 proxysql && exit 0 || exit 1"
interval 1
weight -10
fall 1
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass fs3D4Gr
}
virtual_ipaddress {
192.168.0.100
/24
dev ens33 label ens33:0
}
#调用脚本
track_script {
chk_down
chk_proxysql
}
notify_master
"/etc/keepalived/notify.sh master"
notify_backup
"/etc/keepalived/notify.sh backup"
notify_fault
"/etc/keepalived/notify.sh fault"
}
|
这里设定了虚拟IP（VIP），因此需要将prosql.conf配置文件内的IP更改，注意node1、node2都需要更改。
|
1
2
|
mysql_ifaces=
"192.168.0.100:6032;/tmp/proxysql_admin.sock"
interfaces=
"192.168.0.100:3306;/tmp/proxysql.sock"
|
2 通知脚本（notify.sh）,用于通知服务器故障转移。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
[root@node1 keepalived]
# cat notify.sh
#!/bin/bash
#
contact=
'root@localhost'
notify() {
mailsubject=
"vrrp:$(hostname) to be $1"
mailbody=
"$(hostname) to be $1,vrrp transition, $(date)."
echo
"$mailbody"
| mail -s
"$mailsubject"
$contact
}
case
$1
in
master)
notify master
service proxysql start
;;
backup)
notify backup
service proxysql start
;;
fault)
notify fault
service proxysql stop
;;
*)
echo
"Usage: $(basename $0) {master|backup|fault}"
exit
1
;;
esac
|
3 node2 keepalived.conf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
[root@node2 keepalived]
# cat keepalived.conf
! Configuration File
for
keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node2
vrrp_mcast_group4 224.1.101.114
}
#服务器宕机，既执行降级，切换服务器；否则退出。
vrrp_script chk_down{
script
"[[ -f /etc/keepalived/down ]] && exit 1 || exit 0"
interval 1
weight -10
fall 1
rize 1
}
#健康状态检测脚本，检测proxysql是否存活
vrrp_script chk_proxysql {
script
"killall -0 proxysql && exit 0 || exit 1"
interval 1
weight -10
fall 1
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass fs3D4Gr
}
virtual_ipaddress {
192.168.0.100
/24
dev ens33 label ens33:0
}
#调用脚本
track_script {
chk_down
chk_proxysql
}
notify_master
"/etc/keepalived/notify.sh master"
notify_backup
"/etc/keepalived/notify.sh backup"
notify_fault
"/etc/keepalived/notify.sh fault"
}
|
notify.sh脚本同上面保持一致。此时高可用已经配置完成，当然你可以通过停止某个节点的服务来测试高可用是否实现。
5.4 通过MHA实现MySQL主节点高可用
MHA服务有两种角色，MHA Manager(管理节点)和 MHA Node(数据节点)。
MHAManager，通常单独部署在一台独立机器上管理多个master/slave 集群，每master/slave 集群称作一个application。
MHA node：运行在每台MySQL服务器上(master/slave/manager)，它通过监控，具备解析和清理 logs功能的脚本来加快故障转移。
MHA需要基于SSH通信，这个我们在一开始的环境部署就配置好了。
1 下载MHA安装包（管理包、数据包）
|
1
2
|
wget -c http:
//192
.168.0.123
/install_package/down/Sources/mha/mha4mysql-manager-0
.56-0.el6.noarch.rpm
wget -c http:
//192
.168.0.123
/install_package/down/Sources/mha/mha4mysql-node-0
.56-0.el6.noarch.rpm
|
2 node1、node2做MHA管理节点并做高可用，因此在node1、node2安装mha所有rpm包
|
1
|
yum
install
-y mha4mysql*
|
3 其他节点安装mha数据节点rpm安装包
|
1
|
yum
install
mha4mysql-node-0.56-0.el6.noarch.rpm -y
|
MHA manager管理节点管理其他的MHA node数据节点需要配置一个配置文件用于定义管理事项。全局配置文件默认为/etc/masterha_default.cnf，其为可选配置。如果是仅有一组Application，我们可以自定义一个配置文件。
本文将使用以下路径的配置文件。
4 node1、node2都需要执行如下的操作
|
1
2
|
mkdir
/etc/masterha
vim
/etc/masterha/app1
.cnf
|
5 自定义的管理配置文件app1.cnf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
[root@node1 ~]
# cat /etc/masterha/app1.cnf
[server default]
user=michael
password=password
manager_workdir=
/data/masterha/app1
manager_log=
/data/masterha/app1/manager
.log
remote_workdir=
/data/masterha/app1
ssh_user=root
repl_user=repladmin
repl_password=replpass
ping_interval=1
[server1]
hostname
=192.168.0.53
candidate_master=1
[server2]
hostname
=192.168.0.54
candidate_master=1
[server3]
hostname
=192.168.0.55
candidate_master=1
[server4]
hostname
=192.168.0.56
candidate_master=1
|
6 检测各节点SSH通信是否成功
|
1
2
3
4
|
masterha_check_ssh --conf=
/etc/masterha/app1
.cnf
#最后一行输出如下信息，表示通过检测。
[info] All SSH connection tests passed successfully.
|
7 修改主节点（node3）和从节点（node4、5、6）的配置
|
1
2
3
4
5
6
7
|
#主节点master配置：
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 1
log_bin = log-bin
relay-log=relay-bin
|
8 修改所有slave节点依赖的配置
|
1
2
3
4
5
6
7
8
|
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id = 11
#id根据不同node做相应地修改
relay_log=relay-log
read_only=ON
log_bin = log-bin
relay_log_purge=0
|
9 检查MySQL的复制集群是否通信成功
|
1
|
masterha_check_repl --conf=
/etc/masterha/app1
.cnf
|
10 如果成功，则启动MHA
|
1
|
nohup
masterha_manager --conf=
/etc/masterha/app1
.cnf >
/data/masterha/app1/manager
.log 2>&1 &
|
11 查看master节点的当前状态
|
1
2
|
[root@node1 ~]
# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:75846) is running(0:PING_OK), master:192.168.0.53
|
此时配置已经完成，这时候我们可以模拟一下数据库主节点（node3）的故障用来测试MHA高可用功能是否实现。一旦我们主节点因为某种原因发生故障，就被自动转移其他的节点成为主节点。而这个时候，我们需要立即在新的主节点中进行备份操作。而此时的故障节点就应该立即进行故障处理，恢复正常。一旦恢复正常，我们就应该导入数据库的备份文件，也需要将故障节点的mysql配置文件修改成从服务器的配置属性，使其成为从节点。主从配置上面已有介绍，因此我就不啰嗦了。
本文转自 PowerMichael 51CTO博客，原文链接：http://blog.51cto.com/huwho/1953641，如需转载请自行联系原作者
