MySql搭建主从复制

LiJunYi 5/17/2022 mysql

# MySql搭建主从复制✨

主从复制（也称AB复制)允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器(从服务器)。

复制是异步的从站不需要永久连接以接收来自主站的更新。

# 工作原理

master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件，binary log events)；
slave将master的binary log events拷贝到它的中继日志(relay log)；
slave重做中继日志中的事件，将改变反映它自己的数据。

mysql

# 搭建主从复制

数据库的安装，可以参考这篇博文，默认已经安装好MySQL，且有两台服务器

# 1、Master 节点配置

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
## 同一局域网内注意要唯一
server-id=100

## 开启二进制日志功能，可以随意取(关键)
log-bin=mysql-bin

##设置 binlog_format 格式
binlog_format=MIXED

## 复制过滤：不需要备份的数据库，不输出(mysql库一般不同步)
binlog-ignore-db=mysql

## 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M

## 主从复制的格式(mixed,statement,row，默认格式是statement)
binlog_format=mixed

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

# 2、slave节点配置

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
## 同一局域网内注意要唯一
server-id=102

## 开启二进制日志功能，可以随意取(关键)
log-bin=mysql-slave-bin

## 设置 binlog_format 格式
binlog_format=MIXED

## relay_log 配置中继日志
relay_log=edu-mysql-relay-bin

## 复制过滤：不需要备份的数据库，不输出(mysql库一般不同步)
binlog-ignore-db=mysql

## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true

## 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M

## 主从复制的格式(mixed,statement,row，默认格式是statement)
binlog_format=mixed

##跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
##如:1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

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

# 3、重启MySQL

配置结束之后需要重启MySQL

systemctl restart mysqld

# 4、在master服务器授权slave服务器可以同步权限(master节点执行)

mysql > mysql -uroot -pmaster的密码
# 授予slave服务器可以同步master服务
mysql > grant replication slave,replication client on *.* to 'root'@'slave服务的ip' identified by 'slave服务器的密码';
mysql > flush privileges;

# 查看MySQL现在有哪些用户及对应的IP权限(可以不执行，只是一个查看)
mysql > select user,host from mysql.user;

# 5.7密码策略加强，可能会遇如下错误
mysql> grant replication slave,replication client on *.* to 'root'@'115.236.38.120' identified by 'admin123';
# ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to 'root'@'115.236.38.120' identified by 'admin123';
Query OK, 0 rows affected, 1 warning (0.01 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

# 5、查看MySQL现在有哪些用户及对应的IP权限

[root@localhost ~]# mysql -uroot -padmin123
mysql> select user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| root          | %              |
| root          | 115.236.38.120 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

# 6、查询master服务的binlog文件名和位置(master节点执行)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      622 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# 7、slave进行关联master节点(slave节点执行)

这里注意一下 master_log_file和 master_log_pos 都是 master 服务器通过 show master status 获得。

> change master to master_host='master服务器ip', master_user=' root ', master_password='master密码', master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=622

mysql> change master to master_host='115.236.38.101', master_user='root', master_password='admin123', master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=622;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 115.236.38.101
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
 #  两个都为yes表示成功
             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: 531
              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: 100
                  Master_UUID: 2b66e049-8ab0-11eb-8601-000c29e77dc3
             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)

ERROR: 
No query specified

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

# 问题排查

# 主从复制Connecting 问题

mysql

网络不通
检测IP、端口
密码不对
检查是否创建用于同步的用户和密码是否正确
pos不对
检查Master的 Postion

# MYSQL镜像服务器因错误停止的恢复—Slave_SQL_Running: No

> stop slave;
> set global sql_slave_skip_counter=1;
> start slave;
> show slave status\G;

1
2
3
4

# 从MYSQL服务器Slave_lO_Runnng: No的解决

master节点执行，获取日志文件和post

> show master status;

slave节点重新绑定

> stop slave;
> change master to master_host='115.236.38.101', master_user='root', master_password='admin123', master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=622;
> start slave;

1
2
3

我的开发笔记

Choose mode