mysql主从复制以及读写分离

23rwd

之前我们已经对LNMP平台的Nginx做过了负载均衡以及高可用的部署，今天我们就通过一些技术来提升数据的高可用以及数据库性能的提升。

一、mysql主从复制

       首先我们先来看一下主从复制能够解决什么问题。

1、数据库简介

       在现在的世界发展的道路上，数据已经是必不可缺的一部分了，对数据的安全性，也成为了现在的一个值得探讨的问题。那有什么方法能够解决数据的安全性呢？   

       我们通过mysql本身的功能来实现数据的备份，之前我们也对数据可的数据进行了一些备份，但是那些都不是很好的解决办法，因为无论之前的导入导出也好，还是直接对数据库所在目录直接进行拷贝，这些技术不能保证实时性，而我们今天所介绍的就是能够对数据库实现热备份，从而提高数据库的安全性——mysql主从复制

2、主从复制原理1）mysql支持的复制类型

       （1）基于语句的复制。在主服务器上执行的sql语句，在从服务器上执行同样的语句。mysql默认使用基于语句的复制，效率比其他方式较高。

       （2）基于行的复制，把改变的内容复制过去，而不是在从服务器上在执行一遍。

       （3）混合复制类型，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2）复制的工作过程

mysql复制的工作过程如下所示：

图1

mysql复制的工作过程

       （1）在每个事务跟新完成之前。master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。

       （2）Slave将Master的Binary log复制到其中继日志。首先，slave打开一个工作线程——I/O线程，I/O线程在Master上打开一个网络连接，然后开始Binlog dump process。 Binlog dump process从Master的二进制日志中读取事件，如果已经成功链接到Master，他会进行睡眠并等待Master产生新的事件。I/O线程将这些事件写入中继日志

       （3）SQL slave thread （SQL 从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新Slave的数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志开销很小。

       以上就是mysql主从复制的原理，Slave可以有多台，主服务也可以有多台，可以使用keepalived做HA的高可用性，建议mysql的数据不要只放在共享存储上，而是每个Slave都拥有一个单独的存储存放数据。

       复制的过程中有一个很重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作，也就是不能同时执行。

3、mysql读写分离原理

       简单来说就是实现读与写的分离（图2）就是读在从服务器上读取数据，在写数据的时候是写在主服务上的。基本原理就是让主服务器处理一些简单的事务性查询，而从服务器处理select查询、数据库复制被用来把事务性查询导致的变更同步到集群的从数据库中。

图2

       目前较为常见的Mysql读写分离分为两种

1)基于程序代码内部实现

       在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支：缺点是需要开发人员来实现，运维人员无从下手。

2）基于中间代理层实现

       代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。

（1）mysql_proxy。mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断，虽然是mysql的官方产品，但是mysql官方并不推荐将其部署在生产环境下。

（2）Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程

（3）Moeeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存数过程。

       我们今天所演示的就是amoeba这款软件。版本2.2.0

二、案例环境

       本案例使用5台服务器搭建，具体拓扑如图3所示

图3

      

主机	操作系统	IP地址	主要软件
Master	CentOS 6.5 x86_64	192.168.1.2	cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz
Slave1	CentOS 6.5 x86_64	192.168.1.3	cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz
Slave2	CentOS 6.5 x86_64	192.168.1.4	cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz
Amoeba	CentOS 6.5 x86_64	192.168.1.1	amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin
客户端	CentOS 6.5 x86_64	192.168.1.5

       以上就是今天的图扑环境，如果需要解决单点故障的话，可以使用前面所讲的keepalived实现，只不过多加了几台计算机而已。Master也可以使用keepalived去避免单点故障，之前已经讲过了，这里就不在过多的进行讲解了。

1、配置时间同步

       由于主从复制的时候时间必须要保持一致，这是我们可以再master作为时间同步服务器为salve提供时间同步。我们使用rpm方式安装的ntp软件包，采用yum的方式安装

[iyunv@centos2 ~]# yum -y install ntp

[iyunv@centos2 ~]# vim /etc/ntp.conf

server 127.127.1.0

fudge 127.127.1.0 stratum 8   //这两行在任意地方添加

[iyunv@centos2 ~]# iptables  -I  INPUT - p udp  --dport  123  -j ACCEPT    //ntp  默认使用 udp 的123号端口

[iyunv@centos2 ~]# service ntpd start  //启动ntp服务

客户端同步时间

        如果没有ntpdate命令可以使用yum安装ntpdate软件包

slave1

       [iyunv@centos3 ~]# ntpdate  192.168.1.2  之后的服务器一样。

