MySQL 主从复制 实例讲解

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

一、什么是主从复制
将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

二、主从复制的作用
1、主数据库出现问题，可以切换到从数据库。
2、可以进行数据库层面的读写分离，
3、可以在从数据库上进行日常备份

三、复制过程

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。
实现MySQL

主从复制需要进行的配置
主服务器：
开启二进制日志
配置唯一的 server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号
从服务器：
配置唯一的 server-id
使用master分配的用户账号读取master二进制日志
启用slave服务

环境准备
准备好两台云主机或者虚拟机
我准备的为 京东云 和 腾讯云 的两台主机 ，系统为centos 7.4
主从数据库的版本最好可以一致
本实例讲解中为 Mysql 5.7.22
主从数据库的内容最好可以一致
本次实例讲解中，Mysql 数据库都为空

主数据库 master 配置
修改 my.cnf 文件 在 [mysqld] 加上如下的配置

[mysqld]
log-bin=mysql-bin     #开启二进制日志
server-id=1           #设置server-id
character_set_server=utf8
init_connect='SET NAMES utf8'

重启 mysql 如果是yum安装可以使用 systemctl restart mysqld
登陆Mysql，创建用于同步的用户账号

CREATE USER 'repl'@'139.199.***.***' IDENTIFIED BY 'YourPassword9#';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'139.199.***.***';
FLUSH PRIVILEGES;
139.199.***.***  这里填上自己从服务器的 ip 

查看master状态，记录二进制文件名 mysql-bin.000001 和位置 2930

mysql>  SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2930 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从数据库 slave 设置
修改 my.cnf 文件 在 [mysqld] 加上如下的配置

[mysqld]
server-id=2           #设置server-id
character_set_server=utf8
init_connect='SET NAMES utf8'

重启 mysql 如果是yum安装可以使用 systemctl restart mysqld
登陆Mysql，并执行同步SQL语句

mysql> CHANGE MASTER TO
->     MASTER_HOST='116.196.***.***',             # 主服务器ip
->     MASTER_USER='repl',                        # 主服务器登陆名
->     MASTER_PASSWORD='YourPassword9#',          # 主服务器登陆密码
->     MASTER_LOG_FILE='mysql-bin.000001',        # 二进制文件的名称
->     MASTER_LOG_POS=2930;                       # 二进制文件的位置

启动 slave 同步进程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看 slave 状态

mysql> show slave status\G;
如果以下两项都是 yes 就表示主从同步设置成功了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes