MySQL(4)-AB主从复制与读写分离

    本篇博客介绍的是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力实现mysql高可用性

    首先 , 我们先了解AB主从复制  . AB复制即在A主机上做create , update, insert, delete 等数据库 , 表 , 记录的增删该查操作 , B主机会自动做数据库 , 表 , 记录的同步跟新

AB主从复制

工作原理和过程

                                                                                                                    (图片源于网络)

说明 : 1.在主库上把数据更改记录到二进制日志中
         2.备库将主库上的日志复制到自己的中继日志中
         3.备库读取中继日志中的事件 , 将其重放到备库数据库上
         4.备库根据其中的内容做出主库相应的操作 , 对数据进行更新

过程 : 

         A主机 : create database db ----- > 将此命令写入本机的二进制日志中
         B主机: I/O线程 监测并读取主机A主机上的二进制日志文件新增的内容 , 并且将更新的内容写入到B主机自己的中继日志文件中
                   SQL线程 : 读取B主机上的中继日志文件中心的SQL语句 , 并且自动执行这些SQL语句 , 最终在B主机上创建db这个库

读写分离

原理和工作过程

        MySQL的主从复制和MySQL的读写分离两者有着紧密的联系 , 首先部署主从复制 , 只有主从复制完了 , 才能在此基础上进行数据的读写分离 .

        简单来说 , 读写分离就是在主服务器上写 , 只在从服务器上读 , 基本的原理是让主数据库处理事务性查询 , 而从数据库处理select查询 , 数据库复制被用来把事务性查询导致的改变同步到集群中的从数据库

        举个例子来说 , 主数据库就相当于银行的存款取款一体机 , 从就相当于取款机 , 当用户要存钱(写入数据)就在存取款机上操作 , 当用户要取款(读)就在取款机上操作

        读写分离可以基于程序代码内部实现 , 也可以基于中间件(mysql-proxy , amoeba , Atlas)代理层实现

 AB主从复制与读写分离实现数据库高可用部署

实验拓扑图 :

环境 :centos7

主数据库服务器(master) : 192.168.11.11

从数据库服务器(slave) : 192.168.11.12

代理服务器 : 192.168.11.13  

一 . AB主从复制部署

部署思路  :

1.在A主机上的/etc/my.cnf主配置文件中开启binlog二进制日志文件功能 , 并且给主机设置server-id唯一的标识编号 , 重启Mariadb服务
2.在A主机上创建用于AB主从复制的用户账号 , 并查看master状态中的binlog日志文件的position位置数值
3.在B主机上的/etc/my.cnf主配置文件中设置server-id唯一标识编码 , 重启mariadb服务
4.在B主机上用help change master to 查命令帮助 , 并用change master 命令告诉B主机它的master主人的正确幸喜
5.在B主机上用start slave 启动mariadb的随从服务 , 并用show slave status 查看Ab主从复制的数据同步状态 , 确定两个线程的状态为yes

--------在A主机(192.168.11.11)的操作--------

1 . 安装好数据库 , 并启动数据库服务

部署流程可参考(不做过多解释):https://blog.51cto.com/14181896/2361492

2 . 编辑主配置文件 /etc/my.cnf  ,添加下图234行内容

3 . 重启服务 , 创建用于AB主从复制的用户账号 (账号密码均为rep) , 如图:

4 . 查看master状态中的binlog日志文件的position位置数值(position值会根据重启次数改变)

--------在B主机(192.168.11.12)的操作--------

1 . 同A主机安装部署好数据库服务

2 . 修改主配置文件 /etc/my.cnf , 添加如下内容 :

3 . 重启服务 , 测试账号rep用户是否能远程访问master主机的数据库服务

4 . 在数据库中 , 用change master命令告诉B主机他的主人master的正确信息

CHANGE MASTER TO
MASTER_HOST='192.168.11.11',                          #A主机IP
MASTER_USER='rep',                                    #用于主从复制的用户
MASTER_PASSWORD='rep',                                #用于主从复制的密码
MASTER_PORT=3306,                                     #端口号为3306
MASTER_LOG_FILE='master-bin.000001',                  #对应A主机master状态切记的值
MASTER_LOG_POS=403,                                   #A主机position值
MASTER_CONNECT_RETRY=10;                              #值和主连不上重试时间为10S

