mysql主从复制与读写分离
主从复制
为什么要布置主从复制
在企业应用中,成熟的业务通常数据量都比较大
单台MySQL在安全性,高可用性和高并发方面都无法满足实际的需求
配置多台主从数据库服务器以实现读写分离(基于主从复制)
类型
基于语句复制基于行的复制混合类型的复制
复制的过程
1.master将用户对数据库更新的操作以二进制格式保存到binary log日志文件中
2.slave上面的IO进程连接上master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容
IO进程:从数据库复制主数据库上二进制日志的进程
3.master接收到来自slave的IO进程的请求后,通过负责复制的IO进程,根据请求信息读取日志指定位置的日志信息,返回给slave的IO进程。返回的信息中除了日志所包含的信息之外,还包括本次返回的信息已经到master端的bin-log文件的名称以及bin-log的位置
4.slave的IO进程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的最末端,并将读取到的master端的bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发送给我”
5.slave的sql进程检测到replay-log中新增加了内容后,会马上解析relay-log的内容
6.将relay-log中可执行的内容在master端真实执行
读写分离
只在主服务器上写,只在从服务器上读
主数据库处理事务性查询,从数据库处理select查询
数据库复制用于将事务性查询的变更同步到集群中的从数据库
读写分离方案
基于程序代码实现
基于中间代理层:mysql-proxy、amoeba
实地部署
基本流程
主从数据库之间先时间同步,后主从复制,amoeba配置读写分离,授权,验证登录
准备
主数据库ip:20.0.0.10
从数据库1:20.0.0.20
从数据库2:20.0.0.30
amoeba服务器:20.0.0.70
客户端:20.0.0.80
systemctl stop firewalld
setenforce 0 #所有关闭防火墙(临时)
时间同步
在主服务器上
yum -y install ntpdate ntp #安装ntp软件
ntpdate ntp.aliyun.com # 时间同步
date #查看当前日期
vi /etc/ntp.conf #编辑配置文件
8// restrict default nomodify #定义默认访问规则,nomodify禁止远程主机修改本地服务器配置
17// restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap #在20.0.0.0网段的主机都可以使用我们的ntp服务来时间同步
21// fudge 127.127.1.0 stratum 10 #设置本机的时间层级为10级,0级表示时间层级为0级,是向其他服务器提供时间同步源的意思,不要设置为0级
22// server 127.127.1.0 #设置本机为时间同步源
systemctl restart ntpd #重启服务
netstat -anptu | grep ntpd #查看端口状态
crontab -e #部署任务计划
*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com #每隔10分钟进行一次同步对象为阿里云时钟网站的时间同步
在从服务器上
yum -y install ntpdate #安装ntp
ntpdate 20.0.0.10 #设置同步源
crontab -e 部署任务计划
*/2 * * * * /usr/sbin/ntpdate 20.0.0.10 #每隔2分钟进行一次同步对象为20.0.0.10(主数据库)的时间同步
主从复制配置
主数据库上
vi /etc/my.cnf
#添加
log_bin=master_bin #同步的二进制日志开启前缀
log_slave_updates=true #允许日志同步给从数据库
systemctl restart mysqld #重启服务
mysql -uroot -p #登录mysql
grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123123'; #设置授权
#grant replication slave授予复制权限
#on *.*可复制所有库所有表
# 'myslave'@'20.0.0.%'被授予权限的用户名和ip或网段范围
# identified by '123123'; 识别的密码
flush privileges; #刷新权限
show mster status; #查看主数据库状态信息
现二进制日志名称未改变,原因是之前添加的二进制日志文件名,将其删除,重启mysql
show mster status; #进入数据库,查看主数据库状态信息
从数据库上
vi /etc/my.cnf
server-id = 2 #服务id为2
relay_log=relay-log-bin #二进制日志的中继日志前缀
relay_log_index=slave-relay-bin.index #中继日志索引
systemctl restart mysqld #重启服务
mysql -uroot -p #登录mysql
change master to master_host='20.0.0.10',master_user='myslave',master_password='123123',master_log_file='master_bin.000001',master_log_pos=154;
#master_host='20.0.0.10' #主数据库的ip地址
#master_user='myslave' #自身以用户名‘myslave’登录主数据库
#master_password='123123' #登录密码为123123
#master_log_file='master_bin.000001' #主从同步的日日志文件
#master_log_pos=154 #日志开始路径,主从复制的位置
start slave; #开启从数据库状态
show slave status\G #查看从数据库状态信息
若是slave_IO_running显示为no则可能是主从的网络不通或是主库授权错误(或未授权)若从库查看连接主库I/0线程状态为conneting,-直是这个状态,考虑双方的防火墙是否开启。重新关闭防火墙后,查看slave状态
SQL线程显示为NO:从库日志和位置点与主不同步
在从数据库2上同样配置(server_id改为3)
主从测试
在主数据库里创建一个test数据库,查看从数据库中是否会同步出test数据库(在读写分离时使用的ameoba,在授权时默认的数据库为test)
主数据库
从数据库
读写分离
ameoba服务器上
yum -y install ntpdate #安装ntp软件
ntpdate 20.0.0.10 #设置同步源
crontab -e #设置任务计划
*/2 * * * * /usr/sbin/ntpdate 20.0.0.10 #每隔2分钟进行一次同步对象为20.0.0.10(主数据库)的时间同步
tar zxvf jdk-8u91-linux-x64.tar.gz #解压缩java软件包
cp -rv jdk1.8.0_91/ /usr/local/java #将软件主题复制到该位置
vi /etc/profile 设置环境变量
#末尾添加
export JAVA_HOME=/usr/local/java #设置java根目录为全局变量
export JRE_HOME=/usr/local/java/jreexport PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin #在PATH环境变量中添加java根目录下的bin
export CLASSPATH=./:$JAVA_HOME/lib:$JRE_HOME/lib #java类所在目录
source /etc/profile #设置生效
echo $PATH #输出环境信息
echo $CLASSPATH #输出类路径信息
java -version #查看版本
安装amoeba
unzip amoeba-mysql-3.0.5-RC-distribution.zip #解压缩软件包
mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba #移动软件
chmod -R 755 /usr/local/amoeba/ #授予权限
vi /usr/local/amoeba/jvm.properties #编辑配置文件
32// JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" #缓存空间大小设置
创建启动脚本
vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 10 20
export JAVA_HOME=/usr/local/java #环境
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin
name=Amoeba
start=/usr/local/amoeba/bin/launcher #开启服务路径
stop=/usr/local/amoeba/bin/shutdown #关闭服务路径
pid=/usr/local/amoeba/Amoeba-MySQL.pid #进程文件路径
scripname=/etc/init.d/amoeba #脚本文件
case "$1" in
start)
echo -n "Staring $name..."
$start
echo "done"
;;
stop)
echo -n "Stopping $name..."
$stop
rm -rf $pid
echo "done"
;;
restart)
$stopsleep 1
$start
;;
*)
echo "Usage:$scripname {start|stop|restart}"
exit 1
esac
chmod 755 /etc/init.d/amoeba #添加权限
chkconfig --add amoeba #加入系统管理
chkconfig --list #查询系统服务的默认启动状态
systemctl start amoeba #启动服务
netstat -anpt | grep 8066 #查看端口状态
在主从数据库上授权
grant all privileges on *.* to 'test'@'20.0.0.%' identified by '123123';
flush privileges;
在amoeba服务器上
vi /usr/local/amoeba/conf/amoeba.xml
授权客户端登录的用户名和密码
<property name="user">user</property>
<property name="password">123123</property>
设置读写池
vi /usr/local/amoeba/conf/amoeba.xml
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
设置访问地址
vim /usr/local/amoeba/conf/amoeba.xml
26// <property name="user">test</property>
28// <property name="password">123123</property>
43// <dbServer name="master" parent="abstractServer">
46// <property name="ipAddress">20.0.0.10</property>
50// <dbServer name="slave1" parent="abstractServer">
53// <property name="ipAddress">20.0.0.20</property>
dbServer name="slave2" parent="abstractServer"> #复制从服务器1的信息,修改后,放在其后
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">20.0.0.30</property>
</factoryConfig>
</dbServer>
64// <dbServer name="slaves" virtual="true"> #设置定义池
70// <property name="poolNames">slave1,slave2</property>
systemclt restart amoeba #重启服务
配置客户机
yum -y install mariadb* #安装软件
systemctl start mariadb.service #启动服务
mysql
exit #测试性登录数据库后退出
mysql -uuser -p123123 -h 20.0.0.70 -P8066 #登录前端数据库
测试读写分离
在客户端上
mysql -uuser -p123123 -h 20.0.0.70 -P8066 #登录前端数据库
use test;
create table ph(id int(4),name varchar(128)); #插入字段
insert into ph values(1,'p'); #插入数据
select * from ph; #查看表ph
在主从数据库上查看
select * from test.ph;
在两个从数据库上关闭主从同步,因为主数据库负责写的操作,从数据库负责读的操作,此时应该只有主数据库上右数据更新,从数据库上没有数据跟新
在两个从数据库上关闭主从同步
stop slave;
在客户端上写入数据
insert into ph values(2,'h');
主数
据库上有数据更新
两个从服务器上无数据更新
同时客户端上也没有显示自己更新的数据
恢复两个从数据库
start slave;
此时客户端上显示出自己更新的数据
关闭主从同步后在两个从数据库上写入不同的数据
insert into ph values(3,‘p’); #在从数据库1上插入数据
insert into ph values(4,‘h’); #在从数据库2上插入数据
在客户端上查看,轮询显示两个从数据库上的数据