MySQL 主從，5 分鐘帶你掌握

MySQL 主從一直是面試?？?，里面的知識(shí)點(diǎn)雖然基礎(chǔ)，但是能回答全的同學(xué)不多。

在定襄等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站制作、成都做網(wǎng)站 網(wǎng)站設(shè)計(jì)制作按需策劃,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),成都營銷網(wǎng)站建設(shè),外貿(mào)營銷網(wǎng)站建設(shè),定襄網(wǎng)站建設(shè)費(fèi)用合理。

比如樓哥之前面試小米，就被問到過主從復(fù)制的原理，以及主從延遲的解決方案，因?yàn)榛卮鸬姆浅２诲e(cuò)，給面試官留下非常好的印象。你之前面試，有遇到過哪些 MySQL 主從的問題呢？

所謂 MySQL 主從，就是建立兩個(gè)完全一樣的數(shù)據(jù)庫，一個(gè)是主庫，一個(gè)是從庫， 主庫對(duì)外提供讀寫的操作，從庫對(duì)外提供讀的操作 ，下面是一主一從模式：

對(duì)于數(shù)據(jù)庫單機(jī)部署，在 4 核 8G 的機(jī)器上運(yùn)行 MySQL 5.7 時(shí)，大概可以支撐 500 的 TPS 和 10000 的 QPS， 當(dāng)遇到一些活動(dòng)時(shí)，查詢流量驟然，就需要進(jìn)行主從分離。

大部分系統(tǒng)的訪問模型是讀多寫少，讀寫請(qǐng)求量的差距可能達(dá)到幾個(gè)數(shù)量級(jí)，所以我們可以通過一主多從的方式， 主庫只負(fù)責(zé)寫入和部分核心邏輯的查詢，多個(gè)從庫只負(fù)責(zé)查詢，提升查詢性能，降低主庫壓力。

MySQL 主從還能做到服務(wù)高可用，當(dāng)主庫宕機(jī)時(shí)，從庫可以切成主庫，保證服務(wù)的高可用，然后主庫也可以做數(shù)據(jù)的容災(zāi)備份。

整體場(chǎng)景總結(jié)如下：

MySQL 的主從復(fù)制是依賴于 binlog 的，也就是記錄 MySQL 上的所有變化并以二進(jìn)制形式保存在磁盤上二進(jìn)制日志文件。

主從復(fù)制就是將 binlog 中的數(shù)據(jù)從主庫傳輸?shù)綇膸焐?，一般這個(gè)過程是異步的，即主庫上的操作不會(huì)等待 binlog 同步的完成。

詳細(xì)流程如下：

當(dāng)主庫和從庫數(shù)據(jù)同步時(shí)，突然中斷怎么辦？因?yàn)橹鲙炫c從庫之間維持了一個(gè)長(zhǎng)鏈接，主庫內(nèi)部有一個(gè)線程，專門服務(wù)于從庫的這個(gè)長(zhǎng)鏈接的。

對(duì)于下面的情況，假如主庫執(zhí)行如下 SQL，其中 a 和 create_time 都是索引：

我們知道，數(shù)據(jù)選擇了 a 索引和選擇 create_time 索引，最后 limit 1 出來的數(shù)據(jù)一般是不一樣的。

所以就會(huì)存在這種情況：在 binlog = statement 格式時(shí)，主庫在執(zhí)行這條 SQL 時(shí)，使用的是索引 a，而從庫在執(zhí)行這條 SQL 時(shí)，使用了索引 create_time，最后主從數(shù)據(jù)不一致了。

那么我們改如何解決呢？

可以把 binlog 格式修改為 row，row 格式的 binlog 日志記錄的不是 SQL 原文，而是兩個(gè) event:Table_map 和 Delete_rows。

Table_map event 說明要操作的表，Delete_rows event用于定義要?jiǎng)h除的行為，記錄刪除的具體行數(shù)。 row 格式的 binlog 記錄的就是要?jiǎng)h除的主鍵 ID 信息，因此不會(huì)出現(xiàn)主從不一致的問題。

