欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時(shí)間：2025年02月21日 16:10:40   作者：熱心市民運(yùn)維小孫  
文章詳細(xì)介紹了MySQL主從復(fù)制的理論知識(shí)、必要前提、實(shí)現(xiàn)原理、binlog的三種類型以及主從同步的具體步驟,通過配置主庫和從庫的MySQL配置文件,并創(chuàng)建用于主從同步的用戶,可以實(shí)現(xiàn)主從復(fù)制,最后,文章還討論了主從同步的驗(yàn)證和故障切換的方法

Mysql主從復(fù)制

理論知識(shí)

主從復(fù)制必要前提

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

實(shí)現(xiàn)原理

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

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

binlog的三種類型

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

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

配置步驟

  • 兩臺(tái)機(jī)器時(shí)間一致
  • 對(duì)主庫已有的數(shù)據(jù)庫不會(huì)進(jìn)行自動(dòng)同步。
  • 主從同步之前，主庫上已有數(shù)據(jù)庫備份，需要在從庫上手動(dòng)導(dǎo)入同步
操作系統(tǒng)IPmysql版本主從類型
Anolis OS8.9192.168.200.81mysql8.4主
Anolis OS8.9192.168.200.83mysql8.4從

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

show master status; 不能用了

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

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

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

change master to不能用了

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

# 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之前

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

show slave status不能用了

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

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

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

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

編輯/etc/my.cnf

#==================== 主從同步配置=========================
#節(jié)點(diǎn)id編號(hào)，各個(gè)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（默認(rèn)）表示讀寫（主機(jī)），1表示只讀（從機(jī)）
read-only=0
#[可選]啟用中繼日志
relay-log=/data/log/mysql/mysql-relay
#[可選] 單個(gè)binlog最大的文件大小，默認(rèn)是1G
max_binlog_size=500M
#[可選]設(shè)置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可選]設(shè)置日志文件保留的時(shí)長，單位是秒(默認(rèn)不刪除文件)
#binlog_expire_logs_seconds=6000
#[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫
#binlog-ignore-db=test
#[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫,默認(rèn)全部記錄。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字
  • relay-log中繼日志可以看主從復(fù)制之間的細(xì)節(jié)
  • log_bin_index，用來索引，主從復(fù)制時(shí)提供日志文件順序
  • binlog_format=row
  • row哪里更改都會(huì)記錄下來，不會(huì)出現(xiàn)無法正確復(fù)制的問題。很詳細(xì)，缺點(diǎn)是日志量大
  • read-only=0 主的設(shè)置0讀寫，從的設(shè)置1只讀

修改配置后重啟數(shù)據(jù)庫:

systemctl restart mysql
or
service mysql restart

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

##節(jié)點(diǎn)id編號(hào)，各個(gè)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
#[可選] 單個(gè)binlog最大的文件大小，默認(rèn)是1G
max_binlog_size=500M
#[可選]設(shè)置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可選]0（默認(rèn)）表示讀寫（主機(jī)），1表示只讀（從機(jī)）
read-only=1
# #[可選]設(shè)置日志文件保留的時(shí)長，單位是秒(默認(rèn)不刪除文件)
# #binlog_expire_logs_seconds=6000
# #[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫
# #binlog-ignore-db=test
# #[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫,默認(rèn)全部記錄。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字

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

在從節(jié)點(diǎn)登錄主節(jié)點(diǎn)服務(wù)器獲取服務(wù)器公鑰

防止后續(xù)出現(xiàn)從節(jié)點(diǎn)連接主節(jié)點(diǎn)服務(wù)器報(bào)無公鑰的錯(cuò)誤

mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
  • 修改配置后重啟數(shù)據(jù)庫:
systemctl restart mysql
or
service mysql restart

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

root 用戶也可以但是不安全

  1. MySQL8新版本
