一、MySQL复制

扩展方式： Scale Up ，Scale Out
复制的功用
- 数据分布
- 负载均衡读
- 备份
- 高可用和故障切换
- MySQL升级测试

一主一从

一主多从

1.1 mysql 分片

MySQ L垂直分区

MySQL 水平分片（Sharding）

对应 shard 中查询相关数据

1.1 主从复制

主从复制线程：
- 主节点：
  dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其
  发送binary log events
- 从节点：
  I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
  SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
- master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
- relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
主从复制特点：
- 异步复制
- 主从数据不一致比较常见
复制架构：
- Master/Slave, Master/Master, 环状复制
- 一主多从
- 从服务器还可以再有从服务器
- 一从多主:适用于多个不同数据库
复制需要考虑二进制日志事件记录格式
- STATEMENT（5.0之前）
- ROW（5.1之后，推荐）
- MIXED
参考
https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://mariadb.com/kb/en/setting-up-replication/

MySQL复制模型

1.1.1 主从复制原理

(1) 在 slave 服务器上执行start slave 命令，开启复制开关，开始进行主从复制
(2) 此时， slave 服务器的 I/O 线程会通过 master 上已经授权的复制用户权限请求连接 master 服务器，并请求从指定 binlog 日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行 change master 命令指定的）之后开始发送 binlog 日志内容，
(3) master 服务器接收到来自 slave 服务器的 I/O 线程的请求后， master 上负责复制的 I/O 线程会根据 slave 服务器的 I/O 线程请求的信息分批读取指定 binlog 日志文件指定位置之后的 binlog 日志信息，然后返回给 slave 端的 I/O 线程。
(4) 返回的信息有
- binlog 日志信息
- master 服务器记录的新的 binlog 文件名称，以及在新的 binlog 中的下一个指定更新位置
(5) 当 slave 服务器的 i/o 线程获取到 master 服务器上的 I/0 线程发送的日志内容(日志文件和日志位置点)后，
- 首先会将 binlog 日志内容依次写到 slave 自身的 relay-log （中继日志）文件的最末端，格式: mysql-relay-bin.xxxx，
- 其次将新的 bin-log 文件名和文件位置记录到 master-info 文件中，以便一下次读取 master 端 bin-log 日志时能够告诉 master 服务器从新 bin-log 日志指定的文件和位置开始请求新的 bin-log 日志内容
(6) slave 服务器的 sql 线程会实时的检测本地的 relay-log 中 I/O线程新增加的日志内容，然后及时的把 relay log 文件中的内容解析成 sql 语句，并在 relay-log.info 中记录当前应用中继日志的文件名和位置点
(7) 经历了上面的过程，就可以保证 master 端和 slave 端执行了相同的 sql 语句，当复制状态正常时， master 和 slave 端的数据是完全一样的

1.1.2 主从复制实验

#主节点配置：
# 启用二进制日志
vim /etc/my.cnf
    [mysqld]
    server_id=1   # 为当前节点设置一个全局惟一的ID号，整数即可
    log-bin=/data/binlog # 也可以不写路径
service mysqld restart # 重启mysql

# 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';


# 从节点配置：
# 启动中继日志
vim /etc/my.cnf
    [mysqld]
    server_id=2   # 为当前节点设置一个全局惟的ID号
    read_only=ON   # 设置数据库只读
    relay_log=relay-log    #relay log 的文件路径，默认值 hostname-relay-bin
    relay_log_index=relay-log.index   # 默认值 hostname-relay-bin.index
service mysqld restart # 重启mysql

# 使用有复制权限的用户账号连接至主服务器，并启动复制线程
# 查看 MASTER_LOG_FILE 与 MASTER_LOG_POS
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000001 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

mysql> CHANGE MASTER TO
        MASTER_HOST='192.168.7.7',
        MASTER_USER='repluser',
        MASTER_PASSWORD='123456',
        MASTER_LOG_FILE='logbin.000001',
        MASTER_LOG_POS=154;
mysql> START SLAVE;


# 注意：
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
    通过备份恢复数据至从服务器
    复制起始位置为备份时，二进制日志文件及其POS
如果要启用级联复制,需要在从服务器启用以下配置
    [mysqld]
    log_bin
    log_slave_updates

# 复制架构中应该注意的问题：
1、限制从服务器为只读
  在从服务器上设置read_only=ON
    注意：此限制对拥有SUPER权限的用户均无效
  阻止所有用户, 包括主服务器复制的更新
    mysql> FLUSH TABLES WITH READ LOCK;

2、RESET SLAVE ：从服务器清除master.info ，relay-log.info, relay log ，开
始新的relay log
    RESET SLAVE ALL：清除所有从服务器上设置的主服务器同步信息，如
PORT, HOST, USER和 PASSWORD 等
   注意：以上都需要先STOP SLAVE

3、sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量

4、如何保证主从复制的事务安全
  参看https://mariadb.com/kb/en/library/server-system-variables/
  在master节点启用参数：
    sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
    如果用到的为InnoDB存储引擎：
    innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
    innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
    sync_master_info=# #次事件后master.info同步到磁盘
  在slave节点启用服务器选项：
    skip-slave-start=ON 不自动启动slave
  在slave节点启用参数：
    sync_relay_log=# #次写后同步relay log到磁盘
    sync_relay_log_info=#  #次事务后同步relay-log.info到磁盘

1.2 主主复制

主主复制：互为主从
- 容易产生的问题：数据不一致；因此慎用
- 考虑要点：自动增长id
  - 配置一个节点使用奇数id
    auto_increment_offset=1 开始点
    auto_increment_increment=2 增长幅度
  - 另一个节点使用偶数id
    auto_increment_offset=2
    auto_increment_increment=2
