MySQL主从复制与读写分离
MySQL主从复制与读写分离
为什么需要主从架构
MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
主要作用：
1）数据同步备份：主库master发生故障后，可以马上切换到从库slave，降低服务风险。
2）读写分离：可以把写操作放在master，读取操作放在slave，减轻单一数据库的操作压力。
3）高可用：随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。
主主、主从、主备的区别：
主主：两台都是主数据库，同时对外提供读写操作，客户端访问任意一台，数据存在双向同步。
主从：一台是主数据库，同时对外提供读写操作，一台是从数据库，对外提供读的操作，数据从主库同步到从库。
主备：一台是主数据库，同时对外提供读写操作，一台是备库，只作为备份作用，不对外提供读写，主机挂了它就取而代之，数据从主库同步到备库。
主从复制实现原理
原理解析：
前提条件需要停止对master数据库的操作，把master中的数据库全部导入到slave，使两边数据库完全一致。建议MySql版本一致且后台以服务运行，主从所有配置项都配置在[mysqld]节点下，且都是小写字母。主库的更新事件（update、insert、delete）被写到binlog，从库发起连接，连接到主库，主库创建一个binlog dump thread线程，把binlog的内容发送到从库。从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log。还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db。
主从复制原理，简言之，分三步曲进行：
1）主数据库有个bin log二进制文件，纪录了所有增删改SQL语句。（binlog线程）
2）从数据库把主数据库的bin log文件的SQL语句复制到自己的中继日志relay log（io线程）
3）从数据库的relay log重做日志文件，再执行一次这些sql语句。（Sql执行线程）
数据库如何保证主从数据一致
1）长链接
主库和从库在同步数据的过程中断怎么办呢，数据不就会丢失了嘛。因此主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。
2）binlog格式
binlog日志有三种格式，分别是statement，row和mixed。如果是statement格式，binlog记录的是SQL的原文，如果主库和从库选的索引不一致，可能会导致主从数据不一致。我们来分析一下。假设主库执行删除这个SQL（其中a和create_time都有索引）如下：
delete from t where a > '666' and create_time<'2022-03-01' limit 1;
我们知道，数据选择了a索引和选择create_time索引，最后limit 1出来的数据一般是不一样的。所以就会存在这种情况：在binlog = statement格式时，主库在执行这条SQL时，使用的是索引a，而从库在执行这条SQL时，使用了索引create_time。最后主从数据不一致了。
如何解决这个问题呢？
可以把binlog格式修改为row。row格式的binlog日志，记录的不是SQL原文，而是两个event:Table_map和Delete_rows。Table_map event说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。row格式的binlog记录的就是要删除的主键ID信息，因此不会出现主从不一致的问题。但是如果SQL删除10万行数据，使用row格式就会很占空间的，10万条数据都在binlog里面，写binlog的时候也很耗IO。但是statement格式的binlog可能会导致数据不一致，因此设计MySQL的大叔想了一个折中的方案，mixed格式的binlog。所谓的mixed格式其实就是row和statement格式混合使用，当MySQL判断可能数据不一致时，就用row格式，否则使用就用statement格式。
主从延迟的原因与解决方案
与主从数据同步相关的时间点有三个：
1）主库执行完一个事务，写入binlog，我们把这个时刻记为T1。
2）主库同步数据给从库，从库接收完这个binlog的时刻，记录为T2。
3）从库执行完这个事务，这个时刻记录为T3。
所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即T3-T1。
哪些情况会导致主从延迟呢？
1）如果从库所在的机器比主库的机器性能差，会导致主从延迟，这种情况比较好解决，只需选择主从库一样规格的机器就好。
2）如果从库的压力大，也会导致主从延迟。比如主库直接影响业务的，大家可能使用会比较克制，因此一般查询都打到从库了，结果导致从库查询消耗大量CPU，影响同步速度，最后导致主从延迟。这种情况的话，可以配置一主多从的架构，即多接几个从库分摊读的压力。另外，还可以把binlog接入到Hadoop这类系统，让它们提供查询的能力。
3）大事务也会导致主从延迟。如果一个事务执行就要10分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟10分钟。日常开发中，我们为什么特别强调，不要一次性delete太多SQL，需要分批进行，其实也是为了避免大事务。另外，大表的DDL语句，也会导致大事务，大家日常开发关注一下哈。
4）网络延迟也会导致主从延迟，这种情况你只能优化你的网络啦，比如带宽20M升级到100M类似意思等。
5）如果从数据库过多也会导致主从延迟，因此要避免复制的从节点数量过多。从库数据一般以3-5个为宜。
6）低版本的MySQL只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟。可以换用更高版本的Mysql，可以支持多线程复制。
数据库高可用方案
1）双机主备
架构描述：两台机器A和B，A为主库，负责读写，B为备库，只备份数据。如果A库发生故障，B库成为主库负责读写。修复故障后，A成为备库，主库B同步数据到备库A。
优点：一个机器故障了可以自动切换，操作比较简单。
缺点：只有一个库在工作，读写压力大，未能实现读写分离，并发也有一定限制。
2）一主一从
架构描述: 两台机器A和B，A为主库，负责读写，B为从库，负责读数据。如果A库发生故障，B库成为主库负责读写。修复故障后，A成为从库，主库B同步数据到从库A。
优点：从库支持读，分担了主库的压力，提升了并发度。一个机器故障了可以自动切换，操作比较简单。
缺点：一台从库，并发支持还是不够，并且一共两台机器，还是存在同时故障的机率，不够高可用。
3）一主多从
架构描述: 一台主库多台从库，A为主库，负责读写，B、C、D为从库，负责读数据。如果A库发生故障，B库成为主库负责读写，C、D负责读。修复故障后，A也成为从库，主库B同步数据到从库A。
优点：多个从库支持读，分担了主库的压力，明显提升了读的并发度。
缺点：只有一台主机写，因此写的并发度不高。
4）MariaDB同步多主机集群
架构描述：由代理层实现负载均衡，多个数据库可以同时进行读写操作；各个数据库之间可以通过Galera Replication方法进行数据同步，每个库理论上数据是完全一致的。
优点：读写的并发度都明显提升，可以任意节点读写，可以自动剔除故障节点，具有较高的可靠性。
缺点：数据量不支持特别大。要避免大事务卡死，如果集群节点一个变慢，其他节点也会跟着变慢。
5）数据库中间件
架构描述：mycat分片存储，每个分片配置一主多从的集群。
优点：解决高并发高数据量的高可用方案。
缺点：维护成本比较大。
主从复制实现方式
MySQL配置方式实现主从
1）修改master配置文件
[mysqld]
# [必须]启用二进制日志，指明路径。比如：自己本地的路径/log/mysqlbin
Log_bin=mysql-bin
# [必须]服务器唯一ID，默认是1，一般取IP最后一段
server-id=10
# [可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
# 设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
# 控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
# [可选]设置不要复制的数据库
binlog-ignore-db=test
# [可选]设置需要复制的数据库，默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
# [可选]设置binlog格式
# STATEMENT模式：基于SQL语句的复制(statement-based replication, SBR)
# ROW模式：基于行的复制(row-based replication, RBR)
# MIXED模式：混合模式复制(mixed-based replication, MBR)
binlog_format=STATEMENT
2）修改slave配置文件
[mysqld]
# [可选]启用二进制日志
Log_bin=mysql-bin
# [必须]从服务器唯一ID
Server_id=11
3）master创建授权用户
-- 登陆主服务器MySql命令行，创建一个用于从服务器复制的用户。
mysql -u root -p 密码
-- 在主机MySQL里执行授权主从复制的命令
-- "*.*"表示对所有库的所有操作，“%”表示所有客户端都可能连，也可用具体客户端IP代替，如192.168.33.11，加强安全。
# 5.5,5.7
grant replication slave on *.* to 'root'@'%' identified by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123';
-- 如果使用的是MySQL8，需要如下的方式建立账户，并授权slave
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
-- 或者通过下面语句创建
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 此语句必须执行，刷新生效
flush privileges;
4）记录master状态信息
-- 查看二进制日志文件名，及最新位置。让slave知道用哪个用户信息访问master，知道读取哪个日志文件，及从哪儿开始读。
show master status;
-- 其中file、position字段需要记录下值，mysql-bin.000001是用于主从复制的文件名，437是日志文件内的最新位置。
5）将slave指向master
-- 登陆从服务器mysql命令行，使用之前创建的用户和master的日志文件及其位置。slave中使用被授权用户信息及日志文件信息，进行指向master。这时已经建立了和master的联系，明确了从哪儿读取日志文件。
CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;
-- 注意不要断开，“437”无单引号，如
change master to master_host='192.168.33.10',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=437;
-- 启动slave，执行启动slave的命令，开始主从复制
start slave;
-- 查看slave状态
-- 结果中有两个重要数据项：
-- 1) Slave_IO_Running: Yes IO线程状态，必须YES
-- 2) Slave_SQL_Running: Yes SQL线程状态，必须YES
-- 常见的问题是SQL线程没有正常工作Slave_SQL_Running: No，通常是两边的数据库不是完全对应的，需要确保master上的库及到目前为止的最新记录都复制到slave上了。
show slave status\G;
-- 重启slave，删除slave数据库的relaylog日志文件，并重新启用新的relaylog文件
reset slave;
-- 注意配置完成后需要重启mysqlserver才能生效。
systemctl restart mysqld
-- 注意主从机都关闭防火墙或者指定端口开放
# CentOS 6
service iptables stop
# CentOS 7
systemctl stop firewalld.service
Mysql Utilities工具方式实现主从
Mysql Utilities下载地址：http://dev.mysql.com/downloads/utilities/1.5.html
Mysql Utilities文档：http://dev.mysql.com/doc/mysql-utilities/1.6/en/utils-overview.html
Mysql Utilities是一个MySql的工具箱（基于 python），里面有不少好用的小工具，其中的mysqlreplicate命令，可以让我们通过一个命令就能快速配置好主从复制环境。下载解压Mysql Utilities，进入解压后的目录，执行编译安装命令python ./setup.py build和python ./setup.py install，执行完成后，就可以使用其中的工具命令了。
mysqlreplicate \
--master=root:111111@192.168.31.168:3306 \
--slave=root:111111@192.168.31.101:3306 \
--rpl-user=replutil:111111
-- master指定主库的连接信息，slave指定从库的连接信息，rpl-user指定用于复制的用户信息，这个用户需要提前在master上创建好，例如：
grant ALL PRIVILEGES on *.* to replutil@"192.168.31.101" Identified by "111111";
-- 注意，创建用户时，其中的从库IP要明确，不要用'%'。
-- 可以看到，总共只需要两步，几秒钟就完成了主从配置：
-- （1）master上创建用于复制的用户
-- （2）执行mysqlreplicate命令
Docker方式实现主从
参考资料：https://blog.csdn.net/qq_41296669/article/details/124460465
读写分离实现方式
MaxScale中间件实现读写分离
MaxScale中间件简介
中间件简介：
配置好了MySql的主从复制结构后，我们希望实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡。读写分离和负载均衡是MySql集群的基础需求，MaxScale就可以帮着我们方便的实现这些功能。MaxScale是MySql的兄弟公司MariaDB开发的，现在已经发展得非常成熟。MaxScale是插件式结构，允许用户开发适合自己的插件。MaxScale目前提供的插件功能分为5类：
1）认证插件：提供了登录认证功能，MaxScale会读取并缓存数据库中user表中的信息，当有连接进来时，先从缓存信息中进行验证，如果没有此用户，会从后端数据库中更新信息，再次进行验证。
2）协议插件：包括客户端连接协议，和连接数据库的协议。
3）路由插件：决定如何把客户端的请求转发给后端数据库服务器，读写分离和负载均衡的功能就是由这个模块实现的。
4）监控插件：对各个数据库服务器进行监控，例如发现某个数据库服务器响应很慢，那么就不向其转发请求了。
5）日志和过滤插件：提供简单的数据库防火墙功能，可以对SQL进行过滤和容错。
MaxScale配置读写分离
MaxScale下载地址：https://downloads.mariadb.com/files/MaxScale
在开始配置之前，需要在master中为MaxScale创建两个用户，用于监控模块和路由模块：
-- 1主2从配置案例
-- 创建监控用户
create user 'maxmon'@'%' identified by '123456';
-- replication slave监控主从的状态是否正常
-- replication client监控主从的服务是否运行
grant replication slave,replication client on *.* to 'maxmon'@'%';
-- 创建路由用户
create user 'maxrou'@'%' identified by '123456';
grant select on mysql.* to 'maxrou'@'%';
-- 刷新生效
flush privileges;
用户创建完成后，开始配置vi /etc/maxscale.cnf，找到 [server1] 部分，修改其中的address和port，指向master的IP和端口，复制2次 [server1] 的整块儿内容，改为 [server2] 与 [server3] ，同样修改其中的address和port，分别指向slave1和slave2（日志文件在/var/log/maxscale）：
找到 [MySQL Monitor] 部分，修改servers为server1、server2、server3，修改user和passwd为之前创建的监控用户的信息（maxmon，123456）：
找到 [Read-Write Service] 部分，修改servers为server1、server2、server3，修改user和passwd为之前创建的路由用户的信息（maxrou，123456）：
由于我们使用了 [Read-Write Service]，需要删除另一个服务 [Read-Only Service]，删除其整块内容即可，底部还有一个 [Read-Only Client] 也需要删除。配置完成，保存并退出编辑器。
-- 执行启动命令
maxscale --config=/etc/maxscale.cnf
-- 查看MaxScale的响应端口是否已经就绪
-- 其中4006是Read-Write Listener使用的端口，用于连接MaxScale
-- 6603是MaxAdmin Listener使用的端口，用于MaxScale管理器
netstat -ntelp
-- maxscale软件的日志信息，如果没有启动可以查看错误信息
ls /var/log/maxscale
-- 查看进程信息
ps -C maxscale
-- 查看端口信息
ss -lnutp | grep maxscale
-- 登录MaxScale管理器，查看一下数据库连接状态，默认的用户名和密码是admin/mariadb
maxadmin --user=admin --password=mariadb
-- 可以看到，MaxScale已经连接到了master和slave
MaxScale> list servers
-- 开启事务后，就自动路由到了master，普通的查询操作，是在slave上
-- 在master上创建一个测试用户
create user 'rtest'@'%' identified by '111111';
grant ALL PRIVILEGES on *.* to 'rtest'@'%';
-- 通过java或者可视化连接，其中账号密码是master分配的账号，IP和端口是MaxScale中间件的配置
mysql -urtest -p'111111' -h'192.168.33.11' -P4006
MaxScale配置文件说明
vim /etc/maxscale.cnf，其中51为主，52为从
[maxscale]
# 默认服务启动后线程的个数，auto自动，可以自己修改
threads=auto
# 线程是进程的最小工作单位，他两的区别，线程共享资源，进程独享资源
# 指定服务器的IP地址，有两台所以要写两次，并且名称不能一致，其他为默认配置
[server1]
type=server
address=192.168.4.51
port=3306
protocol=MariaDBBackend
# 定义服务器的IP地址
[server2]
type=server
address=192.168.4.52
port=3306
protocol=MariaDBBackend
# 监视进程
[MariaDB-Monitor]
type=monitor
module=mariadbmon
# 数据库服务器列表
servers=server1,server2
# 使用哪个用户执行这个程序，使用哪个用户，需要到数据库服务器进行相应的授权，监视数据库
user=maxscalemon
password=123456
# 多长时间查看一次，默认单位毫秒
monitor_interval=2000
# 只读服务的配置
# 我们注释掉这个配置，因为我们希望在访问时，既可以读也可以写
#[Read-Only-Service]
# type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave
# 定义读写服务
[Read-Write-Service]
type=service
router=readwritesplit
# 读写分离用户
servers=server1,server2
# 路由用户，用来验证监视用户（客户端连接用户）是否存在
# 我们使用客户端连接数据库，MySQL代理怎样知道你当前登录的用户是否存在，使用这个用户去查看user表
user=maxscalerouter
password=123456
# 管理服务
[MaxAdmin-Service]
type=service
# 命令行
router=cli
# 定义只读的端口，因为上面我们不需要这个选项注释掉，这里也需要注释
#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008
# 监听读写服务的端口
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006
# 管理服务端口号，如果不想让他选择默认，也可以进行添加
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
#socket=default
# 自定义端口
port=4016
MySQL-Proxy中间件实现读写分离
MySQL-Proxy是MySQL官方提供的一个数据库代理层产品，和MySQLServer一样，相当于是一个基于GPL开源协议的开源产品。可用来监视、分析或者传输他们之间的通讯信息，具备的功能主要有连接路由、Query分析、Query过滤和修改、负载均衡以及主要的HA机制等。实际上MySQL-Proxy本身并不具有上述全部的这些功能，而是提供了实现上述功能的基础。要实现这些功能，还须要通过我们自行编写LUA脚本来实现。MySQL-Proxy实际上是在client请求与MySQLServer之间建立了一个连接池。全部client请求都是发向MySQL-Proxy，然后经由MySQL-Proxy进行对应的分析。推断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也能够起到负载均衡的效果。
配置参考：https://www.cnblogs.com/luckcs/articles/2543607.html