#創(chuàng)建nomax用戶
CREATE USER'nomax'@'%' IDENTIFIED BY'nomax';
#給nomax用戶授予數(shù)據(jù)同步的權(quán)限
GRANT replication slave on *.* to 'nomax'@'%';
#刷新權(quán)限
flush privileges;
  1. mysql5 老版本
GRANT REPLICATION SLAVE ON *.*  TO  'nomax'@'%'  identified by 'nomax';
flush privileges;

參數(shù)解析

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

老版本權(quán)限用戶可一起生成

  • GRANT replication slave：授予改用戶從庫中進(jìn)行復(fù)制的權(quán)限
  • ON *.*：第一個(gè) * 代表數(shù)據(jù)庫名，第二個(gè) * 代表表名，*.* 意味著所有數(shù)據(jù)庫中的所有表。
  • TO 'nomax' ：用戶名字
  • '@'%' ：表示用戶可以在任何主機(jī)連接數(shù)據(jù)庫
  • identified by 'nomax' ：為用戶設(shè)置nomax的密碼
  1. 查詢確認(rèn)創(chuàng)建的用戶
SELECT User FROM mysql.user;

四、開啟主從復(fù)制

  1. 查看主節(jié)點(diǎn)binlog執(zhí)行位置（主節(jié)點(diǎn)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)
  1. 從節(jié)點(diǎn)開啟主節(jié)點(diǎn)同步操作（從節(jié)點(diǎn)192.168.200.83來執(zhí)行以下命令）：
#從節(jié)點(diǎn)配置連接主的信息
CHANGE REPLICATION SOURCE TO
#主節(jié)點(diǎn)的IP
SOURCE_HOST='192.168.200.81',    
#主節(jié)點(diǎn)的端口號(hào)
SOURCE_PORT=3306,      
#主節(jié)點(diǎn)的用戶
SOURCE_USER='nomax',      
#主節(jié)點(diǎn)的密碼
SOURCE_PASSWORD='nomax',   
#通過 SHOW BINARY LOG STATUS;查看
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

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

#查看從節(jié)點(diǎn)的狀態(tài)
show replica status \G;
方便復(fù)制版本
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’;

確保下面四項(xiàng)參數(shù)正確

Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Error:Last_SQL_Error:

報(bào)錯(cuò)：

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.

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

解決方法：

  1. 使用復(fù)制用戶請(qǐng)求服務(wù)器公鑰
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key

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

重新在從庫配置change masrer to并且start slave，復(fù)制可以正常啟動(dòng)：

#停止主從復(fù)制
#清空之前的主從復(fù)制配置信息
stop replica;
reset replica;
#從新配置主從復(fù)制
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;

五、主從同步驗(yàn)證

  • 也可以用navicat去試試，倆IP的數(shù)據(jù)庫都連上進(jìn)行創(chuàng)建庫表啥的操作
  1. 主節(jié)點(diǎn)上進(jìn)行建庫、建表、插入表數(shù)據(jù)操作
#創(chuàng)建數(shù)據(jù)庫
mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
#查看數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.06 sec)
#切換數(shù)據(jù)庫
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)

#插入表數(shù)據(jù)
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
#插入表數(shù)據(jù)
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)
#查看表數(shù)據(jù)
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

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

如下所示，從節(jié)點(diǎn)也都自動(dòng)完成了主節(jié)點(diǎn)上所進(jìn)行的相關(guān)操作~

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

至此，主從同步部署完成

六、故障切換

mysql主從，主節(jié)點(diǎn)宕機(jī)，如何進(jìn)行切換

  1. 在從節(jié)點(diǎn)執(zhí)行（版本以8.4為例，之前的命令不同參考本文理論知識(shí)模塊命令注意）
mysql> stop replica;
mysql> reset replica;
  1. 查看是否是只讀模式
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服務(wù)。

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

  1. 查看
mysql> show replica status\G
  1. 在程序中將原來主庫IP地址改為現(xiàn)在的從庫IP地址，測(cè)試應(yīng)用連接是否正常

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服務(wù)。**

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

 查看

