搭建MySQL5.6主从复制

概要：

MySQL版本：5.6

主节点ip：192.168.100.128

从节点ip：192.168.100.129

主从复制方式：基于日志（binlog）

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

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

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

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

mysql单向一主一从配置：

master:192.168.100.128  slave:192.168.100.129

Master端的配置：

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

[root@localhost ~]# vi /etc/my.cnf

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

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

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

[root@localhost ~]# service mysql restart

##创建数据同步用户repl，并授予相应的权限

mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.100.129' identified by '123456';
## 刷新授权表信息
mysql> flush privileges;、
## 查看MySQL现在有哪些用户及对应的IP权限

mysql> select user,host from mysql.user;

## 查看position号，记下file名称和position号（从机上需要用到这个position号和现在的日志文件)

mysql> show master status;

Master重启后会修改mysql-bin文件名（序号加1）
 
4、创建mydb库、表，并写入一定量的数据，用于模拟现有的业务系统数据库

create database if not exists mydb default charset utf8 collate utf8_general_ci;
use mydb;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `user_ame` 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` (user_name, pwd) VALUES ('yzr','123456'),('test','123456'),('you','654321');
 
5、为保证Master和Slave的数据一致，我们采用主备份，从还原来实现初始数据一致
## 先临时锁表

mysql> flush tables with read lock;
## 这里我们实行全库备份，在实际中，我们可能只同步某一个库，那也可以只备份一个库

[root@localhost ~]# mysqldump -p3306 -uroot -p --add-drop-table mydb > /tmp/master-mydb.sql;

## 解锁表
mysql> unlock tables;

## 注意：实际生产环境中大数据量（超2G数据）的备份，建议不要使用mysqldump进行比分，因为会非常慢。此时推荐使用 XtraBackup 进行备份。
 
将Master上备份的数据远程传送到Slave上，以用于Slave配置时恢复数据

[root@localhost ~]# scp /tmp/master-mydb.sql root@192.168.100.129:/tmp/

Slave端配置：

1、修改/etc/my.cnf文件：

[root@localhost ~]# vi /etc/my.cnf

## 在 [mysqld] 中增加以下配置项
## 设置server_id，一般设置为IP
server_id=129
## 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=mydb
##复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志，以备Slave作为其它Slave的Master时使用
log-bin=edu-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=edu-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
#read_only=1

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

[root@edu-mysql-02 ~]# service mysql restart

Slave上创建相同库：
create database if not exists mydb default charset utf8 collate utf8_general_ci;
use mydb;
导入数据

[root@localhost ~]# mysql -uroot -p mydb < /tmp/master-mydb.sql

3、登录Slave数据库，添加相关参数（Master的IP、端口、同步用户、密码、position号、读取哪个日志文件)

在master那边，执行：
flush logs;
show master status;记下File, Position。
在slave端，执行：
stop slave;
mysql> change master to master_host='192.168.100.128', master_user='repl', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=429, master_connect_retry=30;

此处的master_log_file的值为上面查询的file值，master_log_pos为Position值。
上面执行的命令的解释：
master_host='192.168.100.128'                  ## Master的IP地址
master_user='repl'                             ## 用于同步数据的用户（在Master中授权的用户）
master_password='123456'     ## 同步数据用户的密码
master_port=3306                              ## Master数据库服务的端口
master_log_file='edu-mysql-bin.000001'        ##指定Slave从哪个日志文件开始读复制数据（可在Master上使用show master status查看到日志文件名）
master_log_pos=429                         ## 从哪个POSITION号开始读
master_connect_retry=30  ##当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。
 
## 查看主从同步状态
mysql> show slave status\G;
可看到Slave_IO_State为空， Slave_IO_Running和Slave_SQL_Running是No，表明Slave还没有开始复制过程。

##保证各个db的uuid不一样
找到每个实例下data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可

## 开启主从同步
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 为处理slave的I/O线程的连接。
 2.row 为处理MySQL客户端连接线程。
 3.row 为处理本地命令行的线程。
 
Slave : mysql> show processlist\G;
1.row 为I/O线程状态。
2.row 为SQL线程状态。
3.row 为处理本地命令行的线程。
 
主从数据复制同步测试
(1) 在Master中的mydb库上变更数据的同步测试；
mysql> INSERT INTO `user` VALUES (4,'同步测试1','123456'),(5,'同步测试2','123456');
Master中添加完之后，登录Slave中查看数据是否已同步。
 
(2) 在Master上新建一个youdb库
mysql> create database if not exists youdb default charset utf8 collate utf8_general_ci;
在Slave中查看数据库
mysql> show databases;

重置主从同步设置

如果遇到同步出错，可在Slave上重置主从复制设置：
(1) mysql> reset slave;
(2) mysql> change master to master_host='192.168.100.128',
master_user='repl',
master_password='123456',
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中手动插入数据，那样数据就会不一致，主从就会断开，就需要重新配置了。

主从配置需要注意的点

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

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

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

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