但是如果 SQL 刪除 10 萬行數(shù)據(jù)，使用 row 格式就會(huì)很占空間的，10 萬條數(shù)據(jù)都在 binlog 里面，寫 binlog 的時(shí)候也很耗 IO。但是 statement 格式的 binlog 可能會(huì)導(dǎo)致數(shù)據(jù)不一致。

設(shè)計(jì) MySQL 的大叔想了一個(gè)折中的方案，mixed 格式的 binlog，其實(shí)就是 row 和 statement 格式混合使用， 當(dāng) MySQL 判斷可能數(shù)據(jù)不一致時(shí)，就用 row 格式，否則使用就用 statement 格式。

有時(shí)候我們遇到從數(shù)據(jù)庫中獲取不到信息的詭異問題時(shí)，會(huì)糾結(jié)于代碼中是否有一些邏輯會(huì)把之前寫入的內(nèi)容刪除，但是你又會(huì)發(fā)現(xiàn)，過了一段時(shí)間再去查詢時(shí)又可以讀到數(shù)據(jù)了，這基本上就是主從延遲在作怪。

主從延遲，其實(shí)就是“從庫回放” 完成的時(shí)間，與 “主庫寫 binlog” 完成時(shí)間的差值， 會(huì)導(dǎo)致從庫查詢的數(shù)據(jù)，和主庫的不一致 。

談到 MySQL 數(shù)據(jù)庫主從同步延遲原理，得從 MySQL 的主從復(fù)制原理說起：

總結(jié)一下主從延遲的主要原因 ：主從延遲主要是出現(xiàn)在 “relay log 回放” 這一步，當(dāng)主庫的 TPS 并發(fā)較高，產(chǎn)生的 DDL 數(shù)量超過從庫一個(gè) SQL 線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與從庫的大型 query 語句產(chǎn)生了鎖等待。

我們一般會(huì)把從庫落后的時(shí)間作為一個(gè)重點(diǎn)的數(shù)據(jù)庫指標(biāo)做監(jiān)控和報(bào)警，正常的時(shí)間是在毫秒級(jí)別，一旦落后的時(shí)間達(dá)到了秒級(jí)別就需要告警了。

解決該問題的方法，除了縮短主從延遲的時(shí)間，還有一些其它的方法，基本原理都是盡量不查詢從庫。

具體解決方案如下：

在實(shí)際應(yīng)用場(chǎng)景中，對(duì)于一些非常核心的場(chǎng)景，比如庫存，支付訂單等，需要直接查詢從庫，其它非核心場(chǎng)景，就不要去查主庫了。

兩臺(tái)機(jī)器 A 和 B，A 為主庫，負(fù)責(zé)讀寫，B 為從庫，負(fù)責(zé)讀數(shù)據(jù)。

如果 A 庫發(fā)生故障，B 庫成為主庫負(fù)責(zé)讀寫，修復(fù)故障后，A 成為從庫，主庫 B 同步數(shù)據(jù)到從庫 A。

一臺(tái)主庫多臺(tái)從庫，A 為主庫，負(fù)責(zé)讀寫，B、C、D為從庫，負(fù)責(zé)讀數(shù)據(jù)。

如果 A 庫發(fā)生故障，B 庫成為主庫負(fù)責(zé)讀寫，C、D負(fù)責(zé)讀，修復(fù)故障后，A 也成為從庫，主庫 B 同步數(shù)據(jù)到從庫 A。

安全最重要！MySQL配置主從復(fù)制，主主復(fù)制

為了保障數(shù)據(jù)的安全與穩(wěn)定性，我們常用數(shù)據(jù)庫的主從復(fù)制與主主復(fù)制來實(shí)現(xiàn)。主從復(fù)制為從機(jī)實(shí)時(shí)拷貝一份主機(jī)的數(shù)據(jù)，當(dāng)主機(jī)有數(shù)據(jù)變化時(shí)，從機(jī)的數(shù)據(jù)會(huì)跟著變，當(dāng)從機(jī)數(shù)據(jù)有變化時(shí)，主機(jī)數(shù)據(jù)不變；同樣地，主主復(fù)制就是，多個(gè)主機(jī)之間，只要有一個(gè)主機(jī)的數(shù)據(jù)變化了，其它主機(jī)數(shù)據(jù)也會(huì)跟著變化。

