Mysql8主從復制解讀(兼容低高版本)

更新時間：2025年02月21日 16:10:40 作者：熱心市民運維小孫

文章詳細介紹了MySQL主從復制的理論知識、必要前提、實現原理、binlog的三種類型以及主從同步的具體步驟,通過配置主庫和從庫的MySQL配置文件,并創(chuàng)建用于主從同步的用戶,可以實現主從復制,最后,文章還討論了主從同步的驗證和故障切換的方法

Mysql主從復制

理論知識

主從復制必要前提

主從復制必要的條件：
主庫開啟binlog日志（設置log-bin參數）
主從server-id不同
從庫服務器能連同主庫

實現原理

原理：實現整個主從復制，需要由slave服務器上的IO進程和Sql進程共同完成；要實現主從復制，首先必須打開Master端的binary log（bin-log）功能，因為整個MySQL 復制過程實際上就是Slave從Master端獲取相應的二進制日志，然后再在自己本地（slave端）按照執(zhí)行日志中所記錄的順序，全部操作一遍。

---在主庫上把有數據更改的（DDL DML DCL）sql語句都記錄到二進制日志（Binary Log）中。
---備庫的I/O線程將主庫上的日志復制到自己的中繼日志（Relay Log）中。
---備庫的SQL線程讀取中繼日志中的事件，將其重放到備庫數據庫之上。

binlog的三種類型

binlog有三種模式：statement模式、mixed模式和row模式。

statement模式記錄的是SQL語句
row模式記錄的是每一行數據的變化
mixed模式是自動組合 STATEMENT 和 ROW 模式，按照最優(yōu)方式來記錄日志。
Binlog日志的開啟和關閉可以通過設置MySQL的配置文件實現。

配置步驟

兩臺機器時間一致
對主庫已有的數據庫不會進行自動同步。
主從同步之前，主庫上已有數據庫備份，需要在從庫上手動導入同步

操作系統(tǒng)	IP	mysql版本	主從類型
Anolis OS8.9	192.168.200.81	mysql8.4	主
Anolis OS8.9	192.168.200.83	mysql8.4	從

需要注意：mysql 版本不同，有些命令是不同的

show master status; 不能用了

查看主節(jié)點binlog的命令

# mysql 8.4版本前使用這條命令查看
show master status; 

# MySQL 8.4版本后使用這條命令查看
SHOW BINARY LOG STATUS;

change master to不能用了

從節(jié)點配置主節(jié)點信息的命令

# MSQL 8.23前
CHANGE MASTER TO MASTER_HOST='192.168.200.81', MASTER_USER='nomax', MASTER_PASSWORD='nomax', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=158;
# MSQL 8.23后
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158;

start slave不能用了

# 開啟同步
start replica ; #8.0.22之后 
start slave ; #8.0.22之前

#停止同步
stop replica ; #8.0.22之后 
stop slave ; #8.0.22之前

#清空之前的主從復制配置信息
reset replica ; #8.0.22之后 
reset slave ; #8.0.22之前

show slave status不能用了

查看從節(jié)點狀態(tài)的

# 查看狀態(tài),\G表示行轉列，便于查看
show replica status\G ; #8.0.22之后 
show slave status\G ; #8.0.22之前

一、主節(jié)點配置(在192.168.200.81操作)

主從節(jié)點配置的差異：由于后續(xù)需要演示主從切換，所以無論是主從節(jié)點，都需要提前開啟binlog和relaylog。故而這里主從配置基本一致，具體配置選項差異只有：server_id、read-only選項

編輯/etc/my.cnf

#==================== 主從同步配置=========================
#節(jié)點id編號，各個mysql的server_id需要唯一
server_id=1
#指定binlog和binglog index的文件名
log_bin=/data/log/mysql/mysql-bin
log_bin_index=/data/log/mysql/mysql-bin.index
#[可選]0（默認）表示讀寫（主機），1表示只讀（從機）
read-only=0
#[可選]啟用中繼日志
relay-log=/data/log/mysql/mysql-relay
#[可選] 單個binlog最大的文件大小，默認是1G
max_binlog_size=500M
#[可選]設置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可選]設置日志文件保留的時長，單位是秒(默認不刪除文件)
#binlog_expire_logs_seconds=6000
#[可選]設置不要復制的數據庫
#binlog-ignore-db=test
#[可選]設置需要復制的數據庫,默認全部記錄。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要復制的主數據庫名字

relay-log中繼日志可以看主從復制之間的細節(jié)
log_bin_index，用來索引，主從復制時提供日志文件順序
binlog_format=row
row哪里更改都會記錄下來，不會出現無法正確復制的問題。很詳細，缺點是日志量大
read-only=0 主的設置0讀寫，從的設置1只讀