2、安装部署mysql服务器

              这里我以slave2服务器为例演示安装mysql服务器，master slave1 与slave2安装一样。

       slave2：

（1）安装mysql并创建程序用户

[iyunv@centos4 cmake-2.8.12]# ./configure  &&  gmake  &&  gmake install

[iyunv@centos4 ~]# tar zxf mysql-5.5.38.tar.gz

[iyunv@centos4 ~]# cd mysql-5.5.38

[iyunv@centos4 mysql-5.5.38]# cmake  \

> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DSYSCONFDIR=/etc/ -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all  && make  &&  make install

[iyunv@centos4 mysql-5.5.38]# useradd  -M -s /sbin/nologin  mysql
（2）优化程序执行路径

[iyunv@centos4 mysql-5.5.38]# echo   \   

>"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

（3）创建主配置文件

[iyunv@centos4 mysql-5.5.38]# cp support-files/my-medium.cnf.sh  /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

[iyunv@centos4 mysql-5.5.38]#

（4）创建服务脚本并添加为系统服务

[iyunv@centos4 mysql-5.5.38]# cp support-files/mysql.server /etc/init.d/mysqld

[iyunv@centos4 mysql-5.5.38]#

[iyunv@centos4 mysql-5.5.38]# chkconfig  --add mysqld

[iyunv@centos4 mysql-5.5.38]# chkconfig  mysqld on

[iyunv@centos4 mysql-5.5.38]# chmod  +x  /etc/init.d/mysql

（5）初始化数据库

[iyunv@centos4 mysql-5.5.38]# /usr/local/mysql/scripts/mysql_install_db  --user=mysql  --basedir=/usr/local/mysql/  --datadir=/usr/local/mysql/

[iyunv@centos4 mysql-5.5.38]#

（6）修改安装目录权限

[iyunv@centos4 mysql-5.5.38]# chown -R mysql:mysql  /usr/local/mysql/

（7）启动服务

[iyunv@centos4 mysql-5.5.38]# service mysqld start

（8）为用户root设置密码

[iyunv@centos4 mysql-5.5.38]# mysqladmin  -u root -p password '123.abc'

默认没有密码，直接在确认旧密码处回车即可。

（9）登录mysql数据库

[iyunv@centos4 mysql-5.5.38]# mysql -u root -p123.abc

3、配置master服务器（1）修改/etc/my.cnf 主配置文件

[iyunv@centos2 ~]# vim /etc/my.cnf

server-id       = 1                        //mysql数据的唯一标示（不能重复）

log-slave-updates=true                   //允许连级复制   （增加）

log-bin=master-bin                      //二进制文件名（修改）

（2）重启mysql服务

[iyunv@centos2 ~]# service mysqld restart

（3）登录mysql数据库

[iyunv@centos2 ~]# mysql -u root -p123.abc

（4）建立授权用户赋予对所有库和所有表的replication和slave权限，用户为myslave来源为这个网段的任意IP密码123.abc  //这个用户是从数据库用来复制二进制文件的用户

mysql> grant  replication  slave  on *.*  to  myslave@'192.168.1.%' identified by '123.abc';

（5）查看数据库的二进制文件名和段偏移量的值

图4

（6）退出mysql数据库

mysql> exit

Bye

（7）建立防火墙规则

root@centos2 ~]# iptables  -I  INPUT  -p  tcp  --dport  3306  -j ACCEPT  //允许目标端口为3306的入站

4、配置从服务器（slave1）（1）修改/etc/my.cnf

[iyunv@centos3 ~]# vim /etc/my.cnf

server-id       = 2                                      //不能与其他实例重复

log-bin=mysql-bin                                           //二进制日志文件名

relay-log=relay-log-bin                                  //复制过来的二进制文件名

relay-log-index=slave-relay-bin.index              //中继日志存放的文件名称

（2）重新启动mysqld服务

[iyunv@centos3 ~]# service mysqld restart

（3）登录mysql数据库

[iyunv@centos3 ~]# mysql -u root -p123.abc

（4）配置mysql数据同步

mysql> change master  to  master_host='192.168.1.2'  ,  master_user='myslave'  ,  master_password='123.abc'  , master_log_file='master-bin.000001'  ,  master_log_pos=261;

ps:最后两个配置项一定要与图4的一样否则不能同步

IP地址、用户、密码都master的数据库信息
（5）启动同步
mysql> start slave;
（6）查看同步信息


