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)境搭建詳細教程的相關資料,需要的朋友可以參考下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中的一段程序，是SQL中管理數據的有力工具，本文給大家詳細介紹SQL觸發(fā)器的定義語法知識，感興趣的朋友一起看看吧2023-01-01
這篇文章主要介紹了連接mysql連接超時報錯問題以及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
這篇文章主要介紹了MySQL數據類型DECIMAL用法詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-02-02
以下的文章主要介紹的是MySQL update 語句的實際用法，我們首先是以單表的UPDATE語句來引出實現MySQL update 語句的實際方案，以下就是文章的詳細內容描述，望你看完之后會有收獲2012-01-01
本文主要介紹了mysql使用instr達到in(字符串)的效果，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-04-04
這篇文章主要介紹了MySQL數據庫的主從同步配置與讀寫分離,需要的朋友可以參考下2018-01-01
眾所周知，MySql 提供了很多存儲引擎，這里來比較一下常見引擎的優(yōu)劣。幫助大家選擇合適的存儲引擎2021-06-06