mysql主从复制和读写分离

什么叫主从复制？

主从复制架构图和数据流向

主MySQL上的数据、新增、修改库、表、表里的数据。都会同步到从MySQL上

面试题：MySQL的主从复制模式

1、异步复制：MySQL的默认复制就是异步复制。工作中也一般使用异步复制。只要执行完之后，客户端提交事务，主MySQL会立即把结果返回给从服务器。主MySQL并不关心从MySQL是否已经接受并且处理。主一旦崩溃，主MySQL已经提交的事务可能没有传到从MySQL。

这个时候如果强行把从服务器提升为主服务器，可能导致新的主MySQL数据不完整。（情况很少见。工作中都是异步复制）

异步：类似于udp。只管发送，不管有没有收到

2、全同步复制：主库执行完成一个事务，所有的从库都执行了该事务之后才会返回客户端。因为需要等待所有从库全部执行完成。性能必然下降（对数据一致性，和数据完整要求很高的场景）

3、半同步复制：介于异步复制和全同步复制之间。主库执行完一个客户端提交的事务之后，至少等待一个从库接受并处理完成之后才会给客户端。半同步在一定程度上提高了数据的安全性。和全同步复制一样，也会有一定的延迟。延迟的时间一般是一个tcp/ip的往返时间（从发送到接收的时间，单位是毫秒）。半同步复制最好是在低延迟的网络中使用。

时间<1ms：round-trip time RTT

如何实现主从复制？

架构主从复制和读写分离

MySQL：主 20.0.0.50

MySQL2：从 20.0.0.60

MySQL3：从 20.0.0.70

test1：读写分离的服务器 20.0.0.20

test3：客户端 20.0.0.30

yum -y install ntp #时间同步工具


关闭五台主机的防火墙
回到三台MySQL
主从服务器的时间也要同步：
yum -y install ntp
#保证主从服务器时间必须一致设置MySQL2和MySQL3与主MySQL同步MySQL1：vim /etc/ntp.conf
#在尾行添加
server 127.127.0.0
fudge 127.127.0.0 stratum 8server 127.127.0.0
fudge 127.127.0.0 stratum 8
#数字越小，时间精确度越高，设置fudge 8 时间层级是8 最高到15.从本地获取时间源同步，不走网络。
systemctl restart ntpd开启两台从MySQL2和MySQL3的ntp服务
/usr/sbin/ntpdate 20.0.0.50
#两台从服务器的时间源分别指向主MySQL 20.0.0.50
crontab -u root -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.50
#生产环境中一般创建一个定时任务创建完成后查看一下三台主机的时间
date回到主：
vim /etc/my.conf
log-bin=master-bin
#主服务器开启二进制日志
binlog_format=MIXED
#处理方式开启混合模式，如果并发量变高会自动切换成row
log-slave-updates=ture
#允许从服务器复制数据时，可以从主的二进制日志写到自己的二进制日志当中
systemctl restart mysqldmysql -u root -p123456
#进入主数据库grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456';
#给从库授权可以访问主库。创建一个用户名称叫myslave来自20.0.0.%这个网段，密码是123456.给从库授权可以访问主库
flush privileges;
#刷新一下权限
show master status;
#查看主MySQL的位置点到从库MySQL2：
hostnamectl set-hostname
#改名从库，可做可不做vim /etc/my.conf
server-id = 2
#id千万不能相同
relay-log=relay-log-bin
#修改日志的名称
relay-log-index=slave-relay-bin.index
#告诉你中继日志的索引名称叫这个
relay_log_recovery=1
#默认是0，1表示开启中继日志的恢复。如果从服务器出现异常或者崩溃时，从服务器会从主服务器的二进制日志正确读取和应用中继日志。起同步作用，会自动从主服务器获取，完成同步
systemctl restart mysqld
#重启从MySQL服务到从库MySQL3：
vim /etc/my.conf
server-id = 3
#id千万不能相同
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
relay_log_recovery=1systemctl restart mysqld
#重启从MySQL服务现在两台从MySQL都修改完成
mysql -u root -p123456
mysql -u root -p123456
#两台从MySQL都进入MySQL
两个从服务器都要:
CHANGE master to CHANGE master to master_host='20.0.0.50',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=自己的号码;start slave;show slave status\G
#纵向查看进入工具验证一下
连接三台服务器
create database kgc;
#创建库
查看一下是否同步实现主从复制。
create table test (name varchar(10));
#创建一个表测试一下
在表内写一点数据测试一下
主从服务完成！在从上创建一个库
create database kgc1;
#主从复制是单向的只能从主复制到从服务器

