MySQL 5.7 数据库主从复制方案与配置实现

主从复制方案

主从复制的好处

MySQL 的 Replication（英文为复制）是一个多 MySQL 数据库做主从同步的方案，特点是异步复制，广泛用在各种对 MySQL有更高性能、更高可靠性要求的场合。主从复制有以下四方面的好处：

1. 数据备份 (Data Backup) 只是简单的对数据库进行备份，降低数据丢失的风险，有时也用于报表等对数据时效 性要求不高的场合。
2. 负载均衡 (Load Balance) 主要用在 MySQL 集群，解决单点故障或做故障切换；以降低单台服务器的负载和风险，如实现读写分离，可以使得服务器访问负荷比较均衡。
3. 数据分发 (Data Distribution) 主要用于多数据中心或异地备份，实现数据分发与同步。
4. 高可用和数据容错 (High Availability and Failover) MySQL 自带的健康监控和检测，根据配置的时间间隔，可以检测主库是否正常工作， 一旦发现主库宕机或无法正常工作，就会选择到最好的一个备库上。

主从复制类型

MySQL主从复制有下面三种类型：

• 1、基于语句的复制（Statement-Based）： 在主服务器上执行的 SQL语句，然后将语句写入二进制日志文件，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
• 2、基于行的复制（Row-Based）：把改变的内容（数据本身）复制过去，而不是把命令在从服务器上执行一遍，从 mysql 5.0 开始支持。
• 3、混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

主从复制常用拓扑结构

一主一从

这是最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。

一主多从

一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。还可以 对多台 Slave 进行分工，服务于不同的系统，例如一部分 Slave 负责网站前台的读请求，另 一部分 Slave 负责后台统计系统的请求。因为不同系统的查询需求不同，对 Slave 分工后，可以创建不同的索引，使其更好的服务于目标系统。

Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据 自己的具体情况，并评估好目标结构的延时对系统的影响。

主从复制实施配置实例

配置环境准备

这里以一主一从的拓扑结构为例来进行配置。一主多从拓扑结构的配置类似。以下配置在 MySQL5.5.x 和 MySQ 5.7.x 版本上配置测试通过，主从数据库版本必须一样。

安装环境：

• 1、操作系统：Window7 x64（跟操作系统关系不大，只是命令和操作上有差别）；
• 2、数据库版本：MySQL 5.5.16(Master主库)、MySQL5.5.29(Slave从库)；最好是同一大版本系列，小版本差别不大。
• 3、Master 主库 IP 地址： 192.168.1.10；
• 4、Slave 从库 IP 地址： 192.168.1.20；

这里强调数据库版本，是因为不同 MySQL 版本的性能有差别；目前 MySQL 5.7.x 版本系列的性能是最好的，实际系统使用时最好用此系列的数据库版本。

MySQL 5.7 号称性能是 MySQL 5.6 的 3 倍，而 MySQL 5.6 号称性能是 MySQL 5.5 的 2 倍，你信吗？这个问题还是要辩证的理解，首先性能肯定是改进了，但为什么大部份人升级后也没有感觉？这就要从测试方法说起，大部份厂商发布新版本都会说性能提升了多少多少， 因为这是最能吸引客户的数据。从官方的测试数据来看，首先是 32 个并发以上的简单查询，并且超过 10 万 QPS 才有区别，如果你的系统没有达到这个并发度那肯定没有体会的。另外是纯内存与 CPU 计算，如果你的系统瓶颈在磁盘或网络 IO 那也不会有明显的效率提升。当然，我认为 MySQL 5.6 的 ICP 这种特性其实对于业务来说更有意义，说不定刚好你的慢 SQL可以解决掉。

主从复制参数配置

主从复制参数配置内容及步骤顺序如下：

1、在 Master 主库上创建复制用的账号，并授权：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'copyer'@'192.168%' IDENTIFIED BY 'copyer password';
mysql> FLUSH PRIVILEGES; 

2、对 Master 主库的 my.ini 或 my.cnf 配置文件做更改：