添加以下內(nèi)容

如果你是使用我之前那種方式啟動(dòng)的MySQL，那么你只需要去你相關(guān)聯(lián)的宿主機(jī)的配置文件夾里面去建立一個(gè) my.cnf 然后寫入上面的類容就好了。

比如：我的啟動(dòng)命令如下（不應(yīng)該換行的，這里為了方便查看，我給它分行了）

那么我只需要在 /docker/mysql_master/conf 這個(gè)目錄下創(chuàng)建 my.cnf 文件就好了。

這個(gè)命令是需要在容器里面執(zhí)行的

docker重啟mysql會(huì)關(guān)閉容器，我們需要重啟容器。

確保在主服務(wù)器上 skip_networking 選項(xiàng)處于 OFF 關(guān)閉狀態(tài), 這是默認(rèn)值。 如果是啟用的，則從站無法與主站通信，并且復(fù)制失敗。

我的命令如下

在從服務(wù)器配置連接到主服務(wù)器的相關(guān)信息 （在容器里面的mysql執(zhí)行）

上面代碼的xxxxx你需要換成你的IP，docker 查看容器 IP 的命令如下：

啟動(dòng)的那個(gè)從服務(wù)器的線程

測(cè)試的話，你可以在主服務(wù)器里面，創(chuàng)建一個(gè)數(shù)據(jù)庫，發(fā)現(xiàn)從服務(wù)器里面也有了，就成功了。

如果你還想要一個(gè)從服務(wù)器，那么你只需要按照上面配置從服務(wù)器再配置一個(gè)就行了，新建的從服務(wù)器，會(huì)自動(dòng)保存主服務(wù)器之前的數(shù)據(jù)。（測(cè)試結(jié)果） 如果你上面的主從復(fù)制搞定了，那么這個(gè)主主復(fù)制就很簡(jiǎn)單了。我們把上面的從服務(wù)器也改成主服務(wù)器

1）、修改上面的從服務(wù)器的my.cnf文件，和主服務(wù)器的一樣（注意這個(gè)server-id不能一樣）然后重啟服務(wù)器 2）、在從服務(wù)器里面創(chuàng)建一個(gè)復(fù)制用戶創(chuàng)建命令一樣（這里修改一下用戶名可以改為 repl2） 3）、在之前的主服務(wù)器里面運(yùn)行下面這個(gè)代碼

上面主要是教你怎么搭建一個(gè)MySQL集群，但是這里面還有很多其它的問題。也是我在學(xué)習(xí)過程中思考的問題，可能有的小伙伴上來看到文章長(zhǎng)篇大論的看不下去，只想去實(shí)現(xiàn)這樣一直集群功能，所以我就把問題寫在下面了。

1）、MySQL的replication和pxc MySQL的集群方案有replication和pxc兩種，上面是基于replication實(shí)現(xiàn)的。

replication: 異步復(fù)制，速度快，無法保證數(shù)據(jù)的一致性。 pxc: 同步復(fù)制，速度慢，多個(gè)集群之間是事務(wù)提交的數(shù)據(jù)一致性強(qiáng)。

2）、MySQL的replication數(shù)據(jù)同步的原理 我們?cè)谂渲玫臅r(shí)候開啟了它的二進(jìn)制日志，每次操作數(shù)據(jù)庫的時(shí)候都會(huì)更新到這個(gè)日志里面去。主從通過同步這個(gè)日志來保證數(shù)據(jù)的一致性。

3）、可否不同步全部的數(shù)據(jù) 可以配置，同步哪些數(shù)據(jù)庫，甚至是哪些表。

4）、怎么關(guān)閉和開始同步

5）、我就我的理解畫出了，主從、主從從、主主、復(fù)制的圖。

