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

Linux之MySQL主從復(fù)制方式

 更新時間:2024年11月16日 09:08:48   作者:何老生  
本文介紹了MySQL的主從復(fù)制原理和配置步驟,包括主從庫的配置、同步操作和異常處理,主從復(fù)制通過二進制日志實現(xiàn)數(shù)據(jù)同步,適用于讀寫分離和備份等場景,配置過程中需要注意server_id的唯一性,確保主從同步的順利進行

概述

MySQL的主從復(fù)制(Master-Slave Replication)是一種數(shù)據(jù)復(fù)制解決方案,將主數(shù)據(jù)庫的DDL(數(shù)據(jù)定義語言)和DML(數(shù)據(jù)操縱語言)操作通過二進制日志傳到從庫服務(wù)器中,然后在從庫上對這些日志重新執(zhí)行(也叫重做),從而是的從庫和主庫的數(shù)據(jù)保存同步。

MySQL支持將數(shù)據(jù)從一個MySQL服務(wù)器(主服務(wù)器)復(fù)制到一個或多個其他MySQL服務(wù)器(從服務(wù)器),從庫同時也可以作為其他從服務(wù)器的主庫,實現(xiàn)鏈狀復(fù)制。

MySQL主從復(fù)制的優(yōu)點主要包含以下三個方面:

主庫出現(xiàn)問題,可以快速切換到從庫提供服務(wù);實現(xiàn)讀寫分離,降低主庫的訪問壓力;可以在從庫中執(zhí)行備份,以避免備份期間影響主庫服務(wù);

需要注意的是,MySQL的主從復(fù)制是異步的,這意味著從服務(wù)器的數(shù)據(jù)可能會與主服務(wù)器的數(shù)據(jù)存在一定的延遲。因此,在使用主從復(fù)制時,需要根據(jù)具體的業(yè)務(wù)場景和需求來選擇合適的配置和策略。

工作原理

從上圖來看,主從復(fù)制分成三步:

  1. Master主庫在事務(wù)提交時,會把數(shù)據(jù)變更記錄在二進制日志文件Binlog中;
  2. 從庫讀取主庫的二進制日志文件Binlog,寫入到從庫的中繼日志Relay Log;
  3. Slave重做中繼日志中的事件,將改變數(shù)據(jù)更新同步到從庫中;

說白了就是Master主庫上執(zhí)行的增刪改的SQL語句同步到對應(yīng)的Slave從庫上,然后再在Slave從庫中同樣再次執(zhí)行一遍SQL語句以作備份。

綜合案例

前期準備

準備兩臺虛擬機,需要提前安裝好MySQL數(shù)據(jù)庫(必須要開啟二進制日志)。

如下所示:

主從庫IP地址
主庫192.168.111.135
從庫192.168.111.137

注意:以上只是示例說明,具體以自己的虛擬機情況為主。

例外如果克隆的兩臺虛擬機IP地址一致,可根據(jù)以下操作修改實現(xiàn)動態(tài)ip(基于mac地址發(fā)配IP)

切換目錄到:/etc/netplan 并且編輯00-installer-config.yaml文件

如下圖指定位置加入:dhcp-identifier: mac(嚴格縮進格式要求)

重啟網(wǎng)絡(luò)刷新修改:netplan apply

主庫配置

修改主庫服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf,并添加如下配置信息(開啟二進制日志):

[mysqld]
...
# 開啟二進制日志(必須)
log-bin = mysql-bin
# MySQL服務(wù)ID,保證整個集群環(huán)境中唯一,默認為1(必須)
server-id = 1
# 二進制日志格式,默認ROW(可選)
binlog_format = ROW
# 忽略的數(shù)據(jù),不需要同步的數(shù)據(jù)庫
# binlog-ignore-db = db1
# binlog-ignore-db = db2
# 指定同步的數(shù)據(jù)庫
# binlog-do-db = db3
  • 注意:這里binlog-ignore-dbbinlog-do-db配置項沒有指定,默認同步所有數(shù)據(jù)庫信息。
  • 從 MySQL 5.7 開始,binlog-ignore-db 的優(yōu)先級高于 binlog-do-db。這意味著即使某個數(shù)據(jù)庫被 binlog-do-db 指定,如果它同時出現(xiàn)在 binlog-ignore-db 的列表中,那么它的更改將不會被記錄到二進制日志中

重啟MySQL服務(wù)器。

systemctl restart mysql

