mysql学习：主从复制

介绍

环境：centos6.9，mysql-5.5.55编译安装

master：192.168.170.10

slave1：192.168.170.11

slave2：192.168.170.12

MySQL主从复制的方式

MySQL5.6开始主从复制有两种方式：基于日志（binlog）、基于GTID（全局事务标示符）。

本测试主要讲基于日志（binlog）的复制。

MySQL主从复制官方文档：http://dev.mysql.com/doc/refman/5.6/en/replication.html

MySQL主从复制（也称A/B复制）的原理

(1) Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)；

(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)；

(3) Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致；

(2) Master和Slave数据库的版本要一致；

(3) Master和Slave数据库中的数据要一致；

(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；

主从配置的简要步骤

1、Master上的配置

(1) 安装数据库；

(2) 修改数据库配置文件，指明server_id，开启二进制日志(log-bin)；

(3) 启动数据库

(4) 登录数据库，授权数据复制用户（IP地址为从机IP地址，如果是双向主从，这里的还需要授权本机的IP地址，此时自己的IP地址就是从IP地址)；

(5) 加读锁，然后查看当前是哪个日志，position号是多少；备份数据库，完成后解锁；

(6) 传送备份数据到Slave上；

2、Slave上的配置

(1) 安装数据库；

(2) 修改数据库配置文件，指明server_id（如果是搭建双向主从的话，也要开启二进制日志log-bin）；

(3) 启动数据库，还原备份；

(4) 指定Master的地址、用户、密码等信息；

(5) 开启同步，查看状态。

单向主从环境（也称MySQL A/B复制）的搭建

1、Master（192.168.170.10）和Slave（192.168.170.11，192.168.170.12）上都安装了相同版本的数据库

注意：两台数据库服务器的的selinux都要disable（永久关闭selinux，请修改/etc/selinux/config，将SELINUX改为disabled）

2、修改Master的配置文件/etc/my.cnf

vim /usr/local/mysql/conf/my.cnf
## 在 [mysqld] 中增加以下配置项
## 设置server-id，一般设置为IP
server-id=10
## 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=dbname
## 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）,这里全部同步
#binlog-ignore-db=dbname
## 开启二进制日志功能，可以随便取，最好有含义
log-bin=/data/mysql_binlog/mysql-bin
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=30
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
#slave_skip_errors=1062

2.1 MySQL对于二进制日志 (binlog)的复制类型

(1) 基于语句的复制：在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。   

(2) 基于行的复制：把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。

(3) 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

3、启动/重启Master数据库服务，登录数据库，创建数据同步用户，并授予相应的权限，因为后面会涉及到主从切换，这里授权一个网段

mysql> grant replication slave, replication client on *.* to 'rep_user'@'192.168.170.%' identified by '234567';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、创建web库、表，并写入一定量的数据，用于模拟现有的业务系统数据库，即当前master已经运行一段时间后，怎么将现有的一台主机做主从复制

create database if not exists web default charset utf8 collate utf8_general_ci;

use web;

DROP TABLE IF EXISTS `user_info`;

