MySQL配置主从复制

发表于 分类于 阅读次数： 本文字数： 762 阅读时长 ≈ 3 分钟

MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点，MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表

准备

192.168.1.104 # master
192.168.1.105 # slave

主从复制步骤

  1. 主库的更新事件会被写到 bin log 日志中
  2. 从库启用 slave 服务连接到主库
  3. 从库创建一个 I/O 线程，从主库读取 bin log 日志的内容并写入到 relay log 日志中
  4. 从库创建一个 SQL 线程，从 relay log 里读取内容并写入

master 配置

vim /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[mysqld]
# 服务器唯一ID,一般取IP最后一段
server-id=104
# 启用二进制日志
log-bin=mysql-bin
# 日志格式
binlog-format=mixed
# 需要同步的数据库
binlog-do-db=test_db
# 不需要同步的数据库
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 重启 mysql 服务
service mysqld restart

# 进入 mysql
mysql -uroot -p
# 创建用户并授权
create user 'slave'@'%' identified by '123456';
grant replication slave on *.* to 'slave'@'%';
# 刷新权限
flush privileges; 
# 查看主节点配置（记着 File 和 Position 等会要用到）
show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     1677 | test_db      | sys,mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

slave 配置

vim /etc/my.cnf

1
2
3
4
5
[mysqld]
# 服务器唯一 ID,一般取 IP 最后一段
server-id=105
# 设置只读
super_read_only=1
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# 重启 mysql 服务
service mysqld restart

# 进入 mysql
mysql -uroot -p
# 关闭从节点的复制功能
stop slave;
# 配置从节点
change master to master_host='192.168.1.104',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1677; 
# 刷新权限
flush privileges;
# 启动从节点的复制功能
start slave; 
# 检查从节点复制功能状态
show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.104
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1677
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 324
        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: 1677
              Relay_Log_Space: 537
              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: 104
                  Master_UUID: 0478182c-284e-11ec-9539-000c29ecfc3e
             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)

测试

在主库创建，会同步到从库

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE test_db;

USE test_db;

CREATE TABLE `test_user` (
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test_db`.`test_user` (`username`, `password`) VALUES ('slave', '123456');

问题处理

使用命令 show slave status\G 后出现错误提示 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

原因：这是由于从服务器是克隆的主服务器系统，导致主从 mysql uuid 相同

解决方案：

1
2
3
4
5
6
7
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 42affe55-2995-11ec-b52e-000c29c49344 |
+--------------------------------------+
1 row in set (0.00 sec)

vim ./data/auto.cnf

1
2
[auto]
server-uuid=42affe55-2995-11ec-b52e-000c29c49344