MySQL主从复制、读写分离、高可用集群搭建

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 -F

4.2 关闭防火墙或者定义防火墙规则（这里为了实验方便、选择关闭）

systemctl stop firewalld.servicesystemctl disable firewalld.service

4.3 关闭Selinux

sed -i 's@SELINUX=enforcing@SELINUX=disabled@g' /etc/selinux/configsetenforce 0

4.4 关闭NetworkManager

systemctl stop NetworkManagersystemctl disable NetworkManager

4.5 时间同步

   ntpdate 172.16.0.1

4.6 hosts文件修改

192.168.0.51 node1192.168.0.52 node2192.168.0.53 node3192.168.0.54 node4192.168.0.55 node5192.168.0.56 node6

4.7 设置主机名

hostnamectl set-hostname node1    hostnamectl set-hostname node2 hostnamectl set-hostname node3hostnamectl set-hostname node4hostnamectl set-hostname node5hostnamectl set-hostname node6

4.8 分发sshkey（node3上操作）

#创建密钥[root@node3 ~]# ssh-keygen -t rsa -P ''#分发公钥到每一台主机ssh-copy-id -i .ssh/id_rsa.pub root@node3ssh-copy-id -i .ssh/id_rsa.pub root@node1ssh-copy-id -i .ssh/id_rsa.pub root@node2ssh-copy-id -i .ssh/id_rsa.pub root@node4ssh-copy-id -i .ssh/id_rsa.pub root@node5ssh-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 -y

2 主节点Mariadb服务配置

vim /etc/my.cnf.d/server.cnf[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id = 1log_bin = log-bin

3 启动服务

systemctl start mariadb.service

4 创建主从配置账号

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=ONskip_name_resolve=ONserver_id = 11relay_log=relay-logread_only=ON#node4[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id = 12relay_log=relay-logread_only=ON#node5[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id = 13relay_log=relay-logread_only=ON

6 全部启动服务

systemctl start mariadb.service

7 主节点查看日志文件

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;#启动SLAVESTART 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: 0Master_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 测试主从复制

#主节点创建数据库hellodbMariaDB [(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 -ywget -c ftp://172.16.0.1/pub/Sources/7.x86_64/proxysql/proxysql-1.3.6-1-centos7.x86_64.rpmyum install proxysql-1.3.6-1-centos7.x86_64.rpm -y

2 配置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=4max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:3306;/tmp/proxysql.sock"default_schema="information_schema"stacksize=1048576server_version="5.5.30"connect_timeout_server=3000monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_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 Domainport = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domainhostgroup = 0        # master node status = "ONLINE"     # default: ONLINEweight = 1            # default: 1compression = 0       # default: 0},{address="192.168.0.54"port=3306hostgroup=1  #slave nodestatus = "ONLINE"     # default: ONLINEweight = 1            # default: 1compression = 0       # default: 0},{address="192.168.0.55"port=3306hostgroup=1 #slave nodestatus = "ONLINE"     # default: ONLINEweight = 1            # default: 1compression = 0       # default: 0},{address="192.168.0.56"port=3306hostgroup=1 #slave nodestatus = "ONLINE"     # default: ONLINEweight = 1            # default: 1compression = 0       # default: 0})#MySQL用户配置段mysql_users:({username = "michael" # no default , requiredpassword = "password" # default: ''default_hostgroup = 0 # default: 0max_connections=1000defult_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 startStarting ProxySQL: DONE![root@node2 init.d]# ss -tnlState      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.153Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 387Server version: 5.5.52-MariaDB MariaDB ServerCopyright (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 keepalivedglobal_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      ;;esac

3 node2 keepalived.conf配置

[root@node2 keepalived]# cat keepalived.conf ! Configuration File for keepalivedglobal_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.rpmwget -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包

yum install -y mha4mysql*

3 其他节点安装mha数据节点rpm安装包

yum install  mha4mysql-node-0.56-0.el6.noarch.rpm -y

MHA manager管理节点管理其他的MHA node数据节点需要配置一个配置文件用于定义管理事项。全局配置文件默认为/etc/masterha_default.cnf，其为可选配置。如果是仅有一组Application，我们可以自定义一个配置文件。

本文将使用以下路径的配置文件。

4 node1、node2都需要执行如下的操作

mkdir /etc/masterhavim /etc/masterha/app1.cnf

5 自定义的管理配置文件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/app1ssh_user=root repl_user=repladminrepl_password=replpassping_interval=1[server1] hostname=192.168.0.53candidate_master=1[server2] hostname=192.168.0.54candidate_master=1[server3] hostname=192.168.0.55candidate_master=1[server4] hostname=192.168.0.56candidate_master=1

6 检测各节点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=ONskip_name_resolve=ONserver_id = 1log_bin = log-binrelay-log=relay-bin

8 修改所有slave节点依赖的配置

[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id = 11        #id根据不同node做相应地修改relay_log=relay-logread_only=ONlog_bin = log-binrelay_log_purge=0

9 检查MySQL的复制集群是否通信成功

masterha_check_repl --conf=/etc/masterha/app1.cnf

10 如果成功，则启动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.cnfapp1 (pid:75846) is running(0:PING_OK), master:192.168.0.53

此时配置已经完成，这时候我们可以模拟一下数据库主节点（node3）的故障用来测试MHA高可用功能是否实现。一旦我们主节点因为某种原因发生故障，就被自动转移其他的节点成为主节点。而这个时候，我们需要立即在新的主节点中进行备份操作。而此时的故障节点就应该立即进行故障处理，恢复正常。一旦恢复正常，我们就应该导入数据库的备份文件，也需要将故障节点的mysql配置文件修改成从服务器的配置属性，使其成为从节点。主从配置上面已有介绍，因此我就不啰嗦了。