查看: 1365|回复: 0

[Mysql数据库] MYSQL主从复制与读写分离

发表于 2018-3-9 08:00:02

在实际的生产环境中,如果对数据库的读和写都在同一个数据库服务器中操作,无论是在安全性、高可用性还是高并发性等各个方面都不能完全满足实际需求的。因此,一般都是通过主从复制的方式来同步数据源,再通过读写分离来提升数据库的并发负载能力,通过这样的方案进行部署与实施的。本篇博客详细介绍MySQL主从复制和MySQL读写分离的原理与配置,希望能够对您有所帮助。


1. MySQL主从复制的工作原理

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

1)MySQL支持的复制类型

(1)基于语句的复制。在服务器上执行sql语句,在从服务器上执行同样的语句,mysql默认采用基于语句的复制,执行效率高。

(2) 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍。

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

2)复制的工作过程

MySQL复制的工作过程如下图所示:

杨书凡13.png

(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缓存中,所以中继日志的开销很小。



2.MySQL读写分离的工作原理

读写分离就是只在主服务器上写,数据会同步到从服务器,从服务器只能读,实现备份的同时也实现了数据库性能的优化,提升了服务器安全。

杨书凡14.png

目前较为常见的MySQL读写分离分为以下两种:

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

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

2)基于中间代理层实现

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

(1)MySQL_Proxy。MySQL_Proxy是MySQL的一个开源项目,通过其自带的lua脚本进行SQL判断。

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

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

经过上述简单的比较,不是所有的应用都能够在基于程序代码中实现读写分离,像一些大型的Java应用,如果在程序代码中实现读写分离对代码的改动就较大,所以,像这种应用一般会考虑使用代理层来实现,那么今天就使用Amoeba为例,完成主从复制和读写分离。




案例概述:

本案例使用五台服务器模拟搭建,具体如下所示:

杨书凡15.png

主机
操作系统
IP地址
客户端Redhat 6.5192.168.1.10/24
AmoebaRedhat 6.5192.168.1.10/24
Master
Redhat 6.5192.168.1.30/24
Slave1
Redhat 6.5192.168.1.40/24
Slave2Redhat 6.5192.168.1.50/24


1. 搭建MySQL主从复制

(1)在Master、Slave1、Slave2上安装mysql数据库

参考我之前的博文 http://blog.51cto.com/yangshufan/2049291,这里就不在赘述了

(2)启动MySQL服务

  1. [root@localhost ~]# service mysqld start
  2. Starting MySQL.. [确定]
  3. [root@localhost ~]# chkconfig mysqld on
  4. [root@localhost ~]# mysqladmin -u -root password 123456 //为root用户设置密码
复制代码

(3)配置MySQL主服务器(Master)

  1. [root@localhost ~]# yum -y install ntp //建立时间同步环境、安装NTP
  2. [root@localhost ~]# vim /etc/ntp.conf //配置NTP,添加如下两行
  3. server 127.127.1.0
  4. fudge 127.127.1.0 stratum 8
  5. [root@localhost ~]# service iptables stop //关闭防火墙或指定端口开放
  6. [root@localhost ~]# service ntpd restart //重启服务
  7. 关闭 ntpd: [失败]
  8. 正在启动 ntpd: [确定]
  9. [root@localhost ~]# vim /etc/my.cnf //修改添加配置文件
  10. server-id = 1 //mysql数据的唯一标示(不能重复)
  11. log-slave-updates=true //允许连级复制 (增加)
  12. log-bin=master-bin //二进制文件名(修改)
  13. [root@localhost ~]# service mysqld restart
  14. Shutting down MySQL. [确定]
  15. Starting MySQL.. [确定]
  16. [root@localhost ~]# mysql -u root -p123456 //登录MySQL程序
  17. mysql> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123';
  18. Query OK, 0 rows affected (0.00 sec) //给从服务器授权
  19. mysql> flush privileges;
  20. Query OK, 0 rows affected (0.00 sec)
  21. mysql> show master status;
  22. +------------------+----------+--------------+------------------+
  23. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  24. +------------------+----------+--------------+------------------+
  25. | mysql-bin.000009 | 587 | | |
  26. +------------------+----------+--------------+------------------+
  27. 1 row in set (0.00 sec) //File列显示日志名、Position列显示偏移量,这两个值在后面配置从服务器的时候需要
复制代码


(4)配置从服务器(Slave1、Slave1

  1. [root@localhost ~]# yum -y install ntpdate //安装ntpdate
  2. [root@localhost ~]# /usr/sbin/ntpdate 192.168.1.30 //在从服务器进行时间同步
  3. [root@localhost ~]# service iptables stop //关闭防火墙或指定端口开放
  4. [root@localhost ~]# vim /etc/my.cnf
  5. server-id = 2 //不能与主服务器相同,但其他从服务器要相同
  6. relay-log=relay-log-bin //复制过来的二进制文件名,增加
  7. relay-log-index=slave-relay-bin.index //中继日志存放的文件名称,增加
  8. [root@localhost ~]# service mysqld restart
  9. Shutting down MySQL. [确定]
  10. Starting MySQL.. [确定]
  11. [root@localhost ~]# mysql -u root -p123456 //登录MySQL,配置同步
  12. mysql> change master to master_host='192.168.1.30',master_user='myslave',
  13. -> master_password='123',master_log_file='master-bin.000009',
  14. -> master_log_pos=587;
  15. Query OK, 0 rows affected (0.02 sec)
  16. mysql> start slave; //启动同步
  17. Query OK, 0 rows affected, 1 warning (0.00 sec)
  18. mysql> show slave status\G; //确保以下两个值为Yes
  19. Slave_IO_Running: Yes
  20. Slave_SQL_Running: Yes
复制代码


(5)验证主从复制效果

1)在主服务器上新建数据库“yangshufan”

  1. [root@localhost ~]# mysql -u root -p123456
  2. mysql> create database yangshufan;
  3. Query OK, 1 row affected (0.00 sec)
