Mysql主从复制，读写分离
原创
©著作权归作者所有：来自51CTO博客作者wx630a51437c41f的原创作品，请联系作者获取转载授权，否则将追究法律责任
一、Mysql主从复制概述
在企业中，业务数据量通常都比较大，单台Mysql在安全性，高可用性和高并发方面都无法满足实际需求，因此可以通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力，mysql主从复制是对数据库中的数据，语句做备份。
MySQL支持的复制类型
1、STATEMENT：基于语句的复制，在服务器上执行sql语句，在从服务器上执行同样的语句mysql默认采用基于语句的复制，执行效率高。
2、ROW：基于行的复制，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
3、MIXED：混合类型的复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
二、Mysql主从复制原理
主从复制原理：客户端写入数据，主服务器存放在硬盘里，保存为sql语句放在二进制日志中，dump线程监听来自I/O线程的请求，并将二进制日志中更新的数据发送给I/O线程，slave节点会探测主的二进制日志，若发现数据更新，IO线程会请求二进制日志的数据，dump线程给他响应。I/O拿到数据后存放在从服务器的中继日志中，由SQL进程读取并存放在硬盘中，使主从数据一致。
三、主从复制工作过程
1、Master节点将数据的改变记录变成二进制日志（binlog），当Master上的数据发生改变时，则将其改变写入二进制日志中。
2、slave节点会在一定时间间隔内对Master的二进制日志进行探测是否发生改变，如果发生改变，则开始一个I/O线程请求Master的二进制日志。
3、同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制日志，并保存至slave节点本地的中继日志中（relay），slave节点将启动SQL线程从中继日志中读取二进制日志，在本地解析为sql语句执行，使数据和Master节点一致，最后I/O线程和SQL线程将进入睡眠模式，等待下一次被唤醒。
注：中继日志通常会位于OS缓存中，所以中继日志的开销很小。
复制过程由一个重要的限制，即复制在slave上是串行化的，也就是说Master上的并行更新操作不能在slave上并行操作。
四、搭建MySQL主从复制
systemctl stop firewalld.service
systemctl disable firewalld.service
setenforce 0
1、Mysql主从服务器时间同步
主服务器设置
[root@localhost ~]# yum install -y ntp
[root@localhost ~]# vim /etc/ntp.conf #在末尾添加下面字段
server 127.127.246.0 #设置本地是时钟源，注意网段
fudge 127.127.246.0 stratum 8 #设置时间层级为8（限制在15内）
[root@localhost ~]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
从服务器设置
[root@localhost ~]# yum install -y ntp
[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# /usr/sbin/ntpdate 192.168.246.77 #进行时间同步
30 Nov 23:10:24 ntpdate[1835]: adjust time server 192.168.246.77 offset -0.034122 sec
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.246.77
2、主服务器配置
[root@localhost ~]# vim /etc/my.cnf #添加下面字段
log-bin=master-bin #添加主服务器开启二进制日志
binlog_format = MIXED #复制类型为混合模式
log-slave-updates=true #添加允许slave从master复制数据时可以写入到自己的二进制日志
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'myslave'@'192.168.246.%' identified by '123456';
#给从服务器授权
flush privileges；
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 604 | | | |
+-------------------+----------+--------------+------------------+-------------------+
#File表示日志名，Position：表示偏移量
3、从服务器1配置
[root@localhost ~]# vim /etc/my.cnf
server-id = 2 #修改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从
库上开启此功能。
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -uroot -p123456
CHANGE master to master_host='192.168.246.77',master_user='mysalve',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604
-> ;
#配置同步master_log_file和master_log_pos的值要与master查询的一致
mysql> start slave; #启动同步，若有报错执行reset slave;
show slave status\G #查看slave状态
Slave_IO_Running: Yes #负责与主机的IO通信
Slave_SQL_Running: Yes #负责自己的slave mysql进程
#要确保IO和SQL线程都是yes
从服务器2配置
vim /etc/my.cnf
server-id = 3
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
systemctl restart mysqld.service
mysql -u root -p123456
change master to master_host='192.168.246.77' , master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=154;
start slave;
show slave status\G
Slave_IO_Running: No的原因：
1.网络不通
2.my.cnf配置有问题
3.密码，file文件名，pos偏移量不对
4.防火墙没有关闭
验证：
主服务器创建一个库
mysql> create database lzy;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzy |
| mysql |
| performance_schema |
| sys |
+--------------------+
从服务器查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzy |
| mysql |
| performance_schema |
| sys |
+--------------------+
如数据中途加入主从复制的库，需要导出主服务器库的库文件并且导入到从服务器中
五、搭建MySQL读写分离
1、安装java环境
[root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@localhost opt]# cd /usr/local/
[root@localhost local]# ls
bin boost etc games include jdk-6u14-linux-x64.bin lib lib64 libexec mysql sbin share src
[root@localhost local]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost local]# ./jdk-6u14-linux-x64.bin
[root@localhost local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost local]# ls
bin boost etc games include jdk1.6 jdk-6u14-linux-x64.bin lib lib64 libexec mysql sbin share src
[root@localhost local]# vim /etc/profile.d/java.sh
[root@localhost local]# cat /etc/profile.d/java.sh
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
[root@localhost local]# source /etc/profile.d/java.sh
[root@localhost local]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
2、安装Amoeba
[root@localhost local]# cd /opt
[root@localhost opt]# mkdir /usr/local/amoeba
[root@localhost opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/
[root@localhost opt]# /usr/local/amoeba/b
benchmark/ bin/
[root@localhost opt]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
3、在master，slave的mysql上给Amoeba赋权访问
mysql> grant all on *.* to 'amb'@'192.168.246.%' identified by '123456';
mysql> flush privileges;
#3台服务器都要执行
4、配置amoeba服务
[root@localhost opt]# cd /usr/local/amoeba/conf/
[root@localhost conf]# cp amoeba.xml ./amoeba.xml.bak #备份
[root@localhost conf]# vim amoeba.xml
30 <property name="user">amb</property> #修改amb
32 <property name="password">123456</property> #修改密码123456
115 <property name="defaultPool">master</property> #设置默认连接池master
118 <property name="writePool">master</property> #修改写连接池master
119 <property name="readPool">slaves</property> #修改读连接池slaves
[root@localhost conf]# vim dbServers.xml
26 <property name="user">amb</property> #注释，默认进入amb库
29 <property name="password">123456</property> #设置密码
45 <dbServer name="master" parent="abstractServer"> #设置master
48 <property name="ipAddress">192.168.246.77</property>#设置主服务器地址
52 <dbServer name="slave1" parent="abstractServer"> #slave1
55 <property name="ipAddress">192.168.246.177</property>#slave1地址
58 </dbServer>
59
60 <dbServer name="slave2" parent="abstractServer">
61 <factoryConfig>
62 <!-- mysql ip -->
63 <property name="ipAddress">192.168.246.97</property>
64 </factoryConfig>
65 </dbServer>
#58行到65行复制上面slave1的配置粘贴到slave1配置下面改为slave2的地址
67 <dbServer name="slaves" virtual="true">
68 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
69 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
70 <property name="loadbalance">1</property>
71
72 <!-- Separated by commas,such as: server1,server2,server1 -->
73 <property name="poolNames">slave1,slave2</property>
#修改67行name=slaves；73行poolname=slave1，slave2
/usr/local/amoeba/bin/amoeba start& #&表示交给后台启动
#启动Amoeba软件，按ctrl+c 返回
netstat -anpt | grep java
#查看8066端口是否开启，默认端口为TCP 8066
5、测试读写分离
yum install -y mariadb-server mariadb
systemctl start mariadb.service
在客户端服务器上测试:
mysql -u amoeba -p123456 -h 192.168.246.77 -P8066
通过amoeba服务器代理访问mysql，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器