图5
       \G  这个选项是让结果格式化输出
       验证的结果一定是这两个值必须都是yes如果一般情况下出现Slasve_IO_Running: connect  有可能是防火墙问题，再不就是上面命令的红色部分与图3所显示的值不一致导致，建议重启master的mysqld服务之后在显示值，之后在salve上进行修改。在修改之前请先stop slave；之后在进行修改，改完之后在启动复制，在进行验证
       slave2的配置与slave1的配置完全一样，建议修改的地方直接复制slave1的配置，在数据库中操作时注意书写不要错误。
5、安装amoeba
（1）部署jdk环境
[iyunv@centos1 ~]# chmod  +x jdk-6u14-linux-x64.bin
[iyunv@centos1 ~]# ./jdk-6u14-linux-x64.bin
       在之后出现的阅读许可协议的时候直切按q退出就可以了
       最后会出现以下信心
Do you agree to the above license terms? [yes or no] 是否同意以上许可，这里我们直接yes  输入no则退出安装
       之后会进入一个安装过程
Press Enter to continue.....  //直接回车完成安装
安装完成之后会在当前目录下产生以下目录
[iyunv@centos1 ~]# ls  jdk1.6.0_14/  -ld
drwxr-xr-x. 10 root root 4096 Nov 11 00:54 jdk1.6.0_14/
       由于amoeba是基于jdk1.5所开发的，所以不建议使用高于1.6的jdk环境
