Mysql主从复制与读写分离

1、主从复制与读写分离的必要性

■ 在企业应用中，成熟的业务通常数据量都比较大
■ 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
■ 配置多台主从数据库服务器以实现读写分离

2、MySQL主从复制原理（复制主上的二进制日志）

■ MySQL的复制类型
● 基于语句的复制
● 基于行的复制
● 混合类型的复制
■ MySQL主从复制的工作过程

2.1、复制的基本过程如下

• Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中
• Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
  IO进程：从数据库复制主数据库上二进制日志的进程
• Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
• Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
• Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行.
  Sql进程：将二进制日志内容翻译成SQL语句写进从数据库

3、MySQL读写分离原理

■ 只在主服务器上写，只在从服务器上读
■ 主数据库处理事务性查询，从数据库处理SEL ECT查询
■ 数据库复制用于将事务性查询的变更同步到集群中的从数据库
■ 读写分离方案
● 基于程序代码内部实现
● 基于中间代理层实现
◆ MySQL-Proxy
◆ Amoeba

3.1、设备配置

MySQL主数据库： 192.168.140.40
MySQL从数据库1：192.168.140.50
MySQL从数据库2： 192.168.140.60
Amoeba：192.168.140.20
客户端：192.168.140.30

3.2、mysql主从复制与读写分离步骤

3.2.1、主数据库配置

[root@master ~]# systemctl stop firewalld    #开启会话功能，关闭防护墙
[root@master ~]# setenforce 0                       #关闭核心防护，关闭会话功能
[root@master ~]# yum -y install ntpdate ntp  #安装ntp软件
[root@master ~]# ntpdate ntp.aliyun.com   #与阿里云ntp服务器做时间同步
[root@master ~]# date                                   #查看当前日期
[root@master ~]# vi /etc/ntp.conf                 #进入配置文件
第8行 restrict default nomodify   #定义默认访问规则，nomodify禁止远程主机修改本地服务器配置
 第17行 restrict 192.168.140.0 mask 255.255.255.0 nomodify notrap
从192.168.140.1-192.168.140.254的主机都可以使用ntp服务来时间同步
第21行（删除原21-24行） fudge 127.127.1.0 stratum 10
设置本机的时间层级为10级，0级表示时间层级为0级，是向其他服务器提供时间同步源的意思，不要设置为0级
第22行 server 127.127.1.0   #设置本机为时间同步源

[root@master ~]# systemctl restart ntpd                #重启服务
[root@master ~]# netstat -anptu | grep ntpd           #查看服务状态

[root@master ~]# crontab -e             #服务器上创建任务计划

[root@master ~]# date                       #查看日期

3.2.2、从数据库配置

[root@slave1 ~]# yum -y install ntpdate        #安装ntp
[root@slave1 ~]# ntpdate 192.168.140.40      #设置同步源
[root@slave1 ~]# date                        #查看当前日期
[root@slave1 ~]# crontab -e                  #部署任务计划
*/3 * * * * /usr/sbin/ntpdate 192.168.140.40 
#每隔3分钟进行一次同步对象为192.168.140.40 （主数据库）的时间同步
[root@slave1 ~]# date        #查看当前日期

[root@slave2 ~]# yum -y install ntpdate        #安装ntp
[root@slave2 ~]# ntpdate 192.168.140.40      #设置同步源
[root@slave2 ~]# date                                      #查看当前日期
[root@slave2 ~]# crontab -e                             #部署任务计划
*/3 * * * * /usr/sbin/ntpdate 192.168.140.40 
#每隔3分钟进行一次同步对象为192.168.140.40 （主数据库）的时间同步
[root@slave2 ~]# date        #查看当前日期
总结：主从时间同步已完成

3.2.3、配置主从复制（主数据库配置）

[root@master ~]# vi /etc/my.cnf
添加
log_bin=master_bin          #同步的二进制日志开启前缀
log_slave_updates=true   #允许日志同步给从数据库

