189 8069 5689
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 查看服务器硬件信息
dmidecode | grep "Product Name"3.2 查看 CPU CPU型号
lscpu | grep "Model name"3.3 查看CPU个数
lscpu | grep "^CPU(s)"3.4 查看内存大小
free -h | grep Mem|awk '{print $2}'四、系统初始化
4.1 清空防火墙规则
iptables -F4.2 关闭防火墙或者定义防火墙规则（这里为了实验方便、选择关闭）
systemctl stop firewalld.service systemctl disable firewalld.service4.3 关闭Selinux
sed -i 's@SELINUX=enforcing@SELINUX=disabled@g' /etc/selinux/config setenforce 04.4 关闭NetworkManager
systemctl stop NetworkManager systemctl disable NetworkManager4.5 时间同步
ntpdate 172.16.0.14.6 hosts文件修改
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 node64.7 设置主机名
hostnamectl set-hostname node1 hostnamectl set-hostname node2 hostnamectl set-hostname node3 hostnamectl set-hostname node4 hostnamectl set-hostname node5 hostnamectl set-hostname node64.8分发sshkey（node3上操作）
#创建密钥 [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个节点）
yum install mariadb mariadb-server -y2 主节点Mariadb服务配置
vim /etc/my.cnf.d/server.cnf [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 1 log_bin = log-bin3 启动服务
systemctl start mariadb.service4 创建主从配置账号
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服务配置
#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=ON6 全部启动服务
systemctl start mariadb.service7 主节点查看日志文件
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 建立主从复制关系
#从节点建立主从关系 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: 19 测试主从复制
#主节点创建数据库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安装
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 -y2 配置ProxySQL
[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
[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连接测试，连接成功
[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配置
[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都需要更改。
mysql_ifaces="192.168.0.100:6032;/tmp/proxysql_admin.sock" interfaces="192.168.0.100:3306;/tmp/proxysql.sock"2 通知脚本（notify.sh）,用于通知服务器故障转移。
[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 ;; esac3 node2 keepalived.conf配置
[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安装包（管理包、数据包）
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.rpm2 node1、node2做MHA管理节点并做高可用，因此在node1、node2安装mha所有rpm包
yum install -y mha4mysql*3 其他节点安装mha数据节点rpm安装包
yum install mha4mysql-node-0.56-0.el6.noarch.rpm -yMHA manager管理节点管理其他的MHA node数据节点需要配置一个配置文件用于定义管理事项。全局配置文件默认为/etc/masterha_default.cnf，其为可选配置。如果是仅有一组Application，我们可以自定义一个配置文件。
本文将使用以下路径的配置文件。
4 node1、node2都需要执行如下的操作
mkdir /etc/masterha vim /etc/masterha/app1.cnf5 自定义的管理配置文件app1.cnf配置
[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=16 检测各节点SSH通信是否成功
masterha_check_ssh --conf=/etc/masterha/app1.cnf #最后一行输出如下信息，表示通过检测。 [info] All SSH connection tests passed successfully.7 修改主节点（node3）和从节点（node4、5、6）的配置
#主节点master配置： [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 1 log_bin = log-bin relay-log=relay-bin8 修改所有slave节点依赖的配置
[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=09 检查MySQL的复制集群是否通信成功
masterha_check_repl --conf=/etc/masterha/app1.cnf10 如果成功，则启动MHA
nohup masterha_manager --conf=/etc/masterha/app1.cnf >/data/masterha/app1/manager.log 2>&1 &11 查看master节点的当前状态
[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配置文件修改成从服务器的配置属性，使其成为从节点。主从配置上面已有介绍，因此我就不啰嗦了。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。