从01开始
2023-06-28
目录

双主双从复制方案

# 70.双主双从复制方案

接下来我们搭建双主双从的环境。 　　一个主机 m1 用于处理所有写请求，从机 s1，主机 m2 和 从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机：

​​

对于Java应用而言，不需要关系哪个数据库是master，哪个是从机，只需将请求发送到MyCat即可。

‍

# 环境说明

这里说明下环境：

编号 角色 IP地址 机器名
1 Master1 192.168.56.101 node1
2 Slave1 192.168.56.103 node2
3 Master2 192.168.56.104 node3
4 Slave2 192.168.56.105 node4

‍

# 配置MySQL

在开始配置之前，由于我们还是打算使用mydb1来复制，因此可以先删除该数据库：

DROP DATABASE IF EXISTS mydb1;
1

‍

‍

# 配置master1

/etc/my.conf​：

#主服务器唯一ID
server-id=1


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


# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema


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


#设置logbin格式
binlog_format=STATEMENT


# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates


#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是 1 .. 65535
auto-increment-increment=2


# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=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

‍

和之前的配置相比，主要是多了后续的3个配置。

为什么作为从机时，也要写入日志：这是因为master1有可能宕机，恢复后就会作为从机，此时也需要写入日志，这样slave1才能读取到写入的操作。

为什么自增长量设置为2：因为有两个master，想要区分开来。然后下一行设置从1开始自增，这样数字就是1,3,5,7,9....

‍

‍

# 配置master2

#主服务器唯一ID
server-id=3


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


# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema


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


#设置logbin格式
binlog_format=STATEMENT


# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates


#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是 1 .. 65535
auto-increment-increment=2


# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2
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

注意server-id要修改。注意，自增的时候我们从2开始，这样数字就是2,4,6,8.....

‍

‍

# 配置从机

从机的配置就比较简单了，例如slave1：

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


#启用中继日志
relay-log=mysql-relay
1
2
3
4
5
6

‍

‍

slave2：注意server-id要改

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


#启用中继日志
relay-log=mysql-relay
1
2
3
4
5
6

‍

# 重启MySQL

我们重启每台机器上的MySQL服务：systemctl restart mysqld​

重启完后记得检查下是否正常：systemctl status mysqld​

注意检查防火墙是否开放了端口，或者直接关闭防火墙

‍

# 在master上创建用户并授权

为了方便区分，我们在两个master上新建一个用户：

create user 'slave2'@'%' identified by 'Mycat0520@';
ALTER USER slave2@'%' IDENTIFIED WITH mysql_native_password BY 'Mycat0520@';

grant replication slave on *.* to 'slave2'@'%';
flush privileges;
1
2
3
4
5

‍

# 查询Master的状态

查询master1的状态：

mysql> show master STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     157  | mydb1        |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

‍

master2：

mysql> show master STATUS;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |     1157 | mydb1        | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

分别记录下File和Position的值。执行完此步骤后不要再操作MYSQL，防止主服务器状态值变化

‍

‍

# 配置slave

配置Slava1 复制 Master1，Slava2 复制 Master2，配置的格式：

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

‍

‍

我们在slave1上执行：

CHANGE MASTER TO MASTER_HOST='192.168.56.101',
MASTER_USER='slave2',
MASTER_PASSWORD='Mycat0520@',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=157;
1
2
3
4
5

‍

在slave2上执行：

CHANGE MASTER TO MASTER_HOST='192.168.56.104',
MASTER_USER='slave2',
MASTER_PASSWORD='Mycat0520@',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=1157;
1
2
3
4
5

‍

‍

然后开启复制，在两台slave上都执行：

start slave;
1

‍

检查状态：

show slave status\G;
1

‍

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

# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
1
2

‍

# 配置双主之间互相复制

现在我们搭建好了Master1和slave1之间的复制，以及Master2和slave2之间的复制，还差两个master之间的复制了：

​​

‍

配置Master2 复制 Master1，在Master2中执行：

CHANGE MASTER TO MASTER_HOST='192.168.56.101',
MASTER_USER='slave2',
MASTER_PASSWORD='Mycat0520@',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=157;
1
2
3
4
5

‍

配置Master1 复制 Master2，在Master1中执行：

CHANGE MASTER TO MASTER_HOST='192.168.56.104',
MASTER_USER='slave2',
MASTER_PASSWORD='Mycat0520@',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=1157;
1
2
3
4
5

