詳細講解mysql 主從復(fù)制原理

簡介：?什么是主從復(fù)制，如何實現(xiàn)讀寫分離，看這篇你就懂了！

思維導(dǎo)圖

前言

在很多項目，特別是互聯(lián)網(wǎng)項目，在使用Mysql時都會采用主從復(fù)制、讀寫分離的架構(gòu)。

為什么要采用主從復(fù)制讀寫分離的架構(gòu)？如何實現(xiàn)？有什么缺點？讓我們帶著這些問題開始這段學(xué)習(xí)之旅吧！

為什么使用主從復(fù)制、讀寫分離

主從復(fù)制、讀寫分離一般是一起使用的。目的很簡單，就是為了提高數(shù)據(jù)庫的并發(fā)性能。你想，假設(shè)是單機，讀寫都在一臺MySQL上面完成，性能肯定不高。如果有三臺MySQL，一臺mater只負責(zé)寫操作，兩臺salve只負責(zé)讀操作，性能不就能大大提高了嗎？

所以主從復(fù)制、讀寫分離就是為了數(shù)據(jù)庫能支持更大的并發(fā)。

隨著業(yè)務(wù)量的擴展、如果是單機部署的MySQL，會導(dǎo)致I/O頻率過高。采用主從復(fù)制、讀寫分離可以提高數(shù)據(jù)庫的可用性。

主從復(fù)制的原理

①當(dāng)Master節(jié)點進行insert、update、delete操作時，會按順序?qū)懭氲絙inlog中。

②salve從庫連接master主庫，Master有多少個slave就會創(chuàng)建多少個binlog dump線程。

③當(dāng)Master節(jié)點的binlog發(fā)生變化時，binlog dump 線程會通知所有的salve節(jié)點，并將相應(yīng)的binlog內(nèi)容推送給slave節(jié)點。

④I/O線程接收到 binlog 內(nèi)容后，將內(nèi)容寫入到本地的 relay-log。

⑤SQL線程讀取I/O線程寫入的relay-log，并且根據(jù) relay-log 的內(nèi)容對從數(shù)據(jù)庫做對應(yīng)的操作。

如何實現(xiàn)主從復(fù)制

我這里用三臺虛擬機(Linux)演示，IP分別是104(Master)，106(Slave)，107(Slave)。

預(yù)期的效果是一主二從，如下圖所示：

Master配置

使用命令行進入mysql：

mysql -u root -p

接著輸入root用戶的密碼(密碼忘記的話就網(wǎng)上查一下重置密碼吧~)，然后創(chuàng)建用戶：

//192.168.0.106是slave從機的IP

GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.106' identified by 'Java@1234';

//192.168.0.107是slave從機的IP

GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.107' identified by 'Java@1234';

//刷新系統(tǒng)權(quán)限表的配置

FLUSH PRIVILEGES;

創(chuàng)建的這兩個用戶在配置slave從機時要用到。

接下來在找到mysql的配置文件/etc/my.cnf，增加以下配置：

# 開啟binlog

log-bin=mysql-bin

server-id=104

# 需要同步的數(shù)據(jù)庫，如果不配置則同步全部數(shù)據(jù)庫

binlog-do-db=test_db

# binlog日志保留的天數(shù)，清除超過10天的日志

# 防止日志文件過大，導(dǎo)致磁盤空間不足

expire-logs-days=10

配置完成后，重啟mysql：

service mysql restart

可以通過命令行show master status\G;查看當(dāng)前binlog日志的信息(后面有用)：

Slave配置

Slave配置相對簡單一點。從機肯定也是一臺MySQL服務(wù)器，所以和Master一樣，找到/etc/my.cnf配置文件，增加以下配置：

# 不要和其他mysql服務(wù)id重復(fù)即可

server-id=106

接著使用命令行登錄到mysql服務(wù)器：

mysql -u root -p

然后輸入密碼登錄進去。

進入到mysql后，再輸入以下命令：

CHANGE MASTER TO

MASTER_HOST='192.168.0.104',//主機IP

MASTER_USER='root',//之前創(chuàng)建的用戶賬號

MASTER_PASSWORD='Java@1234',//之前創(chuàng)建的用戶密碼

MASTER_LOG_FILE='mysql-bin.000001',//master主機的binlog日志名稱

MASTER_LOG_POS=862,//binlog日志偏移量

master_port=3306;//端口

