mysql 主从复制与读写分离
mysql 主从复制与读写分离
一 mysql 主从复制概述
1.1 mysql 主从同步概述
在实质际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。
因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。
1.2mysql 主从复制类型
- statement:基于语句的复制。在主服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高
- row： 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍
- mixed：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制
1.3 mysql 主从同步工作过程
- master 节点将数据的改变记录成二进制日志（bin log),当master上的数据发生改变时，则将其改变写入二进制日志中。
- slave节点会在一定时间间隔内对master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求
- 同时master节点为每个I/O 线程启动一个dump线程,用于向其发送二进制事件，并保存值slave节点本地的中继日志（relay log）中，slave节点将启动sql线程从中继日志中读取二进制日志，在本地重放，即解析成sql语句逐一执行，使得其数据和master节点的保持一致，最后，I/O线程和sql线程进入睡眠状态，等待下一次被唤醒。
注：
- 中继日志通常会位于OS的缓存中，所以中级日志的开销很小
- 复制过程有一个很重要的限制，即复制在slave上时串行化的，也就是说master上并行更新操作不能子啊slave上并行操作
1.4 主从复制延迟的原因与优化
延迟原因：
- master服务器高并发，形成大量事务
- 网络延迟
- 主从硬件折本导致
- cpu主频，内存io，硬盘io
优化：
- 从库优化mysql参数。比如增大innodb_buffer_pool_size,让更多操作在mysql内存中完成，减少磁盘操作
- 从库使用高性能主机。包括cpu强悍，内存加大。避免使用虚拟云主机，使用物理主机，这样提高了i/o方面性能
- 从库使用ssd磁盘
- 网络优化，避免跨机房实现同步
二：搭建主从同步
2.1 拓扑
master:192.168.23.103 mysq5.7
slave 1：192.168.23.13 mysql5.7
slave 2：192.168.23.12 mysql5.7
客户端：192.168.23.104 mysql
关闭服务器的 firewalld和selinux设置
2.2 配置主从服务器时间同步
配置时间同步，可以使用chrony ，也可以使用ntp，但是两者只能使用其一
2.2.1 主服务器配置
yum -y install ntp
vim /etc/ntp.conf
#在文件末尾添加
server 127.127.23.0 #设置时间源为本地
fudge 127.127.23.0 stratum 8 #设置时间层级（15以内）
#启动ntpd服务
service ntpd start
#查看udp123端口
netstat -naup | grep :123
2.2.2 从服务器配置
yum -y install ntp
service ntpd start
#进行时间同步，同步主服务器的时间
/usr/sbin/ntpdate 192.168.23.10
#配置周期性同步时间
crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.23.103
2.3 主服务器mysql配置
systemctl stop firewalld
setenforce 0
vim /etc/my.cnf
.....
server-id = 103 #配置server id，集群内mysql服务器server -id不可以重复
log-bin=mysql-bin #主服务器开启二进制日志
binlog_format=mixed #使用混合模式
log-slave-updates=true #允许从服务器更新二进制日hi
#重启mysql服务
systemctl restart mysqld
#进入数据库
mysql -uroot -pabc123
#刷新二进制日志
flush logs;
#创建主从复制授权用户
grant replication slave on *.* to 'myslave'@'192.168.23.%' identified by 'abc123';
#查看当前 二进制日志名file和偏移量position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 451 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.4 从服务器mysql配置
#关闭firewalld和selinux
systemctl stop firewalld
setenforce 0
vim /etc/my.cnf
....
#配置slave1的server id为12，slave 2 的server-id为13
server-id= 12 （server-id=13 #slave2）
relay-log=relay-log-bin #开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index #定义中继日志的路径
#重启mysqld服务
systemctl restart mysqld
#进入数据库
mysql -uroot -pabc123
#配置同步
change master to
master_host='192.168.23.103', #主服务器地址
master_user='myslave', #使用的用户
master_password='abc123', #密码
master_log_file='mysql-bin.000001', #当前二进制日志名
master_log_pos=451; #二进制日志偏移量
start slave; #启动同步。如果报错，执行reset slave，然后重新设置
#查看slave状态，Slave_IO_Running和Slave_SQL_Running 状态都是yes
#如果状态不对，则stop slave；停止同步，然后再使用change命令重新配置
show slave status \G
注意：
一般Slave_IO_Running:No 的可能性有：
- 网络不通
- my.cnf配置有问题
- 密码、file文件名、pos偏移量不对
- 防火墙没有关闭
2.5 验证主从复制
#在主服务器上进行增删改操作
#创建数据库test02
mysql> create database test02;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| mysql |
| performance_schema |
| sys |
| test |
| test02 |
+--------------------+
#从服务器上进行查看
#此时，从服务器上自动创建了数据库 test02
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test02 |
+--------------------+
5 rows in set (0.00 sec)
三： 读写分离概述
3.1、什么是读写分离
读写分离，基本是的原理是让主数据处理事务性增，删，改操作（insert,update,delete), 而从数据库处理select 查询操作。数据库复制被用来把事务性操作导致的变更同步到集群从数据库
3.2 为什么做读写分离
因为数据库的 ”写“（写10000 条数据可能需要3分钟）操作比较耗时。
但是数据库的”读”（读10000条数据可能只要5秒钟）。
所以读写分离，解决的是数据库的写入影响了查询的效率
3.3 什么时候要读写分离
数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。
3.4常见的mysql 读写分离
3,4,1 基于程序代码内部实现
在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支;缺点是需要开发人员来实现， 运维人 员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。
3.4.2 基于中间代理层实现
代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
-
MySQL-Proxy
- MySQL-Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断。
-
Atlas
- 是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条，支持事物以及存储过程。
-
Amoeba
- 由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
- 由于使用MySQL Proxy需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说是非常困难的。
- Amoeba是一个非常容易便用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。
-
maxscale 服务
- 由mariaDB公司开发
- 下载地址https://downloads.mariadb.com/files/MaxScale
- mycat 软件
- 基于java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案
- 适合数据大量写入的存储需求
- 支持mysql，oracle，sqlserver，MongoDB等
- 提供数据读写分离服务
- 提供数据分片服务
- 基于阿里巴巴Cobar进行研发的开源软件
四 ：使用amoeba 搭建mysql读写分离
4.1 拓扑
master: 192.168.23.103
slave 1:192.168.23.12
slave2：192.168.23.13
amoeba：192.168.23.11 软件 amoeba-mysql-binary-2.2.0.tar.gz ,jdk-6u14-linux-x64.bin
关闭服务器的firewalld和selinux功能
4.2 安装JAVA环境
因为Amoeba 是基于jdk1.5开发的，所以官方推荐使用jdk1.5或1.6版本，高版本不建议使用
cd /opt/
#将ameoba软软件和jdk软件上传到 /opt/目录中
ls amoeba-mysql-binary-2.2.0.tar.gz
ls jdk-6u14-linux-x64.bin
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
#一直按回车，直到出现"please enter “yes” or “no" ，然后输入yes并回车
#出现 Press Enter to continue 。..... ，按下回车
mv /usr/local/jdk1.6.0_14/ /usr/local/jdk1.6
vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
source /etc/profile
java -version #需要1.6版本
4.3 安装 amoeba软件
mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
# 显示amoeba start|stop 说明安装成功
4.4 配置读写分离
4.4.1 主从mysql 开发权限给amoeba
#及群里所有mysql服务器
mysql -uroot -pabc123
#创建amoeba的授权登录用户
grant all on *.* to amoeba@'192.168.23.%' identified by 'abc123';
4.4.2 amoeba 服务器修改/amoeba配置文件
cd /usr/local/amoeba/conf/
cp amoeba.xml{,.bak}
#修改amoeba配置文件，此为xml文件，使用<!-- --> 注释
vim amoeba.xml
<!-- 配置amoeba服务器登录用户名和密码-->
30// <property name="user">amoeba</property>
32// <property name="password">abc123</property>
<!-- defaultpool,默认池，定义写的数据库即maste。 master为自定义名 -->
115// <property name="defaultPool">master</property>
<!--打开原117和 120行的注释,配置 写为master，读为slave1和slave2-->
117// <property name="writePool">master</property>
118// <property name="readPool">slaves</property>
:wq
4.4.3 修改数据库配置文件
cp dbServers.xml{,.bak}
#修改数据库配置文件
vim dbServers.xml
<!--注释23行。注释默认进入test库，以防mysql中没有test库报错-->
23// <!--<property name="schema">test</property>-->
<!--mysql的授权用户为amoeba -->
26// <property name="user">amoeba</property>
<!-- 删除或者取消28和30行的注释,使得29行的类容生效.此行配置mysql授权用户的密码-->
28// <!-- mysql password -->
29// <property name="password">abc123</property>
30//
<!--设置主服务器名为master-->
45// <dbServer name="master" parent="abstractServer">
<!-- 设置主服务器的ip地址-->0
48// <property name="ipAddress">192.168.23.103</property>
<!--设置从服务器名为slave1 -->
52// <dbServer name="slave1" parent="abstractServer">
<!-- 设置slave1 ip地址 -->
55// <property name="ipAddress">192.168.23.12</property>
<!-- 复制slave1的配置（6行)，修改为slave2 配置-->
58// <dbServer name="slave2" parent="abstractServer">
59// <factoryConfig>
60// <!-- mysql ip -->
61// <property name="ipAddress">192.168.23.13</property>
62// </factoryConfig>
63// </dbServer>
<!-- 定义slaves（amoeba的配置文件amoeba.xml的 readpol部分定义的名字是slaves)-->
65// <dbServer name="slaves" virtual="true">
<!--将slave1，slave2加入 -->
71//<property name="poolNames">slave1,slave2</property>
4.5 启动amoeba，并进行测试
4.5.1 启动amoeba
#启动amoeba服务，信息提示框可以使用ctrl +c取消。或者重开一个终端
/usr/local/amoeba/bin/amoeba start &
#amoeba使用tcp的8066端口
netstat -natp | grep java
4.5.2 客户端连接测试
#使用amoeba服务器的用户密码登录， 指定ip为amoeba服务器， 指定端口为8066
mysql -u amoeba -pabc123 -h192.168.23.11 -P 8066
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| mysql |
| performance_schema |
| sys |
| test |
| test02 |
+--------------------+
7 rows in set (0.01 sec)
mysql> create table class (id int,name char(20));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class |
+------------------+
1 row in set (0.01 sec)
4.5.3 从数据库对同一张数据表写入不同的内容
slave 1 配置
mysql> use test02
#在amoeba服务器创建的表，也同步到了slave上
mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class |
+------------------+
1 row in set (0.00 sec)
mysql> insert into class values (1,'slave1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from class;
+------+--------+
| id | name |
+------+--------+
| 1 | slave1 |
+------+--------+
1 row in set (0.00 sec)
slave2配置
mysql> use test02
mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class |
+------------------+
1 row in set (0.00 sec)
mysql> select * from class;
Empty set (0.00 sec)
mysql> insert into class values (2,'slave2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+------+--------+
| id | name |
+------+--------+
| 2 | slave2 |
+------+--------+
1 row in set (0.00 sec)
4.5.4 在amoeba上查看 test02库class表
mysql -u amoeba -pabc123 -h192.168.23.11 -P 8066
#查看class表，内容分别为slave1，和slave2轮询
mysql> select * from test02.class;
+------+--------+
| id | name |
+------+--------+
| 2 | slave2 |
+------+--------+
1 row in set (0.00 sec)
mysql> select * from test02.class;
+------+--------+
| id | name |
+------+--------+
| 1 | slave1 |
+------+--------+
1 row in set (0.00 sec)
