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.inimy.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.inimy.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、 从库查询是否优化(比如存在查询慢,导致从库性能差,处理不过来)

Vote Vote Cancel Collect Collect Cancel

<< 上一篇: 【安徽】30W+招聘5年以上PHP大神 包吃包住 丰厚项目奖金!

>> 下一篇: Laravel6 开源后台系统,提示您的开发效率。