MySQL集群搭建-主主从从(双主双从)模式

/ 0评 / 0

MySQL 的集群都是采用双主双从的经典模式,这也是部署高可用集群的基础。这次笔记我们就开始学习拉~

1.环境准备

上次我们搭建了mysql主备集群架构,如下所示

image-20210925235338016

而这次,需要搭建的是主主(双master)从从

image-20210926003719437

具体Mysql集群配置信息:

IP 系统 端口 MySQL版本 节点 读写 说明
10.143.87.21 红帽7.4 3306 5.7.34 Master 读写 主节点
10.143.87.22 红帽7.4 3306 5.7.34 Stanby 只读,可切换为读写 备节点,允许升级为主节点
(当master故障)
10.143.87.20 红帽7.4 3306 5.7.34 Slave 只读 master-从节点
10.143.87.19 红帽7.4 3306 5.7.34 Slave 只读 Stanby-从节点

参考配置

Master

# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 21330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#### Master or Stanby ####
auto_increment_offset = 1
auto_increment_increment = 2
log_slave_updates = 1
sync_binlog=1
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

Standby

# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 22330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#### Master or Stanby ####
auto_increment_offset = 2
auto_increment_increment = 2
log_slave_updates = 1
sync_binlog=1
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

master-Salve

# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 20330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#### salve ####
read_only=1

Stanby-Salve

# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 19330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#### salve ####
read_only=1

本次添加的配置解释

说明一下,设置 auto_increment_offset, auto_increment_increment 是为了防止两个节点双写时出现主键冲突

注意auto_increment_offset的设置,不同的master设置不应该一样,否则就容易引起主键冲突,比如master1的offset=1,则master2的offset=2,master3的offset=3

2.搭建主主

2.1 重新配置文件,重启Mysql,使配置生效

service mysqld restart

2.2 双 Master 机上创建账号,并授权远程复制

我们创建一个用户名为 repl 的用户,授予 REPLICATION SLAVE 权限专门用来同步(如果按照上一篇的话,已经创建了)

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

或者:

grant replication slave on *.* TO 'repl'@'%' identified by 'repl';

2.3 查询Master和Stanby的状态

show master status;

Master:

Stanby:

分别记录下 File 和 Position 的值 ,执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化。我的就是file内容一样吓死我了

2.4 双Slave机上执行 change master

change master 命令格式如下:

#复制主机的命令
change master to master_host='主机的IP地址',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.具体数字',
master_log_pos=762;

master-Salve:

-- 重置复制
reset slave;


#复制主机的命令
change master to master_host='10.143.87.21',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000005',
master_log_pos=154;

-- 开启同步
start slave;

Stanby-Salve:

-- 重置复制
reset slave;

#复制主机的命令
change master to master_host='10.143.87.22',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000005',
master_log_pos=154;

-- 开启同步
start slave;

2.5 查看从服务器状态的命令:

show slave status\G;

image-20210927184007665

image-20210927183955412

Slave_IO_RunningSlave_SQL_Running 两个参数都是Yes,则说明主从配置成功!

从服务涉及到的命令:

1、停止从服务复制功能
stop slave;
2、重新配置主从 :
stop slave;
reset master;

2.6 两个主机互相复制 change master

因为是双主双从,所以要配置 Stanby复制 Master, Master复制 Stanby。

在 Stanby上执行 的命令

相当于Stanby作为 从机,复制主机 Master上的数据。

#复制主机的命令
change master to master_host='10.143.87.21',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000005',
master_log_pos=154;

在 Master上执行 的命令

相当于Master作为 从机,复制主机 Stanby上的数据。

#复制主机的命令
change master to master_host='10.143.87.22',
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000005',
master_log_pos=154;

启动Master和Stanby 服务器复制功能

start slave;

查看 Master和Stanby 服务的 Slave 服务器状态

show slave status\G;

image-20210927185432136

image-20210927185443873

Slave_IO_RunningSlave_SQL_Running 两个参数都是Yes,则说明主从配置成功!

3. 双主从双 测试

在 Master主机新建库、新建表、 insert 记录, 然后在 Stanby 、slave1、slave2上查看复制 是否成功 。

mysql> 
mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)

mysql> use mytest;
Database changed
mysql> create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default '',
    ->  age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  3 | b    |  17 |
|  5 | c    |  18 |
|  7 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> insert into test1 values(0, 'glj', 25);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  3 | b    |  17 |
|  5 | c    |  18 |
|  7 | d    |  19 |
|  9 | glj  |  25 |
+----+------+-----+
5 rows in set (0.00 sec)

mysql> 

image-20210927185807352

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注