(追求安全,否則可跳過)登錄MySQL數(shù)據(jù)庫,創(chuàng)建遠程連接的賬號,并授予主從復(fù)制權(quán)限。

# 創(chuàng)建xx用戶,并設(shè)置密碼,該用戶可在任意主機連接該MySQL服務(wù)
create usxx'@'%' identified with mysql_native_password by 'xx1234';
# 為'xx'@'%'用戶分配主從復(fù)制權(quán)限
grant replication slave on *.* to 'zking'@'%';

通過指令,查看二進制日志坐標

show master status;

從庫配置

1)修改從庫服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf,并添加如下配置信息:

[mysqld]
...
# 開啟二進制日志(必須)
log-bin = mysql-bin
# MySQL服務(wù)ID,保證整個集群環(huán)境中唯一,默認為1(必須)
server-id = 2
# 二進制日志格式,默認ROW(可選)
binlog_format = ROW
# 是否只讀,1代表只讀,0代表讀寫
read-only = 1

2)重啟MySQL服務(wù)器。

systemctl restart mysql

3)登錄MySQL數(shù)據(jù)庫,設(shè)置主庫配置。

  • MySQL8.0.23之前的版本,執(zhí)行如下SQL語句:
change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;
change master to master_host='192.168.111.135',master_user='root',master_password='123',master_log_file='mysql_bin.000008',master_log_pos=2756;
  • MySQL8.0.23之后的版本,執(zhí)行如下SQL語句:
change replication source to source_host='xxx.xxx.xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

參數(shù)說明:

參數(shù)名含義8.0.23之前
source_host主庫IP地址master_host
source_user連接主庫的用戶名master_user
source_password連接主庫的密碼master_password
source_log_filebinlog日志文件名master_log_file
source_log_posbinlong日志文件位置master_log_pos

4)開啟同步操作

# 8.0.22之后
start replica; 
# 8.0.22之前
start slave;

5)查看主從同步狀態(tài)

# 8.0.22之后
show replica status; 
# 8.0.22之前
show slave status;

格式化顯示:show slave status\G;

上述圖中顯示Slave_IO_Running: No,很明顯主從復(fù)制開啟失敗。經(jīng)過問題分析之后,發(fā)現(xiàn)是虛擬機是克隆的,導(dǎo)致主庫和從庫的MySQLserver id都是一樣的。

解決方案:修改任意主庫和從庫的server id即可解決問題。

修改/var/lib/mysql/auto.cnf文件。將server-uuid屬性修改為唯一值即可。

[auto]
server-uuid = 任意uuid

方案二:

  • 停止mysql服務(wù)
  • 刪除auto.cnf
  • 啟動mysql服務(wù)

修改完畢保存并退出,最后重啟MySQL服務(wù)后,并再次登錄MySQL查看主從復(fù)制是否成功。

數(shù)據(jù)測試

1)登錄主庫MySQL,并執(zhí)行以下SQL語句:

# 切換數(shù)據(jù)庫
use db1;
# 創(chuàng)建數(shù)據(jù)表t_student
create table t_student(sid int primary key auto_increment,sname varchar(20) not null,sage int default 0,ssex varchar(2) default '1');
# 批量添加數(shù)據(jù)
insert into t_student(sname,sage,ssex) values('張三',26,'男'),('王五',22,'女'),('小七',23,'女');

2)登錄從庫MySQL,查看主從復(fù)制結(jié)果:

# 切換數(shù)據(jù)庫
use db1;
# 查看是否存在t_student表
show tables;
# 查看t_student表中是否存在數(shù)據(jù)
select * from t_student;

存在數(shù)據(jù)即MySQL主從復(fù)制同步成功(主庫操作,從庫也會有)。

異常處理

# 授權(quán)&創(chuàng)建用戶
mysql> grant select,insert,file on test.* to test@'%' identified by '123';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
?
Database changed
mysql> select host,user from user;(test并沒有權(quán)限)
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | test          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
4 rows in set (0.00 sec)
mysql> show grants for test;
+----------------------------------+
| Grants for test@% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |【為默認權(quán)限,所有用戶都有】
+----------------------------------+
1 row in set (0.00 sec)

mysql> grant select,insert on test.* to test@'%' identified by '123';

Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for test;
+------------------------------------------------+
| Grants for test@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
| GRANT SELECT, INSERT ON `test`.* TO 'test'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

在創(chuàng)建用戶時對 test 庫授予 SELECT、INSERT、FILE 權(quán)限,因 FILE 權(quán)限不能授予某個數(shù)據(jù)庫而導(dǎo)致語句執(zhí)行失敗。

