14-主从复制&分库分表
1. 基本原理
MySQL 的主从复制就是基于二进制日志完成的。
复制是指将主数据库的 DDL 和 DML 操作通过 [二进制日志] 传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL 支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
MySQL 复制过程分成 3 步（异步且串行化）：
- Master 主库在事务提交时，会把数据变更作为事件（Events）记录在二进制日志文件 Binlog 中，这些记录过程叫做“二进制日志事件（Binary Log Events）”；
- Slave 将 Master 的 Binary Log Events 拷贝到它的中继日志（Relay Log）；
- Slave 重做中继日志中的事件，将改变应用到自己的数据库中。
2. 复制优势
MySQL 复制的有点主要包含以下 3 个方面：
- 主库出现问题，可以快速切换到从库提供服务。
- 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
- 可以在从库中执行备份，以避免备份期间影响主库的服务。
复制的最大问题：延时。
3. 基本原则
- 每个 Slave 只有一个 Master；
- 每个 Slave 只能有一个唯一的服务器 ID；
- 每个 Master 可以有多个 Slave。
4. 一主一从常见配置
- MySQL 版本一致且后台以服务运行；
- 主从都配置在
[mysqld]结点下，都是小写； - 修改前，记得将配置文件备份；
- 防火墙都关闭掉。
4.1 主机操作
a. 修改 my.ini
# 服务ID：保证整个集群环境中唯一
server-id=1
# 启用二进制日志，配置binlog日志存储路径和文件名
log-bin=U:/mysql/mysqlbin
# 错误日志,默认已经开启
# log-err=...
# 是否只读：1代表只读,0代表读写
read-only=0
# 忽略的数据, 指不需要同步(复制)的数据库
binlog-ignore-db=mysql
# 指定同步(复制)的数据库，写一个不存在的数据库名称 → 从机从接入点开始复制
binlog-do-db=mydb_1101
# 设置 logbin 格式，就用默认的 MIXED
# binlog_format=STATEMENT
MySQL 复制主要有 3 种方式：基于 SQL 语句的复制（statement-based replication，SBR）、基于行的复制（row-based replication，RBR）、混合模式复制（mixed-based replication，MBR）。对应的，binlog 的格式也有 3 种：STATEMENT、ROW、MIXED。
- 【STATEMENT 模式】每一条会修改数据的 SQL 语句会记录到 binlog 中。优点是并不需要记录每一条 SQL 语句和每一行的数据变化，减少了 binlog 日志量，节约 IO，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致（e.g. sleep()、last_insert_id() 以及 user-defined functions(udf) 等会出现问题）；
- 【ROW 模式】不记录每条 SQL 语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、function、trigger 的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是 ALTER TABLE 的时候会让日志暴涨；
- 【MIXED 模式】以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式。
b. 建立帐户并授权 slave
# 授权给 Slave
GRANT REPLICATION SLAVE ON *.* TO 'salve1101'@'192.168.206.129' IDENTIFIED BY '1101';
# 刷新权限列表
FLUSH PRIVILEGES;
# 查看 Master 状态
SHOW MASTER STATUS;
- File：从哪个日志文件开始推送日志文件
- Position：从哪个位置开始推送日志
- Binlog_Ignore_DB：指定不需要同步的数据库
执行完此步骤后不要再操作主服务器 MySQL，防止主服务器状态值变化。
c. 关闭防火墙+重启服务
4.2 从机操作
a. 修改 my.cnf
vim /etc/my.cnf
b. 启动从机复制功能
# 在从机上配置需要复制的主机：指定当前从库对应的主库的IP地址
# 用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志
CHANGE MASTER TO
MASTER_HOST='192.168.206.1',
MASTER_USER='salve1101',
MASTER_PASSWORD='1101',
MASTER_LOG_FILE='mysqlbin.000001',
MASTER_LOG_POS=107;
START SLAVE; # 启用从服务器复制功能
SHOW SLAVE STATUS\G; # 查看从机状态
c. 关闭防火墙+重启服务
systemctl stop firewalld.service # 停止firewall
systemctl restart mysqld.service # 重启mysql
4.3 其他操作
- 停止从服务器复制功能：
STOP SLAVE; - 重新配置主从
STOP SLAVE; RESET MASTER;
5. 验证同步操作
主从关系配置完毕且都 START 之后，再建库建表插数据。
6. 分库分表
分库分表的时机（前提）：
- 项目业务数据逐渐增多，或业务发展比较迅速
- 优化已解决不了性能问题（主从读写分离、查询索引 …）
- IO 瓶颈（磁盘 IO/网络 IO）、CPU 瓶颈（聚合查询、连接数太多）
拆分策略：
- 垂直拆分
- 垂直分库
- 垂直分表
- 水平拆分
- 水平分裤
- 水平分表
（1）垂直分库
（2）垂直分表
（3）水平分库
（4）水平分表
分库分表带来的新的问题：
- 分布式事务一致性问题
- 跨节点关联查询
- 跨节点分页、排序函数
- 主键避重
=> 分库分表中间件
