主从复制

概述

有两台MySQL数据库服务器master和slave，master为主服务器，slave为从服务器，初始状态时两台服务器数据相同，当master数据变化时slave中的数据同步变化，达到备份的目的。

原理

Mysql 之间数据复制的基础是二进制日志文件，slave服务器通过一个 I/O线程与master主服务器保持通信，并监控master的二进制日志文件的变化，则会把变化复制到自己的日志中并执行相关的数据库操作，从而实现了主从复制。

准备

1. 服务器均为Centos7系统，在主服务器（Master）：192.168.71.128，从服务器（slave）：192.168.71.129中分别安装mysql5.7，建议保证数据一致，如果两个mysql是复制来的相同镜像，需要修改uuid，两者不能相同，编辑/var/lib/mysql/auto.cnf

[auto]
server-uuid=a4c933fe-b1ae-11e8-b05a-000c296fb796

2. 在Master服务器上添加远程访问用户，用于slave服务器访问

CREATE USER 'slave'@'192.168.71.128' IDENTIFIED WITH mysql_native_password BY 'slave'; # 创建用户
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'192.168.71.128'; # 分配权限
FLUSH PRIVILEGES # 刷新权限

• 这里注意mysql5.7默认使用caching_sha2_password密码策略，这里要手动指定mysql_native_password密码策略，后文才能实现主从通信。

3. 关闭防火墙 / 开启访问端口

systemctl stop firewalld  # 关闭防火墙
systemctl status firewalld  #检查防火墙状态

主服务器（Master）配置

1. 在主服务器上，必须启用二进制日志记录并建立唯一的服务器其标识，编辑/etc/my.cnf，在[mysqld]下添加log-bin和server-id

[mysqld]
....
log-bin=mysql-bin # 开启二进制日志记录
server-id=1      # 保证唯一，与从服务器（slave）不同

• 保存并重启mysql服务。

2. 配置主从通信的用户：使用前文添加的远程访问用户

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.71.128';
flush privileges;   #刷新权限

3. 在主服务器（Master）查看状态并记录日志file和日志position的值，用于后面从服务器的配置

SHOW MASTER STATUS

从服务器（slave）配置

1. 同样编辑 /etc/my.cnf，添加唯一标识

[mysqld]
....
server-id=2   # 保证唯一，与主服务器（master）不同

• 保存并重启mysql服务。

2. 配置主从复制：执行SQL语句

mysql> CHANGE MASTER TO
    ->  MASTER_HOST='192.168.71.128',    # 主服务器ip  
    ->  MASTER_USER='slave',             # 主从通信用户账号
    ->  MASTER_PASSWORD='slave',         # 主从通信用户密码
    ->  MASTER_LOG_FILE='mysql-bin.000003',  # 主服务器配置中的日志文件
    ->  MASTER_LOG_POS=73;  # 主服务器配置中的日志文件位置

3. 启动同步进程

mysql> start slave;  # 开启slave
mysql> stop slave;  # 关闭slave

4. 在从服务器（slave）查看同步状态

mysql> show slave status\G;

• 出现两个Yes说明成功启动。

同步不一致情况

主库宕机

• 编辑主库配置/etc/my.cnf， 确保每次事务提交之后都能将二进制日志同步到磁盘上

[mysqld]
....
sync_binlog=1 
innodb_flush_log_at_trx_commit = 1

从库宕机

• 编辑从库配置 /etc/my.cnf， 确保从库读取日志位置信息同步到磁盘上

[mysqld]
....
sync_relay_log = 1          # 默认为10000，即每10000次事件才会刷新到磁盘
master_info_repository = TABLE     # 将位置信息存放到表 
relay_log_info_repository = TABLE
sync_master_info = 1               # 为1则每次事件会更新到存放表
sync_relay_log_info = 1

从库同步报错

跳过报错步骤，重新同步

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; # 跳过1个操作步骤
mysql> start slave ;