主主复制的配置步骤：
- (1) 各节点使用一个惟一server_id
- (2) 都启动binary log和relay log
- (3) 创建拥有复制权限的用户账号
- (4) 定义自动增长id字段的数值范围各为奇偶
- (5) 均把对方指定为主节点，并启动复制线程

1.3 半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

半同步复制实现

# 主服务器
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # 安装插件
# 配置
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  #超时长1s
SHOW GLOBAL VARIABLES LIKE '%semi%';
SHOW GLOBAL STATUS LIKE '%semi%';

# 从服务器配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;

1.4 复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表
注意：在使用复制过滤器的时候，主服务器需要使用USE DATABASE_NAME,再对表进行操作，否则复制过滤器不起作用。
两种实现方式
- 方式1：服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
  注意：此项和binlog_format相关
  参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
  binlog-do-db = 数据库白名单列表，多个数据库需多行实现
  binlog-ignore-db = 数据库黑名单列表
  问题：基于二进制还原将无法实现；不建议使用
- 方式2： 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
  问题：会造成网络及磁盘IO浪费
从服务器上的复制过滤器相关变量

#指定复制库的白名单
replicate_do_db= DATABASE_NAME

#指定复制库黑名单
replicate_ignore_db= DATABASE_NAME

#指定复制表的白名单
replicate_do_table= TABLE_NAME

#指定复制表的黑名单
replicate_ignore_table= TABLE_NAME

#支持通配符
replicate_wild_do_table= foo%.bar%
replicate_wild_ignore_table=

1.5 MySQL 复制加密

基于SSL复制：
在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性
配置实现：参看：https://mariadb.com/kb/en/library/replication-with-secure-connections/
- 主服务器开启SSL，配置证书和私钥路径并且创建一个要求必须使用SSL连接的复制账号 mysql>GRANT REPLICATION SLAVE ON . TO ‘repluser’@’192.168.8.%’IDENTIFIED BY ‘magedu’ REQUIRE SSL;
- 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项

# Master 服务器配置
vim /etc/my.cnf
    [mysqld]
    log-bin
    server_id=1
    ssl
    ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    ssl-cert=/etc/my.cnf.d/ssl/master.crt
    ssl-key=/etc/my.cnf.d/ssl/master.key

# Slave 服务器配置
mysql>
CHANGE MASTER TO
MASTER_HOST='MASTERIP',
MASTER_USER='rep',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

1.6 复制的监控和维护

# 清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER
RESET SLAVE

# 复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

# 从服务器是否落后于主服务
Seconds_Behind_Master：0

# 如何确定主从节点数据是否一致
percona-tools

# 数据不一致如何修复
删除从数据库，重新复制

1.7 GTID复制

GTID复制：（global transaction id 全局事务标识符） MySQL5.6版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步
GTID 架构
GTID = server_uuid:transaction_id，在一组复制中，全局唯一 server_uuid 来源于 auto.cnf
GTID 服务器相关选项
gtid_mode gtid模式
enforce_gtid_consistency 保证GTID安全的参数

GTID在binlog中的结构和GTID event 结构

GTID配置示例

# 主服务器
vim /etc/my.cnf
    [mysqld]
    server-id=1
    log-bin=mysql-bin
    gtid_mode=ON
    enforce_gtid_consistency
    mysql> grant replication slave on *.* to 'repluser'@'192.168.7.%' identified by '123456';

# 从服务器
vim /etc/my.cnf
    [mysqld]
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency
    mysql>CHANGE MASTER TO MASTER_HOST='192.168.7.17',
    MASTER_USER='repluser',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
    mysql>start slave;

二、MySQL读写分离

2.1 读写分离案例

mysql-proxy：Oracle：https://downloads.mysql.com/archives/proxy/
Atlas，Qihoo：https://github.com/Qihoo360/Atlas
dbproxy，美团：https://github.com/Meituan-Dianping/DBProxy
Cetus，网易乐得：https://github.com/Lede-Inc/cetus
Amoeba：https://sourceforge.net/projects/amoeba/
Cobar，阿里巴巴，Amoeba的升级版：https://github.com/alibaba/cobar
Mycat，基于Cobar：http://www.mycat.io/
ProxySQL：https://proxysql.com/

2.2 ProxySQL

ProxySQL： MySQL中间件
两个版本：官方版和 percona 版，percona 版是基于官方版基础上修改C++语言开发，轻量级但性能优异(支持处理千亿级数据)具有中间件所需的绝大多数功能，包括：
- 多种方式的读/写分离
- 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
- 缓存查询结果
- 后端节点监控
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
准备：
实现读写分离前，先实现主从复制
注意：slave节点需要设置read_only=1
ProxySQL组成：
- 服务脚本：/etc/init.d/proxysql
- 配置文件：/etc/proxysql.cnf
- 主程序：/usr/bin/proxysql
- 基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：service proxysql start
- 启动后会监听两个默认端口
  6032：ProxySQL的管理端口
  6033：ProxySQL对外提供服务的端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：

2.2.1 ProxySQL安装

# 基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl= http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install proxysql
# 基于RPM下载安装
https://github.com/sysown/proxysql/releases

2.2.2 ProxySQL 实现读写分离

数据库说明：
main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效，SAVE 使其存到硬盘以供下次重启加载
disk 是持久化到硬盘的配置，sqlite数据文件
stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表
- 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
- 执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
  参考: https://github.com/sysown/proxysql/wiki/Global-variables
  https://blog.csdn.net/weixin_42758707/article/details/95657214?spm=1001.2014.3001.5501