[root@master ~]# systemctl restart mysqld         #重启服务
[root@master ~]# mysql -uroot -p123456             #登录数据库         
mysql> grant replication slave on *.* to 'myslave'@'192.168.140.%' identified by '123456';
#grant replication slave                          #授权从数据库复制权限
#on *.* ：可复制所有库，所有表，
#'myslave'@'192.168.140.%'                    #授权用户，用户登录范围，网段，
#identified by ：                                 #识别密码
mysql> flush privileges;                         #刷新权限
mysql> show master status;                       #查看主数据库的状态信息

3.2.4、从数据库配置

[root@slave1 ~]# vi /etc/my.cnf

添加修改
server-id = 2                       #服务id为2，不能重复
relay_log=relay-log-bin     #二进制日志的中继日志前缀
relay_log_index=slave-relay-bin.index      #中继日志索引
[root@slave1 ~]# systemctl restart mysqld   #重启服务

[root@slave1 ~]# mysql -uroot -p123456                #登录数据库
mysql> change master to master_host='192.168.140.40',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=604;
master_host='192.168.140.40'：                   #主数据库ip地址
master_user='myslave'：                              #主数据库授权用户名
master_password='123456'：                        #授权密码
master_log_file='master_bin.000001'             #主从同步日志文件
master_log_pos=604：                                    #日志开始路径，主从复制位置
mysql> start slave;                                           #开启从数据库  
mysql> show slave status\G                            #查看从数据库的状态

如果这里线程报错的原因：
1.I/0线程显示为NO:主库与从库网络不通、主库未授权给从库
若从库查看连接主库I/0线程状态为conneting,-直是这个状态，考虑双方的防火墙是否开启。
2.SQL线程显示为NO:从库日志和位置点与主不同步

3.2.5、切换到主数据库，并且从二数据库同从一配置

mysql> show master status;

[root@slave2 ~]# vi /etc/my.cnf                #进入配置文件

添加修改
server-id = 3                                               #服务id为3
relay_log=relay-log-bin                             #二进制日志的中继日志前缀
relay_log_index=slave-relay-bin.index    #中继日志索引
[root@slave2 ~]# systemctl restart mysqld          #重启服务
[root@slave2 ~]# mysql -uroot -p123456             #登录数据库
mysql> change master to master_host='192.168.140.40',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=604;
master_host='192.168.140.40'：                   #主数据库ip地址
master_user='myslave'：                              #主数据库授权用户名
master_password='123456'：                        #授权密码
master_log_file='master_bin.000001'             #主从同步日志文件
master_log_pos=604：                                    #日志开始路径，主从复制位置
mysql> start slave;                                             #开启从数据库  
mysql> show slave status\G                               #查看从数据库的状态

总结：主从复制配置已完成

3.2.6、进行主从测试，在主数据库上进行配置

mysql> show databases;                             #查看数据库
mysql> create database aaa;                       #创建新的数据库'aaa'
mysql> show databases;                            #查看数据库    

3.2.6.1、在从数据库上进行查看是否生成新的库’aaa’

mysql> show databases;                #slave1上查看

mysql> show databases;         #slave2上查看

注：因为ameoba用户授权给用户时默认的数据库为test
所以在主数据库时创建数据库test

3.2.6.2、在主数据库创建数据库test

mysql> create database test;                    #创建数据库'test'
mysql> show databases;                           #查看数据库

3.3、设置读写分离

3.3.1、在代理服务器ameoba上实现时间同步

