Mysql 主从复制与读写分离

读写分离

什么是读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

为什么要读写分离

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的
 
但是数据库的“读"（读10000条数据可能只要5秒）
所以读写分离，解决的是，数据库的写入，影响了查询的效率

什么时候要读写分离

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能

MYSQL读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。

基本的原理是让主数据库处理事务性操作，而从数据库处理select查询。

数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

目前常见的读写分离

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

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。
（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

主从复制

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

主从复制的工作原理

1.主服务器将写入的数据记录到二进制日志中
 
2.从服务器开启io线程连接到主服务器，并且监测主的二进制日志是否更新
如果更新，从请求读取复制主服务器上的二进制日志
 
3.主服务器开启dump线程，向从发送二进制日志
 
4.从保存二进制日志文件到中继日志，并将其中的数据记录应用到自己的数据库中
 
5.从开启SQL线程读取中继日志，解析sql语句，实现主从同步
 
主：dump线程，二进制日志
从：I/O线程，SQL线程，中继日志
 
 
 
主从复制的核心是二进制日志（binlog），它记录了主服务器上所有的写操作。
当从服务器连接到主服务器时，它会请求复制主服务器上的数据，并从主服务器上获取二进制日志文件。
从服务器读取二进制日志文件，并将其中的数据记录应用到自己的数据库中。
从服务器还会定期向主服务器发送心跳包，以检测主从复制的状态。
 
需要注意的是，主从复制需要保证主服务器和从服务器之间的网络连接畅通，
否则可能会导致数据复制失败。
此外，主从复制还需要考虑到数据一致性、复制延迟、复制冲突等问题，以保证复制的正确性和可靠性。

主：dump线程，二进制日志
从：I/O线程，SQL线程，中继日志
 
dump线程的作用
1.用于监听I/0线程请求
2.将二进制日志中更新数据发送给slave I/O线程

mysql支持的复制类型

（1）STATEMENT：基于语句的复制。
在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
 
（2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
 
（3）MIXED：混合类型的复制。
默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

MySQL主从复制的几个同步模式

异步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。

相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步复制在什么情况下降为异步复制

当主服务器发送二进制文件日志，在规定时间内没有得到回应，则降为异步复制。
如果在下一次发送二进制日志时，在规定时间内重新回应，则又升为半同步复制

数据库主从数据不一致解决方案

方法一：忽略错误后，继续同步 

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

方式二：重新做主从，完全同步 

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

MySQL主从复制延迟原因

主从服务器配置有差异，开启了同步复制，网络延迟，大事务，高并

1、master服务器高并发，形成大量事务

2、网络延迟

3、主从硬件设备导致（cpu主频、内存io、硬盘io）

4、是同步复制、而不是异步复制

从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。 从库使用SSD磁盘 网络优化，避免跨机房实现同步

主从复制读写分离实验

主从复制实验

 
Master 服务器：20.0.0.110        
Slave1 服务器：20.0.0.120		        
Slave2 服务器：20.0.0.130		  
 
         
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

Mysql主从服务器时间同步

主服务器设置

yum install ntp -y
 
vim /etc/ntp.conf
--末尾添加--
server 127.127.0.0							#设置本地是时钟源
fudge 127.127.0.0 stratum 8				#设置时间层级为8（限制在15内）
 
service ntpd start

从服务器设置

yum install ntp ntpdate -y
 
service ntpd start
/usr/sbin/ntpdate 20.0.0.110			#进行时间同步  
 
crontab -e
*/30 * * * * /usr/sbin/ntpdate 

主服务器的mysql配置

vim /etc/my.cnf
server-id = 11
log-bin=master-bin			#添加，主服务器开启二进制日志
binlog_format = MIXED       #指定二进制日志的格式
log-slave-updates=true		#添加，允许slave从master复制数据时可以写入到自己的二进制日志
 
systemctl restart mysqld
 
 
 
 
mysql -u root -proot
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' IDENTIFIED BY '123456';		     	#给从服务器授权
FLUSH PRIVILEGES;   #刷新权限
 
show master status;
显示下面的信息
+-------------------+----------+--------------+------------------
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB 
+-------------------+----------+--------------+------------------
| master-bin.000001 |      599 |              |                  
+-------------------+----------+--------------+-----------------
 
