MySQL主从复制原理解析与最佳实践

一、主从复制

1、复制的基本原理

• 主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程
• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中
• I/O 线程 ：负责在主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中执行
  

2、复制的基本原则

• 每个master可以有多个slave
• 每个slave只有一个master
• 每个slave只能有一个唯一的服务器ID

二、最佳实践

• 本次实践采用一主一从复制，一主为windows，一从为虚拟机Centos7，MySQL版本5.7保持一致

1、ping测试

• 虚拟机采用NAT连接，物理机关闭防火墙
• 双方互相进行ping测试，记录主从各自服务器的IP地址

2、主服务器配置

• 修改my.ini文件，默认在C:\ProgramData\MySQL\MySQL Server 5.7

主服务器唯一ID

[mysqld]
server-id=1

启用二进制文件

log-bin="mysqlbin"

关闭只读

#可选
read-only=0

忽略备份

#可选
binlog-ignore-db=mysql

设置备份

#可选
binlog-do-db=test

3、从服务器配置

从服务器唯一ID

[mysqld]
server-id=2

启用二进制文件

log-bin="mysqlbin"

4、主服务器设置

• CMD打开MySQL会话

创建用户

#可选
CREATE USER 'test'@'从服务器IP' IDENTIFIED BY 'test';
#CREATE USER 'test'@'192.168.189.129' IDENTIFIED BY 'test';

赋予权限

grant replication slave on *.* to 'test'@'192.168.189.129' identified by 'test';

刷新权限

flush privileges;

查看master状态

show master status\G;

记录二进制文件名和位置

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

5、从服务器设置

• CMD打开MySQL会话

执行同步SQL

#主服务器IP、用户名、密码、二进制文件名、位置
change master to master_host='192.168.189.1',
master_user='test',
master_password='test',
master_log_file='mysql-bin.000003',master_log_pos=73;

开启slave

start slave;

查看slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.92.172.80
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

• 当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。如果不成功，先关闭slave，重新进行从服务器设置

6、主从测试

• 在主服务器备份的数据库（本次为test）中插入一条数据，在从服务器中验证是否复制成功（成功）
• 在主服务器中新建一个数据库，插入多条数据，在从服务器中验证是否复制成功（备份指定数据库，复制失败）
• 关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了

7、关闭slave

stop slave;