Linux之MySQL主從復(fù)制方式
概述
MySQL的主從復(fù)制（Master-Slave Replication）是一種數(shù)據(jù)復(fù)制解決方案，將主數(shù)據(jù)庫的DDL（數(shù)據(jù)定義語言）和DML（數(shù)據(jù)操縱語言）操作通過二進(jìn)制日志傳到從庫服務(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ù)制分成三步：
Master主庫在事務(wù)提交時，會把數(shù)據(jù)變更記錄在二進(jìn)制日志文件Binlog中；- 從庫讀取主庫的二進(jìn)制日志文件
Binlog，寫入到從庫的中繼日志Relay Log； Slave重做中繼日志中的事件，將改變數(shù)據(jù)更新同步到從庫中；
說白了就是Master主庫上執(zhí)行的增刪改的SQL語句同步到對應(yīng)的Slave從庫上，然后再在Slave從庫中同樣再次執(zhí)行一遍SQL語句以作備份。
綜合案例
前期準(zhǔn)備
準(zhǔn)備兩臺虛擬機(jī)，需要提前安裝好MySQL數(shù)據(jù)庫（必須要開啟二進(jìn)制日志）。
如下所示：
| 主從庫 | IP地址 |
|---|---|
| 主庫 | 192.168.111.135 |
| 從庫 | 192.168.111.137 |
注意：以上只是示例說明，具體以自己的虛擬機(jī)情況為主。
例外如果克隆的兩臺虛擬機(jī)IP地址一致，可根據(jù)以下操作修改實現(xiàn)動態(tài)ip（基于mac地址發(fā)配IP）
切換目錄到：/etc/netplan 并且編輯00-installer-config.yaml文件
如下圖指定位置加入：dhcp-identifier: mac(嚴(yán)格縮進(jìn)格式要求)
重啟網(wǎng)絡(luò)刷新修改：netplan apply
主庫配置
修改主庫服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，并添加如下配置信息（開啟二進(jìn)制日志）：
[mysqld] ... # 開啟二進(jìn)制日志（必須） log-bin = mysql-bin # MySQL服務(wù)ID，保證整個集群環(huán)境中唯一，默認(rèn)為1（必須） server-id = 1 # 二進(jìn)制日志格式，默認(rèn)ROW（可選） binlog_format = ROW # 忽略的數(shù)據(jù)，不需要同步的數(shù)據(jù)庫 # binlog-ignore-db = db1 # binlog-ignore-db = db2 # 指定同步的數(shù)據(jù)庫 # binlog-do-db = db3
- 注意：這里
binlog-ignore-db和binlog-do-db配置項沒有指定，默認(rèn)同步所有數(shù)據(jù)庫信息。 - 從 MySQL 5.7 開始，
binlog-ignore-db的優(yōu)先級高于binlog-do-db。這意味著即使某個數(shù)據(jù)庫被binlog-do-db指定，如果它同時出現(xiàn)在binlog-ignore-db的列表中，那么它的更改將不會被記錄到二進(jìn)制日志中
重啟MySQL服務(wù)器。
systemctl restart mysql
（追求安全，否則可跳過）登錄MySQL數(shù)據(jù)庫，創(chuàng)建遠(yuǎn)程連接的賬號，并授予主從復(fù)制權(quán)限。
# 創(chuàng)建xx用戶，并設(shè)置密碼，該用戶可在任意主機(jī)連接該MySQL服務(wù) create usxx'@'%' identified with mysql_native_password by 'xx1234'; # 為'xx'@'%'用戶分配主從復(fù)制權(quán)限 grant replication slave on *.* to 'zking'@'%';
通過指令，查看二進(jìn)制日志坐標(biāo)
show master status;
從庫配置
1）修改從庫服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，并添加如下配置信息：
[mysqld] ... # 開啟二進(jìn)制日志（必須） log-bin = mysql-bin # MySQL服務(wù)ID，保證整個集群環(huán)境中唯一，默認(rèn)為1（必須） server-id = 2 # 二進(jìn)制日志格式，默認(rèn)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_file | binlog日志文件名 | master_log_file |
| source_log_pos | binlong日志文件位置 | 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)是虛擬機(jī)是克隆的，導(dǎo)致主庫和從庫的MySQL的server 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'@'%' |【為默認(rèn)權(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獲得當(dāng)前日期時間函數(shù)示例詳解
這篇文章主要給大家介紹了關(guān)于MySQL獲得當(dāng)前日期時間函數(shù)的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)
這篇文章主要介紹了mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
Linux系統(tǒng)中安裝MySQL的詳細(xì)圖文步驟
本文的主要內(nèi)容是在 Linux 上安裝 MySQL，以下內(nèi)容是源于 B站 - MySQL數(shù)據(jù)庫入門到精通 整理而來,需要的朋友可以參考下2023-06-06
mysql數(shù)據(jù)存儲過程參數(shù)實例詳解
這篇文章主要介紹了mysql數(shù)據(jù)存儲過程參數(shù)實例詳解，小編覺得挺不錯的，這里分享給大家，供需要的朋友參考。2017-10-10
MySQL數(shù)據(jù)庫的觸發(fā)器和事務(wù)
這篇文章主要介紹了MySQL數(shù)據(jù)庫的觸發(fā)器和事務(wù)，觸發(fā)器是SQL?server提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，是由事件來觸發(fā)2022-08-08
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.13 解壓版安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-11-11
深入理解MySQL雙字段分區(qū)(OVER(PARTITION BY A,B)
本文主要介紹了MySQL中的窗口函數(shù)雙字段分區(qū)功能(OVER(PARTITION BY A,B),分析其在數(shù)據(jù)分組和性能優(yōu)化中的應(yīng)用,提高查詢效率,具有一定的參考價值,感興趣的可以了解一下2024-09-09
當(dāng)unique列在一個UNIQUE鍵上插入包含重復(fù)值的記錄時，默認(rèn)insert的時候會報1062錯誤，MYSQL有三種不同的處理方法，下面我們分別介紹。2012-09-09
SQL去重是數(shù)據(jù)分析工作中比較常見的一個場景,下面這篇文章主要給大家介紹了關(guān)于SQL去重的3種實用方法的相關(guān)資料,文中通過圖文以及實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-10-10
mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識點總結(jié)
這篇文章主要介紹了mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識點,結(jié)合實例形式總結(jié)分析了mysql中關(guān)于null的判斷、使用相關(guān)操作技巧與注意事項,需要的朋友可以參考下2019-12-12