CentOS6.5下MySQL5.6的主从同步配置

主从复制的方式开始主从复制有两种方式：基于日志、基于GTID（全局事务标示符）。本教程主要讲基于日志的复制

MySQL主从复制（也称A/B复制）的原理
(1) Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)；
(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)；
(3) Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)。

环境CentOS6.5

MYSQL5.6.35

服务器IP       服务器主机名

192.168.1.177 master

192.168.1.158 slave

注意：

1.两个系统版本一样

2.数据库版本一样

3.两个数据库的数据要一样

4.两台服务器的selinux都要设为selinux=disable

#vim /etc/selinux/config将SELINUX=disabled

5.Master必须开启二进制日志，master和slave的server_id在局域网内必须唯一，一般使用当前机器的IP最后的值

主从配置的简要步骤
1、Master上的配置
(1) 安装数据库；
(2) 修改数据库配置文件，指明server_id，开启二进制日志(log-bin)；
(3) 启动数据库，查看当前是哪个日志，position号是多少；
(4) 登录数据库，授权数据复制用户（IP地址为从机IP地址，如果是双向主从，这里的还需要授权本机的IP地址，此时自己的IP地址就是从IP地址)；
(5) 备份数据库（记得加锁和解锁）；
(6) 传送备份数据到Slave上；
(7) 启动数据库；
以下步骤，为单向主从搭建成功，想搭建双向主从需要的步骤：
(1) 登录数据库，指定Master的地址、用户、密码等信息（此步仅双向主从时需要）；
(2) 开启同步，查看状态；

2、Slave上的配置
(1) 安装数据库；
(2) 修改数据库配置文件，指明server_id（如果是搭建双向主从的话，也要开启二进制日志log-bin）；
(3) 启动数据库，还原备份；
(4) 查看当前是哪个日志，position号是多少（单向主从此步不需要，双向主从需要）；
(5) 指定Master的地址、用户、密码等信息；
(6) 开启同步，查看状态。

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

1.主服务器配置

#vim /etc/my.cnf

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

## 设置server_id，一般设置为IP
server_id=177
## 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=mydb
## 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志功能，可以随便取，最好有含义
log-bin=master-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
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true

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

(1) 基于语句的复制：在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。   
(2) 基于行的复制：把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。
(3) 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

重启Master数据库，并赋权限

#service mysql restart

#mysql -u root -p

mysql>create user mysql@'%' IDENTIFIED BY 'mysql';

mysql>GRANT ALL  on *.* to mysql@'%';//这里可根据自己的数据库和权限进行设置

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

mysql>flush privileges;

mysql>select host,user from mysql.user;

mysql>show master status;

记住下面的master-bin.000006和 120,在配置slave时人用到，File_Set和Position每次重新配置都会改变

File_Set                       Position          Binlog_Do_DB    Binlog_Ignore_DB  

master-bin.000006      120              mydb

配置从服务器slave

#vim /etc/my.cnf

在[mysqld]下面加上server_id=158binlog-do=db=mydbbinlog-ignore-db=mysql#log-bin=slave1-bin//这个可以先不加binlog_cache_size=1Mbinlog_format=mixedexpire_logs_days=7slave_skip_errors=1062relay_log=mysql-relay-binlog_slave_updates=1read_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）：

保存后重启MYSQL

#service mysql restart

登陆slave的MYSQL，配置参数

#mysql -u root -p

mysql>change master to master_host='192.168.1.177',master_user='mysql',master_password='mysql',master_port=3306,master_log_file='master-bin.000006',master_log_pos=120,master_connect_retry=30;//这里的master_log_file和master_log_pos要和show master status中显示的master相关信息对应

开启主从同步

mysql>start slave;

查看主从同步状态

#show slave status \G

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

主要看以下两个参数，这两个参数如果是Yes就表示主从同步正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
由截图中的主从同步状态信息可以看出，我们配置的主从同步是正常的

可查看master和slave上线程的状态。在master上，可以看到slave的I/O线程创建的连接：
Master : mysql> show processlist\G;
1.row 为处理slave的I/O线程的连接。
 2.row 为处理MySQL客户端连接线程。
 3.row 为处理本地命令行的线程。
Slave : mysql> show processlist\G;
如果未进行数据同步，可在slave上
mysql>show slave status
这时会显示同步过程中发现的错误




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

(1) mysql> reset slave;

(2) mysql> change master to master_host='192.168.1.177',master_user='mysql',master_password='mysql',master_port=3306,master_log_file='master-bin.000006',master_log_pos=120,master_connect_retry=30;

(此时，master_log_file和master_log_pos要在Master中用show master status 命令查看)

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

上面所搭建的是单向复制的主从，也是用的比较多的，而双向主从其实就是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
#vim /etc/selinux/config
将SELINUX=disabled