MySQL主从复制配置

部署环境

操作系统： CentOS Linux release 7.2.1511

MySQL版本: mysql-5.6.26.tar.gz

主节点IP: 172.16.16.156 主机名: ZZN-VM-01

从节点IP:172.16.16.206 主机名: ZZN-VM-02

MySQL主从复制官方文档

https://dev.mysql.com/doc/refman/5.6/en/replication.html

MySQL主从复制的方式

MySQL5.6开始主从复制有两种方式：基于日志(bin log)，基于GTID（全局事物表示符）。本教程基于日志(bin log)的复制方式。

MySQL主从复制(A/B复制)的原理

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

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

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

主从配置需要注意的点

a: 主从服务器操作系统版本和位数一致；

b: Master 和 Slave 数据库的版本要一致；

c: Master 和 Slave 数据库中的数据要一致；

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

主从配置的就要步骤

1、Master上的配置

     (1) 安装数据库；

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

     (3) 启动数据库，查看当前是哪个日志，position号是多少；

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

     (5) 备份数据库(记得加锁和解锁)

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

     (7)启动数据库；

     以上不走，为单向主从搭建成功，想搭建双向主从需要的步骤:

     a: 登录数据库，指定Master的地址，用户、密码等信息

     b: 开启同步，查看状态；

2、 Slave上的配置

      (1)安装数据库

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

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

      (4)查看当前是哪个日志，position号是多少(单向主从此步骤不需要，双向主从需要)

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

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

单向主从环境搭建(A/B复制)

1、Master (172.16.16.159) 和 Slave(172.16.16.140) 上都安装了相同版本的数据库

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

   #  vi  /etc/my.cnf

   在 [mysqld] 中增加以下配置项

## 设置 server_id，一般设置为 IP
server_id=159

## 复制过滤： 需要备份的数据库， 输出 binlog
#binlog-do-db=roncoo

## 复制过滤： 不需要备份的数据库， 不输出（ mysql 库一般不同步）
binlog-ignore-db=mysql

## 开启二进制日志功能， 可以随便取， 最好有含义
log-bin=zzn-mysql-bin
## 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（ mixed,statement,row，默认格式是 statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
## 如： 1062 错误是指一些主键重复， 1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062

  2.1复制过滤可以让你只复制服务器中的一部分数据，有两种复制过滤：

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

3、启动/重启Master数据库服务，登入数据库，创建数据同步用户，并授予相应的权限

     #  service  mysql  restart

     #  mysql -uroot -p

     mysql> grant replication slave, replication client on *.* to 'repl'@'172.16.16.140' identified by 'repl';

     mysql>  flush privileges;

     mysql> show master  status;



4、创建 zzndb库，表，并写入一定量的数据，用于模拟现有的业务系统数据库

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

use zzndb;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `edu_user` (
`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` VALUES (1,'zzn','123456'),(2,'zzn','123456'),(3,'zzn','1');


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

## 先临时锁表
mysql> flush tables with read lock;
## 这里我们实行全库备份，在实际中，我们可能只同步某一个库，那也可以只备份一个库
# mysqldump -p3306 -uroot -p --add-drop-table zzndb > /tmp/zzndb.sql;

## 注意:实际生产环境中大数据量(超过2G数据)的备份，建议不要使用mysqldump进行备份，因为会比较慢。推荐使用XtraBackup进行备份。

## 解锁表
mysql> unlock tables;

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

# scp /tmp/zzndb.sql  [email protected]:/tmp

6、接下来处理Slave（172.16.16.140）。

# vi  /etc/my.cnf

## 设置 server_id，一般设置为 IP
server_id=140
## 复制过滤： 需要备份的数据库，输出 binlog
#binlog-do-db=roncoo
##复制过滤： 不需要备份的数据库， 不输出（ mysql 库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志，以备 Slave 作为其它 Slave 的 Master 时使用
log-bin=zzn-mysql-slave1-bin

## 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
## 主从复制的格式（ mixed,statement,row，默认格式是 statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
## 如： 1062 错误是指一些主键重复， 1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log 配置中继日志
relay_log=zzn-mysql-relay-bin
## log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1

如果Slave为其它Slave的Master时，必须设置log-bin。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname,但是，如果hostname改变则会出现问题)。relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志。当设置log_slave_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的时间写进自己的二进制日志(binary  log)，然后，他的slave可以获取这些事件并执行它。如下图(发送复制事件到其它Slave)。

7、保存后重启MySQL服务，还原备份数据

#  service  mysql  restart

slave上创建相同库：

create database if not exists zzndb  default charset utf8 collate utf8_general_ci;
use zzndb;

导入数据

# mysql -uroot -p zzndb < /tmp/zzndb.sql

8、登录Slave数据库，添加相关参数

#  mysql  -uroot -p

mysql> change master to master_host='172.16.16.159, master_user='repl', master_password='repl', master_port=3306, master_log_file='zzn-mysql-bin.000001',
             master_log_pos=1994, master_connect_retry=30;

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

## 查看主从同步状态
mysql> show slave status\G;

可看到 Slave_IO_State 为空， Slave_IO_Running 和 Slave_SQL_Running 是 No， 表明 Slave 还没有开始复制过程。

## 开启主从同步
mysql> start slave;

## 再查看主从同步状态
mysql> show slave status\G;

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

可查看 master 和 slave 上线程的状态。在 master 上，可以看到 slave 的 I/O 线程创建的连接：
Master:  mysql> show processlist\G;

1.row 为本地命令行的线程

2.row 为处理slave的 I/O线程连接

3.row为MySQL客户端的敛财线程

Slave :  mysql> show  processlist\G;

3.row  为I/O线程状态

4.row 为SQL线程状态

9、主从数据复制同步测试

     (1)  在Master中的zzndb库中变更数据，在Slave中查看数据是否同步

     (2)在Master中新增一个数据库，看Slave中是否同步

10、如果在测试过程中遇到同步出错， 可在Slave上重置设置(可选操作)：

    (1)mysql> reset  slave;

    (2)mysql> change master to master_host='172.16.16.159',  master_user='repl', master_password='repl',master_port=3306,master_log_file='edu-mysql-bin.00000x',
                     master_log_pos=xx,master_connect_retry=30;
       (此时， master_log_file 和 master_log_pos 要在 Master 中用 show master status 命令查看)

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

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

MySQL 主从数据同步延迟问题的调优
基于局域网的 Master/Slave 机制在通常情况下已经可以满足“实时”备份的要求了。如果延
迟比较大，可以从以下几个因素进行排查：
(1) 网络延迟；
(2) Master 负载过高；
(3) Slave 负载过高；
一般的做法是使用多台 Slave 来分摊读请求，再单独配置一台 Slave 只作为备份用， 不进行
其他任何操作，就能相对最大限度地达到“实时” 的要求了。
两个可以减少主从复制延迟的参数（ 按需配置）：
MySQL 可以指定 3 个参数， 用于复制线程重连主库： --master-retry-count， --master-connectretry， --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 ‘XXXXbin.000256’ position 23456


参考链接：

http://www.it165.net/database/html/201311/4851.html
http://blog.csdn.net/hguisu/article/details/7325124
http://www.woqutech.com/?p=1116
http://blog.chinaunix.net/uid-10661836-id-4116512.html
http://my.oschina.net/cimu/blog/165019
http://linuxguest.blog.51cto.com/195664/686813/
http://blog.itpub.net/29096438/viewspace-1409405/
http://blog.csdn.net/lxpbs8851/article/details/38455223
http://blog.csdn.net/seteor/article/details/17264633