往期推薦：

利用Docker僅花1分鐘時(shí)間安裝好MySQL服務(wù)

Linux下MySQL 5.7的離線與在線安裝（圖文）

Linux下安裝MySQL8.0（收藏?。?/p>

如何配置兩個(gè)MySQL數(shù)據(jù)庫之間的主從同步功能？

IP的設(shè)置：A主機(jī) IP：10.10.0.119；Mask：255.255.0.0；B主機(jī) IP：10.10.8.112；Mask：255.255.0.0

在IP設(shè)置完成以后，需要確定兩主機(jī)的防火墻確實(shí)已經(jīng)關(guān)閉?？梢允褂妹顂ervice iptables status查看防火墻狀態(tài)。如果防火墻狀態(tài)。

為仍在運(yùn)行。使用service iptables stop來停用防火墻。如果想啟動(dòng)關(guān)閉防火墻，可以使用setup命令來禁用或定制。最終以兩臺(tái)主機(jī)可以相互ping通為佳。

3.2 配置A主(master) B從(slave)模式；3.2.1 配置A 為master。

增加一個(gè)用戶同步使用的帳號(hào)：

GRANT FILE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’;

GRANTREPLICATION SLAVE ON *.* TO ‘backup’@'10.10.8.112' IDENTIFIED BY ‘1234’。

賦予10.10.8.112也就是Slave機(jī)器有File權(quán)限，只賦予Slave機(jī)器有File權(quán)限還不行，還要給它REPLICATION SLAVE的權(quán)限才可以。

增加一個(gè)數(shù)據(jù)庫作為同步數(shù)據(jù)庫：create database test;

創(chuàng)建一個(gè)表結(jié)構(gòu)：create table mytest (username varchar(20),password varchar(20));

修改配置文件：修改A的/etc/my.cnf文件。

在my.cnf配置項(xiàng)中加入下面配置：

server-id = 1 #Server標(biāo)識(shí)

log-bin

binlog-do-db=test #指定需要日志的數(shù)據(jù)庫

重起數(shù)據(jù)庫服務(wù)：

service mysqld restart

查看server-id：

show variable like ‘server_id’。

mysql主從配置

主從同步原理圖

mysql復(fù)制的模式

具體配置

master_log_file是第四步中File的值

master_log_pos是第四步中Postion的值

Slave_IO_Running等于yes代表已經(jīng)連接到主庫

Slave_SQL_Running等于yes 可執(zhí)行主庫binlog

這四個(gè)記錄了 IO或者SQL連接或者執(zhí)行出現(xiàn)的錯(cuò)誤

mysql主從原理如何配置

1.在主數(shù)據(jù)庫服務(wù)器為從服務(wù)器添加一個(gè)擁有權(quán)限訪問主庫的用戶:

GRANT REPLICATION SLAVE ON *.* TO ' test'@'%' IDENTIFIED BY 'test';

(%表示允許所有IP，可設(shè)置指定從服務(wù)器IP)

添加用戶后:

可在從服務(wù)器上用mysql -h127.0.0.1 -utest -ptest; 來測(cè)試是否有權(quán)限訪問主數(shù)據(jù)庫

2.在主據(jù)庫配置文件加上:

#master config

server-id = 1

log-bin = mysql-bin

3.在從服務(wù)器數(shù)據(jù)庫配置文件：

server-id = 2

master-host = 10.0.0.199

master-user = test

master-password = test

replicate-do-db = test

master-port = 3306

log-bin = mysql-bin

如果你的一切配置順利

你在從服務(wù)器上輸入命令：show slave status\G

成功情況：

Slave_IO_Running:yes

Slave_SQL_Running:yes

在主服務(wù)器上輸入show master status

當(dāng)前題目：mysql怎么配置主從 mysql配置主從數(shù)據(jù)庫
本文路徑：http://chinadenli.net/article22/dodeijc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、自適應(yīng)網(wǎng)站、網(wǎng)站設(shè)計(jì)公司、品牌網(wǎng)站設(shè)計(jì)、建站公司、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)