MySQL 主從復(fù)制原理與實(shí)踐詳解
本文實(shí)例講述了MySQL 主從復(fù)制原理與實(shí)踐。分享給大家供大家參考,具體如下:
簡(jiǎn)介
MySQL 的主從復(fù)制又叫 Replication、AB 復(fù)制。至少需要兩個(gè) MySQL 服務(wù)(可以是同一臺(tái)機(jī)器,也可以是不同機(jī)器之間進(jìn)行)。比如A服務(wù)器做主服務(wù)器,B服務(wù)器做從服務(wù)器,在A服務(wù)器上進(jìn)行數(shù)據(jù)的更新,通過(guò) binlog 日志記錄同步到B服務(wù)器上,并重新執(zhí)行同步過(guò)來(lái)的 binlog 數(shù)據(jù),從而達(dá)到兩臺(tái)服務(wù)器數(shù)據(jù)一致。
MySQL 數(shù)據(jù)庫(kù)的主從復(fù)制方案,與使用 scp/rsync 等命令進(jìn)行的文件級(jí)別復(fù)制類(lèi)似,都是數(shù)據(jù)的遠(yuǎn)程傳輸。
只不過(guò) MySQL 的主從復(fù)制是其自帶的功能,無(wú)需借助第三方工具,而且MySQL的主從復(fù)制并不是數(shù)據(jù)庫(kù)磁盤(pán)上的文件直接拷貝,而是通過(guò)邏輯的 binlog 日志復(fù)制到要同步的服務(wù)器本地,然后由本地的線程讀取日志里面的 SQL 語(yǔ)句,重新應(yīng)用到 MySQL 數(shù)據(jù)庫(kù)中。
作用
1. 可以實(shí)時(shí)災(zāi)備,用于故障切換;
2. 讀寫(xiě)分離,提供查詢(xún)服務(wù),實(shí)現(xiàn)負(fù)載均衡;
3. 數(shù)據(jù)熱備,避免影響業(yè)務(wù)。
原理

1、主服務(wù)器MySQL服務(wù)將所有的寫(xiě)操作記錄在 binlog 日志中,并生成 log dump 線程,將 binlog 日志傳給從服務(wù)器MySQL服務(wù)的 I/O 線程。2、從服務(wù)器MySQL服務(wù)生成兩個(gè)線程,一個(gè)是 I/O 線程,另一個(gè)是 SQL 線程。
3、從庫(kù) I/O 線程去請(qǐng)求主庫(kù)的 binlog 日志,并將 binlog 日志中的文件寫(xiě)入 relaylog(中繼日志)中。
4、從庫(kù)的 SQL 線程會(huì)讀取 relaylog 中的內(nèi)容,并解析成具體的操作,來(lái)實(shí)現(xiàn)主從的操作一致,達(dá)到最終兩個(gè)數(shù)據(jù)庫(kù)數(shù)據(jù)一致的目的。
注意點(diǎn): - 主從復(fù)制是異步的邏輯的 SQL 語(yǔ)句級(jí)的復(fù)制; - 復(fù)制時(shí),主庫(kù)有一個(gè) I/O 線程,從庫(kù)有兩個(gè)線程,及 I/O 和 SQL 線程; - 實(shí)現(xiàn)主從復(fù)制的必要條件是主庫(kù)要開(kāi)啟記錄 binlog 的功能; - 作為復(fù)制的所有 MySQL 節(jié)點(diǎn)的 server-id 都不能相同; - binlog 文件只記錄對(duì)數(shù)據(jù)內(nèi)容有更改的 SQL 語(yǔ)句,不記錄任何查詢(xún)語(yǔ)句。
形式
一主一從

主主復(fù)制

一主多從

多主一從(5.7后開(kāi)始支持)

聯(lián)級(jí)復(fù)制

