文章目录
一、mysql主从复制
主从复制的必要性
-
首先要部署主从复制,只有主从复制完成了,才能在此基础上进行数据的读写分离。
-
实时灾备,用于故障切换
-
读写分离,提供查询服务
-
备份,避免影响业务
1. MySQL主从复制工作过程
文字叙述如下:
- 主从复制 参与的线程和日志分别为 dump线程,I/O线程,sql线程
- 主服务器里的日志为二进制日志 从服务器里的为中继日志
在MySQL主服务器里 通过dump线程生成二进制日志,从服务器里的I/O线程读取 主服务器里二进制日志内容,并写入备服务器的中继日志,然后通过sql线程 进行重放和读取中继日志内容 向备服务器里更新数据
2. MySQL主从复制类型(主要类型)
- 基于语句的复制(STATEMENT):在主服务器上执行的 SQL 语句,在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制,效率比较高。
- 基于行的复制(ROW):把改变的内容复制过去,而不是把命令在从服务器上执行一遍。
- 混合类型的复制(MIXED):默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制。
主从复制部署
- 单做主从复制最少需要三台服务器(一台master 两台slave)
- 需要部署完mysql5.6或5.7
1. 关闭防火墙及安全机制
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
2. 搭建主从服务器时间同步
主服务器配置
yum -y install ntp 下载ntp软件包(默认已安装)
vim /etc/ntp.conf 打开配置文件 在末尾添加两行
server 127.127.100.0 #设置本地是时钟源,注意修改网段
fudge 127.127.100.0 stratum 8 #设置时间层级为8(限制在15内)
service ntpd start
两个从服务器配置
yum -y install ntp ntpdate
service ntpd start
/usr/sbin/ntpdate 192.168.100.133 #进行时间同步,指向Master服务器IP
crontab -e #设置周期性任务
*/30 * * * * /usr/sbin/ntpdate 192.168.100.133
3. 服务器配置
1. 主服务器配置
vim /etc/my.cnf
server-id = 1
log-bin=master-bin #添加,主服务器开启二进制日志
log-slave-updates=true #添加,允许从服务器更新二进制日志
systemctl restart mysqld
mysql -u root -p 登陆进入mysql
给从服务器授权
grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '000000';
刷新系统权限表
flush privileges;
查看主数据库的状态及偏移量
show master status;
#File 列显示日志名,Fosition 列显示偏移量
2. 从服务器配置
vim /etc/my.cnf
server-id = 2 #需要注意 各个服务器id都不能相同
relay-log=relay-log-bin
添加,定义中继日志文件的位置和名称
relay-log-index=slave-relay-bin.index
systemctl restart mysqld
mysql -u root -p
配置同步,注意 master_log_file 和 master_log_pos 的值要与Master查询的一致,
change master to master_host='192.168.100.133' , master_user='myslave',master_password='000000',master_log_file='master-bin.000001',master_log_pos=412;
start slave; #启动同步,如有报错执行 reset slave;
show slave status\G #查看 Slave 状态
确保 IO 和 SQL 线程都是 Yes,代表同步正常。
Slave_IO_Running: Yes #负责与主机的io通信
Slave_SQL_Running: Yes #负责自己的slave mysql进程
3. 验证
4. 关于主从复制可能遇到的问题
1. 从服务器上的Slave_IO_Running: No
有可能原因:
- my.cnf配置有问题
- 网络问题
- 防火墙问题
- 密码、file文件名、pos偏移量不对
2. 主从复制延迟
首先查看服务器状态:show slave status\G
大致参数解释如下:
- show slave status显示参数Seconds_Behind_Master不为0,这个数值可能会很大
- show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大,说明bin-log在从库上没有及时同步,所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
- mysql的从库数据目录下存在大量mysql-relay-log日志,该日志同步完成之后就会被系统自动删除,存在大量日志,说明主从同步延迟很厉害
解决方法(大致解决方案 如有其他见解 欢迎补充指正)
1)、架构方面
1.业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力。
2.单个库读写分离,一主多从,主写从读,分散压力。这样从库压力比主库高,保护主库。
3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
4.不同业务的mysql物理上放在不同机器,分散压力。
5.使用比主库更好的硬件设备作为slave
总结,mysql压力小,延迟自然会变小。
2)、硬件方面
1.采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好。
2.存储用ssd或者盘阵或者san,提升随机写的性能。
3.主从间保证处在同一个交换机下面,并且是万兆环境。
总结,硬件强劲,延迟自然会变小。一句话,缩小延迟的解决方案就是花钱和花时间。
3)、mysql主从同步加速
1、sync_binlog在slave端设置为0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用slave端的binlog
4、slave端,如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2
二、MySQL读写分离
- 顾名思义 使用多台MySQL服务器 将读与写(删,改)操作分离工作
- 来以此提升服务器稳定及负载 以及用户体验
因为读操作远比写操作要耗时短 占用资源少 所以如果同时读写情况下 难免会影响服务器性能以及读的效率,这也是读写分离存在的主要意义
1. 读写分离原理
- 基本的原理是让主数据库处理事务性查询,而从数据库处理 select 查询。
- 数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。
- 具体在哪台上进行读或写 根据生产环境调整设置
2. 实现读写分离的两种方式
1. 代码内部实现
- 在代码中根据 select、insert 进行路由分类,这类方法也是目前生产环境应用最广泛的。
优缺点:
- 优点是性能较好,因为在程序代码中实现,不需要增加额外的设备为硬件开支;
- 缺点是需要开发人员来实现,运维人员无从下手。
并不是所有的应用都适合在程序代码中实现读写分离,像一些大型复杂的Java应用,如果在程序代码中实现读写分离对代码改动就较大。
2. 基于中间代理层实现
- 代理一般位于客户端和服务器之间,代理服务器接到客户端请求后通过判断后转发到后端数据库
- 常用的中间代理程序
- MySQL-Proxy:MySQL-Proxy 为 MySQL 开源项目,通过其自带的 lua 脚本进行SQL 判断。
- Amoeba 该程序由Java语言进行开发,阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。但是是一个非常容易使用,可移植性非常强的软件,因此它在生产环境中被广泛用于数据库的代理层
但是MySQL-Proxy 对于新手不太友好 需要自己编写大量的Lua脚本来实现功能
所以综上所述 我们用Amoeba程序作为中间代理进行实现读写分离
读写分离部署
实验架构大致所需服务器为五台:
- 三台mysql服务器(一主两备)
- 一台客户端
- 一台部署amoeba
所需软件包在这里
jdk-6u14-linux-x64.bin (建议使用1.5或1.6 高版本不建议 )
amoeba-mysql-binary-2.2.0.tar
链接: 软件包.
提取码0410
1. 安装jdk环境
将jdk文件复制到/usr/local目录下
执行jdk文件
编辑全局配置文件
vim /etc/profile
在末行添加
export JAVA_HOME=/usr/local/jdk1.6.0_14
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
java -version
2. 安装amoeba软件包
创建一个amoeba目录
mkdir /usr/local/amoeba
解压到指定目录
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
给其目录赋权
chmod -R 755 /usr/local/amoeba/
启动amoeba
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功
3. 配置读写分离
配置读写分离前提是要将主从服务器的mysql开放权限给amoeba访问
在数据库内进行提权
grant all on *.* to test@'192.168.100.%' identified by '000000';
此命令表示 给来源于192.168.100.0网段的test用户 所有库和表的所有权限 密码为000000
配置amoeba服务
进入amoeba配置文件目录
cd /usr/local/amoeba/conf/
给配置文件先做一个备份(强烈建议)
cp amoeba.xml amoeba.xml.bak
修改amoeba配置文件
vim amoeba.xml
#---------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库时,会报错
<!-- mysql schema
<property name="schema">test</property>
-->
#---------26修改-----------------------------------------
<!-- mysql user -->
<property name="user">test</property>
#---------28-30去掉注释----------------------------------
<property name="password">000000</property>
#---------45修改,设置主服务器的名Master------------------
<dbServer name="master" parent="abstractServer">
#---------48修改,设置主服务器的地址----------------------
<property name="ipAddress">192.168.100.133</property>
#---------52修改,设置从服务器的名slave1-----------------
<dbServer name="slave1" parent="abstractServer">
#---------55修改,设置从服务器1的地址---------------------
<property name="ipAddress">192.168.100.134</property>
#---------58复制上面6行粘贴,设置从服务器2的名slave2和地址---
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.100.135</property>
#---------修改后的65或66修改-------------------------------------
<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
4.测试读写分离
- 测试端为客户端
- 需安装虚拟mysql客户端(模拟客户端)
yum install -y mysql mysql-server
用户名和密码是amoeba配置文件里设置的 ip可以通过查询得到的(看上图)
mysql -u amoeba -p123456 -h 192.168.100.129 -P8066
在master上创建测试数据
将slave1和slave2服务器关闭同步 并添加数据并查询
slave1:
slave2:
master
客户端查询测试:
ps:上面测试把同步关闭是为了能够查看明显 ,需要再打开即可(start slave;)
打开时 会发现有存在“数据不同步的”问题
- 这是因为 所谓主从复制 是基于主服务器上的数据(二进制日志) 来复制 从服务器之间并不会相互复制 图中的3,4数据是主服务器上的数据 所以两个从服务器里都有 不同的是从服务器自己的数据
- 这并不影响使用 因为正常情况下 都是从主服务器或者客户端内写入数据