基于 binlog 文件进行 MySQL 主从搭建

2019-11-11 本文已影响0人 x0e

前言

环境 : CentOS 7.7 丶Docker 19.03.4丶MySQL 8.0.18

本教程仅用于对MySQL主从搭建方案进行说明示例,本文只针对通过binlog日志文件进行数据同步的方案,对其他复制方案不做讨论,文中提及相关知识参考自MySQL官方文档 :

环境准备

服务器准备

本文使用两台CentOS 7.7服务器进行测试

节点	hostname	IP
`Master`	node1	172.16.146.38
`Slaver`	node2	172.16.146.39

多节点部署

在两个服务器主机分别部署MySQL,本文仅用于说明主从搭建示例,对MySQL部署在此不过多阐述,详情请参考 :

主从复制

修改配置文件

--------------修改 my.cnf 文件----------------

# Master 节点 my.cnf 文件修改
server-id=38                # 保证局域网内唯一,一般为ip后缀  
log-bin=mysql-bin               # 开启二进制日志功能,可以随便取（关键）

## Slaver 节点 my.cnf 文件修改
server-id=39                    # 保证局域网内唯一,一般为ip后缀
log-bin=mysql-slave-bin         # 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
relay_log=edu-mysql-relay-bin   # relay_log配置中继日志

docker restart [容器ID]                   # 重启MySQL容器

创建用户

## 进入 Master 容器
docker exec -it [容器ID] bash

## 连接 MySQL,密码账号需自行确认
mysql -uroot -ppassword

## 创建用户,同步账号尽量采用非root账号
CREATE USER 'slave'@'%' IDENTIFIED BY 'password';    

## 用户授权
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password 
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

配置连接

## 在 Master节点执行SQL语句
SHOW MASTER STATUS;

结果如下 :

  +------------------+----------+--------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000002 |      155 |              |                  |                   |
  +------------------+----------+--------------+------------------+-------------------+
  1 row in set (0.00 sec)

File和Position用于标识当前日志记录文件及记录位置,所以在此过程中需保证Master节点不做任何操作,否则将会引起状态变化,从而导致File和Position值发生改变

  ## 在 Slaver 节点执行,指定Master节点,配置主从同步
CHANGE MASTER TO MASTER_HOST='[Master节点IP]', MASTER_USER='slave',     MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002',   MASTER_LOG_POS= 155, MASTER_CONNECT_RETRY=30;

命令说明 :

MASTER_HOST : Master 节点IP地址

MASTER_PORT : Master 节点端口号,指定是容器的端口号(这里是3306)

MASTER_USER : 用于同步的用户(这里是上文创建的用户slave)

MASTER_PASSWORD :用于同步的密码

MASTER_LOG_FILE : 指定Slave从哪个日志文件开始复制数据

MASTER_LOG_POS : 指定从哪个位置开始读(这里是上文提及的Position)

MASTER_CONNECT_RETRY : 连接失败时,重试时间间隔,默认60(单位 :秒)

开启复制
```
START SLAVE;
```

查看状态

 SHOW SLAVE STATUS \G;

结果如下 :

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.146.38
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 155
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            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: 155
              Relay_Log_Space: 534
              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: 0
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: 3306
                  Master_UUID: ee2d4bbe-006b-11ea-a27a-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

查看Slave_IO_Running和Slave_SQL_Running状态是否为Yes,若为No则表示主从复制未开启,若为Connecting请检查Master端口是否可以ping通,排查是否为防火墙未开放指定端口

主主复制

