MySQL---主从复制和读写分离

MySQL—主从复制和读写分离

主从复制

mysql主从复制的作用

1. 实现读写分离
2. 跨主机热备份数据
3. 作为数据库高可用的基础

mysql主从复制的分类

1. 基于SQL语句的复制（STATEMENT）：优点：执行效率高，占用空间小，性能消耗低；缺点：无法保证在高并发高负载时候的主从复制精确度
2. 基于行的复制（ROW）：优点：精确度高；缺点：占用空间大，性能消耗高
3. 混合类型的复制（MIXED）：默认采用基于SQL语句的复制，一旦发现基于语句无法保证精确复制时，就会采用基于行的复制

mysql主从复制原理

（关键词：两个日志 bin log、relay log ，三个线程 IO线程 SQL线程 DUMP线程）

1. 主库master如果发生数据更新，会将写入操作记录到二进制日志（bin log）里
2. 从库slave探测到主库的二进制日志发生了更新，就会开启IO线程向主库请求二进制日志事件
3. 主库master会为每个从库的IO线程请求开启DUMP线程，并发送二进制日志事件给从库
4. 从库slave接收到二进制日志事件后会保存到自己的中继日志（relay log）里
5. 从库slave还会开启SQL线程读取中继日志里的事件，并在本地进行重放（将事件解析程SQL语句逐一执行），从而实现主库和从库的数据一致

mysql主从复制的配置步骤

1. 主从服务器先做时间同步
2. 修改主从数据库的配置文件，配置 二进制日志、中继日志、server-id（每个节点都不同）、gtid 等相关参数
3. 在主库创建主从复制的用户，并授予主从复制权限
4. 在从库使用 change master to 对接主库，并 start slave 开启同步
5. 在从库使用 show slave status 查看 IO线程 和 SQL线程 的状态是否都是 YES

mysql主从复制的同步模式

• 异步复制 主库在执行完客户端提交的事务后就会立即响应给客户端
• 半同步复制 主库在执行完客户端提交的事务后，只要等待一个从库返回响应给主库，才会响应给客户端
• 全同步复制 主库在执行完客户端提交的事务后，要等待所有从库都返回响应给主库，才会响应给客户端

在什么情况下半同步复制会将为异步复制？

当主库在半同步复制超时时间内没有收到从库的响应，就会降为异步复制，半同步复制超时时间参数为rpl_semi_sync_master_timeout（默认值为10s）
当主库发送完一个事务事件后，主库在半同步复制超时时间内收到了从库的响应，就会恢复为半同步复制

mysql主从复制不一致问题如何解决？

1. 在主库使用mysqldump对数据不一致的库或表进行完全备份，并show master status检查当前的二进制日志和偏移量
   mysqldump -uroot -p密码 库名 表名 > mysql_bak.sql

   show master status;

2. 使用scp把备份文件远程复制到从库，在从库关闭同步，导入数据备份
   scp mysql_bak.sql 从库IP:目录/

   stop slave;

   mysql -uroot -p密码 库名 < mysql_bak.sql

3. 在从库使用change master to 重新进行主从复制对接，再开启同步
   change master to master_host=‘主库IP’,master_port=3306,master_user=‘用户’,master_password=‘密码’,master_log_file=‘binlog文件名’,master_log_pos=偏移量;
   change master to master_host=‘主库IP’,master_port=3306,master_user=‘用户’,master_password=‘密码’,master_auto_position=1; #使用GTID主从复制模式

4. start slave;

主要步骤： 导出主库数据，恢复到从库，重新开启主从同步。

mysql主从复制延迟问题

原因

主库可以并发多线程执行写入操作，而从库的SQL线程默认是单线程串行化复制，从库的复制效率可能会跟不上主库的写入速度

如何判断发生了主从复制延迟？

可通过在从库执行show slave status查看输出的Seconds_Behind_Master参数的值来判断是否发生了主从复制延迟。如果值为正值则表示已经出现主从复制延迟，数值越大表示从库落后主库越多

导致主从复制延迟有哪些因素？

1. 主库写入操作并发量太大，事务数太多
2. 网络延迟
3. 从库硬件比主库差太多
4. 使用了全同步复制
5. 事务太大，慢SQL语句太多

