一主一从复制

# 50.一主一从复制

我们通过 Mycat 和 MySQL 的主从复制功能，搭建数据库的读写分离，实现 MySQL 的高可用性。

我们先搭建一主一从，读写分离，也就是一个主机用于处理所有写请求，一台从机负责所有读请求，架构图：　　

‍

# MySQL 主从复制原理

我们可以回顾下Redis的主从复制，一旦配置了从机，主机将rdb数据文件给到从机，从机替换自己的rdb文件，然后读回内存里，主机做的任何操作，从机都会实时复制。

‍

MySQL稍有不同。Master会将所有写操作写进一个叫binlog的二进制日志文件；

从机不直接访问主机，而是读取binlog日志（IO），写入relaylog中继日志，然后再执行写操作，示意图：

‍

可以看到，MySQL复制存在延时问题：因为读取binlog有IO操作，写入relaylog也有IO操作，执行写操作也有IO操作，而IO操作是很慢的

‍

与Redis对比：

相同点：主机只能有一个，从机可以有多个
不同点：Redis是从头开始复制；而MySQL主从数据复制从接入时间点开始，Master在开启主从复制之前的记录，与从机无关；也就是说如果某个表有100w数据，然后开启了主从复制，那么从机不会从头开始复制

‍

# 搭建主从复制

接下来我们开始搭建，这里我选192.168.56.101作为主机，192.168.56.103作为从机。配置主从是通过修改MySQL的配置文件 /etc/my.cnf来实现，在修改前读者可以先自己备份下该文件。

‍

# 配置Master

这里是192.168.56.101 原始的my.cnf文件的内容（已去掉注释）：

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1
2
3
4
5

‍

然后我们在下追加这样的配置（加了注释，配置的时候可以去掉）：

# 主服务器唯一ID，每台机都得配，而且id不能一样
server-id=1


# 启用二进制日志
log-bin=mysql-bin


# 设置需要复制的数据库（重要）
binlog-do-db=mydb1


# 设置不要复制的数据库(可设置多个)，例如MySQL自带的4个库，其实设置了要复制的数据库后，这里不设置也可以
binlog-ignore-db=mysql
binlog-ignore-db=information_schema


# 设置binlog格式，默认STATEMENT
binlog_format=STATEMENT

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

注意，配置要复制的数据库的时候，最好配置一个还没建立的数据库。如果在配置之前，已经建了数据库，那么配置之后，由于建立数据库的语句没有同步到从机，从机就不会建立这个数据库，导致后续写操作报错

‍

binlog 日志，可以有三种格式：

statement：所有写操作（执行的SQL）都写到binlog里。缺点：如果Master执行了获取时间的sql，那么由于存在延迟，从机执行获取时间的SQL后，两者的时间是不一致的，这会导致数据不一致。
ROW：行模式，不记录写SQL，记录每一行的改变，然后从机同步修改该行，这样数据就可以一致。缺点：如果有全表更新，记录就会全部变化，从机也会全表更新，效率很低
MIXED：自动选择。如果SQL里有函数（例如获取时间的函数）则是行模式，如果没有则用statement模式。

缺点：识别不了系统变量。两个@符号后面一个字符串，就是系统变量。比如@@hostname就是Linux主机名。读者可以在MySQL中执行SELECT @@hostname，查看结果，这是和在Linux中执行hostname的结果是一样的。

‍

# 配置从机

从机的配置修改起来就简单，我们修改从机 192.168.56.103 的配置文件，添加如下内容：

# 从服务器唯一ID
server-id=2

# 启用中继日志
relay-log=mysql-relay

1
2
3
4
5

‍

# 重启MySQL

我们重启下两个服务器的MySQL服务：systemctl restart mysqld

重启完后可以查看下状态：systemctl status mysqld

‍

# 在Master上创建用户和授权