##必须在 mysqld 节点上进行配置 
[mysqld] 
##设置服务器 ID，必须保持唯一性 
server-id=10
##设置需要写 bin 日志的数据库，多个数据库则要多行分别设置 
binlog-do-db=test
#binlog-do-db=xxxx 
##设置不需要写 bin 日志的数据库，多个数据库则要多行分别设置 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
##注：binlog-do-db, binlog-ignore-db 为互斥关系，只需设置其中一项即可；
##更要注意 binlog-do-db 库的 SQL 不能有操作 binlog-ignore-db 库的语句或动作，否则报错
##日志模式 ROW|STATEMENT|MIXED，默认是语句模式，现设置为行模式 
binlog-format=row 
##二进制日志文件存放位置，可以设置路径；也可以不设路径，此时存放到 datadir 目录下。
log-bin=E:/MySQL5/Data/test-binlog 

3、对 Slave 从库的 my.ini 或 my.cnf 配置文件做更改：

##必须在 mysqld 节点上进行配置 
[mysqld] 
##设置服务器 ID，必须保持唯一性 
server-id=20
##设置需要复制的数据库，多个数据库则要多行分别设置 
replicate-do-db=test
replicate-do-db=xxxx 
##设置不需要复制的数据库，多个数据库则要多行分别设置 
replicate-ignore-db=mysql 
replicate-ignore-db=information_schema 

4、在完成 Master 主库和 Slave 从库的配置后，分别重启主从 MySQL 数据库，然后在 Master 主库里用 root 账号登录、执行以下命令，这些参数在 Slave从库启动复制时需要用到。

然后在 Slave 从库（记住是 Slave 从库）也用 root 账号登录、在 MySQL 命令行执行以下命令：

mysql> change master to
-> master_host='192.168.1.10', 
-> master_user='copyer', 
-> master_password='copyer password', 
-> master_log_file='test-binlog.000005', 
-> master_log_pos=107; 
Query OK, 0 rows affected (0.10 sec) 

注意：里面的 host/user/password/log_file/log_pos 要根据实际参数做更改。如果 MySQL Server 不是默认的 3306 端口，那么必须加上 master_port=xxxx 参数，设置具体端口数字。如果有经过防火墙的话，在防火墙上必须开放对应端口。

然后在 Slave 从库里启动 Slave 复制，这样主从复制配置完成。

mysql> start slave;
Query OK, 0 rows affected (0.00 sec) 

如果复制过程中有问题，可以先 mysql> stop slave; 然后再 mysql> start slave;

主从复制状态监测

在 Master 主库的 MySQL 命令行里可以执行以下命令来监测主从复制执行情况：

mysql> show master status; ##显示 master 复制状态 
mysql> show master logs; ##显示 Master 库 bin log 情况，跟 show binary logs; 命令一样。
mysql> show processlist; ##查看 MySQL进程状态，包括复制进程，看 state 字段说明

在 Slave 主库的 MySQL 命令行里，可以执行以下命令来监测主从复制执行情况：

mysql> show processlist; ##查看 MySQL进程状态，包括复制进程，看 state 字段说明 
mysql> show slave status; ##显示 Slave复制状态，状态很多，具体要查看手册另外是直接查看主从复制的库和表里的数据，是否有及时正常复制过来。

在 Slave主库通过 show slave status 命令主要监测以下四个参数：

• 1、Slave_IO_State IO 进程处理状态
• 2、Slave_IO_Running IO 线程是否打开：YES/NO/NULL三种状态
• 3、Slave_SQL_Running SQL 线程是否运行：YES/NO/NULL三种状态
• 4、Seconds_Behind_Master 落后主库的时间（秒）

可能导致主从复制延时的因素：

• 1、 主从复制的服务器时钟是否一致；
• 2、 网络通信是否存在延时；
• 3、 是否和日志类型、数据量过大有关；
• 4、 从库性能，有没有开启 binlog
• 5、 从库查询是否优化（比如存在查询慢，导致从库性能差，处理不过来）