MySQL主从复制与读写分离

概念

什么是读写分离?

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

为什么要读写分离呢?

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。

但是数据库的“读”（读10000条数据可能只要5秒钟）；

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

什么时候要读写分离?

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

主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync（文件同步工具），但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

mysql支持的复制类型

（1） STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制（5.7版本之前），执行效率高。高并发的情况可能会出现执行顺序的误差，事务的死锁。

（2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一 遍。精确，但效率低，保存的文件会更大。（5.7版本之后默认采用ROW模式）

（3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。更智能，所以大部分情况下使用MIXED。

主从复制的工作过程

Master节点需要开启二进制日志，Slave节点需要开启中继日志。

（1）Master 节点将数据的改变记录成二进制日志（bin log） ，当Master上的数据发生改变时（增删改），则将其改变写入二进制日志中。

（2）Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求Master的二进制事件。（请求二进制数据）

（3）同时Master 节点为每个I/O线程启动一个dump线程，用于通知和向其发送二进制事件，I/O线程接收到bin-log内容后，将内容保存至slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制事件，在本地重放，即解析成sql 语句逐一执行，使得其数据和Master节点的保持一致。最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

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

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

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

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

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

mysql从服务器挂了 恢复后怎么保证数据同步？

• 物理方法：  rsync 磁盘文件同步。 使用文件恢复，主节点需要停服务。
• 主从复制：  将从节点原有库删除，通过偏移量，重新做一次主从复制。

实验操作

实验环境

四台虚拟机：主服务器master，从服务器slave1，slave2，amoeba服务器

master：192.168.222.101

slave1：192.168.222.102

slave2：192.168.222.103

amoeba：192.168.222.104

主从复制过程

给主机改名

[master服务器]

hostnamectl set-hostname  master

su

[slave1服务器]

hostnamectl set-hostname slave1

su

[slave2服务器]

hostnamectl set-hostname slave2

su

[amoeba服务器]

hostnamectl set-hostname amoeba

su

设置master服务器时间

yum install ntp -y

vim /etc/ntp.conf

在最后加入 server 127.127.222.0   #设置本地时钟源

fudge 127.127.222.0 stratum 8  #设置时间层级为8 限制在15 以内

service ntpd start

从服务器【两台】设置时间同步

yum install ntp ntpdate -y

service ntpd start

/usr/sbin/ntpdate 192.168.222.101

crontab -e

*/30 * * * * /usr/sbin/ntpdate 192.168.222.101

开启二进制日志文件

vim /etc/my.cnf  

log-bin=master-bin        #开启二进制日志

binlog_format=MIXED       #二进制日志格式

log-slave-updates=true    #开启从服务器同步

systemctl restart mysqld.service

开启从服务器授权

mysql -uroot -pabc123

grant replication slave on *.* to 'myslave'@'192.168.222.%' identified by 'abc123';

flush privileges;

show master status;

从服务器设置主从复制

vim /etc/my.cnf

server-id = 11 #slave1和slave2的id不能相同，我slave2设置的22

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

systemctl restart mysqld.service

配置同步，查看slave状态

mysql -uroot -pabc123

change master to master_host='192.168.222.101',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=604; #这里二进制文件和位置号要和主服务器上的对应

start slave;

show slave status\G; #查看IO_RUNNING和SQL_RUNNING是否是yes

验证主从同步

在master数据库上创建库表 在slave1和slave2数据库上查看是否同步

读写分离过程

读写分离是要在主从复制的基础上完成，是要另外配置一台amoeba服务器

导入jdk和安装包

cd /opt

cp jdk-6u14-linux-x64.bin /usr/local/

chmod +x /usr/local/jdk-6u14-linux-x64.bin

cd /usr/local/

./jdk-6u14-linux-x64.bin

mv jdk1.6.0_14/ 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

配置amoeba

mkdir /usr/local/amoeba

cd /opt/

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

数据库上授权

在master，slave1，slave2数据库上

grant all on *.* to test@'192.168.222.%' identified by 'abc123';

flush privileges;

备份配置文件

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak

cp dbserver.dtd dbserver.dtd.bak

修改amoeba.xml配置文件

vim amoeba.xml

30行  改名设置amoeba

32行  设置密码abc123

115行  设置默认池为master

118行  设置写池为master

119行  设置读池为slaves

修改dbServers.xml配置文件

vim dbServers.xml

23行  注释

26行  设置登录用户

28行  删除

29行  设置登录密码

45行  设置服务池名master

48行  添加地址，主服务器地址192.168.222.101

52行  服务器名改slave1

55行  ip修改192.168.222.102

52行复制6行 59行粘贴6行

59行  改服务器名slave2

62行  IP修改192.168.222.104

66行  定义池名slaves

72行  写从节点名 slave1，slave2

amoeba  start

netstat -ntap | grep  java

测试读写分离

yum install mariadb mariadb-server.x86_64 -y #安装mariadb

mysql -uamoeba -pabc123 -h 192.168.222.100 -P8066 #-h amoeba服务器ip，端口号8066登录amoeba