（2）移动安装目录
[iyunv@centos1 ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
（3）修改profile文件
export JAVA_HOME=/usr/local/jdk1.6  //设置jdk的根目录
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jir
e/lib //将jdk的程序文件赋予CLASSPATH变量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME
/bin  //将jdk的程序文件赋予PATH变量
export AMOEBA_HOME=/usr/local/amoeba       //定义AMOEBA的根目录
export PATH=$PATH:$AMOEBA_HOME/bin  将amoeba的程序文件复制给PATH变量
[iyunv@centos1 ~]# . /etc/profile   //刷新profile文件
（4）创建amoeba的解压目录
[iyunv@centos1 ~]# mkdir -p /usr/local/amoeba
[iyunv@centos1 ~]# chmod -R 755 /usr/local/amoeba/
（5）解压amoeba软件包
[iyunv@centos1 ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/
（6）验证环境变量部署是否正确
[iyunv@centos1 ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
       出现以上提示则表明环境变量设置正确，jdk已经开始工作了，我们可以查看一下jdk的版本
[iyunv@centos1 ~]# javac -version
javac 1.6.0_28
6、配置amoeba实现读写分离
       声明：所有配置文件注释都是以 <!-- 内容 -->，再删除注释时请将内容也一并删除，最好是删除正行，但是有些时候只需要删除头和尾即可，里面的配置项是可以直接使用的。
（1）修改amoeba安装目录下的conf目录下的amoeba.xml主配置文件
       这个配置文件需要定义两个配置，第一是应用程序使用什么用户连接amoeba访问到后端的mysql数据库，第二个是定义默认写池以及读池。
       因为配置文件较多，只列出修改的部分
[iyunv@centos1 ~]# vim /usr/local/amoeba/conf/amoeba.xml
<property name="authenticator">
<bean      class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property>
<property name="password">123.abc</property>
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">                                                      <property name="ipFile">${amoeba.home}/conf/access_list.
conf</property>
               </bean>
             </property>
       </bean>
</property>
       粗体部分是需要修改的部分
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml </property>
          </bean>
    </property>
<property   name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml
</property>
      <property name="LRUMapSize">1500</property>
      <property name="defaultPool">master</property>     //定义默认的写池
      <property name="writePool">master</property>  //定义写池
      <property name="readPool">slaves</property> //定义读池，以上名字随便             <property name="needParse">true</property>
</queryRouter>
       以上字体加粗部分就是这个配置文件需要修改的内容
（2）修改dbServer.xml配置文件，定义写的服务器以及读的服务器并且指定算法。
[iyunv@centos1 ~]# vim  /usr/local/amoeba/conf/dbServers.xml
<property name="user">test</property>                   //26行
<property name="password">123.abc</property> // 27行
       以上两行为使用什么身份访问后台数据库  这个用户需要在所有数据库进行授全，权限为对所有库的所有表拥有所有权限，并且允许amoeba这台主机的IP访问用户名不是固定的，根据实际情况建立。
<dbServer name="master"  parent="abstractServer">
   <factoryConfig>  //定义写的服务器这个名字必须与前面定义的写池保持一致
<!-- mysql ip -->
<property name="ipAddress">192.168.1.2</property> //写服务器的IP
    </factoryConfig>
</dbServer>
<dbServer name="slave1"  parent="abstractServer"> //这个名字随便
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.3</property>//读服务器IP
   </factoryConfig>
</dbServer>
<dbServer name="slave2"  parent="abstractServer">//这个名字随便
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.4</property>//读服务器的IP
      </factoryConfig>
</dbServer>
以上部分都是需要修改的，slave2所在的部分是复制slave1的配置
<dbServer name="slaves" virtual="true">//调用主配置文件定义的读池
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property>//定义读服务器所在的配置型
    </poolConfig>
</dbServer>
       这部分内容是定义读池负载均衡算法，1表示轮询、2表示加权轮询
       以上就是所有配置文件的修改以及解释
3、在master、slave1、slave2上建立test用户，这个用户需要根据配置文件建立
mysql> grant all  on *.* to test@'192.168.1.%' identified by '123.abc';
       好了，现在可以将amoeba的服务启动并验证
[iyunv@centos1 ~]# amoeba start&    //默认amoeba启动之后需要占用一个终端，为了防止这种情况发生，我们可以直接将其放到后台运行，在启动过程中，如果发现出现很长的提示信息并且都是以java结束，那么表名服务没有启动成功，amoeba默认监听8066端口，我们可以进行验证
[iyunv@centos1 ~]# netstat -anpt | grep 8066
tcp        0      0 :::8066       :::*                        LISTEN      3405/java         
7、建立防火墙规则
[iyunv@centos1 ~]# iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
8、master、slave1、slave2都需要开放3306端口入站
[iyunv@centos2 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[iyunv@centos2 ~]# service iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables：     [确定]
[iyunv@centos2 ~]#
       另外两台从服务器也是一样的配置，或者直接将iptables stop掉
9、测试
（1）在应用服务器上
[iyunv@web ~]# yum -y install mysql
       通过代理访问mysql
[iyunv@web ~]# mysql -u amoeba -p123.abc -h 192.168.1.1 -P8066
       -P 指定amoeba的默认端口  这样默认设置需要指定端口，我们可以修改amoeba的amoeba.xml配置文件的第一个8066改为3306之后建立一条防火墙规则为允许3306端口入站并且重新启动amoeba服务
       amoeba stop
       amoeba start&
<property name="port">3306</property> //在11行
[iyunv@centos1 ~]# amoeba stop
[iyunv@centos1 ~]# amoeba start&
[iyunv@centos1 ~]# iptables -I INPUT  -p  tcp  --dport  3306  -j ACCEPT
在进行登录amoeba
[iyunv@web ~]# mysql -u amoeba -p123.abc -h 192.168.1.1
（2）在master上创建banji库，在benji库中新建class表，看是否同步到其他服务器上，然后关掉各个服务器上的lave功能，再插入区别语句
Master操作
mysql> create database banji;
mysql> create table banji.class(id int);
slave1操作


图6
mysql> stop slave;
slave2操作


图7
mysql> stop slave;
Master操作
mysql> insert into banji.class values(1);
Slave1操作

mysql> insert into banji.class values(2);

Slvae2操作

mysql> insert into banji.class values(3);

client操作

图8

       从上图可以看出执行了两次查询分别从slave1和slave2各自的数据库中读取，内容是各自的值，因为没有同步，所以值只有一个

mysql> insert into banji.class values(4);

图9      

       看不到刚插入的4，也就证明了，写操作在Master我们可以去Master上看一下

Master操作


图10
       在Master可以看见刚插入的值，我们将各个slave的功能开启
Slave1，slave2操作
mysql> start slave;
client操作
图11       开启之后我们看到的数据已经同步了，没有3，这是因为数据不一致导致的，如果数据一致就不会出现这种情况了。mysql主从复制以及读写分离就OK了       总结一下：       ①配置主从复制时注意用户权限、防火墙以及server-id（不能重复）       ②配置主从复制时，一定要根据Master的状态信息填写change 后面的值       ③配置amoeba时注意环境变量不要有书写错误，修改完成之后注意刷新profile文件使其修改生效，可以使用echo验证，也可以直接启动amoeba进行验证，或者验证jdk版本都可以。       ④修改amoeba配置文件时，注意默认读写池以及dbServer文件调用时名称一定要与主配置文件一样       ⑤注意开启相对应对防火墙端口，如果在登录的时候麻烦可以选择性的修改amoeba的端口使其改为3306这样在登录的时候就无需指定端口登陆了。       ⑥验证时一定安装上面的步骤进行验证，在测试环境中需要注意不要出现混乱。       ⑦mysql服务一定要保证开机自启动。       如果要解决掉amoeba和Master的单点问题，请使用keepalived工具实现。