【MySQL】主从配置

 

 一主一从(或二从)部署:

 

参考博客:

https://www.cnblogs.com/lelehellow/p/9633315.html

 

正好借着3台机器,可以做一个 1主 2从的分配

192.168.101.12 centos7-02 # 主库
192.168.101.12 centos7-03 # 从库1
192.168.101.12 centos7-04 # 从库2

3台机器都已经安装好RPM版的8.0.27

 

主库需要做的事情是

1、开启Binlog日志

1.5、设置服务ID

2、分配从库的监听账号

3、拿到binlog文件名

首先找到配置文件:

[root@centos7-03 ~]# find / -name my.cnf
/etc/my.cnf

配置这两个参数项:

[mysqld]
# 开启二进制日志 log-bin=mysql-bin # 设置server-id server-id=1

保存后重启mysqld服务,生效配置

systemctl restart mysqld

 

然后登陆上主库,分配从库的账号及其权限

-- 创建两个从库的监听账号
CREATE USER 'SLAVE-01'@'centos7-03' IDENTIFIED BY '123456';
CREATE USER 'SLAVE-02'@'centos7-04' IDENTIFIED BY '123456';

-- 分配分配【从库副本权限】给监听账号
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE-01'@'centos7-03';
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE-02'@'centos7-04';

-- 刷新权限
FLUSH PRIVILEGES;

 

获取主库的bin日志文件

SHOW MASTER STATUS;

展示信息:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

 

然后从库要和主库一样设置服务ID,为了区分MySQL

# centos7-03 机器 设置
vim /etc/my.cnf
server-id=2

# centos7-04 机器 设置
vim /etc/my.cnf
server-id=3 

# 然后都重启服务
systemctl restart mysqld

 

登陆上从库的MySQL,执行绑定SQL:

-- centos7-03的mysql执行
CHANGE MASTER TO
   -- 主库连接信息
    MASTER_PORT= 3306, -- 主库端口
    MASTER_HOST='centos7-02', -- 主库地址
    MASTER_USER='SLAVE-01', -- 主库提供的同步账号
    MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
        
   -- 主库的 SHOW MASTER STATUS 展示的信息
    MASTER_LOG_FILE='mysql-bin.000001', -- 主库的日志文件名称
    MASTER_LOG_POS=156; -- 主库的日志文件最后位置6


-- centos7-04的mysql执行
CHANGE MASTER TO
   -- 主库连接信息
    MASTER_PORT= 3306, -- 主库端口
    MASTER_HOST='centos7-02', -- 主库地址
    MASTER_USER='SLAVE-02', -- 主库提供的同步账号
    MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
        
   -- 主库的 SHOW MASTER STATUS 展示的信息
    MASTER_LOG_FILE='mysql-bin.000001', -- 主库的日志文件名称
    MASTER_LOG_POS=156; -- 主库的日志文件最后位置6

然后开启从库同步:

START SLAVE;

 

关闭从库同步:

STOP SLAVE;

 

查看从库同步状态:

SHOW SLAVE STATUS;
注意两个参数 Slave_IO_Running & Slave_SQL_Running
值都为Yes的时候,才算主从配置成功
mysql> SHOW SLAVE STATUS;
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State       | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File              | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                                                                                        | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                  | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Connecting to source | centos7-02  | SLAVE-02    |        3306 |            60 | mysql-bin.000001 |                 156 | centos7-04-relay-bin.000001 |             4 | mysql-bin.000001      | Connecting       | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 156 |             156 | None            |                |             0 | No                 |                    |                    |                 |                   |                | NULL                  | No                            |          2061 | error connecting to master 'SLAVE-02@centos7-02:3306' - retry-time: 60 retries: 31 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 | NULL                | Replica has read all relay log; waiting for more updates |              86400 |             | 220201 18:09:21         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set (0.04 sec)

 

报错原因在 【Last_IO_Error】

error connecting to master 'SLAVE-02@centos7-02:3306' - retry-time: 60 retries: 31 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

连接要求密码配有加密插件

回到主库更改账号的密码插件项:

ALTER USER 'SLAVE-01'@'centos7-03' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'SLAVE-02'@'centos7-04' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
UNLOCK TABLES;

 

停止从库绑定:

STOP SLAVE;

重置从库