還沒完，設(shè)置完之后需要啟動：

# 啟動slave服務(wù)

start slave;

啟動完之后怎么校驗是否啟動成功呢？使用以下命令：

show?slave?status\G;

可以看到如下信息（摘取部分關(guān)鍵信息）：

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.104

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 619

Relay_Log_File: mysqld-relay-bin.000001

Relay_Log_Pos: 782

Relay_Master_Log_File: mysql-bin.000001 //binlog日志文件名稱

Slave_IO_Running: Yes //Slave_IO線程、SQL線程都在運行

Slave_SQL_Running: Yes

Master_Server_Id: 104 //master主機的服務(wù)id

Master_UUID: 0ab6b3a6-e21d-11ea-aaa3-080027f8d623

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Auto_Position: 0

另一臺slave從機配置一樣，不再贅述。

測試主從復(fù)制

在master主機執(zhí)行sql：

CREATE TABLE `tb_commodity_info` (

`id` varchar(32) NOT NULL,

`commodity_name` varchar(512) DEFAULT NULL COMMENT '商品名稱',

`commodity_price` varchar(36) DEFAULT '0' COMMENT '商品價格',

`number` int(10) DEFAULT '0' COMMENT '商品數(shù)量',

`description` varchar(2048) DEFAULT '' COMMENT '商品描述',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品信息表';

接著我們可以看到兩臺slave從機同步也創(chuàng)建了商品信息表：

主從復(fù)制就完成了！

讀寫分離

主從復(fù)制完成后，我們還需要實現(xiàn)讀寫分離，master負責(zé)寫入數(shù)據(jù)，兩臺slave負責(zé)讀取數(shù)據(jù)。怎么實現(xiàn)呢？

實現(xiàn)的方式有很多，以前我公司是采用AOP的方式，通過方法名判斷，方法名中有g(shù)et、select、query開頭的則連接slave，其他的則連接master數(shù)據(jù)庫。

但是通過AOP的方式實現(xiàn)起來代碼有點繁瑣，有沒有什么現(xiàn)成的框架呢，答案是有的。

Apache ShardingSphere 是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈，它由 JDBC、Proxy兩部分組成。

ShardingSphere-JDBC定位為輕量級 Java 框架，在 Java 的 JDBC 層提供的額外服務(wù)。 它使用客戶端直連數(shù)據(jù)庫，以 jar 包形式提供服務(wù)，無需額外部署和依賴，可理解為增強版的 JDBC 驅(qū)動，完全兼容 JDBC 和各種 ORM 框架。

讀寫分離就可以使用ShardingSphere-JDBC實現(xiàn)。

下面演示一下SpringBoot+Mybatis+Mybatis-plus+druid+ShardingSphere-JDBC代碼實現(xiàn)。

項目配置

版本說明：

SpringBoot：2.0.1.RELEASE

druid：1.1.22

mybatis-spring-boot-starter:1.3.2

mybatis-plus-boot-starter：3.0.7

sharding-jdbc-spring-boot-starter:4.1.1

添加sharding-jdbc的maven配置：

org.apache.shardingsphere

sharding-jdbc-spring-boot-starter

4.1.1

然后在application.yml添加配置：

# 這是使用druid連接池的配置，其他的連接池配置可能有所不同

spring:

shardingsphere:

datasource:

names: master,slave0,slave1

master:

type: com.alibaba.druid.pool.DruidDataSource

driver-class-name: com.mysql.jdbc.Driver

url: jdbc:mysql://192.168.0.108:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT

username: yehongzhi

password: YHZ@1234

slave0:

type: com.alibaba.druid.pool.DruidDataSource

driver-class-name: com.mysql.jdbc.Driver

url: jdbc:mysql://192.168.0.109:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT

username: yehongzhi

password: YHZ@1234

slave1:

type: com.alibaba.druid.pool.DruidDataSource

driver-class-name: com.mysql.jdbc.Driver

url: jdbc:mysql://192.168.0.110:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT

username: yehongzhi

password: YHZ@1234

props:

sql.show: true

masterslave:

load-balance-algorithm-type: round_robin

sharding:

master-slave-rules:

master:

master-data-source-name: master

slave-data-source-names: slave0,slave1

sharding.master-slave-rules是標(biāo)明主庫和從庫，一定不要寫錯，否則寫入數(shù)據(jù)到從庫，就會導(dǎo)致無法同步。

load-balance-algorithm-type是路由策略，round_robin表示輪詢策略。

啟動項目，可以看到以下信息，代表配置成功：

編寫Controller接口：

/**

* 添加商品

*

* @param commodityName 商品名稱

* @param commodityPrice 商品價格

* @param description 商品價格

* @param number 商品數(shù)量

* @return boolean 是否添加成功

* @author java技術(shù)愛好者

*/

@PostMapping("/insert")

public boolean insertCommodityInfo(@RequestParam(name = "commodityName") String commodityName,

@RequestParam(name = "commodityPrice") String commodityPrice,

@RequestParam(name = "description") String description,

@RequestParam(name = "number") Integer number) throws Exception {

return commodityInfoService.insertCommodityInfo(commodityName, commodityPrice, description, number);

}

準(zhǔn)備就緒，開始測試！

測試

打開POSTMAN，添加商品：

控制臺可以看到如下信息：

查詢數(shù)據(jù)的話則通過slave進行：

就是這么簡單！

缺點

盡管主從復(fù)制、讀寫分離能很大程度保證MySQL服務(wù)的高可用和提高整體性能，但是問題也不少：

從機是通過binlog日志從master同步數(shù)據(jù)的，如果在網(wǎng)絡(luò)延遲的情況，從機就會出現(xiàn)數(shù)據(jù)延遲。那么就有可能出現(xiàn)master寫入數(shù)據(jù)后，slave讀取數(shù)據(jù)不一定能馬上讀出來。

可能有人會問，有沒有事務(wù)問題呢？

實際上這個框架已經(jīng)想到了，我們看回之前的那個截圖，有一句話是這樣的：

三,常見問題

(1)相同的mysql server uuid,? 導(dǎo)致slave_io_running：no

Slave兩個關(guān)鍵進程:

mysql replication 中slave機器上有兩個關(guān)鍵的進程，死一個都不行，

一個是slave_sql_running，負責(zé)自己的slave mysql進程。

一個是Slave_IO_Running，負責(zé)與主機的io通信.

故障案例：主從同步報錯Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDS

場景一：因為數(shù)據(jù)量非常，大概有1.4T，需要在原先master1-slave1的情況下再創(chuàng)建一個庫slave2，并且掛在slave1下，即master1-slave1-slave2的結(jié)構(gòu)。為了方便，當(dāng)時停掉從庫salve1，show master status記錄狀態(tài)，開啟log_slave_updates，并且關(guān)閉salve1，然后將這個從庫的data文件夾直接copy到新的從庫，結(jié)果在創(chuàng)建slave2和slave1的主從關(guān)系時報錯

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因分析：mysql 5.6的復(fù)制引入了uuid的概念，各個復(fù)制結(jié)構(gòu)中的server_uuid得保證不一樣，但是查看到直接copy ?data文件夾后server_uuid是相同的，

show variables like '%server_uuid%';

果然,主從庫使用了相同的server_uuid;

解決方法：

找到data文件夾下的auto.cnf文件，修改里面的uuid值，保證各個db的uuid不一樣，重啟db即可

結(jié)果:

1.主庫端查看從庫的uuid

show slave hosts;

2.從庫端查看server_uuid

在這里主要是看:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

如果都是Yes,則說明配置成功.

場景二：創(chuàng)建主從關(guān)系時copy了同樣的my.cnf文件，報錯

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

原因分析：

和server_uuid類似，servier_id也得保證不一樣

解決方法：

找到my.cnf配置文件中的server_id，修改從庫的server_id保證和復(fù)制結(jié)構(gòu)中的其他db不一樣，重啟db即可

場景三:

mysql 5.6下因操作兩次drop table導(dǎo)致主從斷開 1051 error

解決方法及其步驟

1.在slave端stop slave

2.修改參數(shù),讓其跳過報錯event,

set global sql_slave_skip_counter = 1 ;

3.start slave

4.show slave status \G;來查看是否正常，通過觀察Seconds_Behind_Master的日志落后是否慢慢縮小，判斷master-slave是否正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

參數(shù)說明:

而set global sql_slave_skip_counter=N的意思，即為在start slave時，從當(dāng)前位置起，跳過N個event。每跳過一個event.但是注意1個event可能會包含多個執(zhí)行語句，要看數(shù)據(jù)庫的負載及其性能情況而定，一般操作前，可能會去binlog或者

relaylog去查看跳過點的語句具體是什么，從而來決定是否進行操作操作來保證master-slave正常進行復(fù)制

場景四:mysql同步故障

Last_Error: Error 'Unknown table 'test'' on query. Default database: 'test'. Query: 'drop table test'

原因：

1.可能在slave上進行了寫操作 （導(dǎo)致slave跟master數(shù)據(jù)不一致）

2.也可能是slave機器重起后，事務(wù)回滾造成的.

3.數(shù)據(jù)同步初始化時，未成功，就是說主庫上的某些表沒有初始化，刪除的時候，導(dǎo)致slave出現(xiàn)'Unknown table 'test'' on query. Default database: 'test'. Query: 'drop table test'報錯

解決辦法1：（手動同步數(shù)據(jù)）

1.首先停掉Slave服務(wù)

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.00 sec)