CREATE TABLE `user_info` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名',
  `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

INSERT INTO `user_info` VALUES (1,'andy','123456'),(2,'bob','234567'),(3,'tom','345678');

mysql> select * from user_info;
+----+----------+--------+
| Id | userName | pwd    |
+----+----------+--------+
|  1 | andy     | 123456 |
|  2 | bob      | 234567 |
|  3 | tom      | 345678 |
+----+----------+--------+
3 rows in set (0.00 sec)

5、为保证Master和Slave的数据一致，我们采用主备份，从还原来实现初始数据一致

## 先临时锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1219 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

## 这里我们实行全库备份，在实际中，我们可能只同步某一个库，那也可以只备份一个库

[root@master ~]#mysqldump -p3306 -uroot -p -A  --events --ignore-table=mysql.events > mysql.sql
Enter password: 
[root@master ~]#ls
mysql.sql

## 注意：实际生产环境中大数据量（超2G数据）的备份，建议不要使用mysqldump进行比分，因为会非常慢。此时推荐使用 XtraBackup 进行备份。

## 解锁表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

设置防火墙，允许连接本机的3306端口进行复制操作

设置防火墙
-A INPUT -s 192.168.170.0/24 -p tcp --dport 3306 -j ACCEPT

然后master上面的操作就完成了，可以进行正常的读写操作了

将Master上备份的数据远程传送到Slave上，以用于Slave配置时恢复数据

[root@master ~]#scp -P 22222 mysql.sql 192.168.170.11:/root
[root@master ~]#scp -P 22222 mysql.sql 192.168.170.12:/root

6，从节点（192.168.170.11，192.168.170.12）设置

## 设置server_id，一般设置为IP
server-id   = 11  #另外一个节点为12
## 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=dbname
##复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）,这里全部
#binlog-ignore-db=dbname
## 开启二进制日志，以备Slave作为其它Slave的Master时使用
log-bin=/data/mysql_binlog/mysql-bin
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=30
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
#slave_skip_errors=1062
## relay_log配置中继日志
relay_log   = /data/mysql_binlog/mysql-relay
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1

如果Slave为其它Slave的Master时，必须设置bin_log。在这里，我们开启了二进制日志，而且显式的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志。

当设置log_slave_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件并执行它。如下图所示（发送复制事件到其它Slave）：

7，下面的操作两个从主机同时操作，从节点重启服务，然后将以前的所有库都删除，保证导入数据不冲突

mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database mysql;
Query OK, 24 rows affected, 2 warnings (0.00 sec)

mysql> drop database performance_schema;
Query OK, 17 rows affected, 2 warnings (0.00 sec)

[root@master ~]#mysql < mysql.sql 

#可以查看到导入成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| web                |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from web.user_info;
+----+----------+--------+
| Id | userName | pwd    |
+----+----------+--------+
|  1 | andy     | 123456 |
|  2 | bob      | 234567 |
|  3 | tom      | 345678 |
+----+----------+--------+
3 rows in set (0.00 sec)

8，到这里两台从主机的数据就到了master锁表时的数据状态了，当然master后来会写入其他数据，这里指定从master锁表的时候开始复制二进制日志，会赶上master的脚步

mysql> change master to master_host='192.168.170.10', 
    -> master_user='rep_user', 
    -> master_password='234567', 
    -> master_port=3306, 
    -> master_log_file='mysql-bin.000004', 
    -> master_log_pos=1219, 
    -> master_connect_retry=30;
Query OK, 0 rows affected (0.02 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.170.10
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1219
               Relay_Log_File: mysql-relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No

上面执行的命令的解释：

master_host='192.168.170.10'    ## Master的IP地址
master_user='rep_user'    ## 用于同步数据的用户（在Master中授权的用户）
master_password='234567'    ## 同步数据用户的密码
master_port=3306    ## Master数据库服务的端口
master_log_file='mysql-bin.000004'    ##指定Slave从哪个日志文件开始读复制数据（可在Master上使用show master status查看到日志文件名）
master_log_pos=1219    ## 从哪个POSITION号开始读
master_connect_retry=30  ##当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。

9，然后启动slave开始同步数据

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.170.10
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1219
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1219
              Relay_Log_Space: 405
 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
1 row in set (0.00 sec)

主要看以下两个参数，这两个参数如果是Yes就表示主从同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0  表示落后主节点0，表示复制正常
Last_IO_Errno: 0   表示没有io错误
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:

10，测试配置是否正确

主节点操作语句，查看从节点是否能正常同步
mysql> INSERT INTO web.user_info VALUES (4,'alex','qwerty'),(5,'sam','asdfgh');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from web.user_info;
+----+----------+--------+
| Id | userName | pwd    |
+----+----------+--------+
|  1 | andy     | 123456 |
|  2 | bob      | 234567 |
|  3 | tom      | 345678 |
|  4 | alex     | qwerty |
|  5 | sam      | asdfgh |
+----+----------+--------+
5 rows in set (0.00 sec)

经过测试，从节点同步正常

11、测试过程中，如果遇到同步出错，可在Slave上重置主从复制设置（选操作）：

(1) mysql> reset slave;
(2) mysql> change master to master_host='xxx',
master_user='xxx',
master_password='xxx',
master_port=3306,
master_log_file='xxx',  
master_log_pos=xx,
master_connect_retry=30;
(此时，master_log_file和master_log_pos要在Master中用show master status 命令查看)

注意：如果在Slave没做只读控制的情况下，千万不要在Slave中手动插入数据，那样数据就会不一致，主从就会断开，就需要重新配置了。

12、上面所搭建的是单向复制的主从，也是用的比较多的，而双向主从其实就是Master和Slave都开启日志功能，然后在Master执行授权用户（这里授权的是自己作为从服务器，也就是这里的IP地址是Master的IP地址），然后再在Master上进行chang master操作。

MySQL主从数据同步延迟问题的调优

基于局域网的Master/Slave机制在通常情况下已经可以满足“实时”备份的要求了。如果延迟比较大，可以从以下几个因素进行排查：

(1) 网络延迟；

(2) Master负载过高；

(3) Slave负载过高；

一般的做法是使用多台Slave来分摊读请求，再单独配置一台Slave只作为备份用，不进行其他任何操作，就能相对最大限度地达到“实时”的要求了。

两个可以减少主从复制延迟的参数（按需配置）：

MySQL可以指定3个参数，用于复制线程重连主库：–master-retry-count，–master-connect-retry，–slave-net-timeout 。其中 master-connect-retry 和 master-retry-count 需要在 Change Master 搭建主备复制时指定，而 slave-net-timeout 是一个全局变量，可以在 MySQL 运行时在线设置。具体的重试策略为：备库过了 slave-net-timeout 秒还没有收到主库来的数据，它就会开始第一次重试。然后每过 master-connect-retry 秒，备库会再次尝试重连主库。直到重试了 master-retry-count 次，它才会放弃重试。如果重试的过程中，连上了主库，那么它认为当前主库是好的，又会开始 slave-net-timeout 秒的等待。slave-net-timeout 的默认值是 3600 秒，master-connect-retry 默认为 60 秒，master-retry-count 默认为 86400 次。也就是说，如果主库一个小时都没有任何数据变更发送过来，备库才会尝试重连主库。这就是为什么在我们模拟的场景下，一个小时后，备库才会重连主库，继续同步数据变更的原因。

这样的话，如果你的主库上变更比较频繁，可以考虑将 slave-net-timeout 设置的小一点，避免主库 Binlog dump 线程终止了，无法将最新的更新推送过来。当然 slave-net-timeout 设置的过小也有问题，这样会导致如果主库的变更确实比较少的时候，备库频繁的重新连接主库，造成资源浪费。

slave-net-timeout=seconds

参数说明：当Slave从Master数据库读取log数据失败后，等待多久重新建立连接并获取数据，单位为秒，默认设置为3600秒。

在做MySQL Slave的时候经常会遇到很多错误，需要根据具体原因跨过错误继续同步，但有时候是因为网络不稳定、网络闪断造成同步不正常，如果Slave机器非常多的情况下，一个一个登录服务器去stop slave、start slave变得无聊而且重复。从MySQL5.1开始支持的解决方案配置：

master-connect-retry=seconds

参数说明：在主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数。如果主服务器.info文件中的值可以读取则优先使用。如果未设置，默认值为60。

通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟。

一般网络问题的错误是：

[ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=xxxx)

[ERROR] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘edu-mysql-bin.000256’  position 23456

–

参考文档：mysql主从复制

–

–

–