修改配置后重啟數據庫:

systemctl restart mysql
or
service mysql restart

二、從節(jié)點配置(在192.168.200.83操作)

##節(jié)點id編號，各個mysql的server_id需要唯一
server_id=2
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可選]啟用中繼日志
relay-log=/data/mysql/mysql-relay
#[可選] 單個binlog最大的文件大小，默認是1G
max_binlog_size=500M
#[可選]設置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可選]0（默認）表示讀寫（主機），1表示只讀（從機）
read-only=1
# #[可選]設置日志文件保留的時長，單位是秒(默認不刪除文件)
# #binlog_expire_logs_seconds=6000
# #[可選]設置不要復制的數據庫
# #binlog-ignore-db=test
# #[可選]設置需要復制的數據庫,默認全部記錄。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要復制的主數據庫名字

主從節(jié)點配置的差異：開啟binlog和relaylog,具體配置選項差異只有：server_id、read-only選項

在從節(jié)點登錄主節(jié)點服務器獲取服務器公鑰

防止后續(xù)出現從節(jié)點連接主節(jié)點服務器報無公鑰的錯誤

mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key

修改配置后重啟數據庫:

systemctl restart mysql
or
service mysql restart

三、創(chuàng)建用于主從同步的用戶(主從都要創(chuàng)建)

root 用戶也可以但是不安全

MySQL8新版本

#創(chuàng)建nomax用戶
CREATE USER'nomax'@'%' IDENTIFIED BY'nomax';
#給nomax用戶授予數據同步的權限
GRANT replication slave on *.* to 'nomax'@'%';
#刷新權限
flush privileges;

mysql5 老版本

GRANT REPLICATION SLAVE ON *.*  TO  'nomax'@'%'  identified by 'nomax';
flush privileges;

參數解析

新版本需要先創(chuàng)建用戶

老版本權限用戶可一起生成

GRANT replication slave：授予改用戶從庫中進行復制的權限
ON *.*：第一個 * 代表數據庫名，第二個 * 代表表名，*.* 意味著所有數據庫中的所有表。
TO 'nomax' ：用戶名字
'@'%' ：表示用戶可以在任何主機連接數據庫
identified by 'nomax' ：為用戶設置nomax的密碼

查詢確認創(chuàng)建的用戶

SELECT User FROM mysql.user;

四、開啟主從復制

查看主節(jié)點binlog執(zhí)行位置（主節(jié)點192.168.200.81來執(zhí)行以下命令）

# mysql8.4的
SHOW BINARY LOG STATUS; 
# mysql8及其以前的
SHOW BINARY STATUS;

mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

從節(jié)點開啟主節(jié)點同步操作（從節(jié)點192.168.200.83來執(zhí)行以下命令）：

#從節(jié)點配置連接主的信息
CHANGE REPLICATION SOURCE TO
#主節(jié)點的IP
SOURCE_HOST='192.168.200.81',    
#主節(jié)點的端口號
SOURCE_PORT=3306,      
#主節(jié)點的用戶
SOURCE_USER='nomax',      
#主節(jié)點的密碼
SOURCE_PASSWORD='nomax',   
#通過 SHOW BINARY LOG STATUS;查看
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

#開啟從節(jié)點備份
start replica;

#查看從節(jié)點的狀態(tài)
show replica status \G;

方便復制版本
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax', 
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

start replica;

show replica status \G;

CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘192.168.200.81’, SOURCE_LOG_FILE=‘mysql-bin.000013’, SOURCE_LOG_POS=158, SOURCE_PORT=3306, SOURCE_USER=‘nomax’, SOURCE_PASSWORD=‘nomax’;

確保下面四項參數正確

Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Error:Last_SQL_Error:

報錯：

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

原因：根據上一張密碼傳輸方式的第3條，該插件發(fā)現連接未加密，因此需要使用RSA加密來傳輸密碼。但是，服務器不會將公用密鑰發(fā)送給客戶端，并且客戶端未提供公用密鑰，因此它無法加密密碼并且連接失敗

解決方法：

使用復制用戶請求服務器公鑰

mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key

在這種情況下，服務器將RSA公鑰發(fā)送給客戶端，后者使用它來加密密碼并將結果返回給服務器。插件使用服務器端的RSA私鑰解密密碼，并根據密碼是否正確來接受或拒絕連接。

重新在從庫配置change masrer to并且start slave，復制可以正常啟動：