[root@amoeba ~]# yum -y install ntpdate                  #安装ntp软件
[root@amoeba ~]# ntpdate 192.168.140.40              #设置同步源为主数据库
[root@amoeba ~]# date                                             #查看当前时间
[root@amoeba ~]# crontab -e                                   #创建任务计划
[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz   #添加JDK安装包进行解压缩
[root@amoeba ~]# cp -rv jdk1.8.0_91/ /usr/local/java     # -rv：递归，显示过程
[root@amoeba ~]# vi /etc/profile                    #设置环境变量
结尾添加
export JAVA_HOME=/usr/local/java         设置java根目录为全局变量
export JRE_HOME=/usr/local/java/jre    
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin  环境变量，在PATH环境变量中添加JAVA根目录下bin子目录
export CLASSPATH=./:$JAVA_HOME/lib:$JRE_HOME/lib    类路径

[root@amoeba ~]# source /etc/profile        #设置变量立即生效
[root@amoeba ~]# echo $PATH                 #输出环境信息
[root@amoeba ~]# echo $CLASSPATH            #输出类路径信息
[root@amoeba ~]# java -version              #查看版本信息

3.3.2、安装amoeba-mysql-3.0.5-RC-distribution.zip管理软件

[root@amoeba ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip    #解压缩
[root@amoeba ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba   #剪切文件到/usr/local目录下生成amoeba 文件
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/    #对生成的文件授权
[root@amoeba ~]# vi /usr/local/amoeba/jvm.properties  #编辑配置文件
在32行下添加，并注释32行数据
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"   #缓存空间大小设置

3.3.3、创建启动脚本

[root@amoeba ~]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 20 90    #运行级别
export JAVA_HOME=/usr/local/java       #运行环境
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin
NAME=Amoeba             #变量名
AMOEBA_BIN=/usr/local/amoeba/bin/launcher  #二进制文件启动脚本
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown  #二进制文件关闭脚本
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid  
SCRIPTNAME=/etc/init.d/amoeba   #脚本文件
case "$1" in
start)
echo -n "Staring $NAME..."    #提示
$AMOEBA_BIN                #启动
echo "done"                       #输出信息
;;
stop)
echo -n "Stopping $NAME..."
$SHUTDOWN_BIN
rm -rf $PIDFILE                      #删除进程文件
echo "done"
;;
restart)                       #重启
$SHUTDOWN_BIN                  #先执行关闭
sleep 1                        #延时1秒
$AMOEBA_BIN                    #后关闭
;;
*)
echo "Usage:$SCRIPTNAME {start|stop|restart}"
exit 1
esac
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/ 
[root@amoeba ~]# chkconfig --add amoeba
[root@amoeba ~]# chkconfig --list
[root@amoeba ~]# systemctl start amoeba
[root@amoeba ~]# systemctl status amoeba

3.3.4、主从数据库都进行授权

mysql> grant all privileges on *.* to 'test'@'192.168.140.%' identified by '123.com';
mysql> flush privileges;                     #刷新权限

3.3.5、在代理服务器ameoba上授权客户端登录时用户名和密码

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml    #进入配置文件
设置客户端连接amoeba前端服务器时使用的用户名和密码
           <property name="user">amoeba</property>
          <property name="password">123456</property>

3.3.6、设置读与写数据库的池

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml 
<property name="defaultPool">master</property>默认池
删除注释
 <property name="writePool">master</property> 主数据库负责写
 <property name="readPool">slaves</property>  从数据库负责读

3.3.7、编辑配置文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml 
      <property name="user">test</property>
        <property name="password">123.com</property>   
#定义amoeba登录上数据库的授权用户名与密码
43行
  <dbServer name="master"  parent="abstractServer">
              
  <property name="ipAddress">192.168.140.40</property>
         
设置从服务器1，地址
50行
    <dbServer name="slave1"  parent="abstractServer">
              
    <property name="ipAddress">192.168.140.50</property>
复制从服务器1，地址配置数据
设置从服务器2，地址
     <dbServer name="slave2"  parent="abstractServer">
            <factoryConfig>
                  <!-- mysql ip -->
       <property name="ipAddress">192.168.140.60</property>
                </factoryConfig>
        </dbServer>
  <dbServer name="slaves" virtual="true">          #定义池子
                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                        <property name="loadbalance">1</property>

                        <!-- Separated by commas,such as: server1,server2,server1 -->
                        <property name="poolNames">slave1,slave2</property>

[root@amoeba ~]# systemctl restart amoeba.service    #重启服务
[root@amoeba ~]# systemctl status amoeba             #查看状态