RESET SLAVE;

再检查主库binlog的pos值

SHOW MASTER STATUS;

然后重新配置SLAVE,再启动即可

 

 

双主双从的结构设计:

1、双主双从的设计是基于上面一主一从的演变

SLAVE -> MASTER 这是一主一从的结构

同样MASTER也可以变成SLAVE,双方相互监听,这就成了交织

A 挂B,B也挂A

 

2、双主双从就是在AB交织的架构下各自再挂一个从机

 

 

 

 

2022年06月13日更新:

多主一从的实现

在从库需要新增两项配置,以支持多Channel监听:

-- 从库在my.cnf添加两项配置,以支持多CHANNEL绑定
master_info_repository=table
relay_log_info_repository=table

否则在下面设置监听主库SQL时,执行报错

设置监听的主库时,需要设定自定义CHANNEL属性

-- 从库配置监听的主库时需要自定义CHANNEL属性
CHANGE MASTER TO
MASTER_PORT= 3306, -- 主库端口
MASTER_HOST='192.168.118.133', -- 主库地址(真实环境自行修改)
MASTER_USER='SLAVE-01', -- 主库提供的同步账号
MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
MASTER_LOG_FILE='mysql-bin.000002', -- 主库的日志文件名称(就是上面截图当前日志文件名称)
MASTER_LOG_POS=3225 -- 主库的日志文件最后位置(就是上面截图当前位置)
FOR CHANNEL '133-CHANNEL'; 

CHANGE MASTER TO
MASTER_PORT= 3306, -- 主库端口
MASTER_HOST='192.168.118.135', -- 主库地址(真实环境自行修改)
MASTER_USER='SLAVE-01', -- 主库提供的同步账号
MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
MASTER_LOG_FILE='mysql-bin.000002', -- 主库的日志文件名称(就是上面截图当前日志文件名称)
MASTER_LOG_POS=154 -- 主库的日志文件最后位置(就是上面截图当前位置)
FOR CHANNEL '135-CHANNEL'; 

  

开启和关闭SLAVE状态都需要指定对应CHANNEL

-- 开启同步时指定CHANNEL
START SLAVE FOR CHANNEL '133-CHANNEL'; 
START SLAVE FOR CHANNEL '135-CHANNEL'; 
-- 关闭时同理
STOP SLAVE FOR CHANNEL '133-CHANNEL'; 
STOP SLAVE FOR CHANNEL '135-CHANNEL'; 

  

检查监听状态:

-- 展示同步状态
SHOW SLAVE STATUS 

 

2022年06月14日更新:

GTID方式来部署MySQL集群,所有的MASTER和SLAVE库都需要配置以下参数(my.ini & my.cnf):

# 开启GTID模式
gtid_mode=on	

# 强制gtid一致性,开启后对于特定create table不被支持
enforce_gtid_consistency=on

# 从库从主库复制数据时可以写入到binlog日志,可作为其他从库的主库支持
log-slave-updates=1

# 日志格式:行
binlog_format=row

# slave复制进程不随mysql启动而启动
skip_slave_start=1

  

和之前的区别在于,不需要指定BINLOG日志最新位置

有MASTER_AUTO_POSITION=1可以自动同步

CHANGE MASTER TO
MASTER_PORT= 3306, -- 主库端口
MASTER_HOST='192.168.118.133', -- 主库地址(真实环境自行修改)
MASTER_USER='SLAVE-01', -- 主库提供的同步账号
MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
MASTER_AUTO_POSITION=1
FOR CHANNEL '133-CHANNEL'; 

CHANGE MASTER TO
MASTER_PORT= 3306, -- 主库端口
MASTER_HOST='192.168.118.135', -- 主库地址(真实环境自行修改)
MASTER_USER='SLAVE-01', -- 主库提供的同步账号
MASTER_PASSWORD='123456', -- 主库提供的同步账号密码
MASTER_AUTO_POSITION=1
FOR CHANNEL '135-CHANNEL'; 

 

检查GTID开启状态:

SHOW VARIABLES LIKE '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

  

解决同步失败的方案:

清空所有库的数据以及binlog,初始化到清空状态

RESET MASTER -- 清除日志

  

 

posted @ 2022-02-01 18:25  emdzz  阅读(57)  评论(0编辑  收藏  举报