MySql主從復(fù)制實(shí)現(xiàn)原理及配置

數(shù)據(jù)庫(kù)讀寫分離對(duì)于大型系統(tǒng)或者訪問(wèn)量很高的互聯(lián)網(wǎng)應(yīng)用來(lái)說(shuō)，是必不可少的一個(gè)重要功能。對(duì)于MySQL來(lái)說(shuō)，標(biāo)準(zhǔn)的讀寫分離是主從模式，一個(gè)寫節(jié)點(diǎn)Master后面跟著多個(gè)讀節(jié)點(diǎn)，讀節(jié)點(diǎn)的數(shù)量取決于系統(tǒng)的壓力，通常是1-3個(gè)讀節(jié)點(diǎn)的配置。而一般的讀寫分離中間件，例如Mycat的讀寫分離和自動(dòng)切換機(jī)制，需要mysql的主從復(fù)制機(jī)制配合。

主從配置需要注意的地方

1、主DB server和從DB server數(shù)據(jù)庫(kù)的版本一致

2、主DB server和從DB server數(shù)據(jù)庫(kù)數(shù)據(jù)名稱一致

3、主DB server開啟二進(jìn)制日志,主DB server和從DB server的server_id都必須唯一MySQL主服務(wù)器配置

第一步：修改my.conf文件：

在[mysqld]段下添加：

binlog-ignore-db=mysql

#啟用二進(jìn)制日志
log-bin=mysql-bin
//二進(jìn)制日志的格式，有三種：statement/row/mixed
binlog_format=row
#主服務(wù)器唯一ID，一般取IP最后一段
server-id=82

第二步：重啟mysql服務(wù)

service mysql restart

第三步：建立帳戶并授權(quán)slave

mysql>GRANT FILE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slavepw';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'%' identified by 'slavepw';

一般不用root帳號(hào)，“%”表示所有客戶端都可能連，只要帳號(hào)，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強(qiáng)安全。

刷新權(quán)限

mysql> FLUSH PRIVILEGES;

第四步：查詢master的狀態(tài)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   881 |       | mysql      |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MySQL從服務(wù)器配置

第一步：修改my.conf文件

[mysqld]#從服務(wù)器唯一ID，一般取IP最后一段server-id=83

第二步：配置從服務(wù)器

mysql>change master to master_host='192.168.11.82',master_port=3306,master_user='slave',master_password='slavepw',master_log_file='mysql-bin.000001',master_log_pos=881;

注意語(yǔ)句中間不要斷開，master_port為mysql服務(wù)器端口號(hào)(無(wú)引號(hào))，master_user為執(zhí)行同步操作的數(shù)據(jù)庫(kù)賬戶，“881”無(wú)單引號(hào)(此處的881就是show master status 中看到的position的值，這里的mysql-bin.000001就是file對(duì)應(yīng)的值)。

第三步：?jiǎn)?dòng)從服務(wù)器復(fù)制功能

mysql>start slave;

第四步：檢查從服務(wù)器復(fù)制功能狀態(tài)：

mysql> show slave status;

Slave_IO_Running: Yes //此狀態(tài)必須YES

Slave_SQL_Running: Yes //此狀態(tài)必須YES

注：Slave_IO及Slave_SQL進(jìn)程必須正常運(yùn)行，即YES狀態(tài)，否則都是錯(cuò)誤的狀態(tài)(如：其中一個(gè)NO均屬錯(cuò)誤)。

進(jìn)行驗(yàn)證

在主節(jié)點(diǎn)上創(chuàng)建表、插入數(shù)據(jù)，發(fā)現(xiàn)從節(jié)點(diǎn)也創(chuàng)建表并插入數(shù)據(jù)。

MySQL 主從復(fù)制原理的是啥？

主庫(kù)將變更寫入 binlog 日志，然后從庫(kù)連接到主庫(kù)之后，從庫(kù)有一個(gè) IO 線程，將主庫(kù)的 binlog 日志拷貝到自己本地，寫入一個(gè) relay 中繼日志中。接著從庫(kù)中有一個(gè) SQL 線程會(huì)從中繼日志讀取 binlog，然后執(zhí)行 binlog 日志中的內(nèi)容，也就是在自己本地再次執(zhí)行一遍 SQL，這樣就可以保證自己跟主庫(kù)的數(shù)據(jù)是一樣的。

這里有一個(gè)非常重要的一點(diǎn)，就是從庫(kù)同步主庫(kù)數(shù)據(jù)的過(guò)程是串行化的，也就是說(shuō)主庫(kù)上并行的操作，在從庫(kù)上會(huì)串行執(zhí)行。所以這就是一個(gè)非常重要的點(diǎn)了，由于從庫(kù)從主庫(kù)拷貝日志以及串行執(zhí)行 SQL 的特點(diǎn)，在高并發(fā)場(chǎng)景下，從庫(kù)的數(shù)據(jù)一定會(huì)比主庫(kù)慢一些，是有延時(shí)的。所以經(jīng)常出現(xiàn)，剛寫入主庫(kù)的數(shù)據(jù)可能是讀不到的，要過(guò)幾十毫秒，甚至幾百毫秒才能讀取到。

