MySQL集群 - 一主多从、双主双从

最新推荐文章于 2024-08-31 15:26:19 发布

mango1698

最新推荐文章于 2024-08-31 15:26:19 发布

阅读量1.2k

点赞数 18

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45682053/article/details/141264900

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

1. 主从复制

1.1 简介

在实际生产中，数据的重要性不言而喻。

如果我们的数据库只有一台服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者崩溃，那么服务就不可用了。再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故。

为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务。

MySQL 提供了主从复制功能以提高服务的可用性与数据的安全可靠性。主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就是说从也可以写，主也可以读，只不过一般我们不这么做。但是对于Redis来说，主机master只能读/写，而从机slave只能读，不能写。

主从复制可以实现对数据库备份和读写分离。

1.2 主从复制架构

一主多从

双主双从

1.3 主从复制原理

当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中。

salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变，如果探测倒 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志。

同时 master 主服务器为每个 I/O Thread 启动一个 dump Thread，用于向其发送二进制事件日志。slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中。salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致。

最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒。

2. 多实例安装

使用docker创建多个mysql实例。分别为：

mysql-1：172.17.0.2:3306，映射到本地端口为3307。
mysql-2：172.17.0.3:3306，映射到本地端口为3308。
mysql-3：172.17.0.4:3306，映射到本地端口为3309。
mysql-4：172.17.0.5:3306，映射到本地端口为3300。

docker run -d -it --name mysql-1 -p 3307:3306 -v /Users/xx/myfile/environment/docker-mount/mysql-1/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=密码 mysql:8.0.33 --lower-case-table-names=1

docker run -d -it --name mysql-2 -p 3308:3306 -v /Users/xx/myfile/environment/docker-mount/mysql-2/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=密码 mysql:8.0.33 --lower-case-table-names=1

docker run -d -it --name mysql-3 -p 3309:3306 -v /Users/xx/myfile/environment/docker-mount/mysql-3/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=密码 mysql:8.0.33 --lower-case-table-names=1

docker run -d -it --name mysql-4 -p 3300:3306 -v /Users/xx/myfile/environment/docker-mount/mysql-4/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=密码 mysql:8.0.33 --lower-case-table-names=1

在3个mysql实例进行配置：

[client]
port=3306
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
lower_case_table_names=1
autocommit=1

采用的docker镜像为：mysql:8.4

3. 集群配置

3.1 一主多从

3.1.1 配置

当系统对数据的读取比较多时，为了分担读的压力，可以采用一主多从架构，实现读写分离。

环境配置

在mysql-4的配置文件中配置

# mysql-4
[mysqld]
port=3306
character-set-server=utf8
lower_case_table_names=1
autocommit=1
# 表示启用二进制文件，主库需要配置
log-bin=mysql-bin  
# server编号，在集群中要唯一
server-id=3300

[client]
port=3306
default-character-set=utf8

在mysql-1、mysql-2、mysql-3的配置文件中分别添加如下配置：

# mysql-1
server-id=3307
# mysql-2
server-id=3308
# mysql-3
server-id=3309

配置主从关系

我们将mysql-4:3300作为master主库，将msql-1:3307、mysql-2:3308、mysql-3:3309作为slave从库。

配置mysql-4主库master

使用show master status;命令查看二进制文件内容。该命令是主库特有的。

可以看到偏移量为157。

首先我们在主服务器上创建复制数据的账号，并授权。

create user 'copy'@'%' identified by '密码';
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';

执行完成后，我们再次使用show master status;命令查看。

可以看到，偏移量变为681了。因为我们创建账号，也算是一个写操作。

我们需要将偏移量回归到默认值！因为这个账号数据，从库是不需要拷贝的，所以我们需要重置偏移量。

执行命令，重置偏移量：

reset master;

重置完成后，我们再次执行show master status;命令。偏移量恢复为157。

这样主库就算配置好了，只需要准备一个账号即可。

从库slave配置

使用show slave status ;查看从库状态。目前信息全部为空。

如果服务器不是初始状态，建议重置一下。使用stop slave命令和reset slave命令。

设置从服务器的master，在从机myql1~3的客户端执行：

# 指定主库的ip、port、账号、密码、二进制日志文件、二进制日志文件偏移量
# 注意：如果没有重置主机的偏移量，那么偏移量就写681
change master to
    master_host='172.17.0.5',
    master_user='copy',
    master_port=3306,
    master_password='密码',
    master_log_file='mysql-bin.000003',
    master_log_pos=157;

