mysql 同步 有log_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)

可以看到数据已经同步。