数据库的数据，是敏感的，日志文件也是敏感的，不能说谁想看就能看，如果从机想要读取，得先在Master上授权，这里在Master上新建一个用户用来授权：

create user 'slave'@'%' identified by 'learnMycatPassword0520@';
ALTER USER slave@'%' IDENTIFIED WITH mysql_native_password BY 'learnMycatPassword0520@';

grant replication slave on *.* to 'slave'@'%';
flush privileges;

1
2
3
4
5

‍

在MySQL 8之后，加密规则默认是caching_sha2_password，该规则对安全要求较高，后续从机连接的时候会报错 Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

所以我们修改下加密规则为native的（第2行）。

‍

# 查看master状态

我们在Master上执行以下SQL：

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

1
2
3
4
5
6
7

能看到binlog日志的名字，position是接入点（就是从哪里开始复制的意思），这里我们要记下。后面几列是要复制的数据库，以及不用复制的数据库，一般配置一个就行

执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

‍

# 在从机上配置主从复制

以下操作在从机上执行。

我们先配置Master的信息，配置格式：

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog文件名', 
MASTER_LOG_POS='position的值';

1
2
3
4
5

例如，这是我的配置：

CHANGE MASTER TO MASTER_HOST='192.168.56.101',
MASTER_USER='slave',
MASTER_PASSWORD='learnMycatPassword0520@',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=858;

1
2
3
4
5

‍

然后开启主从复制功能：

start slave;

‍

查看从服务器状态：

show slave status\G;  --数据太多的情况下，加上\G就是按列的模式展现，而不是一行一行的展示

‍

如果下面两个参数都是Yes，则说明主从配置成功！

# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

1
2

‍

如果有问题，先看看Last_Error的值。例如我之前没有修改用户的密码机制，导致报错：

Last_IO_Error: error connecting to master 'slave@192.168.56.101:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

‍

# 停止主从复制功能

如果想要停止从机复制Master数据，在从机上执行：

stop slave;

‍

# 重新配置主从复制

如果从机配错master的IP了，可以重新配置，在从机的MySQL执行：

stop slave;
reset master;

1
2

‍

然后重新配置：

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog文件名', 
MASTER_LOG_POS='position的值';

1
2
3
4
5

‍

# 创建数据库

怎么验证主从配置是生效的呢？很简单，我们在master上新建数据库和表：

CREATE DATABASE mydb1;
use mydb1;


CREATE TABLE mytbl (id INT, NAME VARCHAR(50));
INSERT INTO mytbl VALUES (1, "zhang3");

1
2
3
4
5
6

然后查看从机是否有该数据库和表，数据是否一样。

‍

# 常见错误

# 密码规则问题

Last_IO_Error: Error connecting to source 'slave@192.168.56.101:3306'. This was attempt 3/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

原因：在MySQL 8之后，加密规则默认是caching_sha2_password，该规则对安全要求较高

解决方法：在master的MySQL里执行：

ALTER USER slave@'%' IDENTIFIED WITH mysql_native_password BY 'learnMycatPassword0520@';

‍

# 密码错误次数太多

Last_IO_Error: Error connecting to source 'slave@192.168.56.101:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Host '192.168.56.103' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因：当从机尝试很多次连接master，都不行的话，就会被master暂时“拉黑”

解决方法：在master服务器上执行mysqladmin flush-hosts -p，然后输入MySQL的root用户密码

‍

# Position对不上

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 1141. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

分析：从机配置主从复制的时候，由于有问题导致没配置成功，后续master的position有变化，而从机没有及时更新，导致报错。

‍

解决方法：重新配置主从复制。重置master：

stop slave;
reset master;

1
2

‍

重新配置master

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog文件名', 
MASTER_LOG_POS='position的值';

1
2
3
4
5

‍

开启主从复制：

start slave;

‍

在GitHub上编辑此页

上次更新: 2023/6/28 11:11:51

← MyCat的配置文件一主一从读写分离→