MySQL 主从复制 & 读写分离

MySQL 的复制默认的异步的，主从复制至少需要两个 MySQL 服务，这些服务可以分布在不同服务器上，也可以在同一个台服务器上。

主从复制

原理

binlog 是 MySQL 的逻辑日志，用于记录数据库执行的写入性操作（不含查询）语句，以二进制的形式保存在磁盘中，使用任何存储引擎的 MySQL 都会记录。
binlog 通过追加的方式进行写入，设置 max_binlog_size 参数配置 binlog 文件的大小。
Master 开启 binlog，将 binlog 发送到各个 Slave，Slave 按 binlog 记录的语句重写，达到主从数据一致

优化

MySQL 通过 sync_binlog 参数控制 biglog 的写入时机，取值范围是 0-N：

0：不强制要求，由系统自行判断何时写入
1：每次 commit 的时候都要写入
N：每执行 N 个事务，才会写入

sync_binlog 最安全的设置是 1，（MySQL 5.7.7 之后版本默认），能有效保证主从的一致性；
不过对数据库磁盘性能消耗较大，设置大一些的值可以提升数据库性能；
可以按实际情况牺牲一致性来获取更好的性能。

格式

binlog 日志有三种格式，分别为：STATMENT、ROW 和 MIXED

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，之后默认值是 ROW；通过 binlog-format 参数指定

STATMENT

基于 SQL 语句的复制（statement-based replication, SBR），每一条会修改数据的 SQL 语句会记录到 binlog 中

优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 I/O，从而提高了性能
缺点：在某些情况下会导致主从数据不一致，如执行 sysdate()、slepp() 等
ROW

基于行的复制（row-based replication, RBR），不记录每条 SQL 语句的上下文信息，仅需记录哪条数据被修改

优点：不会出现某些特定情况下的存储过程或 function、trigger 的调用和触发无法被正确复制的问题
缺点：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
MIXED

基于上述两种模式的混合复制（mixed-based replication, MBR），一般复制使用 STATEMENT，对于无法复制的操作使用 ROW 模式来保存

部署

主库、从库需要安装相同的稳定版 MySQL，见《MySQL 入门》，并添加如下配置

log-bin = mysql-bin
#同步数据库，主库设置
binlog-do-db = demo
#不同步数据库，主库设置
binlog-ignore-db = nacos
#同步数据库，从库设置
replicate-do-db = demo
#不同步数据库，从库设置
replicate-ignore-db = nacos
#跳过错误：off、num、all、ddl_exist_errors  
slave-skip-errors = 1032,1062,1053,1146,2003

修改主库的配置文件 /etc/my.cnf，开启 BINLOG，并设置 server-id 的值

1 2	log-bin=mysql-bin server-id=1

启动主库并上设置一个复制专用账户，并授予 REPLICATION SLAVE 权限

mysqld_safe --defaults-file=/app/conf/mysql/my.cnf &
mysql -A -uroot -p'' -e "CREATE USER 'rep'@'%' IDENTIFIED BY 'PASSWORD';GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';FLUSH PRIVILEGES;"
mysql -A -urep -pPASSWORD -e "select 'ok';"
mysql -uroot -pPASSWORD

锁定主库的表，确保备份之前没有新的数据操作，避免造成数据不一致；并查看主库信息：主要是 File 和 Position 的数值

1 2	flush tables with read lock; show master status;

重开一个窗口，修改从库的配置文件 /etc/my.cnf，设置 server-id 的值

1	server-id=2

启动并登录从库

1 2	mysqld_safe --defaults-file=/app/conf/mysql/my.cnf & mysql -uroot -pPASSWORD

根据上述获取到的主库信息配置同步信息，然后开始同步

CHANGE MASTER TO
MASTER_HOST='ip',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='PASSWORD,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=156;
start slave;
show slave status\G
#Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 表示成功

修复

登录从库，停止同步

1 2	mysql -uroot -pPASSWORD stop slave;

新开窗口登录主库进行备份，备份完成以后登录主库，并锁定主库，然后查看当前信息：主要是 File 和 Position 的数值

1 2	mysqldump -uroot -pPASSWORD demo > demo.sql show master status;

返回从库所在窗口进行恢复

1	mysql -uroot -pPASSWORD < demo.sql

根据新获取的主库信息配置同步信息

CHANGE MASTER TO
MASTER_HOST='xxx.xxx.xxx.xxx',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='8$TXDBJ*fLULiZ',
MASTER_LOG_FILE='mysql-bin.xxxxxxxx',
MASTER_LOG_POS=xxx;

解开主库的锁

1	UNLOCK TABLES;

在从库上开启复制进程并查看详情

1 2	start slave; show processlist\G;

读写分离

读写分离是主从复制的常规操作，即：主库处理增（INSERT）、删（DELETE）、改（UPDATE）及事务性查询（SELECT）操作，从库只处理非事务性查询（SELECT）操作

数据一致

主从复制的原理是将主库的语句通过读取 binlog 的方式在从库上执行，当服务高并发时，从库的数据更新会有延迟：刚写入主库的数据需要经过几十、几百毫秒、甚至更长才能在从库查询到。
主从复制的原理是将主库的语句通过读取 binlog 的方式在从库上执行，当服务高并发时，从库的数据更新会有延迟：刚写入主库的数据需要经过几十、几百毫秒、甚至更长才能在从库查询到。