目录
主从复制实现原理
MySql主从复制是指数据可以从一个MySql数据库服务器主节点复制到一个或多个从节点。MySql默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。主库master发生故障后，可以马上切换到从库slave，降低服务风险（数据同步备份）。可以把写操作放在master，读取操作放在slave，减轻单一数据库的操作压力（读写分离）。随着系统中业务访问量的增大，如果是单机部署数据库，就会导致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。
MySQL配置方式设置主从
修改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
修改slave配置文件
[mysqld]
# [可选]启用二进制日志
Log_bin=mysql-bin
# [必须]从服务器唯一ID
Server_id=11
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;
记录master状态信息
-- 查看二进制日志文件名，及最新位置。让slave知道用哪个用户信息访问master，知道读取哪个日志文件，及从哪儿开始读。
show master status;
-- 其中file、position字段需要记录下值，mysql-bin.000001是用于主从复制的文件名，437是日志文件内的最新位置。
将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下载地址：MySQL :: Download MySQL Utilities (Archived Versions)
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方式设置主从
参考资料：MySQL集群搭建_深度学习真难的博客-CSDN博客
MaxScale中间件实现读写分离
MaxScale中间件简介
配置好了MySql的主从复制结构后，我们希望实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡。读写分离和负载均衡是MySql集群的基础需求，MaxScale就可以帮着我们方便的实现这些功能。MaxScale是MySql的兄弟公司MariaDB开发的，现在已经发展得非常成熟。MaxScale是插件式结构，允许用户开发适合自己的插件。MaxScale目前提供的插件功能分为5类：
认证插件：提供了登录认证功能，MaxScale会读取并缓存数据库中user表中的信息，当有连接进来时，先从缓存信息中进行验证，如果没有此用户，会从后端数据库中更新信息，再次进行验证。
协议插件：包括客户端连接协议，和连接数据库的协议。
路由插件：决定如何把客户端的请求转发给后端数据库服务器，读写分离和负载均衡的功能就是由这个模块实现的。
监控插件：对各个数据库服务器进行监控，例如发现某个数据库服务器响应很慢，那么就不向其转发请求了。
日志和过滤插件：提供简单的数据库防火墙功能，可以对SQL进行过滤和容错。
MaxScale配置读写分离
MaxScale下载地址：MaxScale - MariaDB
在开始配置之前，需要在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中间件实现读写分离
配置参考：https://www.cnblogs.com/luckcs/articles/2543607.html
MySQL-Proxy是MySQL官方提供的一个数据库代理层产品，和MySQLServer一样，相当于是一个基于GPL开源协议的开源产品。可用来监视、分析或者传输他们之间的通讯信息，具备的功能主要有连接路由、Query分析、Query过滤和修改、负载均衡以及主要的HA机制等。实际上MySQL-Proxy本身并不具有上述全部的这些功能，而是提供了实现上述功能的基础。要实现这些功能，还须要通过我们自行编写LUA脚本来实现。MySQL-Proxy实际上是在client请求与MySQLServer之间建立了一个连接池。全部client请求都是发向MySQL-Proxy，然后经由MySQL-Proxy进行对应的分析。推断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也能够起到负载均衡的效果。
1384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