2.到主服務(wù)器上查看主機狀態(tài)：

記錄File和Position對應(yīng)的值。

mysql> show master status;

3.初始化同步數(shù)據(jù)? （可以嘗試直接跳過該步驟，執(zhí)行下面的步驟，如果可以成功，則不需要執(zhí)行這步。這步屬于重新開始做一次slave的數(shù)據(jù)初始化了）

master：

[root@mysqltest1 mysql]# mysqldump -uroot -p123456 test > /tmp/test.sql

4.到slave服務(wù)器上執(zhí)行手動同步：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.9.145', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=106;

5.再次查看slave狀態(tài)發(fā)現(xiàn)：

mysql> show slave status \G;

解決辦法2：

（確保master與slave的數(shù)據(jù)時一致的，不然會出現(xiàn)數(shù)據(jù)不同步的情況）

mysql> slave stop;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

四.MySQL 內(nèi)部Logrotate的機制

Binary Log rotate機制：

Rotate：每一條binary log寫入完成后，都會判斷當(dāng)前文件是否超過 max_binlog_size，如果超過則自動生成一個binlog file

Delete：expire-logs-days 只在 實例啟動時 和 flush logs 時判斷，如果文件訪問時間早于設(shè)定值，則purge file

Relay Log rotate 機制：

