MySQL主从复制以及读写分离
坚守“ 做人真诚 · 做事靠谱 · 口碑至上 · 高效敬业 ”的价值观，专业网站建设服务10余年为成都成都除甲醛小微创业公司专业提供成都定制网页设计营销网站建设商城网站建设手机网站建设小程序网站建设网站改版，从内容策划、视觉设计、底层架构、网页布局、功能开发迭代于一体的高端网站建设服务。
之前我们已经对LNMP平台的Nginx做过了负载均衡以及高可用的部署，今天我们就通过一些技术来提升数据的高可用以及数据库性能的提升。
一、mysql主从复制
首先我们先来看一下主从复制能够解决什么问题。
在现在的世界发展的道路上，数据已经是必不可缺的一部分了，对数据的安全性，也成为了现在的一个值得探讨的问题。那有什么方法能够解决数据的安全性呢？
我们通过mysql本身的功能来实现数据的备份，之前我们也对数据可的数据进行了一些备份，但是那些都不是很好的解决办法，因为无论之前的导入导出也好，还是直接对数据库所在目录直接进行拷贝，这些技术不能保证实时性，而我们今天所介绍的就是能够对数据库实现热备份，从而提高数据库的安全性——mysql主从复制
（1）基于语句的复制。在主服务器上执行的sql语句，在从服务器上执行同样的语句。mysql默认使用基于语句的复制，效率比其他方式较高。
（2）基于行的复制，把改变的内容复制过去，而不是在从服务器上在执行一遍。
（3）混合复制类型，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
mysql复制的工作过程如下所示：
图1mysql复制的工作过程
（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上并行操作，也就是不能同时执行。
简单来说就是实现读与写的分离（图2）就是读在从服务器上读取数据，在写数据的时候是写在主服务上的。基本原理就是让主服务器处理一些简单的事务性查询，而从服务器处理select查询、数据库复制被用来把事务性查询导致的变更同步到集群的从数据库中。
图2
目前较为常见的Mysql读写分离分为两种
在代码中根据select、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支：缺点是需要开发人员来实现，运维人员无从下手。
代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。
（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去避免单点故障，之前已经讲过了，这里就不在过多的进行讲解了。
由于主从复制的时候时间必须要保持一致，这是我们可以再master作为时间同步服务器为salve提供时间同步。我们使用rpm方式安装的ntp软件包，采用yum的方式安装
[root@centos2 ~]# yum -y install ntp
[root@centos2 ~]# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8 //这两行在任意地方添加
[root@centos2 ~]# iptables -I INPUT - p udp --dport 123 -j ACCEPT //ntp 默认使用 udp的123号端口
[root@centos2 ~]# service ntpd start //启动ntp服务
客户端同步时间
如果没有ntpdate命令可以使用yum安装ntpdate软件包
slave1
[root@centos3 ~]# ntpdate 192.168.1.2 之后的服务器一样。
这里我以slave2服务器为例演示安装mysql服务器，master slave1与slave2安装一样。
slave2：
[root@centos4 cmake-2.8.12]# ./configure && gmake && gmake install
[root@centos4 ~]# tar zxf mysql-5.5.38.tar.gz
[root@centos4 ~]# cd mysql-5.5.38
[root@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
[root@centos4 mysql-5.5.38]# useradd -M -s /sbin/nologin mysql
[root@centos4 mysql-5.5.38]# echo \
>"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos4 mysql-5.5.38]# cp support-files/my-medium.cnf.sh /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# chkconfig --add mysqld
[root@centos4 mysql-5.5.38]# chkconfig mysqld on
[root@centos4 mysql-5.5.38]# chmod +x /etc/init.d/mysql
[root@centos4 mysql-5.5.38]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/
[root@centos4 mysql-5.5.38]#
[root@centos4 mysql-5.5.38]# chown -R mysql:mysql /usr/local/mysql/
[root@centos4 mysql-5.5.38]# service mysqld start
[root@centos4 mysql-5.5.38]# mysqladmin -u root -p password '123.abc'
默认没有密码，直接在确认旧密码处回车即可。
[root@centos4 mysql-5.5.38]# mysql -u root -p123.abc
[root@centos2 ~]# vim /etc/my.cnf
server-id = 1 //mysql数据的唯一标示（不能重复）
log-slave-updates=true //允许连级复制 （增加）
log-bin=master-bin //二进制文件名（修改）
[root@centos2 ~]# service mysqld restart
[root@centos2 ~]# mysql -u root -p123.abc
赋予对所有库和所有表的replication和slave权限，用户为myslave来源为这个网段的任意IP密码123.abc //这个用户是从数据库用来复制二进制文件的用户
mysql> grant replication slave on *.* to myslave@'192.168.1.%' identified by '123.abc';
图4
mysql> exit
Bye
root@centos2 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT //允许目标端口为3306的入站
[root@centos3 ~]# vim /etc/my.cnf
server-id = 2 //不能与其他实例重复
log-bin=mysql-bin //二进制日志文件名
relay-log=relay-log-bin //复制过来的二进制文件名
relay-log-index=slave-relay-bin.index //中继日志存放的文件名称
[root@centos3 ~]# service mysqld restart
[root@centos3 ~]# mysql -u root -p123.abc
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环境
[root@centos1 ~]# chmod +x jdk-6u14-linux-x64.bin
[root@centos1 ~]# ./jdk-6u14-linux-x64.bin
在之后出现的阅读许可协议的时候直切按q退出就可以了
最后会出现以下信心
Do you agree to the above license terms? [yes or no]是否同意以上许可，这里我们直接yes 输入no则退出安装
之后会进入一个安装过程
Press Enter to continue..... //直接回车完成安装
安装完成之后会在当前目录下产生以下目录
[root@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）移动安装目录
[root@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变量
[root@centos1 ~]# . /etc/profile //刷新profile文件
（4）创建amoeba的解压目录
[root@centos1 ~]# mkdir -p /usr/local/amoeba
[root@centos1 ~]# chmod -R 755 /usr/local/amoeba/
（5）解压amoeba软件包
[root@centos1 ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
（6）验证环境变量部署是否正确
[root@centos1 ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
出现以上提示则表明环境变量设置正确，jdk已经开始工作了，我们可以查看一下jdk的版本
[root@centos1 ~]# javac -version
javac 1.6.0_28
6、配置amoeba实现读写分离
声明：所有配置文件注释都是以 ，再删除注释时请将内容也一并删除，最好是删除正行，但是有些时候只需要删除头和尾即可，里面的配置项是可以直接使用的。
（1）修改amoeba安装目录下的conf目录下的amoeba.xml主配置文件
这个配置文件需要定义两个配置，第一是应用程序使用什么用户连接amoeba访问到后端的mysql数据库，第二个是定义默认写池以及读池。
因为配置文件较多，只列出修改的部分
[root@centos1 ~]# vim /usr/local/amoeba/conf/amoeba.xml
conf
粗体部分是需要修改的部分
以上字体加粗部分就是这个配置文件需要修改的内容
（2）修改dbServer.xml配置文件，定义写的服务器以及读的服务器并且指定算法。
[root@centos1 ~]# vim /usr/local/amoeba/conf/dbServers.xml
以上两行为使用什么身份访问后台数据库 这个用户需要在所有数据库进行授全，权限为对所有库的所有表拥有所有权限，并且允许amoeba这台主机的IP访问 用户名不是固定的，根据实际情况建立。
以上部分都是需要修改的，slave2所在的部分是复制slave1的配置
这部分内容是定义读池负载均衡算法，1表示轮询、2表示加权轮询
以上就是所有配置文件的修改以及解释
3、在master、slave1、slave2上建立test用户，这个用户需要根据配置文件建立
mysql> grant all on *.* to test@'192.168.1.%' identified by '123.abc';
好了，现在可以将amoeba的服务启动并验证
[root@centos1 ~]# amoeba start& //默认amoeba启动之后需要占用一个终端，为了防止这种情况发生，我们可以直接将其放到后台运行，在启动过程中，如果发现出现很长的提示信息并且都是以java结束，那么表名服务没有启动成功，amoeba默认监听8066端口，我们可以进行验证
[root@centos1 ~]# netstat -anpt | grep 8066
tcp 0 0 :::8066 :::* LISTEN 3405/java
7、建立防火墙规则
[root@centos1 ~]# iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
8、master、slave1、slave2都需要开放3306端口入站
[root@centos2 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@centos2 ~]# service iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables： [确定]
[root@centos2 ~]#
另外两台从服务器也是一样的配置，或者直接将iptables stop掉
9、测试
（1）在应用服务器上
[root@web ~]# yum -y install mysql
通过代理访问mysql
[root@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&
[root@centos1 ~]# amoeba stop
[root@centos1 ~]# amoeba start&
[root@centos1 ~]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
在进行登录amoeba
[root@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);
mysql> insert into banji.class values(2);
mysql> insert into banji.class values(3);
图8
从上图可以看出执行了两次查询分别从slave1和slave2各自的数据库中读取，内容是各自的值，因为没有同步，所以值只有一个
mysql> insert into banji.class values(4);
图9
看不到刚插入的4，也就证明了，写操作在Master我们可以去Master上看一下
Master操作
图10
在Master可以看见刚插入的值，我们将各个slave的功能开启
Slave1，slave2操作
mysql> start slave;
client操作