延迟问题的解决

网络优化：将从库分布在宇主库相同的局域网或网络延迟较小的环境中
硬件优化：从库配置更好的硬件，使用更高规格的CPU和内存，使用固态硬盘和RAID提升磁盘的读写性能
配置优化：innodb_buffer_pool_size              #从库加大Innodb引擎缓存池的大小，让更多的数据读写在内存中完成，减少磁盘IO压力slave_parallel_workers               #从库使用多线程并行复制log-slave-updates=0                  #从库从主库复制的数据时不写入到二进制日志，减少磁盘IO压力innodb_flush_log_at_trx_commit=2     #从库不使用双1设置，减少磁盘IO压力sync_binlog=0
架构优化：主从复制的同步模式采用 半同步复制 或 异步复制，采用读写分离，避免读写操作相互阻塞
操作优化：将大型事务拆分成多个较小的事务优化一些SQL语句操作，比如可将多个操作合并到一个SQL语句执行创建索引，避免全表扫描

环境准备

Master 服务器：20.0.0.51	mysql-8.0.33
Slave1 服务器：20.0.0.52	mysql-8.0.33
Slave2 服务器：20.0.0.53	mysql-8.0.33

具体流程

一、时间同步
###Master 服务器：20.0.0.51	
yum install -y ntp vim /etc/ntp.conf--末尾添加--
server 20.0.0.0							#设置本地是时钟源，注意修改网段
fudge 20.0.0.0 stratum 8				#设置时间层级为8（限制在15内）service ntpd start###Slave1 服务器：20.0.0.52
###Slave2 服务器：20.0.0.53
yum install -y ntp ntpdate service ntpd start
/usr/sbin/ntpdate 20.0.0.51				#进行时间同步crontab -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.51
----------------------------------------------------------------------------
二、mysql配置
###Master 服务器：20.0.0.51		mysql-8.0.33
vim /etc/my.cnf#修改
server-id=1# 添加
log-bin=mysql-bin						#添加，主服务器开启二进制日志
binlog_format=mixed#选配项	
expire_logs_days=7						#设置二进制日志文件过期时间，默认值为0，表示logs不过期
max_binlog_size=500M					#设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB
skip_slave_start=1						#阻止从库崩溃后自动启动复制，崩溃后再自动复制可能会导致数据不一致的#"双1设置"，数据写入最安全
innodb_flush_log_at_trx_commit=1		#redo log（事务日志）的刷盘策略，每次事务提交时MySQL都会把事务日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认
sync_binlog=1							#在进行每1次事务提交（写入二进制日志）以后，Mysql将执行一次fsync的磁盘同步指令，将缓冲区数据刷新到磁盘=================================================================
#"双1设置"适合数据安全性要求非常高，而且磁盘IO写能力足够支持的业务，比如订单、交易、充值、支付消费系统。"双1模式"下，当磁盘IO无法满足业务需求时，比如11.11活动的压力。推荐一下性能较快的设置，并使用带蓄电池后备电源，防止系统断电异常。
innodb_flush_log_at_trx_commit=2		#每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作
sync_binlog=500							#在进行500次事务提交以后，Mysql将执行一次fsync的磁盘同步指令，将缓冲区数据刷新到磁盘
=================================================================systemctl restart mysqldmysql -u root -p123456
# 创建用户
CREATE USER 'myslave'@'20.0.0.%' IDENTIFIED BY '123456';
ALTER USER 'myslave'@'20.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';# 给从服务器授权
GRANT ALL ON *.* TO 'myslave'@'20.0.0.%';			
FLUSH PRIVILEGES;# 查询 File 列显示日志名，Position 列显示偏移量
show master status;#Slave1 服务器：20.0.0.52
#Slave2 服务器：20.0.0.53
vim /etc/my.cnf# 修改
server-id = 2	/    3						#修改，注意id与Master的不同，两个Slave的id也要不同（此处应该2一个3）# 添加
relay-log=relay-bin						    #开启中继日志，从主服务器上同步日志文件记录到本地#选配项
innodb_buffer_pool_size=2048M		#用于缓存数据和索引的内存大小，让更多数据读写在内存中完成，减少磁盘操作，可设置为服务器总可用内存的 70-80%
sync_binlog=0						#MySQL不做任何强制性的磁盘刷新指令，而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit=2	#每次事务log buffer会写入log file，但一秒一次刷新到磁盘
log-slave-updates=0					#slave 从 master 复制的数据会写入二进制日志文件里，从库做为其他从库的主库时设置为 1
relay_log_recovery=1				#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log， 并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。systemctl restart mysqldmysql -u root -p123456#配置同步，注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致
CHANGE master to master_host='20.0.0.51',master_port=3306,master_user='myslave',master_password='123456',master_log_file='binlog.000004',master_log_pos=157;						
start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 Slave 状态//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes				#负责与主机的io通信
Slave_SQL_Running: Yes				#负责自己的slave mysql进程#一般 Slave_IO_Running: No 的可能性：
1、网络不通
2、my.cnf配置有问题
3、密码、file文件名、pos偏移量不对
4、防火墙没有关闭------------------------------------------------------------------------------
三、验证主从复制效果
Master 服务器：20.0.0.51
create database db_test;Slave1 服务器：20.0.0.52
Slave2 服务器：	20.0.0.53
查看 show databases;