复制代码

2)在主、从服务器上查看数据库,显示数据库相同,说明主从复制成功

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mysql |
  7. | performance_schema |
  8. | test |
  9. | yangshufan |
  10. +--------------------+
  11. 5 rows in set (0.06 sec)
复制代码




2. 搭建MySQL读写分离

(1)在主机Amoeba上安装java环境(amoeba软件基于java平台运行)

  1. [root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
  2. [root@localhost ~]# ./jdk-6u14-linux-x64.bin //选择yes安装
  3. [root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
  4. [root@localhost ~]# vim /etc/profile //增加以下内容
  5. export JAVA_HOME=/usr/local/jdk1.6
  6. export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
  7. export PATH=$JAVA_HOME/lib/$JAVA_HOME/jre/bin:$PATH:$HOME/bin
  8. export AMOEBA_HOME=/usr/local/amoeba/
  9. export PATH=$PATH:$AMOEBA_HOME/bin
  10. [root@localhost ~]# soure /etc/profile //执行文件
  11. [root@localhost ~]# java -version //Java环境配置成功
  12. java version "1.7.0_45"
  13. OpenJDK Runtime Environment (rhel-2.4.3.3.el6-x86_64 u45-b15)
  14. OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode)
复制代码


(2)安装并配置Amoeba软件

  1. [root@localhost ~]# mkdir /usr/local/amoeba
  2. [root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
  3. [root@localhost ~]# chmod -R 755 /usr/local/amoeba/
  4. [root@localhost ~]# /usr/local/amoeba/bin/amoeba //显示此消息说明Amoeba安装成功
  5. amoeba start|stop
复制代码


(3)配置amoeba读写分离,两个slave读负载均衡

1)在主、从服务器上开放权限给amoeba访问

  1. mysql> grant all on *.* to ysf@'192.168.1.%' identified by '123abc';
  2. Query OK, 0 rows affected (0.01 sec)
复制代码


2)编辑amoeba.xml文件

  1. [root@localhost ~]# vim /usr/local/amoeba/conf/amoeba.xml //修改以下文件
  2. <property name="authenticator">
  3. <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
  4. <property name="user">amoeba</property> //这里用户名、密码后面连接Amoeba使用
  5. <property name="password">123456</property>
  6. //……省略部分内容
  7. </property>
  8. <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
  9. <property name="LRUMapSize">1500</property>
  10. <property name="defaultPool">master</property> //定义默认的池,注意去掉注释
  11. <property name="writePool">master</property> //定义写池
  12. <property name="readPool">slaves</property> //定义读池
  13. <property name="needParse">true</property>
  14. </queryRouter>
复制代码


3)编辑dbServers.xml文件

  1. [root@localhost ~]# vim /usr/local/amoeba/conf/dbServers.xml
  2. <!-- mysql user -->
  3. <property name="user">ysf</property> //修改为第3步的用户名、密码
  4. <property name="password">123abc</property> //去掉注释
  5. //……省略部分内容
  6. <dbServer name="master" parent="abstractServer"> //定义master
  7. <factoryConfig>
  8. <!-- mysql ip -->
  9. <property name="ipAddress">192.168.1.30</property>
  10. </factoryConfig>
  11. </dbServer>
  12. <dbServer name="slave1" parent="abstractServer"> //定义slave1
  13. <factoryConfig>
  14. <!-- mysql ip -->
  15. <property name="ipAddress">192.168.1.40</property>
  16. </factoryConfig>
  17. </dbServer>
  18. <dbServer name="slave2" parent="abstractServer"> //定义slave2
  19. <factoryConfig>
  20. <!-- mysql ip -->
  21. <property name="ipAddress">192.168.1.50</property>
  22. </factoryConfig>
  23. </dbServer>
  24. <dbServer name="slaves" virtual="true"> //定义slave池
  25. <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
  26. <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
  27. <property name="loadbalance">1</property>
  28. <!-- Separated by commas,such as: server1,server2,server1 -->
  29. <property name="poolNames">slave1,slave2</property> //用逗号分隔slave名
  30. </poolConfig>
  31. </dbServer>
  32. [root@localhost ~]# /usr/local/amoeba/bin/amoeba start& //启动amoeba默认端口8066
复制代码



4)在客户端测试读写分离

  1. [root@localhost ~]# yum -y install mysql
  2. [root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.1.20 -P8066
复制代码

master、slave1、slave2都需要开放3306端口

amoeba开放8066端口

验证读操作:

(1)在Master创建一个ysf表,同步到各从服务器上

(2)分别在两台从服务器上关闭Slave功能(stop slave;)

(3)分别在主、从服务器ysf表上插入不同的语句

(4)在client上查询ysf表,发现只能查看两台从服务器的数据


验证写操作:

(1)在client上插入一条语句

(2)分别到主、从服务器上查询语句,发现只有主服务器才能查看这条语句


由此验证,写操作都在主服务器上,实现数据的统一更新;从服务器只负责读取,分担了数据库压力






回复

使用道具 举报