MySQL之主从同步
一、主从同步概念
1.1 什么是主从同步？
MySQL 主从同步是指将数据从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
1.2 主从同步使用场景
- 数据备份
- 读写分离
- 高可用
1.3主从同步原理
MySQL 主从同步是基于拷贝 binlog日志 来实现主从复制。
1）主数据库将变更记录写入到二进制日志文件中
2) 从数据库读取主数据库的二进制日志文件并写入到从数据库的 relay_log 文件中
* 基于日志点的复制
* 基于 GTID 的复制
3) 从数据库执行 relay_log 文件的日志
* 基于 SBR 模式会在从数据库上重新执行记录的 SQL
* 基于 RBR 模式会在从数据库上直接对数据库行做修改
二、准备环境
-
拉取
MySQL镜像
[root@VM_0_15_centos conf]# docker pull mysql:5.7
- 创建 docker 自定义网络段
[root@VM_0_15_centos /]# docker network create --subnet=192.168.0.0/16 mynetwork
- 创建三个
MySQL数据库容器
[root@VM_0_15_centos data]# docker run -di --name=my_01 --net mynetwork --ip 192.168.0.2 -p 33306:3306 -v /data/mysql/my1/conf:/etc/mysql/conf.d -v /data/mysql/my1/data:/etc/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
[root@VM_0_15_centos data]# docker run -di --name=my_02 --net mynetwork --ip 192.168.0.3 -p 33307:3306 -v /data/mysql/my2/conf:/etc/mysql/conf.d -v /data/mysql/my2/data:/etc/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
[root@VM_0_15_centos data]# docker run -di --name=my_03 --net mynetwork --ip 192.168.0.4 -p 33308:3306 -v /data/mysql/my3/conf:/etc/mysql/conf.d -v /data/mysql/my3/data:/etc/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
得到如下容器列表：
| 容器名称 | 容器IP |
|---|---|
| my_01 | 192.168.0.2 |
| my_02 | 192.168.0.3 |
| my_03 | 192.168.0.4 |
注：通过 --net mynetwork 命令将容器加入到自定义网络中去，以保证容器之间的通信正常。
三、配置主从同步
3.1 配置 my_01 为主数据库
- 进入
my_01容器
[root@VM_0_15_centos data]# docker exec -it my_01 bash
root@fcfd79f4beff:/# mysql -uroot -p
Enter password:
- 创建一个同步权限的账户
mysql> create user 'sync'@'%' identified by '654321';
- 赋予
FILE权限
mysql> grant FILe on *.* to 'sync'@'%' identified by '654321';
- 赋予主从同步权限
grant replication slave on *.* to 'sync'@'%' identified by '654321';
- 刷新权限表
mysql> flush privileges;
- 退出容器并编辑配置文件
[root@VM_0_15_centos conf]# vim /data/mysql/my1/conf/my.cnf
编辑如下内容：
[mysqld]
#为服务器设置一个独一无二的id 便于区分，这里使用ip地址的最后一位充当server-id
server_id=2
#设置 binlog 文件存放路径
log_bin=mysql-bin
# 设置 binlog 格式
binlog_format=mixed
- 重启
my_01容器,使修改配置生效
[root@VM_0_15_centos conf]# docker restart my_01
8）进入容器，查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1104 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.2 配置 my_02 为从数据库
- 编辑配置文件
[root@VM_0_15_centos conf]# vim /data/mysql/my2/conf/my.cnf
编辑如下内容：
[mysqld]
#为服务器设置一个独一无二的id 便于区分，这里使用ip地址的最后一位充当server-id
log_bin=mysql-bin
server_id=3
#只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户（如超级管理员root用户）的数据修改操作。
read_only=1
# 固定文件名，默认情况下是主机名称，如果主机名称调整了会导致复制中断
relay_log=mysql-relay-bin
- 重启
my_02容器,使修改配置生效
[root@VM_0_15_centos conf]# docker restart my_02
- 进入
my_02容器
[root@VM_0_15_centos data]# docker exec -it my_02 bash
root@fcfd79f4beff:/# mysql -uroot -p
Enter password:
- 查看从库状态
mysql> show slave status
没有配置过从库的话就会出现下面的提示:
Empty set (0.00 sec)
否则的话就需要执行如下命令关闭从库：
stop slave;
- 设置复制链路
mysql> change master to
master_host="192.168.0.2",
master_user="sync",
master_password="654321",
master_log_file="mysql-bin.000003",
master_log_pos=1104;
- 启动从库
mysql> start slave;
- 查看从库状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.18.0.2
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1256
Relay_Log_File: 4928c749d0a0-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
Slave_SQL_Running: Yes #具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1256
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: null #判断主动同步延时的参考值，是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
可以看到已经变为从数据库了并且通信也正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果出现 No 的情况就要查询问题原因并针对性处理。在本次演示过程中我犯了一个错误就是先配置了 my_01 的 binlog 选项然后在创建复制账号授权，导致主数据库 binlog 日志已经记录了创建和授权语句，而从数据库无法执行该语句导致复制中断，解决方案就是在从数据库中跳过这一个事件：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
重复步骤将 my_03 容器也变为从数据库。
注意：如果主库里面有初始化数据，那么在主从复制前需要把数据先导入到从库以保证初始一致性！
四、验证数据同步
- 在主数据库上创建数据库
test
create database test;
- 在主数据库上创建表
mysql> use test;
Database changed
mysql> create table sys_user(id int ,name varchar(50));
Query OK, 0 rows affected (0.06 sec)
- 在主数据库上插入三条记录
mysql> insert into sys_user values(1,'a');
Query OK, 1 row affected (0.02 sec)
mysql> insert into sys_user values(2,'b');
Query OK, 1 row affected (0.02 sec)
mysql> insert into sys_user values(3,'c');
Query OK, 1 row affected (0.01 sec)
5）修改一条记录，删除一条记录
mysql> update sys_user set name='d' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from sys_user where id=3;
Query OK, 1 row affected (0.03 sec)
- 进入任一从数据库，查看数据是否同步
mysql> select * from sys_user;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | d |
+------+------+
2 rows in set (0.00 sec)
可以看到数据已经同步。
