MySQL 主從，5 分鐘帶你掌握

MySQL 主從一直是面試?？停锩娴闹R點雖然基礎(chǔ)，但是能回答全的同學不多。

目前成都創(chuàng)新互聯(lián)已為上千余家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)絡(luò)空間、網(wǎng)站托管、服務器托管、企業(yè)網(wǎng)站設(shè)計、孟連網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

比如樓哥之前面試小米，就被問到過主從復制的原理，以及主從延遲的解決方案，因為回答的非常不錯，給面試官留下非常好的印象。你之前面試，有遇到過哪些 MySQL 主從的問題呢？

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

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

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

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

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

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

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

詳細流程如下：

當主庫和從庫數(shù)據(jù)同步時，突然中斷怎么辦？因為主庫與從庫之間維持了一個長鏈接，主庫內(nèi)部有一個線程，專門服務于從庫的這個長鏈接的。

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

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

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

那么我們改如何解決呢？

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

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

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

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

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

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

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

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

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

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

具體解決方案如下：

在實際應用場景中，對于一些非常核心的場景，比如庫存，支付訂單等，需要直接查詢從庫，其它非核心場景，就不要去查主庫了。

兩臺機器 A 和 B，A 為主庫，負責讀寫，B 為從庫，負責讀數(shù)據(jù)。

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

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

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

如何在一臺windows主機上搭建mysql主

1、首先要本建立兩mysql服務（參考）指定同端口我主（3306）（3307）

2、修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步數(shù)據(jù)庫

#binlog-ignore-db=mysql #同步數(shù)據(jù)庫,指定binlog-do-db應該用指定

log-bin=mysql-bin #要二進制記文件名稱

修改配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、主庫添加用戶 repl 并指定replication權(quán)限

create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --我指定數(shù)據(jù)庫（test.*）報錯指定全庫（*.*）功

4、保持主mysqltest數(shù)據(jù)庫初始狀態(tài)致

般先所表加讀鎖copy磁盤數(shù)據(jù)庫文件夾我直接停止服務數(shù)據(jù)文件拷貝

5、主數(shù)據(jù)庫面運行show master status;記fileposition字段應參數(shù)

mysql show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

6、庫設(shè)置master：

mysql change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='asdf',master_log_file='mysql-bin.000001',master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

master_log_filemaster_log_pos應剛才show master status記參數(shù)

7、庫啟數(shù)據(jù)庫復制功能

slave start;

mysql slave start;

Query OK, 0 rows affected (0.00 sec)

庫通show slave status查看些參數(shù)

8. 主庫創(chuàng)建表或插入數(shù)據(jù)庫快能看

-- 主庫

mysql create table tianyc_02(b int);

Query OK, 0 rows affected (0.16 sec)

mysql insert into tianyc_02 values(2013);

Query OK, 1 row affected (0.13 sec)

-- 庫

mysql show tables;

+----------------+

| Tables_in_test |

+----------------+

| tianyc_01 |

| tianyc_02 |

+----------------+

2 rows in set (0.00 sec)

mysql select * from tianyc_02;

+------+

| b |

+------+

| 2013 |

+------+

1 row in set (0.00 sec)

同理搭建第二、第三節(jié)點

若滿意請點擊右側(cè)【采納答案】若問題請點擊【追問】

希望我答您所幫助望采納

~ O(∩_∩)O~

1、首先要在本地建立兩個mysql服務（參考這里），指定不同的端口。我這里一個主（3306），一個從（3307）。 2、然后修改主配置文件: [mysqld] server-id = 1 binlog-do-db=test #要同步的數(shù)據(jù)庫 #binlog-ignore-db=mysql #不同步的數(shù)據(jù)庫,如果指定了binlog-do-db這里應該可以不用指定的 log-bin=mysql-bin #要生成的二進制日記文件名稱 修改從配置文件: [mysqld] server-id = 2 log-bin = mysql-bin replicate-do-db=test 3、在主庫添加一個用戶 repl 并指定replication權(quán)限 create user 'repl'@'127.0.0.1' identified by 'asdf'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --這里我指定數(shù)據(jù)庫（test.*）時報錯，而指定全庫（*.*）時會成功。 4、保持主從mysql的te...

如何用mysql workbench做mysql主備

雙擊或右鍵打開MySQL Workbench，進入軟件主界面。

點擊new connection,會有個彈出框，讓我們填寫user(用戶名)，password(密碼)。

填寫完用戶名和密碼，點擊確定就會出現(xiàn)我們創(chuàng)建的數(shù)據(jù)庫工作空間，例如：local instance MySQL56。

雙擊local instance MySQL56，進入數(shù)據(jù)庫工作空間。

在左上方找到一個圓柱形帶加號的圖標，單擊該圖標，可以顯示出數(shù)據(jù)庫名讓我們填例如：baidu。

創(chuàng)建好數(shù)據(jù)庫，我們在左菜單欄找到baidu這個數(shù)據(jù)庫，雙擊該數(shù)據(jù)庫，可以看到下面有個tables,右鍵table，選擇create table，就能創(chuàng)建表了。

如何在一臺windows主機上搭建mysql主從配置

先在主數(shù)據(jù)庫中創(chuàng)建新數(shù)據(jù)庫rep_test。

然后編輯主數(shù)據(jù)庫的my.ini文件

在[mysqld]節(jié)點中增加如下內(nèi)容：

server-id=1 #指定唯一的ID，1至32，必須的

log-bin=mysql-log-bin #指定二進制日志存放路徑，必須的

binlog-do-db=rep_test #指定要同步的數(shù)據(jù)庫，必須的

#binlog-ignore-db=mysql #指定不要同步的數(shù)據(jù)庫，如果指定了binlog-do-db就不用再指定該項

重啟主數(shù)據(jù)庫，然后在主數(shù)據(jù)庫中建立一個備份賬戶

mysqlgrant replication slave on *.* to slave@192.168.1.128 identified by 'slave' ;

mysqlflush privileges;

PS:identified by 指定的slave是賬號slave@192.168.1.128 的密碼

顯示主服務器的狀態(tài)信息，并且找到File 和 Position 的值記錄下來；

mysqlshow master status;

在從數(shù)據(jù)庫中創(chuàng)建新的數(shù)據(jù)庫rep_test。

然后編輯從數(shù)據(jù)庫的my.ini文件

在[mysqld]節(jié)點中增加如下內(nèi)容：

server-id=2 #指定唯一的ID，2至32，必須的，并且不能跟主數(shù)據(jù)庫一樣

replicate-do-db=rep_test #指定要同步的數(shù)據(jù)庫，必須的

#replicate-ignore-db=mysql #指定不要同步的數(shù)據(jù)庫，

重啟從數(shù)據(jù)庫，設(shè)置登錄主數(shù)據(jù)庫的賬號和密碼等信息，然后啟動slave

mysqlchange master to master_host='192.168.1.2',master_user='slave',master_password='slave', master_log_file='mysql-bin.000002',master_log_pos=120;

mysqlstart slave;

查看從數(shù)據(jù)庫的信息

mysqlshow slave status \G;

如果出現(xiàn)： Slave_IO_Running: YesSlave_SQL_Running: Yes以上兩項都為Yes，那說明沒問題了