执行完成后，在从机上执行开始复制指令：

start slave;

再次执行show slave status命令。

可以看到Slave_IO_State为Connecting to source状态。Slave_IO_Running并不是yes状态，说明，连接存在问题。

在Last_IO_Error字段中，我们可以看到如下信息：

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

这个错误信息表明，MySQL复制从服务器（slave）在尝试连接到主服务器（source）时遇到了问题。具体来说，是因为主服务器使用了caching_sha2_password身份验证插件，它要求客户端（在这里是从服务器）必须通过安全的连接（即SSL/TLS加密的连接）进行身份验证。

为了解决这个问题，我们可以配置从服务器使用SSL连接到主服务器。

这里，我们采用另一种方法进行解决，更改主服务器上的用户身份验证插件。

将主服务器上用于复制的用户身份验证插件更改为mysql_native_password，这样就不需要SSL连接了。使用ALTER USER命令来更改用户的身份验证插件，并刷新权限。

ALTER USER 'copy'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

reset master;

注意：在执行完该命令后，主库的偏移量会增加，记得再次执行reset master;命令重置主库。

同时，注意master主库的二进制文件名称是否发生改变！

按上述操作执行完毕后，在从库中，先停止复制指令，重置从库，再次与主库建立连接。

stop slave;
reset slave;
change master to
    master_host='172.17.0.5',
    master_user='copy',
    master_port=3306,
    master_password='密码',
    master_log_file='mysql-bin.000001',
    master_log_pos=157;
start slave;

再次执行show slave status;命令查看

可以看到Slave_IO_State为Waiting for source to send event。并且Slave_IO_Running与Slave_SQL_Running都为yes。这就表明主从关系构建成功了。

判断主从关系是否建立成功的依据：可以通过Slave_IO_Running与Slave_SQL_Running的状态进行判断，如果不是yes，就是建立失败。

3.1.2 测试

此时，我们在主库mysql-4:3300中新建一个数据库test_db。

create database test_db;

建立完成后，我们在从库中执行show databases;指令，可以看到，从库中也有test_db存在了。

那么再继续测试，我们在主库中，在test_db中添加一张数据表user。

create table user(
  id int primary key auto_increment,
  name varchar(50)
)ENGINE=InnoDB;

建立完成后，从库中的test_db中也存在user表了，并且数据会随着主库中的数据变化而变化。

3.1.3 问题

请注意：当我们在从库中对数据进行更新时，主库以及其他从库中的数据并不会随之变化。

只要我们对从库进行写操作，就有可能会造成主从数据不能同步！！

比如，我们在从库mysql-1的user中添加一条数据：{id:3,name:'zhangsan'}。此时，我们在主库mysql-4中添加一条数据：{id:3,name:'lisi'}。这时，主键有了冲突，从库mysql-1无法同步主库的数据。并且，此时主库mysql-4再添加一条数据：{id:4,name:'xiaoming'}，此时从库mysql-1中依然也是无法进行数据同步的了。

此时，通过show slave status命令查看从库的状态，可以看到Slave_IO_Running为yes，但是Slave_SQL_Running为no了。这意味着从库可以从主库中同步数据，将数据放入中继日志文件。但是从库无法从中继日志文件中读取数据进行重放。

上诉问题，如下图所示：

那么，遇到这种问题时，该如何解决呢？

将从库中出现问题的数据删除。
执行stop slave命令停止。
执行start slave命令开启。

此时，再次刷新从库中的数据，数据与主库成功同步了。

3.2 多主多从

3.2.1 配置

一主多从，可以缓解读的压力，但是一旦主库宕机了，就不能写了，所以我们可以采用双主双从架构来改进它的不足。

我们将mysql-1:3307、mysql-2:3308作为master主库，将msql-3:3309作为mysql-1的从库，mysql-4:3300作为mysql-2的从库。

注意清除上面我们进行一主三从时的配置及数据。mysql-4执行reset master。从库执行stop slave;reset slave;。

修改mysql-1与mysql-2的配置文件：

#  ------------------ mysql-1 ------------------
[mysqld]
port=3306
character-set-server=utf8
lower_case_table_names=1
autocommit=1
# serviceid，集群中要唯一
server-id=3307
# 表示启用二进制文件，主库需要配置
log-bin=mysql-bin
# 自增字段的自增步长为2
auto_increment_increment=2
# 自增起始值为1，也就是值只能为1，3，5，7，9，...
auto_increment_offset=1
# 从其他库同步过来的数据，也放入二进制日志文件，这样才能保证该主机的从机也能同步由主机从其他主机上同步过来的数据。
log-slave-updates
# 设定事务提交多少次后，才将数据同步到二进制文件。
sync_binlog=1
[client]
port=3306
default-character-set=utf8