因为这里都为Master节点,所以这里暂把原来的Master节点叫Master-one,把Slaver节点叫`Master-two

修改配置文件

## 配置 Master-one
auto_increment_increment=2         # 步进值auto_imcrement,一般有n台主MySQL就填n
auto_increment_offset=1            # 起始值,一般填第n台主MySQL,此时为第一台主MySQL

## 配置 Master-two
auto_increment_increment=2         # 步进值auto_imcrement,一般有n台主MySQL就填n
auto_increment_offset=2            # 起始值,一般填第n台主MySQL,此时为第二台主MySQL

配置完成,重启MySQL容器

创建用户

## 进入 Master 容器
docker exec -it [容器ID] bash

## 连接 MySQL,密码账号需自行确认
mysql -uroot -ppassword

## 创建用户,同步账号尽量采用非root账号
CREATE USER 'slave'@'%' IDENTIFIED BY 'password';    

## 用户授权
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password 
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

配置连接

## 在 Master-two 节点执行
SHOW MASTER STATUS;

结果如下 :

+------------------+----------+--------------+------------------+--------------------+
| File                   | Position| Binlog_Do_DB| Binlog_Ignore_DB|Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+
| mysql-slave-bin.000002 |  1180   |             |                 |                 |
+------------------+----------+--------------+------------------+--------------------+
1 row in set (0.00 sec)

## 在 Master-one 节点执行,指定Master节点,配置主从同步
CHANGE MASTER TO MASTER_HOST='[Master-two节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30;

开启复制

  ## 开启同步,在 Master-one 节点执行
  START SLAVE;

查看状态

 SHOW SLAVE STATUS \G;

结果如下 :

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.146.39
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-slave-bin.000012
          Read_Master_Log_Pos: 2347
               Relay_Log_File: 7c9e945c6c27-relay-bin.000002
                Relay_Log_Pos: 521
        Relay_Master_Log_File: mysql-slave-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1008
                   Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2161
              Relay_Log_Space: 922
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1008
               Last_SQL_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 39
                  Master_UUID: af712e6d-02da-11ea-a2f3-0242ac110002
             Master_Info_File: mysql.slave_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: 191111 04:25:15
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

问题排查

## 若出现数据不一致导致同步报错,执行如下操作,必要的时候需要进行锁表操作
## 锁定主数据库,只允许读取不允许写入加锁,保证主从数据一致
FLUSH TABLES WITH READ LOCK;

## 查看主节点 binlog 读取文件及位置
SHOW MASTER STATUS;

---------以下在从节点执行----------

## 关闭从节点数据同步
STOP SLAVE;

## 重置主从复制配置
RESET SLAVE;

## 配置连接主节点
CHANGE MASTER TO MASTER_HOST='[Master-two节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30;

## 开启主从同步
START SLAVE;

## 查看主从同步状态
SHOW SLAVE STATUS \G;

## 若同步状态OK, 释放锁
UNLOCK TABLES;

双主多从

创建账号

## 进入 Master 容器
docker exec -it [容器ID] bash

## 连接 MySQL,密码账号需自行确认
mysql -uroot -ppassword

## 创建用户,同步账号尽量采用非root账号
CREATE USER 'slave'@'%' IDENTIFIED BY 'password';    

## 用户授权
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password 
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

配置连接

## 查看主节点 binlog 读取文件及位置
SHOW MASTER STATUS;

## 配置连接主节点
CHANGE MASTER TO MASTER_HOST='[Slaver节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30;

开启复制

## 开启主从同步
START SLAVE;

## 查看主从同步状态
SHOW SLAVE STATUS \G;

问题处理

## 在双主多从模式下,从库仅连接了一个主库,在另一个主库进行数据操作时,从库不会同步过来,所以需要在双主节点的 my.cnf 文件中添加如下: 
log-slave-updates=on

## 保存并重启双主节点
docker restart [容器ID]

至此,本教程结束,文中所述三种方式都是基于binlog日志文件进行数据同步,故数据同步会有延迟,此方式适用于对数据一致性要求不高的数据备份需求场景

基于 binlog 文件进行 MySQL 主从搭建

前言

环境准备

服务器准备

多节点部署

主从复制

修改配置文件

创建用户

配置连接

开启复制

查看状态

主主复制

修改配置文件

创建用户

配置连接

开启复制

查看状态

问题排查

双主多从

创建账号

配置连接

开启复制

问题处理

猜你喜欢

热点阅读