Slave_IO_Running:yes #负责和主库的io通信。获取主库的io通信

Slave_SQL_Running:yes #负责自己的sql进程。写入从库

Slave_IO_Running:no #如果这里出问题，可能是配置文件错了、密码写错了、防火墙没有关

主从复制是单向的只能从主复制到从服务器

主从复制总结

主从复制的工作过程：

1、主节点的数据记录发生变化都会记录在二进制日志

2、 slave节点会在一定时间内对主库的二进制文件进行探测。探测是否发生变化。如果有变化从库会开启一个IO的线程。请求主库的二进制事件。

3、主库会给每一个I/O的线程启动一个dump线程，用于发送二进制事件给从库。从库通过I/O线程获取跟新，slave_sql负责将更新写入到从库本地，实现主从一致。

主从复制的问题：

1、只能在主库上发生变化，然后同步到从。从库的更新不会同步到主

2、复制的过程是串行化过程，在从库上复制是串行的。主库的并行更新不能在从库上并行操作。

3、主从复制的设计目的：就是为了在主库上写，在从库上查。读写分离，实现高可用。

如何实现读写分离？

要实现读写分离必须要先实现主从复制

读写分离：所有的写入操作都在主库，从库只负责读。（select）如果有更新，是从主库复制到从库。

为什么要有读写分离？

1、数据库在写入数据时，比较耗时（MySQL写一万条数据需要3分）

2、数据库在读的时候，速度很快（读一万条数据需要4.96秒）

3、读写分离之后，数据库的写入和读取时分开的。哪怕写入的数据量比较大，但是不影响查询的效率。

什么场景下需要读写分离？

数据库不是一定需要读写分离的。只有在某些程序在使用数据库过程中，更新少，但是查询角度，这种情况可以考虑读写分离。

如果读和查的需求差不多，也可以考虑读写分离。

生产库一般都会做读写分离。

测试库一般不管。

在工作中，数据库的读写不会在同一个库中完成。既不安全，也不能满足高可用，也不能实现高并发。工作中都会做读写分离。

MySQL读写分离的原理：

1、根据脚本实现。在代码中实现路由分类。select insert进行路由分类。这种方式时最多的。性能好，在代码中就可以实现，不需要额外的硬件设备。缺点：开发实现的，跟我们无关。如果大型的复杂的应用，涉及改动的代码非常多。

2、基于中间代理层实现：mysql-proxy自带的开源项目，基于自带的lua脚本。这些lua脚本不是现成的，要自己写，不熟悉他的内置变量写不出来。

atlas 360内部自己使用的代理工具：不对外公开。每天的读写请求承载量可以到几十亿条，还支持事务，还支持存储过程。

3、 Amoeba 是由java开发的一个开源软件。不支持事务也不支持存储过程。但是Amoeba还是用的最多的，功能比较强大的软件。

Amoeba来实现读写分离

架构主从复制和读写分离

MySQL：主 20.0.0.50

MySQL2：从 20.0.0.60

MySQL3：从 20.0.0.70

test1：读写分离的服务器 Amoeba 20.0.0.20

test3：客户端 20.0.0.30

jdk1.5开发的，官方推荐使用1.5或者1.6

读写分离架构图和数据流向