读写分离

两种

1. 基于程序代码内部实现：优点：性能好，不需要额外的硬件设备支出；缺点：由开发人员负责实现和维护，在一些现有的大型应用中现实读写分离对代码改动会较为困难

2. 基于中间代理层实现：优点：实现起来较为简单，不需要重构代码；缺点：需要额外的硬件设置支出，由运维人员负责维护
   典型代表：mysql-proxy、mycat、ameoba

读写分离的原理

在主库master上负责处理事务性写入操作，在从库slave上负责处理查询读操作，再通过主从复制将主库上的数据同步给从库。

读写分离的作用

通过读写分离可以分担数据库单节点的负载压力，还可避免读写操作相互阻塞，从而提高数据库的读写性

环境准备

Master 服务器：20.0.0.51	mysql-8.0.33
Slave1 服务器：20.0.0.52	mysql-8.0.33
Slave2 服务器：20.0.0.53	mysql-8.0.33Amoeba 服务器：20.0.0.54	jdk1.6、Amoeba
客户端  服务器：20.0.0.55	  mysql(8.0.33有问题)      /   或者直接使用navicat 连接20.0.0.54 amoeba 端口8066

具体流程

一、Amoeba 服务器：20.0.0.54配置
Amoeba 服务器：20.0.0.54# 安装 Java 环境
查看java版本并删除
java -version
yum remove -y java*将jdk-6u14-linux-x64.bin 移入/opt，并安装
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，按entermv jdk1.6.0_14/ /usr/local/jdk1.6vim /etc/profile# 添加
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin:$PATHsource /etc/profile
java -version# 安装 Amoeba
将amoeba-mysql-binary-2.2.0.tar.gz 移入 /opt
mkdir /usr/local/amoeba
tar zxvf 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 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
Master 服务器：20.0.0.51
CREATE USER 'amoeba'@'20.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
grant all on *.* to 'amoeba'@'20.0.0.%';
flush privileges;# 再回到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库时，会报错
<!-- <property name="schema">test</property> -->--26--修改
<property name="user">manager</property>--28-30--去掉注释
<property name="password">123456</property>--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">
--48--修改，设置主服务器的地址
<property name="ipAddress">20.0.0.51</property>--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">
--55--修改，设置从服务器1的地址
<property name="ipAddress">20.0.0.52</property>--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">20.0.0.53</property>--65行--修改
<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客户端  服务器：20.0.0.55
yum install -y mariadb
mysql -u amoeba -p123456 -h 20.0.0.54 -P8066		
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器在主服务器上：
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));在两台从服务器上：
stop slave;											#关闭同步
use db_test;
//在slave1上：
insert into test values('1','zhangsan','this_is_slave1');//在slave2上：
insert into test values('2','lisi','this_is_slave2');//在主服务器上：
insert into test values('3','wangwu','this_is_master');//在客户端服务器上：
use db_test;
select * from test;		//客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据insert into test values('4','qianqi','this_is_client');		//只有主服务器上有此数据//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;