#File 列显示日志名，Position 列显示偏移量

从服务器的mysql配置

vim /etc/my.cnf
server-id = 22/23							
#修改，注意id与Master的不同，两个Slave的id也要不同，即两台从都要不同
 
relay-log=relay-log-bin						
#添加，开启中继日志，从主服务器上同步日志文件记录到本地
 
relay-log-index=slave-relay-bin.index		
#添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录
 
 
relay_log_recovery = 1                      #选配项
#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上获取日志，这样就保证了relay-log 的完整性。
默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时，可在 slave 从库上开启该功能，建议开启。
 
systemctl restart mysqld
 
 
 
 
 
 
 
mysql -u root -proot
					
CHANGE master to master_host='20.0.0.110',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=599;
#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致
 
start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
 
 
#注意点
Slave_IO_Running: Yes				#负责与主机的io通信
Slave_SQL_Running: Yes				#负责自己的slave mysql进程
 
#一般 Slave_IO_Running: No 的可能性：
1、网络不通
2、my.cnf配置有问题
3、密码、file文件名、pos偏移量不对
4、防火墙没有关闭

读写分离实验

实验环境
 
主服务器：20.0.0.110
 
从服务器1：20.0.0.120
 
从服务器2:  20.0.0.130
 
Amoeba 服务器：20.0.0.140  jdk1.6、Amoeba centos7-1

配置Amoeba 服务器20.0.0.140

##安装 Java 环境##

因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
#给执行权限
chmod +x jdk-6u14-linux-x64
#执行
./jdk-6u14-linux-x64.bin
执行后，一直按空格，知道看到yes，输入yes
//按yes，按enter
 
mv jdk1.6.0_14/ /usr/local/jdk1.6
 
vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
 
source /etc/profile
java -version

##安装 Amoeba软件##

mkdir /usr/local/amoeba
tar zxvf 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说明安装成功
 
##配置 Amoeba读写分离，两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
grant all on *.* to test@'20.0.0.%' identified by '123456';
#查看权限
show grants for test@'20.0.0.%';
 
#再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/
 
cp amoeba.xml amoeba.xml.bak
vim amoeba.xml                                    #修改amoeba配置文件
--30行--
<property name="user">amoeba</property>
--32行-- 
<property name="password">123456</property>
--115行--
<property name="defaultPool">master</property>
--117-去掉注释-
<property name="writePool">master</property>
<property name="readPool">slaves</property>
 
cp dbServers.xml dbServers.xml.bak
vim dbServers.xml                                #修改数据库配置文件
--23行--注释掉  作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- <property name="schema">test</property> -->
--26--修改
<property name="user">test</property>
--28-30--去掉注释
<property name="password">123456</property>
--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">
--48--修改，设置主服务器的地址
<property name="ipAddress">20.0.0.110</property>
--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">
--55--修改，设置从服务器1的地址
<property name="ipAddress">20.0.0.120</property>
--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">20.0.0.130</property>
--65行--修改
<dbServer name="slaves" virtual="true">
--71行--修改
<property name="poolNames">slave1,slave2</property>
 
 
/usr/local/amoeba/bin/amoeba start&                    #启动Amoeba软件，按ctrl+c 返回
netstat -anpt | grep java                            #查看8066端口是否开启，默认端口为TCP 8066

 客户端配置20.0.0.140

yum install -y mariadb-server mariadb
systemctl start mariadb.service
 
在客户端服务器上测试：
mysql -u amoeba -p123456 -h 20.0.0.140 -P8066        
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

测试

在主服务器上

在主服务器上：
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));

在两台从服务器上

stop slave;                                            #关闭同步
use db_test;

//在slave1上：
insert into test values('1','zhangsan','this_is_slave1');
 
//在slave2上：
insert into test values('2','lisi','this_is_slave2');
 
//在主服务器上：
insert into test values('3','wangwu','this_is_master');
 
//在客户端服务器上：
use db_test;
select * from test;        //客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据
 
insert into test values('4','qianqi','this_is_client');        //只有主服务器上有此数据
 
//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;