配置读写分离：
java -version
#查看java版本cd /opt
#拖入两个文件
cp jdk /usr/local
cd usr/local
chmod +x jdk
./
ctrl+c
yesmv jdk/ jdk1.6vim /etc/profilesource /etc/profilejava -version
#java环境配置完成安装Amoeba
cd /opt
mkdir /usr/local/amoeba
tar -xf amoeba tar.gz -C /usr/local/amoeba
chmod -R 755 /usr/local/amoeba
./usr/local/amoeba/bin/amoeba
#出现start|stop表示安装成功回到主MySQL和两个从MySQL都要配置
grant all on *.* to 'amoeba'@'20.0.0.%' identified by '123456';
flush privileges;回到Amoeba客户端
cd /usr/local/amoeba/conf/
ls
amoeba.xml:配置用户信息
dbServers.xml:配置数据库信息
cp amoeba.xml amoeba.xml.bak.2023.11.13vim amoeba.xml
set nu
#11行查看一下端口号8066
"user">amoeba
#到30行修改
"passord">123456
#到32行插入密码
name="defaultPool">master
#到115行
"writePool">master
"readPool">slaves
#到117行去掉注释 删除<!--和-->cp dbServers.xml dbServers.xml.bak.2023.11.13
#备份一下vim amoeba.xml									#修改amoeba配置文件
--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">amoeba</property>--28-30--去掉注释
<property name="password">123456</property>--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">--48--修改，设置主服务器的地址
<property name="ipAddress">192.168.233.21</property>--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">--55--修改，设置从服务器1的地址
<property name="ipAddress">192.168.233.22</property>--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.233.23</property>--65行--修改
<dbServer name="slaves" virtual="true">--71行--修改
<property name="poolNames">slave1,slave2</property>/usr/local/amoeba/bin/amoeba start &
#启动amoeba并且后台运行amoebanetstat -antp | grep java
#查看端口是否启动看看端口到客户端查看测试
systemctl restart mariadbnatstat -antp | grep mariadb回到主从服务器打开查询日志记录
vim /etc/my.conf
general_log=ON
general_log_file=
#开启日志记录systemctl restart mysqld
#三台MySQL都重启mysql服务tail -f /usr/log/mysql/data/mysql
#打开三台MySQL的日志回到客户端连接amoeba服务器来测试mysql -u amoeba -p123456 -h20.0.0.20 -P8066
#登录amoeba指定端口20.0.0.20 -P指定端口8066
use kgc;
show tables;
insert into test values('test2')
#插入一条新的数据
回到三台MySQL查看报文是否有写入数据回到客户端
select * from test;
回到从MySQL服务器查看日志是否有查询记录回到客户端
select * from test;
回到从MySQL服务器查看日志是否有查询记录
#两次查询还可以实现轮询读写分离实验完成！

Slave_IO_Running:no如何处理？

1、网络问题。ping一下主机是否能通

2、配置文件错误 my.conf配置文件写错了

3、 CHANGE master to CHANGE master to master_host='20.0.0.50',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=自己的号码; 指定主和从的时候，可能是文件名错误，也可能是位置偏移量不对。

4、防火墙和安全机制问题（面试中别说）

MySQL的主从复制模式问题

这个时候如果强行把从服务器提升为主服务器，可能导致新的主MySQL数据不完整。（情况很少见。工作中都是异步复制）

异步：类似于udp。只管发送，不管有没有收到

主从复制延迟问题

1、网络延迟

2、主从硬件设备（cpu主频。内存的IO、硬件的IO）

3、同步复制而不是异步复制

解决方案：

1、硬件方面：主库一般不需要动的太多，从库的硬件配置要更好。提升随机写的性能。例如：硬盘可以考虑换成固态的，升级cpu的核数，扩展一下内存。尽量使用物理机（不要用云服务器）。

2、从网络层面解决问题：主从服务器都配置在一个局域网内，尽量避免跨网段或者跨机房。

3、架构方面：读写分离，把写入控制设置在主库，从库负责读。减轻从库的负载，降低从库的压力。

4、 MySQL的配置方面：从配置文件的角度实现性能最大化。

如果追求安全性的配置：
innodb_flush_log_at_trx_commit=1
#每次事务提交时都会刷新事务日志以却表持久性。这是最高级别的数据安全性，但是会影响性能，默认就是1.（只要安全就这样配）
#0就是事务提交时不会立刻刷新，而是每秒刷新1次。可以提高性能，但是发生故障会导致数据丢失。
#2就是事务提交时，事务日志不会写入硬盘而是保存在系统缓存，不会进行刷新。有一定的安全性和性能。内存要求比较高。
#生产中默认就是1sync_binlog=1
#1是默认值，也就是每次提交事务之后。直接把二进制日志刷新到磁盘。以确保日志的持久性。会占用比较高的性能，但是安全性高。（只要安全就这样配）
#0会把二进制日志写入到缓存，也不会刷新日志。故障发生也会丢失数据，同时对内存的要求也提高了
#3表示每3个事务执行一次刷新到磁盘。可以提高性能，但是一旦发生崩溃，数据会大量丢失。追求性能化：
sync_binlog=0 （不建议）
innodb_flush_log_at_trx_commit=2 （不建议）
logs-slave-updates=0 （强烈不建议）
#从库的更新不会写入二进制日志。（强烈不建议）
innodb_buffer_pool_size 300M 500G
#innodb存储引擎缓冲池的大小，设置的数据越高，可以提高innodb的性能。更多的数据和索引都可以缓存在内存中。可以减少磁盘的访问次数。对系统内存要求比较高。建议舍弃掉一部分性能，追求安全方面。数据无价。