#  ------------------ mysql-2 ------------------
[mysqld]
port=3306
character-set-server=utf8
lower_case_table_names=1
autocommit=1
server-id=3308
log-bin=mysql-bin
# 自增字段的自增步长为2
auto_increment_increment=2
# 自增起始值为2，也就是值只能为2，4，6，8，10，...
auto_increment_offset=2
log-slave-updates
sync_binlog=1
[client]
port=3306
default-character-set=utf8

配置auto_increment_increment和auto_increment_offset是因为我们有两个主库，为了防止在写入数据时发生主键冲突问题。

log-slave-updates表示从其他库同步过来的数据，也记录进二进制日志文件中。比如mysql-2从mysql-1中同步了数据，这个数据在中继日志文件中，mysql-2读取中继日志文件进行数据重放。如果设置了该项，那么在进行数据重放是，数据会被记录至二进制日志文件中，这样就可以保证mysql-2的从机mysql-4读取mysql-2点二进制日志文件实现数据同步。

sync_binlog是设定事务提交多少次后，才将数据同步到二进制文件。

设置为1就是表明，事务提交1次，就会立刻将数据同步至二进制日志文件。此时性能最差，但是安全性最好。
设置为0时，表明当事务提交之后，mysql不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

在mysql-3和mysql-4的配置文件中，分别添加如下内容，注意，不需要添加log-bin。

# mysql-3
server-id=3309
# mysql-4
server-id=3300

修改配置文件后，记得重启mysql。

主库创建用户

mysql-1创建用户，并授权。同时将主服务器上用于复制的用户身份验证插件更改为mysql_native_password，这样就不需要SSL连接了。

create user 'copy_mysql_1'@'%' identified by '密码';
GRANT REPLICATION SLAVE ON *.* TO 'copy_mysql_1'@'%';

ALTER USER 'copy_mysql_1'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

mysql-2创建用户，并授权

create user 'copy_mysql_2'@'%' identified by '密码';
GRANT REPLICATION SLAVE ON *.* TO 'copy_mysql_2'@'%';

ALTER USER 'copy_mysql_2'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

创建完用户后，重置二进制文件偏移量。在mysql-1和mysql-2中执行：

reset master;

从库slave配置

首先配置mysql-1的从库，mysql-2也可以看作是mysql-1点从库。所以，应在mysql-2和mysql-3中进行配置。

change master to
    master_host='172.17.0.2',
    master_user='copy_mysql_1',
    master_port=3306,
    master_password='密码',
    master_log_file='mysql-bin.000001',
    master_log_pos=157;

对mysql-2的从库进行配置。在mysql-1和mysql-4中进行配置。

change master to
    master_host='172.17.0.3',
    master_user='copy_mysql_2',
    master_port=3306,
    master_password='密码',
    master_log_file='mysql-bin.000001',
    master_log_pos=157;

执行完成上述命令后，从库执行如下命令，因为这里的主库也互为对方的从库，所以这里所有的数据库客户端全部都需要执行如下名，开启复制：

start slave;

执行命令show slave status;，可以看到全部执行成功，主从关系构建成功。

3.2.2 测试

我们首先在mysql-1中新建一个数据库mytest。

create database mytest;

新建完成后，我们在mysql-2~4中也可以看到新创建的这个数据库，数据成功同步。

我们在mysql-1中新建一张表user。

create table user(
  id int primary key auto_increment,
  name varchar(20)
);

新建完成后，我们在mysql-2~4中也可以看到新创建的这个表。

在mysql-2中在user表中新增两条数据

insert into user(name) values ('zhangsan');
insert into user(name) values ('lisi');

主键默认自增，我们设置了主键从2开始自增，步长为2。所以两条数据的主键分别为2和4。在其他从库中数据皆成功同步。

在mysql-1中的user表中新增两条数据

insert into user(name) values ('zhaoyun');
insert into user(name) values ('liubei');

数据成功插入，且从机同步成功。

当mysql-1宕机时，我们依然可以向mysql-2中写入数据，并且mysql-4可以成功同步数据。

但是！mysql-3是无法同步数据的。因为mysql-3同步mysql-2的数据是需要通过mysql-1作为一个中转的，mysql-1宕机，所以mysql-3无法同步mysql-2点数据。

此时只能人工干预，尽快修复mysql-1。