MySQL主从数据库搭建

1 背景

      最近工作需要对比几种数据库技术方案，主从读写分离集群也是其中之一。现将该集群搭建过程记录下来，以便后面查看回忆。

2 主从集群

2.1 原理

      主从复制的原理如下图所示：

2.2 集群划分

      我在搭建主从集群时已经使用用虚拟机安装了docker环境，制作了MySQL镜像，虚拟出来2个MySQL数据库节点。我用的MySQL数据库版本是5.7.26版本。
具体集群划分如下：

2.3 配置

      修改数据库配置文件和配置信息

2.3.1 主节点

      修改主节点my.cnf配置文件

[mysqld]
# 设置字符集
character-set-server=utf8mb4
# 设置排序规则
collation-server=utf8mb4_general_ci
# 允许最大连接数
max_connections=200
# 设置临时表最大大小
tmp_table_size=128M
# 设置最大内存使用量
max_heap_table_size=128M
server-id=1
log-bin=mysql-bin
binlog-do-db=db1
binlog-ignore-db=mysql
binlog-ignore-db=information-schema
binlog_format=statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3.2 从节点

      修改从节点my.cnf配置文件

[mysqld]
server-id=2
relay-log=mysql-relay

1
2
3
4

2.3.3 重启主从节点数据库服务

docker restart mysql
1

2.3.4 在主节点创建slave账号并授权

create user 'slave'@'%' IDENTIFIED by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
1
2

2.3.5 修改配置参数

步骤1：登录主节点，查询master的状态

msql -uroot -p123456
1

show master status
1

执行结果如下：

记录File和Position两列的值。用以配置从节点
步骤2：登录从节点，配置需要复制的主机
登录从节点

msql -uroot -p123456
1

CHANGE MASTER TO MASTER_HOST='主节点IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='刚刚记录的File值',MASTER_LOG_POS=刚刚记录的Position值;
1
2
3
4

示例：

CHANGE MASTER TO MASTER_HOST='172.16.17.231',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
1
2
3
4

步骤3：启动从节点复制功能

start slave;
1

步骤4：查看从节点状态

show slave status\G
1

执行结果：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.17.231
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             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: 154
              Relay_Log_Space: 570
              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: 1
                  Master_UUID: be75605d-4620-11ee-8b15-0242ac110002
             Master_Info_File: /var/lib/mysql/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: 
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

查看Slave_IO_Running、Slave_SQL_Running属性都是Yes，则说明主从配置成功。

3 测试

      主数据库创建一张表并插入一条数据，从节点也做出了相应的变更即代表成功。