mysql> show replica status\G

**在程序中將原來主庫IP地址改為現(xiàn)在的從庫IP地址，測(cè)試應(yīng)用連接是否正常**

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

相關(guān)文章

  • MyCat環(huán)境搭建詳細(xì)教程

    MyCat環(huán)境搭建詳細(xì)教程

    這篇文章主要介紹了MyCat環(huán)境搭建詳細(xì)教程的相關(guān)資料,需要的朋友可以參考下
    2022-11-11
  • 使用pt-kill根據(jù)一定的規(guī)則來kill連接的方法

    使用pt-kill根據(jù)一定的規(guī)則來kill連接的方法

    pt-kill 是一個(gè)優(yōu)秀的kill MySQL連接的一個(gè)工具，是percona toolkit的一部分，在因?yàn)榭臻e連接較多導(dǎo)致超過最大連接數(shù)、某個(gè)有問題的sql導(dǎo)致mysql負(fù)載很高時(shí)，都需要將一些連接kill掉，這個(gè)工具主要就是這個(gè)用途
    2016-04-04
  • MySQL中的SHOW FULL PROCESSLIST命令實(shí)現(xiàn)

    MySQL中的SHOW FULL PROCESSLIST命令實(shí)現(xiàn)

    SHOW FULL PROCESSLIST命令是MySQL中一個(gè)非常有用的工具,可以幫助我們理解和監(jiān)控MySQL服務(wù)器的狀態(tài),本文主要介紹了MySQL中的SHOW FULL PROCESSLIST命令,感興趣的可以了解一下
    2023-11-11
  • SQL觸發(fā)器定義與使用

    SQL觸發(fā)器定義與使用

    SQL的觸發(fā)器和存儲(chǔ)過程一樣，都是嵌入到SQL中的一段程序，是SQL中管理數(shù)據(jù)的有力工具，本文給大家詳細(xì)介紹SQL觸發(fā)器的定義語法知識(shí)，感興趣的朋友一起看看吧
    2023-01-01
  • 連接mysql連接超時(shí)報(bào)錯(cuò)問題以及解決

    連接mysql連接超時(shí)報(bào)錯(cuò)問題以及解決

    這篇文章主要介紹了連接mysql連接超時(shí)報(bào)錯(cuò)問題以及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2023-11-11
  • MySQL數(shù)據(jù)類型DECIMAL用法詳解

    MySQL數(shù)據(jù)類型DECIMAL用法詳解

    這篇文章主要介紹了MySQL數(shù)據(jù)類型DECIMAL用法詳解，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-02-02
  • SQL語句詳解 MySQL update的正確用法

    SQL語句詳解 MySQL update的正確用法

    以下的文章主要介紹的是MySQL update 語句的實(shí)際用法，我們首先是以單表的UPDATE語句來引出實(shí)現(xiàn)MySQL update 語句的實(shí)際方案，以下就是文章的詳細(xì)內(nèi)容描述，望你看完之后會(huì)有收獲
    2012-01-01
  • mysql使用instr達(dá)到in(字符串)的效果

    mysql使用instr達(dá)到in(字符串)的效果

    本文主要介紹了mysql使用instr達(dá)到in(字符串)的效果，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2022-04-04
  • MySQL數(shù)據(jù)庫的主從同步配置與讀寫分離

    MySQL數(shù)據(jù)庫的主從同步配置與讀寫分離

    這篇文章主要介紹了MySQL數(shù)據(jù)庫的主從同步配置與讀寫分離,需要的朋友可以參考下
    2018-01-01
  • MySQL 常見存儲(chǔ)引擎的優(yōu)劣

    MySQL 常見存儲(chǔ)引擎的優(yōu)劣

    眾所周知，MySql 提供了很多存儲(chǔ)引擎，這里來比較一下常見引擎的優(yōu)劣。幫助大家選擇合適的存儲(chǔ)引擎
    2021-06-06

最新評(píng)論