一、主从复制
主从复制是读写分离和高可用MHA的前提。
1.1 主从复制的模式
MySQL的默认模式
异步模式:主库在更新完事务之后,会立即把结果返回给从服务器,但主库并不关心从库是否接受到、是否处理成功。
一旦出现网络问题或其他因素影响,容易导致同步失败。
特点:效率高,不够安全
全同步模式
主库在更新完事务之后,立即把结果返回到从库,所有的从库执行完毕之后才能继续下一个同步。
特点:安全,性能不高
半同步复制
介乎于异步和全同步之间。主库更新完事务之后,也是会同步到从库。同步提交之后有一个等待时间,这个等待时间是一个TCP/IP的往返时间,一般在5ms左右。
特点:一定程度上保证效率,一定程度上保证安全,即数据的完整性
1.2 主从复制的特点
主从复制的常见架构模式,一般是奇数台:一主两从 或 两主三从 ;比较少见两主两从
一般只有主库复制到从库,即主库的更新会同步到从库,但从库的数据更新不会同步到主。
如果是主主模式,则主库之间互为主库,各自的更新会互相同步。
主从复制的延迟怎么解决
- 网络问题:防火墙原因;网络设备问题
- 硬件设备问题:CPU、内存、磁盘出现问题
- 配置文件问题
1.3 主从复制的设置类型
1.3.1 双一设置:安全性设置
配置文件当中进行双一设置可以提高数据配置的一致性,提高数据的安全性。
前提:数据库的存储引擎要是InnoDB
vim /etc/my.cnf
innodb_flush_log_at_trx_commit = 1
#每次提交都会刷新事务日志,确保事务的持久性。但是会影响性能
sync_binlog = 1
#将二进制日志的内容保存到磁盘,确保日志的持久性
双一设置可以提高安全性,但是对性能影响较大,适用于对数据安全性要求极高的场景。
1.3.2 性能化设置
vim /etc/my.cnf
sync_binlog = 0
解释:极端性能化,一旦系统重启、刷新等,会丢失日志
一般设置为:
sync_binlog = N
N为较大的数字,比如10,即最多提交几次事务会进行磁盘刷新,将日志内容保存到磁盘
innodb_flush_log_at_trx_commit = 2
解释:每次更新都保存在内存中,不进行刷新,不推荐设置
innodb_buffer_pool_size = 60M
解释:控制InnoDB存储引擎缓冲池的大小,增大可以提高数据库的性能,但是占用的是系统内存,配置的时候要注意合理化使用
1.4 主从复制的实现
实现是基于mysql的二进制日志,根据主库的二进制文件的标志位,实现主和从的同步。
主从服务器之间,服务器的时间要同步。
1.4.1 架构
主:mysql1 :20.0.0.41
从1:mysql2 :20.0.0.42
从2:mysql3 :20.0.0.43
1.4.2 配置流程
- 对三台mysql服务器统一配置如下
systemctl stop firewalld
setenforce 0
yum -y install ntpdate
date
#查看时间同步情况
ntpdate ntp.aliyun.com
#使用ntp.aliyun.com进行时间同步
- 对主库配置如下
vim /etc/my.cnf
log-bin = master-bin
binlog_format = MIXED
log-slave-updates = true
#允许从库从主库复制数据时可以写入到从库自己的二进制日志当中
systemctl restart mysqld
- 三台库上都创建用户myslave:
mysql -u root -p
create user 'myslave'@'20.0.0.%' identified with mysql_native_password by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%';
FLUSH PRIVILEGES;
- 在主库上查看主的状态,获取后面配置需要的信息
show master status;
#获取当前主库二进制日志文件的position
mysql> show master status;
+-------------------+----------+
| File | Position |
+-------------------+----------+
| master-bin.000001 | 847 |
+-------------------+----------+
#File 列显示二进制日志名,Position 列显示偏移量
- 对从库1配置如下:
vim /etc/my.cnf
server-id = 2
#服务器id不能一致
relay-log = relay-log-bin
#从服务器上获取二进制日志的开头,开启从库的二进制日志
relay-log-index = slave-relay-bin.index
#二进制日志的索引文件的名称
relay_log_recovery = 1
#配置从服务器在启动时是否执行二进制日志的恢复操作(和主库同步),1表示开启
systemctl restart mysqld
- 对从库2配置如下:
vim /etc/my.cnf
server-id = 3
#服务器id不能一致
relay-log = relay-log-bin
#从服务器上获取二进制日志的开头,开启从库的二进制日志
relay-log-index = slave-relay-bin.index
#二进制日志的索引文件的名称
relay_log_recovery = 1
#配置从服务器在启动时是否执行二进制日志的恢复操作(和主库同步),1表示开启
systemctl restart mysqld
- 两台从库mysql服务器同步操作
mysql -u root -p
#建立同步
change master to master_host='20.0.0.41',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=847;
解释:
master_log_file
取决于主库的二进制日志文件具体是哪个,就是上面通过命令show master status;
获取的File
列下面的值master_log_pos
取决于上面通过命令show master status;
获取的主库的二进制日志文件的position
- 启动同步,查看同步是否成功
start slave;
#启动同步
show slave status\G;
#查看slave状态
#查看树形结构的slave 状态
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
#两个都为yes,表示同步开启成功
Slave_IO_Running
:检查从库和主库的IO通信是否正常Slave_SQL_Runing
:检查从库的SQL线程是否正常运行
在主从同步完成之前,需要先停止服务,一般是初始化的时候配置,防止因为其他操作,导致position的位置变化,导致同步失败。
如果IO或SQL检查出现no,需要重置slave设置:
#要先停止slave
stop slave;
#再重置slave
reset slave;
#重新在主查看status
show master status;
#重新获取binlog的编号和position,然后重新进行同步
二、读写分离
读写分离:主从架构中,主库只负责写,从库只负责读
2.1 读写分离的方式
基于程序代码内部实现
需要开发人员来实现,纯靠代码完成,涉及到数据库的二次开发。
性能好,不需要额外的硬件设备。目前生产环境应用最广泛的。
中间层代理:代理服务器转发到主从数据库。在客户端和主从架构之间有一个代理服务器,代理服务器收到客户端的请求之后,通过客户端的SQL语句来进行判断,读转到从,写转到主。
有以下代表性程序。
MySQL-Proxy
MySQL-Proxy 为 MySQL 开源项目,通过其自带的 lua 脚本进行SQL 判断。
Atlas
是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。
它是在mysql-proxy 0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。
支持事物以及存储过程。Amoeba
由陈思儒开发,作者曾就职于阿里巴巴。该程序由Java语言进行开发,阿里巴巴将其用于生产环境。
但是它不支持事务和存储过程。
由于使用MySQL Proxy 需要写大量的Lua脚本,这些Lua并不是现成的,而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol 的人来说是非常困难的。
而Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。
2.2 读写分离的实现
2.2.1 架构
主从服务器:
主:mysql1 :20.0.0.41
从1 :mysql2 :20.0.0.42
从2 :mysql3 :20.0.0.43
20.0.0.10 :test1:代理服务器 jdk1.6 Amoeba (需要准备jdk1.6版本的安装包,以及Amoeba的软件包)
20.0.0.20 :test2 : 客户端 mysql / mariadb
2.2.2 配置流程
基于上文配置完成的主从服务器,继续配置如下:
- test1 和 test2 关闭防火墙、守护进程
systemctl stop firewalld
setenforce 0
代理服务器test1上安装java环境:
因为Amoeba是基于jdk1.5开发的,官方建议环境为jdk1.5或jdk1.6
cd /opt
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
#按yes,按enter
mv jdk1.6.0_14/ /usr/local/jdk1.6
#声明java环境
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
java -version
#验证java环境
- 安装 Amoeba 软件 :
mkdir /usr/local/amoeba
cd /opt
tar -xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
chmod -R 755 /usr/local/amoeba
/usr/local/amoeba/bin/amoeba
#如显示amoeba start|stop说明安装成功
配置 Amoeba读写分离,两个 Slave 读负载均衡
4.1) 先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
如果主从复制处于running,就不需要配置两台从库,在主库上建完用户,从库会自动复制
CREATE USER 'amoeba'@'20.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'amoeba'@'20.0.0.%';
GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'20.0.0.%';
flush privileges;
4.2) 再回到amoeba代理服务器,配置amoeba服务:
cd /usr/local/amoeba/conf
cp amoeba.xml amoeba.xml.bak.2024-07-23
vim amoeba.xml
#修改Amoeba配置文件
#30行用户名设置为 amoeba
<property name="user">amoeba</property>
#32行密码设为123456
<property name="password">123456</property>
#115行defaultpool设为 master
<property name="defaultPool">master</property>
#117行 120行 注释删掉
#writepool 设置为master
#readpool 设置为slaves
<property name="writePool">master</property>
<property name="readPool">slaves</property>
4.3)修改数据库配置文件:
cp dbServers.xml dbServers.xml.bak.2024-07-23
vim dbServers.xml
#23行 注释掉:作用:默认进入test库 以防mysql中没有test库时,会报错
#注释方法 <!-- -->
#27行 user设置为 amoeba
<property name="user">amoeba</property>
#29-31行 取消注释
<property name="password">123456</property>
#46行 设置主服务器的名Master
<dbServer name="master" parent="abstractServer">
#IP地址设置为主的IP地址 20.0.0.41
<property name="ipAddress">20.0.0.41</property>
#53行 设置从服务器的名slave1
<dbServer name="slave1" parent="abstractServer">
#IP地址设置为从库1的IP地址 20.0.0.42
<property name="ipAddress">20.0.0.42</property>
#复制slave1 的dbServer模块
#把slave1 改为slave2
#修改IP地址为从库2的IP地址 20.0.0.43
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">20.0.0.43</property>
#66行dbServer name从multiusers改成slaves,virtual设为true
<dbServer name="slaves" virtual="true">
#71行 poolnames 设为 slave1 slave2
<property name="poolNames">slave1,slave2</property>
#保存退出
4.4)启动Amoeba软件
/usr/local/amoeba/bin/amoeba start &
#启动Amoeba软件,按ctrl+c 返回
netstat -antp | grep java
##查看8066端口是否开启,默认端口为TCP 8066
- 在主从服务器上开启查询日志
vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
cd /usr/local/mysql/data
tail -f mysql_general.log
#追踪客户端读写在主从服务器上的反馈
- 在客户端安装mariadb服务,访问代理服务器验证读写分离
yum -y install mariadb-server mariadb
systemctl start mariadb.service
mysql -u amoeba -p123456 -h 20.0.0.10 -P8066
#通过amoeba服务器代理访问mysql ,在通过客户端连接mysql后
#写入的数据只有主服务会记录,然后同步给从--从服务器
客户机mysql中操作:
create database test;
#主从都建立了库
use test;
create table test (id int(10),name varchar(10),address varchar(20));
#主从都建立了表
#向表里插入数据
insert into test values('3','wangwu','this_is_master');
#主从都插入了数据
#主库是默认提交模式,输入的指令直接提交
#从库的日志中用begin和commit开始和提交事务
#查询数据
select * from test;
#主库没有记录,查询在从库中轮询
三、问题
3.1 主从同步复制原理
(1)Master节点将数据的改变记录成二进制日志(bin log),当Master上的数据发生改变时,
则将其改变写入二进制日志中。
(2)Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变,如果发生改变,
则开始一个I/O线程请求 Master的二进制事件。
(3)同时Master节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,
并保存至Slave节点本地的中继日志(Relay log)中,Slave节点将启动SQL线程从中继日志中读取二进制日志,
在本地重放,即解析成 sql 语句逐一执行,使得其数据和 Master节点的保持一致,最后I/O线程和SQL线程将进入睡眠状态,
等待下一次被唤醒。
3.2 读写分离你们使用什么方式?
amoeba 代理 mycat 代码 sql_proxy
通过amoeba代理服务器,实现只在主服务器上写,只在从服务器上读;
主数据库处理事务性查询,从数据库处理select 查询;
数据库复制被用来把事务查询导致的变更同步的集群中的从数据库
3.3 如何查看主从同步状态是否成功
在从服务器上内输入 show slave status\G 查看主从信息查看里面有IO线程的状态信息,还有master服务器的IP地址、端口事务开始号。
当 Slave_IO_Running和Slave_SQL_Running都是YES时 ,表示主从同步状态成功
3.4 如果I/O不是yes呢,你如何排查?
首先排查网络问题,使用ping 命令查看从服务器是否能与主服务器通信
再查看防火墙和核心防护是否关闭(增强功能)
接着查看从服务slave是否开启
两个从服务器的server-id 是否相同导致只能连接一台
master_log_file master_log_pos的值跟master值是否一致
3.5 show slave status能看到哪些信息(重要)
IO线程的状态信息
master服务器的IP地址、端口、事务开始的位置
最近一次的错误信息和错误位置
最近一次的I/O报错信息和ID
最近一次的SQL报错信息和id
3.6 主从复制慢(延迟)会有哪些可能?怎么解决?
主服务器的负载过大,被多个睡眠或 僵尸线程占用 导致系统负载过大,从库硬件比主库差,导致复制延迟
主从复制单线程,如果主库写作并发太大,来不及传送到从库,就会到导致延迟
慢sql语句过多
网络延迟
3.7 mysql主从复制的版本要求
若主从版本不一致,从的版本一定要高于主,保证可以向下兼容
因为若主的版本更新,低版本的从无法兼容的。