Rotate：每從Master fetch一個events后，判斷當(dāng)前文件是否超過 max_relay_log_size 如果超過則自動生成一個新的relay-log-file

Delete：purge-relay-log 在SQL Thread每執(zhí)行完一個events時判斷，如果該relay-log 已經(jīng)不再需要則自動刪除

因此建議當(dāng)slave不再使用時，一定要通過reset slave來取消relaylog，不然即使重啟mysql，問題還是一樣存在。

五.常用命令操作

查看日志一些命令

1, ?show master status\G;

在這里主要是看log-bin的文件是否相同。

show slave status\G;

在這里主要是看:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

如果都是Yes,則說明配置成功.

2,在master上輸入show processlist\G;

mysql> SHOW PROCESSLIST;

出現(xiàn)Command: Binlog Dump,則說明配置成功.

3.維護管理命令：

stop slave??????????????????????????????????? #停止同步

start slave?????????????????????????????????? #開始同步，從日志終止的位置開始更新。

SET SQL_LOG_BIN=0|1?????????????????????????? #主機端運行，需要super權(quán)限，用來開停日志，隨意開停，會造成主機從機數(shù)據(jù)不一致，造成錯誤

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n?????????? # 客戶端運行，用來跳過幾個事件，只有當(dāng)同步進程出現(xiàn)錯誤而停止的時候才可以執(zhí)行。

4.重啟

RESET MASTER????????????????????????????????? #主機端運行,清除所有的日志，這條命令就是原來的FLUSH MASTER

RESET SLAVE?????????????????????????????????? #從機運行，清除日志同步位置標(biāo)志，并重新生成master.info

#雖然重新生成了master.info,但是并不起用，最好，將從機的mysql進程重啟一下

5.

SHOW MASTER STATUS???????? #主機運行，看日志導(dǎo)出信息

SHOW SLAVE HOSTS?????????? #主機運行，看連入的從機的情況。

SHOW SLAVE STATUS (slave)

SHOW MASTER LOGS (master)

參考：https://blog.csdn.net/cug_jiang126com/article/details/46846031

mysql同步故障

MySQL主從復(fù)制讀寫分離

MySQL數(shù)據(jù)同步，出現(xiàn)Slave_SQL_Running：no和slave_io_running：no問題的解決方法

MySQL 數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。