‍

‍

然后在两个master上开始复制：start slave;​

start slave;
1

‍

查看状态：show slave status\G;​

‍

# 创建数据库

此时我们配置好后，就可以创建数据库和表了：

create database mydb1;
use mydb1;
CREATE TABLE mytbl (id INT, NAME VARCHAR(50));
INSERT INTO mytbl VALUES (1, "zhang3");
1
2
3
4

‍

然后我们可以在其他MySQL上查看是否有数据：

 select * from mytbl;
1

‍

# 修改MyCat的集群配置

明确角色划分：m1主机，m2备机（也负责读），s1从机，s2从机

增加m2和s2两个数据源：

/*+ mycat:createDataSource{ "name":"rwSepw2","url":"jdbc:mysql://192.168.56.104:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Mycat0520@" } */;

/*+ mycat:createDataSource{ "name":"rwSepr2","url":"jdbc:mysql://192.168.56.105:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"Mycat0520@" } */;
1
2
3

‍

可以看到MyCat也多了2个配置文件：

ll /usr/local/mycat/conf/datasources
总用量 20
-rw-r--r--. 1 root root 436 6月  19 21:17 prototypeDs.datasource.json
-rw-r--r--. 1 root root 576 6月  21 07:50 rwSepr2.datasource.json
-rw-r--r--. 1 root root 575 6月  20 07:32 rwSepr.datasource.json
-rw-r--r--. 1 root root 576 6月  21 07:50 rwSepw2.datasource.json
-rw-r--r--. 1 root root 575 6月  20 07:24 rwSepw.datasource.json
1
2
3
4
5
6
7

‍

之前我们已经配置好了逻辑库，现在只需修改集群的信息。

配置文件：/usr/local/mycat/conf/clusters/prototype.cluster.json​，修改后：

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "rwSepw","rwSepw2"
        ],
        "maxCon":2000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "replicas":[
                "rwSepr","rwSepr2","rwSepw2"
        ],
        "switchType":"SWITCH"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

我们主要修改了第11行（增加了master），和第17行（增加了m2和s2，也负责读请求）。然后我们重启MyCat：

cd /usr/local/mycat/bin
./mycat restart
1
2

最好也检查下重启的日志

‍

‍

然后可以查看集群信息，可以看到有新增的数据源：

/*+ mycat:showClusters{} */;
1

‍

‍

# 验证

同理，我们insert一个主机名，使得每个数据库的数据不一样：

INSERT INTO mytbl VALUES(2, @@hostname);
1

‍

然后查询多次，可以看到是轮流查询每个数据库的数据

‍

‍

# 读写分离配置扩展

通过对集群配置的修改，可以根据需求实现更多种情况的读写分离配置，总结如下

‍

（1）读写分离（一主一从，无备，m是主，s是从）

{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

（2）读写分离（一主一从，一备，m是主，s是从备）

{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m","s"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

‍

（3）读写分离（一主一从一备，m是主，s是从，b是备）

{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m","b"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

‍

（4）MHA（一主一从一备）(m是主，s是从，b是备，READ_ONLY判断主)

{
    "clusterType":"MHA",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m","b"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

（5）MGR（一主一从一备）(m是主,s是从,b是备,READ_ONLY判断主)

{
    "clusterType":"MGR",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m","b"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

‍

（6）GARELA_CLUSTER（一主一从，一备，m是主，s是从，b多主）

{
    "clusterType":"GARELA_CLUSTER",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetryCount":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "m","b"
    ],
    "replicas":[
        "s"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH" ,
    "timer":{
        "initialDelay": 30,
        "period":5,
        "timeUnit":"SECONDS"
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

‍

# 常见问题

‍

# UUIDs

Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
1

‍

原因：在mysql 5.6的复制引入了uuid的概念，主从的server_uuid得保证不一样。可以通过以下语句查询：

show variables like 'server_uuid';
1

而由于我的从机是直接用虚拟机复制的方式生成的，导致一样。

‍

解决方法：文件/var/lib/mysql/auto.cnf​里存储了UUID，我们修改里面的内容，和另一个UUID不同即可。

修改完后记得重启，然后再次查询下UUID。

‍

在GitHub上编辑此页 (opens new window)
上次更新: 2023/6/28 11:11:51
一主一从读写分离
分库分表

← 分库分表→

Theme by Vdoing | Copyright © 2022-2023 粤ICP备2022067627号-1 粤公网安备 44011302003646号