實(shí)踐
需求
實(shí)現(xiàn)一主一從復(fù)制模式,同一臺(tái)主機(jī)兩個(gè)MySQL實(shí)例
環(huán)境
Mac:10.15.1 Docker:2.0.0.3//采取docker安裝MySQL主要是管理維護(hù)方便、獨(dú)立IP、啟動(dòng)秒級(jí) MySQL-master:5.7.29//主服務(wù)器 MySQL-master IP:172.17.0.3 MySQL-slave:5.7.29//從服務(wù)器 MySQL-slave IP:172.17.0.4
步驟
第一步:準(zhǔn)備好兩臺(tái)MySQL服務(wù)器
mysql-master(主服務(wù)器):
1. 創(chuàng)建目錄結(jié)構(gòu): master/conf、master/data、master/logs 2. 啟動(dòng)運(yùn)行實(shí)例容器: docker run --name mysql-master > -p 3310:3306 > -v ~/docker/master/conf:/etc/mysql/conf.d > -v ~/docker/master/data:/var/lib/mysql > -v ~/docker/master/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進(jìn)入容器 docker exec -it mysql-master bash 4. 登錄MySQL mysql -uroot -p
mysql-slave(從服務(wù)器):
1. 創(chuàng)建目錄結(jié)構(gòu): slave/conf、slave/data、slave/logs 2. 啟動(dòng)運(yùn)行實(shí)例容器: docker run --name mysql-slave > -p 3310:3306 > -v ~/docker/slave/conf:/etc/mysql/conf.d > -v ~/docker/slave/data:/var/lib/mysql > -v ~/docker/slave/logs:/var/log/mysql > -e MYSQL_ROOT_PASSWORD=123456 > -d mysql:5.7 3. 進(jìn)入容器 docker exec -it mysql-slave bash 4. 登錄MySQL mysql -uroot -p
第二步:配置文件(my.cnf)修改
主服務(wù)器: [mysqld] port = 3306 server-id = 1 #要同步的數(shù)據(jù)庫(kù) binlog-do-db = school #要生成二進(jìn)制日志文件 主服務(wù)器一定要開(kāi)啟 log-bin = mysql-bin 重啟MySQL: docker restart mysql-master
從服務(wù)器: [mysqld] port = 3306 server-id = 2 #要同步的數(shù)據(jù)庫(kù) binlog-do-db = school #要生成二進(jìn)制日志文件(從服務(wù)器可選) log-bin = mysql-bin 重啟MySQL: docker restart mysql-slave
第三步:創(chuàng)建主服務(wù)器復(fù)制用戶(hù)及相關(guān)權(quán)限
create user 'slave'@'%' identified by '123456';//創(chuàng)建用戶(hù) grant replication slave,replication client on *.* to 'slave'@'%';//設(shè)置用戶(hù)權(quán)限 flush privileges;//刷新權(quán)限 show grants for 'slave'@'%';//查看用戶(hù)權(quán)限
第四步:數(shù)據(jù)備份同步
1. 登錄master,執(zhí)行鎖表操作 mysql -uroot -p FLUSH TABLES WITH READ LOCK; 2. 將master中需要同步的db的數(shù)據(jù)dump出來(lái) mysqldump -uroot -p school > school.dump 3. 將數(shù)據(jù)導(dǎo)入slave mysql -uroot -h172.17.0.4 -p school < school.dump 4. 解鎖master UNLOCK TABLES;
第五步:主服務(wù)器復(fù)制狀態(tài)
1. 創(chuàng)建新數(shù)據(jù)表及增加數(shù)據(jù) create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4; insert into user(name) values(222); 2. 主服務(wù)器 binlog 記錄狀態(tài) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 769 | school | | | +------------------+----------+--------------+------------------+-------------------+

第六步:從服務(wù)器開(kāi)始復(fù)制
1. 連接主服務(wù)器及設(shè)置復(fù)制的起始節(jié)點(diǎn) mysql> change master to master_host='172.17.0.3', -> master_port=3306, -> master_user='slave', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=769; 2. 開(kāi)始復(fù)制 start slave; 3. 查看復(fù)制狀態(tài) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.0.3 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 961 Relay_Log_File: 87dc5224655d-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //表示I/O線程讀取成功 Slave_SQL_Running: Yes //表示SQL線程執(zhí)行成功 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 961 Relay_Log_Space: 892 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 45540733-4e0c-11ea-b0ac-0242ac110003 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 4. 查看數(shù)據(jù)表數(shù)據(jù) mysql> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | 222 | +----+------+
復(fù)制的控制相關(guān)命令: stop salve //停止slave連路 reset slave //重置slave連路 start slave //開(kāi)啟slave連路 stop master //停止master連路 reset master //重置master連路 start master //開(kāi)啟master連路
第七步:主從服務(wù)器的進(jìn)程查看
mysql-master:

mysql-slave:

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題:《MySQL查詢(xún)技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
相關(guān)文章
解決Access denied for user root @&nbs
這篇文章給大家介紹了解決:Access denied for user ‘root‘@‘192.168.120.1‘ (using password: YES)的問(wèn)題,文中通過(guò)圖文和代碼給大家分析的非常詳細(xì),具有一定的參考價(jià)值,需要的朋友可以參考下2024-01-01
Mysql 5.7.19 winx64 ZIP Archive 安裝及使用過(guò)程問(wèn)題小結(jié)
本篇文章給大家介紹了mysql 5.7.19 winx64 ZIP Archive 安裝及使用過(guò)程問(wèn)題小結(jié),需要的朋友可以參考下2017-07-07
簡(jiǎn)單談?wù)凪ySQL數(shù)據(jù)透視表
這篇文章主要介紹了簡(jiǎn)單談?wù)凪ySQL數(shù)據(jù)透視表的相關(guān)資料,需要的朋友可以參考下2019-08-08
MySql 8.0.16版本安裝提示已經(jīng)不使用“UTF8B3”而是使用“UTF8B4”問(wèn)題
這篇文章主要介紹了MySql 8.0.16版本安裝提示已經(jīng)不使用“UTF8B3”而是使用“UTF8B4”問(wèn)題 ,需要的朋友可以參考下2019-07-07
MySQL安裝第四步報(bào)錯(cuò)(initializing?database報(bào)錯(cuò))的圖文解決方法
這篇文章主要給大家介紹了關(guān)于MySQL安裝第四步報(bào)錯(cuò)(initializing?database報(bào)錯(cuò))的解決方法,"initializing?database"?通常出現(xiàn)在安裝MySQL的過(guò)程中,表示MySQL數(shù)據(jù)庫(kù)初始化過(guò)程中遇到了問(wèn)題,需要的朋友可以參考下2024-06-06
MySQL插入時(shí)間差八小時(shí)問(wèn)題的解決方法
這篇文章主要給大家介紹了關(guān)于MySQL插入時(shí)間差八小時(shí)問(wèn)題的解決方法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
ubuntu安裝mysql數(shù)據(jù)庫(kù)方法
ubuntu基于linux的免費(fèi)開(kāi)源桌面PC操作系統(tǒng),十分契合英特爾的超極本定位,支持x86、64位和ppc架構(gòu)。這篇文章給大家介紹ubuntu安裝mysql數(shù)據(jù)庫(kù)方法,非常不錯(cuò),需要的朋友參考下吧2019-08-08