而且這里還有另外一個(gè)問(wèn)題，就是如果主庫(kù)突然宕機(jī)，然后恰好數(shù)據(jù)還沒(méi)同步到從庫(kù)，那么有些數(shù)據(jù)可能在從庫(kù)上是沒(méi)有的，有些數(shù)據(jù)可能就丟失了。

所以 MySQL 實(shí)際上在這一塊有兩個(gè)機(jī)制，一個(gè)是半同步復(fù)制，用來(lái)解決主庫(kù)數(shù)據(jù)丟失問(wèn)題；一個(gè)是并行復(fù)制，用來(lái)解決主從同步延時(shí)問(wèn)題。

這個(gè)所謂半同步復(fù)制，也叫semi-sync復(fù)制，指的就是主庫(kù)寫入 binlog 日志之后，就會(huì)將強(qiáng)制此時(shí)立即將數(shù)據(jù)同步到從庫(kù)，從庫(kù)將日志寫入自己本地的 relay log 之后，接著會(huì)返回一個(gè) ack 給主庫(kù)，主庫(kù)接收到至少一個(gè)從庫(kù)的 ack 之后才會(huì)認(rèn)為寫操作完成了。

所謂并行復(fù)制，指的是從庫(kù)開啟多個(gè)線程，并行讀取 relay log 中不同庫(kù)的日志，然后并行重放不同庫(kù)的日志，這是庫(kù)級(jí)別的并行。

MySQL 主從同步延時(shí)問(wèn)題

以前線上確實(shí)處理過(guò)因?yàn)橹鲝耐窖訒r(shí)問(wèn)題而導(dǎo)致的線上的 bug，屬于小型的生產(chǎn)事故。

是這個(gè)么場(chǎng)景。有個(gè)同學(xué)是這樣寫代碼邏輯的。先插入一條數(shù)據(jù)，再把它查出來(lái)，然后更新這條數(shù)據(jù)。在生產(chǎn)環(huán)境高峰期，寫并發(fā)達(dá)到了 2000/s，這個(gè)時(shí)候，主從復(fù)制延時(shí)大概是在小幾十毫秒。線上會(huì)發(fā)現(xiàn)，每天總有那么一些數(shù)據(jù)，我們期望更新一些重要的數(shù)據(jù)狀態(tài)，但在高峰期時(shí)候卻沒(méi)更新。用戶跟客服反饋，而客服就會(huì)反饋給我們。

我們通過(guò) MySQL 命令：

show status

查看Seconds_Behind_Master，可以看到從庫(kù)復(fù)制主庫(kù)的數(shù)據(jù)落后了幾 ms。

一般來(lái)說(shuō)，如果主從延遲較為嚴(yán)重，有以下解決方案：

分庫(kù)，將一個(gè)主庫(kù)拆分為多個(gè)主庫(kù)，每個(gè)主庫(kù)的寫并發(fā)就減少了幾倍，此時(shí)主從延遲可以忽略不計(jì)。打開 MySQL 支持的并行復(fù)制，多個(gè)庫(kù)并行復(fù)制。如果說(shuō)某個(gè)庫(kù)的寫入并發(fā)就是特別高，單庫(kù)寫并發(fā)達(dá)到了 2000/s，并行復(fù)制還是沒(méi)意義。重寫代碼，寫代碼的同學(xué)，要慎重，插入數(shù)據(jù)時(shí)立馬查詢可能查不到。如果確實(shí)是存在必須先插入，立馬要求就查詢到，然后立馬就要反過(guò)來(lái)執(zhí)行一些操作，對(duì)這個(gè)查詢?cè)O(shè)置直連主庫(kù)。不推薦這種方法，你要是這么搞，讀寫分離的意義就喪失了。開啟并行復(fù)制

開啟多線程復(fù)制，默認(rèn)關(guān)鍵的參數(shù)有兩個(gè)：

mysql> show variables like 'slave_parallel_%';
+------------------------+----------+
| Variable_name     | Value  |
+------------------------+----------+
| slave_parallel_type  | DATABASE |
| slave_parallel_workers | 0    |
+------------------------+----------+
2 rows in set (0.00 sec)

slave-parallel-type 默認(rèn)值為database

slave-parallel-workers 默認(rèn)值為0

開啟：

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.05 sec)

mysql> set global slave_parallel_type='LOGICAL_CLOCK';

Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_parallel_workers=4;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.07 sec)

參考資料：

https://www.jianshu.com/p/3932551e0221

https://github.com/doocs/advanced-java/blob/master/docs/high-concurrency/mysql-read-write-separation.md

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。