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

一、主从复制

1.实现原理

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

2.优化

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

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

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

3.格式

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 模式来保存

4.部署

主库、从库需要安装相同的稳定版 MySQL MySQL 数据库入门 (opens new window) 主库上设置一个复制专用账户，并授予REPLICATION SLAVE权限

GRANT REPLICATION SLAVE ON *.* TO 'rep'@xxx.xxx.xxx.xxx IDENTIFIED BY '8$TXDBJ*fLULiZ';

修改主库的配置文件/etc/my.cnf，开启 BINLOG，并设置 server-id 的值，重启 MySQL 服务生效。

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

锁定主库的表，确保备份之前没有新的数据操作，避免造成数据不一致

FLUSH TABLES WITH READ LOCK;

同步主库已有数据到从库
主库操作：
1、停止主库的数据更新操作
flush tables with read lock;
2、新开终端，生成主数据库的备份（导出数据库）
mysqldump -uroot -ptest123 cmdb > cmdb.sql
3、将备份文件传到从库
scp cmdb.sql root@192.168.8.11:/root/
4、主库解锁
unlock tables;
 
从库操作：
slave stop;
create database cmdb default charset utf8;
mysql -uroot -ptest123 < cmdb.sql 

查看主库运行状态，记下 File 和 Position 数值，后续有用

SHOW MASTER STATUS;

将主库数据完整备份到从库，直接复制数据文件夹是最方便的做法，具体情况具体对待

修改从库的配置文件/etc/my.cnf，设置 server-id 参数（唯一值，不能重复）

server-id=2

通过 --skip-slave-start 选项启动从库，暂时跳过复制操作以便后续配置

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;

解开主库的锁

UNLOCK TABLES;

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

start slave;
show processlist\G;

二、读写分离

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

三、数据一致

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

• 网络方面： 保证主、从库之间的网络稳定，以及延迟较小
• 硬件方面： 从库使用更好的配置，提高随机读写的性能
• 并行复制： 从 MySQL 5.7 以后，新增了多线程复制技术，解决了主库同一个 schema 下数据发生变更，从库不能处理并发的问题。