3.3.8、配置客户机

[root@client ~]# yum -y install mariadb*              #安装软件
[root@client ~]# systemctl start mariadb.service     #启动服务
MariaDB [(none)]> exit
[root@client ~]# mysql -uamoeba -p123456 -h 192.168.140.20 -P8066 

注：针对ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.140.20' (111)报错信息
解决方案：去amoeba用脚本重启服务
[root@amoeba ~]# /usr/local/amoeba/bin/shutdown 
[root@amoeba ~]# /usr/local/amoeba/bin/launcher 

3.3.9、测试读写分离

3.3.9.1、客户端上使用数据库

[root@client ~]# mysql -uamoeba -p123456 -h 192.168.140.20 -P8066  #登录数据库
MySQL [(none)]> use test;               #使用数据库
MySQL [test]> create table aaa(id int(10),name varchar(64),address varchar(20));         #创建表
MySQL [test]> insert into aaa values(1,'zhangsan','this is master'); #插入数据记录

3.3.9.2、进入主从数据库进行查看

mysql> use test;
mysql> select * from aaa;

[root@slave1 ~]# mysql -uroot -p123456
mysql> select * from test.aaa;

mysql> select * from test.aaa;

3.3.10、关闭主从同步复制，查看情况

主从复制关闭，再次添加数据，此时因为主数据库负责写操作，从数据库负责读操作，所以此时应只有主数据库上可以查看到新增的数据，从数据库上无变化

3.3.10.1、在客户端添加数据记录

[root@client ~]# mysql -uamoeba -p123456 -h 192.168.140.20 -P8066
MySQL [(none)]> use test;
MySQL [test]> insert into aaa values(2,'lisi','this is master2');

3.3.10.2、在slave1上关闭主从同步

[root@slave2 ~]# mysql -uroot -p123456
mysql> stop slave;

3.3.10.3、再次进入客户机添加数据记录

MySQL [test]> insert into aaa values(3,'wangwu','this is master3');

3.3.10.4、进入主数据库查看

[root@master ~]# mysql -uroot -p123456
mysql> select * from test.aaa;

3.3.10.5、进入slave1、slave2查看

mysql> select * from test.aaa;            #进入slave1查看

mysql> select * from test.aaa;            #进入slave2查看

3.3.10.6、总结：因为slave1关闭了主从同步，所以读不到新增的数据，只有master和slave2可以读到新增数据

3.3.11、写入不同的数据，查看客户端读取时情况

在从数据库上
写入不同的数据，查看客户端读取时情况

3.3.11.1、在slave1上写入数据

mysql> select * from test.aaa;
mysql> delete from test.aaa;
mysql> select * from test.aaa;
mysql> insert into test.aaa values(2,'zhaolei','this is slave1');
mysql> select * from test.aaa;

3.3.11.2、在slave2上写入数据

mysql> select * from test.aaa;
mysql> delete from test.aaa;
mysql> select * from test.aaa;
mysql> insert into test.aaa values(4,'zhouwu','this is slave2');
mysql> select * from test.aaa;

3.3.11.3、客户端进行查看

MySQL [test]> select * from aaa;
MySQL [test]> select * from aaa;

3.3.11.4、进入主数据库查看

mysql> select * from test.aaa;

3.3.11.5、总结：客户端可以以轮询的方式去访问slave1和slave2从数据库，并且说明客户机提供Amoeba写入数据，写在主数据库上的，而从Amoeba中读取数据库信息，是从从数据库中读取的

3.3.12、恢复slave1与master的主从同步

3.3.12.1、先进入master查看主数据库状态

mysql> show master status;   #查看主数据库状态

3.3.12.2、进入slave1恢复主从同步

mysql> change master to master_host='192.168.140.40',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=2415;
mysql> start slave; 
mysql> show slave status\G

3.3.12.3、进入slave2查看主从状态

mysql> show slave status\G

3.3.12.4、总结

slave1恢复主从同步后，线程都已开启，正常与master进行同步