5 . 使用start slave启动Mariadb的随从服务 , 并用show  slave  status 查看AB主从复制的数据同步状态 , 要确认IO和SQL两个线程的状态为yes  

6 . 测试一下AB主从复制是否配置成功 , 在A(192.168.11.11)上创建一个库 , 看B主机(192.168.11.12)是否同步该数据库

A主机 :

B主机 :

可以看到同步到了新创建的数据库testrep , 到此主从复制配置结束 !!

二 . 采用中间件来实现读写分离操作

读写分离的中间件分为很多种 ,在此实验使用Atlas软件来实现读写分离

注 :代理服务器(192.168.11.13)不需要安装mysql服务

atlas介绍 :

由360开发 , 其优点有 :
(1)、基于mysql-proxy-0.8.2进行修改，代码完全开源；
(2)、比较轻量级，部署配置也比较简单；
(3)、支持DB读写分离；
(4)、支持从DB读负载均衡，并自动剔除故障从DB；
(5)、支持平滑上下线DB；
(6)、具备较好的安全机制（IP过滤、账号认证）；
(7)、版本更新、问题跟进、交流圈子都比较活跃。

代理服务器部署流程 :

1 . 在192.168.11.13上安装和配置atlas软件

rpm  -ivh  Atlas-2.2.1.el6.x86_64.rpm       (该源码包需在网上下载 , 百度搜索Atlas rpm源码包)

echo "PATH=$PATH:/usr/local/mysql-proxy/bin/" >> /etc/profile      //设置变量环境

source  /etc/profile                                                                          //启用变量环境

ll /usr/local/mysql-proxy/

             说明 :  

             bin目录下放的都是可执行文件

             1. “encrypt”是用来生成MySQL密码加密的，在配置的时候会用到

             2. “mysql-proxy”是MySQL自己的读写分离代理

             3. “mysql-proxyd”是360弄出来的，后面有个“d”，服务的启动、重启、停止。都是用他来执行的

             conf目录下放的是配置文件

             1. “test.cnf”只有一个文件，用来配置代理的，可以使用vim来编辑

             2.   lib目录下放的是一些包，以及Atlas的依赖

             3 .  log目录下放的是日志，如报错等错误信息的记录

             进入bin目录，使用encrypt来对数据库的密码进行加密，此处用户和密码为rep

cd /usr/local/mysql-proxy/bin/

./encrypt 01   生成加密密码,并复制此密码(上图)

cd /usr/local/mysql-proxy/conf/

cp -v  test.cnf  test.cnf.bak    //备份test.cnf配置文件

vi  test.conf   修改后的读写分享的完整配置文件内容

     test.cnf读写分离配置文件功能说明：

           1:[mysql-proxy]   //读写分离代理配置

           6:admin-username = user    //管理接口的用户名

           9:admin-password = pwd     //管理接口的密码

           12:proxy-backend-addresses = 192.168.100.25:3306    //主数据库的IP地址和端口号(可读可写)

           15:proxy-read-only-backend-addresses = 192.168.100.26:3306@1,192.168.100.27:3306@2   //读服务器的ip、端口和权重

           18:pwds = admin:VFnEp9P4Vu4=, rep:VFnEp9P4Vu4=      //后端MYSQL的用户名和encrypt命令生成的加密密码

           21:daemon = true      //设置为守护进程模式(后台运行)

           24:keepalive = true   //允许keepalive

           27:event-threads = 8   //工作线程数为8

           30:log-level = message   //日志等级为message消息

           33:log-path = /usr/local/mysql-proxy/log    //日志文件路径

           45:proxy-address = 0.0.0.0:3306      //Atlas监听的管理接口IP和端口

           48:admin-address = 0.0.0.0:2345      //Atlas监听的管理接口IP和端口

 2 . 配置好Atlas , 启动atlas服务 

/usr/local/mysql-proxy/bin/mysql-proxyd  test  start

(重启atlas服务：/usr/local/mysql-proxy/bin/mysql-proxyd  test  restart)

设置mysql-proxyd开机启动：

echo "/usr/local/mysql-proxy/bin/mysql-proxyd  test  start" >> /etc/profile

source  /etc/profile

抓包验证 :

tcpdump抓包:tcpdump -i ens33 -nn tcp port 3306

在A主机上创建表 , 并写入数据:

B主机同步

抓包结果 :

    到此 ,AB主从复制和读写分离部署到此完结  !!