但最終結(jié)果是:test@'%' 創(chuàng)建成功,授權(quán)部分失敗。

從上面的測試可知,使用 GRANT 創(chuàng)建用戶其實是分為兩個步驟:創(chuàng)建用戶和授權(quán)。

權(quán)限有問題并不影響用戶的創(chuàng)建,上述語句會導(dǎo)致主庫在 binlog 寫 INCIDENT_EVENT,從而導(dǎo)致主從復(fù)制報錯

故障解決

 mysql> stop slave;
 mysql> set global sql_slave_skip_counter=1; #指定跳過事務(wù)個數(shù)
 mysql> start slave;

總結(jié)

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

相關(guān)文章

  • MySQL數(shù)據(jù)庫備份方法說明

    MySQL數(shù)據(jù)庫備份方法說明

    MySQL數(shù)據(jù)庫備份方法說明...
    2007-07-07
  • mysql特殊語法insert?into?..?on?duplicate?key?update?..使用方法詳析

    mysql特殊語法insert?into?..?on?duplicate?key?update?..使用方

    在我們的日常開發(fā)中經(jīng)常會遇到過這樣的情景,查看某條記錄是否存在,不存在的話創(chuàng)建一條新記錄,存在的話更新某些字段,下面這篇文章主要給大家介紹了關(guān)于mysql特殊語法insert?into?..?on?duplicate?key?update?..使用方法的相關(guān)資料,需要的朋友可以參考下
    2023-04-04
  • MySQL查找NULL值的全面指南

    MySQL查找NULL值的全面指南

    在數(shù)據(jù)庫中,NULL 值表示缺失或未知的數(shù)據(jù),在 MySQL 中,我們可以使用特定的查詢語句來查找包含 NULL 值的數(shù)據(jù),本文將詳細介紹如何在 MySQL 中查找 NULL 值,并提供相關(guān)實例和代碼片段,需要的朋友可以參考下
    2024-05-05
  • MySQL表的CURD使用

    MySQL表的CURD使用

    文章主要介紹了MySQL數(shù)據(jù)庫操作的基本方法,包括創(chuàng)建表、插入數(shù)據(jù)、查詢數(shù)據(jù)、更新數(shù)據(jù)和刪除數(shù)據(jù)等,涵蓋了表結(jié)構(gòu)設(shè)計、數(shù)據(jù)插入、查詢條件、排序、分頁、更新和刪除操作,以及聚合函數(shù)和GROUP BY子句的使用
    2025-02-02
  • MySQL8.0.20單機多實例部署步驟

    MySQL8.0.20單機多實例部署步驟

    本文主要介紹了MySQL8.0.20單機多實例部署步驟,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2022-05-05
  • mysql函數(shù)之常見數(shù)學函數(shù)示例詳解

    mysql函數(shù)之常見數(shù)學函數(shù)示例詳解

    文章總結(jié)了多個數(shù)學和字符串處理函數(shù)的功能和使用示例,包括格式化數(shù)字、計算絕對值、平方根、取整、生成隨機數(shù)、四舍五入、截斷、返回符號、冪運算以及最大值最小值的計算,感興趣的朋友一起看看吧
    2025-03-03
  • MySQL更新刪除操作update和delete使用詳解(小白慎用)

    MySQL更新刪除操作update和delete使用詳解(小白慎用)

    這篇文章主要為大家介紹了MySQL的更新刪除操作update和delete使用但是一定要慎用啊,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪
    2023-05-05
  • MySQL學習筆記3:表的基本操作介紹

    MySQL學習筆記3:表的基本操作介紹

    要操作表首先需要選定數(shù)據(jù)庫,因為表是存在于數(shù)據(jù)庫內(nèi)的;表的基本操作包括:創(chuàng)建表、顯示表、查看表基本結(jié)構(gòu)、查看表詳細結(jié)構(gòu)以及刪除表等等,需要了解的朋友可以參考下
    2013-01-01
  • 最新mysql-5.7.21安裝和配置方法

    最新mysql-5.7.21安裝和配置方法

    這篇文章主要介紹了最新mysql-5.7.21安裝和配置方法,本文圖文并茂給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下
    2018-09-09
  • windows下mysql 8.0.15 詳細安裝使用教程

    windows下mysql 8.0.15 詳細安裝使用教程

    這篇文章主要為大家詳細介紹了windows下mysql 8.0.15 詳細安裝使用教程,文中安裝步驟介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2019-08-08

最新評論