#停止主從復制
#清空之前的主從復制配置信息
stop replica;
reset replica;
#從新配置主從復制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax', 
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

start replica;
show replica status \G;

五、主從同步驗證

也可以用navicat去試試，倆IP的數據庫都連上進行創(chuàng)建庫表啥的操作

主節(jié)點上進行建庫、建表、插入表數據操作

#創(chuàng)建數據庫
mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
#查看數據庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.06 sec)
#切換數據庫
mysql> use test_db;
Database changed
#創(chuàng)建表
mysql> CREATE TABLE `t_test` (
    ->   `id` int(11) NOT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   `score` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.10 sec)

#插入表數據
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
#插入表數據
mysql> INSERT INTO `t_test` VALUES (222, 22, 19);
Query OK, 1 row affected (0.01 sec)
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查看表數據
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

檢查從節(jié)點192.168.200.83是否也都同步成功：

如下所示，從節(jié)點也都自動完成了主節(jié)點上所進行的相關操作~

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
#切換數據庫
mysql> use test_db;
Database changed
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查詢表數據
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

至此，主從同步部署完成

六、故障切換

mysql主從，主節(jié)點宕機，如何進行切換

在從節(jié)點執(zhí)行（版本以8.4為例，之前的命令不同參考本文理論知識模塊命令注意）

mysql> stop replica;
mysql> reset replica;

查看是否是只讀模式

mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

只讀模式需要修改my.cnf文件，注釋read-only=1并重啟mysql服務。

或者不重啟使用命令臨時關閉只讀，但下次重啟后失效：set global read_only=off;

查看

mysql> show replica status\G

在程序中將原來主庫IP地址改為現在的從庫IP地址，測試應用連接是否正常

stop replica;mysql> reset replica;

查看是否是只讀模式

mysql> show variables like ‘read_only';
±--------------±------+
| Variable_name | Value |
±--------------±------+
| read_only | ON |
±--------------±------+
1 row in set (0.00 sec)

**只讀模式需要修改my.cnf文件，注釋read-only=1并重啟mysql服務。**

**或者不重啟使用命令臨時關閉只讀，但下次重啟后失效：`set global read_only=off;`**

查看

mysql> show replica status\G

**在程序中將原來主庫IP地址改為現在的從庫IP地址，測試應用連接是否正常**

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

MyCat環(huán)境搭建詳細教程
這篇文章主要介紹了MyCat環(huán)境搭建詳細教程的相關資料,需要的朋友可以參考下
2022-11-11
使用pt-kill根據一定的規(guī)則來kill連接的方法
pt-kill 是一個優(yōu)秀的kill MySQL連接的一個工具，是percona toolkit的一部分，在因為空閑連接較多導致超過最大連接數、某個有問題的sql導致mysql負載很高時，都需要將一些連接kill掉，這個工具主要就是這個用途
2016-04-04
MySQL中的SHOW FULL PROCESSLIST命令實現
SHOW FULL PROCESSLIST命令是MySQL中一個非常有用的工具,可以幫助我們理解和監(jiān)控MySQL服務器的狀態(tài),本文主要介紹了MySQL中的SHOW FULL PROCESSLIST命令,感興趣的可以了解一下
2023-11-11
SQL觸發(fā)器定義與使用
SQL的觸發(fā)器和存儲過程一樣，都是嵌入到SQL中的一段程序，是SQL中管理數據的有力工具，本文給大家詳細介紹SQL觸發(fā)器的定義語法知識，感興趣的朋友一起看看吧
2023-01-01
連接mysql連接超時報錯問題以及解決
這篇文章主要介紹了連接mysql連接超時報錯問題以及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2023-11-11
MySQL數據類型DECIMAL用法詳解
這篇文章主要介紹了MySQL數據類型DECIMAL用法詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-02-02
SQL語句詳解 MySQL update的正確用法
以下的文章主要介紹的是MySQL update 語句的實際用法，我們首先是以單表的UPDATE語句來引出實現MySQL update 語句的實際方案，以下就是文章的詳細內容描述，望你看完之后會有收獲
2012-01-01
mysql使用instr達到in(字符串)的效果
本文主要介紹了mysql使用instr達到in(字符串)的效果，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2022-04-04
MySQL數據庫的主從同步配置與讀寫分離
這篇文章主要介紹了MySQL數據庫的主從同步配置與讀寫分離,需要的朋友可以參考下
2018-01-01
MySQL 常見存儲引擎的優(yōu)劣
眾所周知，MySql 提供了很多存儲引擎，這里來比較一下常見引擎的優(yōu)劣。幫助大家選擇合適的存儲引擎
2021-06-06