如何基于 MySQL 主從模式搭建上萬并發(fā)的系統(tǒng)架構(gòu)？

點擊關(guān)注公眾號，Java干貨及時送達(dá)??


目錄：

一、主從復(fù)制基本概念

二、MySQL主從復(fù)制介紹

三、主從搭建配置

四、MySQL主從復(fù)制常見問題分析

一、主從復(fù)制基礎(chǔ)概念

在了解主從復(fù)制之前必須要了解的就是數(shù)據(jù)庫的二進(jìn)制日志(binlog),主從復(fù)制架構(gòu)大多基于二進(jìn)制日志進(jìn)行。

1.1 二進(jìn)制日志管理說明

二進(jìn)制日志在哪？如何設(shè)置位置和命名？

在my.cnf文件中使用 log-bin =?指定；命名規(guī)則為 mysql-bin.000000?（后為6位數(shù)字）

二進(jìn)制日志位置：

mysql> show variables like?'%log_bin%'?;
+---------------------------------+-----------------------------------------+
| Variable_name |?Value?|
+---------------------------------+-----------------------------------------+
|?log_bin?| ON |
| log_bin_basename |?/application/mysql/data/mysql-bin?|
|?log_bin_index?| /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators |?OFF?|
|?log_bin_use_v1_row_events?| OFF |
| sql_log_bin |?ON?|
+---------------------------------+-----------------------------------------+
6 rows?in?set (0.06 sec)

日志命名：

mysql> show binary logs;
+------------------+-----------+
| Log_name |?File_size?|
+------------------+-----------+
|?mysql-bin.000001?| 2979 |
| mysql-bin.000002 |?120?|
+------------------+-----------+
2 rows?in?set (0.00 sec)

二進(jìn)制日志記錄什么？二進(jìn)制日志中記錄的是一個個完成的事件

二進(jìn)制日志格式是怎樣的？推薦使用row格式

查看當(dāng)前使用的日志格式：

mysql> show variables like?'%format%';
+--------------------------+-------------------+
| Variable_name |?Value?|
+--------------------------+-------------------+
|?binlog_format?| ROW |
| date_format |?%Y-%m-%d?|
|?datetime_format?|?%Y-%m-%d?%H:%i:%s?|
| default_week_format |?0?|
|?innodb_file_format?| Antelope |
| innodb_file_format_check |?ON?|
|?innodb_file_format_max?| Antelope |
| time_format |?%H:%i:%s?|
+--------------------------+-------------------+
8 rows?in?set (0.00 sec)

二進(jìn)制日志如何滾動？每次重啟都會刷新日志，也可以通過命令進(jìn)行刷新 ?reset master;

二進(jìn)制日志用來干嘛？備份恢復(fù)，起始點的備份恢復(fù)

二進(jìn)制日志的操作命令？查看都有哪些二進(jìn)制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name |?File_size?|
+------------------+-----------+
|?mysql-bin.000001?| 2979 |
| mysql-bin.000002 |?167?|
|?mysql-bin.000003?| 120 |
+------------------+-----------+
3?rows?in?set (0.00?sec)

查看當(dāng)前使用的二進(jìn)制日志文件：

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

binlog相關(guān)詳情參照：

http://www.cnblogs.com/clsn/p/8087678.html#_label6

1.2 mysql傳統(tǒng)備份方式和缺陷

1. 二進(jìn)制日志備份

2. mysqldump

1. 必須有數(shù)據(jù)庫服務(wù)器完成邏輯工作，需要更多地cpu周期

2. 邏輯備份還原速度慢：需要MySQL加載和解釋語句、轉(zhuǎn)化存儲格式、重建引擎

   
   

3. xtrabackup

1. 文件大

2. 不總是可以跨平臺、操作系統(tǒng)和MySQL版本

1.3 MySQL主從復(fù)制能為我們做什么

• 高可用

• 輔助備份

• 分擔(dān)負(fù)載

二、MySQL主從復(fù)制介紹

2.1 復(fù)制技術(shù)

??????作用：

1. 保證數(shù)據(jù)安全(異機(jī)實時備份)

   
   

2. 保證服務(wù)持續(xù)運(yùn)行（宕機(jī)接管）

主從復(fù)制實現(xiàn)基本原理

1. 自帶功能，復(fù)制是 MySQL 的一項功能，允許服務(wù)器將更改從一個實例復(fù)制到另一個實例。

   
   

2. 主服務(wù)器將所有數(shù)據(jù)和結(jié)構(gòu)更改記錄到二進(jìn)制日志中。

   
   

3. 從屬服務(wù)器從主服務(wù)器請求該二進(jìn)制日志并在本地應(yīng)用其內(nèi)容。即通過把主庫的binlog傳送到從庫，從新解析應(yīng)用到從庫。

2.2 復(fù)制架構(gòu)

????? mysql復(fù)制的應(yīng)用常見場景：

應(yīng)用場景1：從服務(wù)器作為主服務(wù)器的實時數(shù)據(jù)備份

應(yīng)用場景2：主從服務(wù)器實現(xiàn)讀寫分離，從服務(wù)器實現(xiàn)負(fù)載均衡

應(yīng)用場景3：把多個從服務(wù)器根據(jù)業(yè)務(wù)重要性進(jìn)行拆分訪問

傳統(tǒng)的 MySQL復(fù)制提供了一種簡單的主–從復(fù)制方法，有一個主，以及一個或多個從。?

主節(jié)點執(zhí)行和提交事務(wù)，然后將它們（異步地）發(fā)送到從節(jié)點，以重新執(zhí)行（在基于語句的復(fù)制中）或應(yīng)用（在基于行的復(fù)制中）。

這是一個 shared-nothing 的系統(tǒng)，默認(rèn)情況下所有 server 成員都有一個完整的數(shù)據(jù)副本。

?

（圖）MySQL 異步復(fù)制

還有一個半同步復(fù)制，它在協(xié)議中添加了一個同步步驟。

這意味著主節(jié)點在提交時需要等待從節(jié)點確認(rèn)它已經(jīng)接收到事務(wù)。只有這樣，主節(jié)點才能繼續(xù)提交操作。

?

（圖）MySQL 異步復(fù)制

在上面的兩個圖片中，可以看到傳統(tǒng)異步 MySQL 復(fù)制協(xié)議（以及半同步）的圖形展示。?

藍(lán)色箭頭表示在不同 server 之間或者 server 與 client 應(yīng)用之間的信息交互。

2.3 MySQL主從復(fù)制原理介紹

??????復(fù)制過程：

1. 開啟binlog日志，通過把主庫的binlog傳送到從庫，從新解析應(yīng)用到從庫。

   
   

2. 復(fù)制需要3個線程（dump、io、sql）完成，5.6從庫多個sql。

   
   

3. 復(fù)制是異步的過程。主從復(fù)制是異步的邏輯的SQL語句級的復(fù)制。

??????復(fù)制前提：

1. 主服務(wù)期一定要打開二進(jìn)制日志

   
   

2. 必須兩臺服務(wù)器（或者是多個實例）

   
   

3. 從服務(wù)器需要一次數(shù)據(jù)初始化

• 如果主從服務(wù)器都是新搭建的話，可以不做初始化

• 如果主服務(wù)器已經(jīng)運(yùn)行了很長時間，可以通過備份將主庫數(shù)據(jù)恢復(fù)到從庫




4. 主庫必須要有對從庫復(fù)制請求的用戶。

   
   

5. 從庫需要有relay-log設(shè)置，存放從主庫傳送過來的二進(jìn)制日志：

• ?show variables? like '%relay%';

  
  

6. 在第一次的時候，從庫需要change master to 去連接主庫。

   
   

7. change master信息需要存放到master.info中??：

• show variables? like '%master_info%';

  
  

8. 從庫怎么知道，主庫發(fā)生了新的變化?通過relay-log.info記錄的已經(jīng)應(yīng)用過的relay-log信息。

   
   

9. 在復(fù)制過程中涉及到的線程

   
   

1. 從庫會開啟一個IO thread(線程)，負(fù)責(zé)連接主庫，請求binlog，接收binlog并寫入relay-log。

   
   

2. 從庫會開啟一個SQL thread(線程)，負(fù)責(zé)執(zhí)行relay-log中的事件。

   
   

3. 主庫會開啟一個dump thrad(線程)，負(fù)責(zé)響應(yīng)從IO thread的請求。

? ? ?

?????主從怎么實現(xiàn)的？

1. 通過二進(jìn)制日志

   
   

2. 至少兩臺（主、從）

   
   

3. 主服務(wù)器的二進(jìn)制日志“拿”到從服務(wù)器上再運(yùn)行一遍。

   
   

4. 通過網(wǎng)絡(luò)連接兩臺機(jī)器，一般都會出現(xiàn)延遲的狀態(tài)。也可以說是異步的。

?

2.4 執(zhí)行原理--第一次開啟主從過程

1. 從庫通過手工執(zhí)行change master to 語句連接主庫，提供了連接的用戶一切條件：

1. （user、password、port、ip）

2. ??并且讓從庫知道，二進(jìn)制日志的起點位置（file名? position號）

3. ???start slave

   
   

2. 從庫的IO和主庫的dump線程建立連接

   
   

3. 從庫根據(jù)change master to 語句提供的file名和position號，IO線程向主庫發(fā)起binlog的請求

   
   

4. 主庫dump線程根據(jù)從庫的請求，將本地binlog以events的方式發(fā)給從庫IO線程

   
   

5. 從庫IO線程接收binlog evnets，并存放到本地relay-log中，傳送過來的信息，會記錄到master.info中。

   
   

6. 從庫SQL線程應(yīng)用relay-log，并且把應(yīng)用過的記錄到relay-log.info,默認(rèn)情況下，已經(jīng)應(yīng)用過的relay會自動被清理purge。

到此為止，一次主從復(fù)制就完成

一旦主從運(yùn)行起來：就不需要手工執(zhí)行change master to，因為信息都會被存放到master.info（user、password、port、ip，上次獲取過的binlog信息file和position）中。

詳細(xì)的mysql replication?過程

三、 主從搭建配置

本次主從搭建使用mysql多實例進(jìn)行實驗。多實例配置參考文檔進(jìn)行配置：

http://www.cnblogs.com/clsn/p/8038964.html#_label8?

3.1 多實例數(shù)據(jù)庫slave配置

系統(tǒng)環(huán)境說明：

[root@db02 ~]# cat /etc/redhat-release
CentOS release?6.9?(Final)
[root@db02 ~]# uname -r
2.6.32-696.el6.x86_64
[root@db02 ~]# /etc/init.d/iptables status
iptables:?Firewall?is?not?running.?#?注意：務(wù)必關(guān)閉防火墻（iptables  selinux）
[root@db02 ~]# getenforce
Disabled
[root@db02 ~]# mysql --version
mysql Ver?14.14?Distrib?5.6.36,?for?Linux (x86_64) using EditLine wrapper

1、啟動多實例數(shù)據(jù)庫

[root@db02 ~]# /data/3306/mysql start
Starting MySQL...
[root@db02 ~]# /data/3307/mysql start
Starting MySQL...

2、配置文件說明：

master 配置文件說明：

[root@db02 ~]# cat /data/3306/my.cnf
[client]
port?=?3306
socket?= /data/3306/mysql.sock

[mysqld]
user?= mysql
port?=?3306
socket?= /data/3306/mysql.sock
basedir?= /application/mysql
datadir?= /data/3306/data
log-bin?= /data/3306/mysql-bin
server-id?=?6?# server id 不能相同
skip_name_resolve?=?0?# 跳過域名解析參數(shù)


[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

slave 配置文件說明：

[root@db02 ~]# cat /data/3307/my.cnf
[client]
port?=?3307
socket?= /data/3307/mysql.sock

[mysqld]
user?= mysql
port?=?3307
socket?= /data/3307/mysql.sock
basedir?= /application/mysql
datadir?= /data/3307/data
log-bin?= /data/3307/mysql-bin
server-id?=?7?# server id 不能相同
skip_name_resolve?=?0?# 跳過域名解析參數(shù)
read_only?=?1?# 從庫只讀 （非root用戶 ）

[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid

3、在主庫創(chuàng)建復(fù)制用戶

登陸到主數(shù)據(jù)庫中：

mysql?-uroot?-p123?-S?/data/3306/mysql.sock

創(chuàng)建授權(quán)用戶，注意是slave用戶。

grant?replication?slave?on?*.*?to?repl@'10.0.0.%'?identified?by?'123';

4、初始化從庫數(shù)據(jù)

備份主庫當(dāng)前數(shù)據(jù)

mysqldump?-uroot?-p123?-A?-B?-F?--master-data=2?-S?/data/3306/mysql.sock?>/tmp/full.sql

部分參數(shù)說明：

• -F 刷新二進(jìn)制日志

• --master-data [=＃]這會導(dǎo)致二進(jìn)制日志的位置和文件名被追加到輸出中。如果等于1，則將其打印為CHANGE MASTER命令;?如果等于2，那么該命令將以注釋符號為前綴。

到從庫進(jìn)行恢復(fù)

mysql?-uroot?-p123?-S?/data/3307/mysql.sock

恢復(fù)備份的數(shù)據(jù)

set?sql_log_bin=0;
source /tmp/full.sql

5、開啟從庫復(fù)制

查看備份的當(dāng)前使用的文件及POS號

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

登入數(shù)據(jù)庫，進(jìn)行slave配置。

mysql?-uroot -p123 -S /data/3307/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=120;
start?slave;?# 啟動從庫復(fù)制

該配置想關(guān)說明可以通過 help 獲得。

mysql>?help?CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

3.2 測試主從同步

查看slave庫的狀態(tài)，主要查看：

Slave_IO_Running與Slave_SQL_Running是否都為Yes

主庫進(jìn)行操作，在從庫驗證

[root@db02 ~]# mysql -uroot -p123 -S /data/3306/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4?rows?in?set (0.00?sec)
mysql> create database clsn;
Query OK,?1?row affected (0.00?sec)

在從庫上可以看到該數(shù)據(jù)庫已創(chuàng)建

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clsn |
| mysql |
| performance_schema |
| test |
+--------------------+
5?rows?in?set (0.00?sec)

至此mysql主從復(fù)制就搭建完成

3.3 忘記數(shù)據(jù)庫密碼？

shell>?/application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --skip-networking &
mysql>?update user?set?password=password('123')?where?user='root'?and host='localhost';
mysql>?flush privileges;

3.4 主從復(fù)制狀態(tài)失敗的原因？

Last_IO_Error:?error?reconnecting?to?master?'[email protected]:3306'?-?retry-time:?60?retries:?1

原因：

1. 主機(jī)沒啟動，或者宕機(jī)，檢查主庫的狀態(tài)。

   
   

2. 網(wǎng)絡(luò)通信問題，使用ping命令進(jìn)行檢查；或使用mysql命令進(jìn)行shell端登陸測試

   
   

3. 防火墻，selinux（務(wù)必檢查）

   
   

4. 復(fù)制用戶和密碼、端口號、地址有問題，使用mysql命令進(jìn)行shell端登陸測試。

   
   

5. mysql自動解析，會將連接的IP解析成主機(jī)名（skip-name-resolve =?0）寫入my.cnf文件即可。

   
   

6. 從庫IO異常關(guān)閉，通過show slave status \G 進(jìn)行查看。

四、MySQL主從復(fù)制常見問題

4.1 從庫binlog落后主庫binlog？

從庫記錄的已經(jīng)主庫已經(jīng)給我傳送的binlog事件的坐標(biāo)，一般在繁忙的生產(chǎn)環(huán)境下會落后于主庫

show?master?status\G?--- 主
show?slave?status?\G?--- 從
Master_Log_File:?mysql-bin.000007
Read_Master_Log_Pos:?729

落后太遠(yuǎn)的原因：

• 硬件條件有關(guān)的，機(jī)器磁盤IO性能不足。

  
  

• 主要還是網(wǎng)絡(luò)問題，網(wǎng)絡(luò)傳輸?shù)男阅堋?/p>

  
  

• 主庫存放二進(jìn)制日志的存儲性能太低，建議binlog日志存咋SSD中。

  
  

• 主庫DUMP線程太繁忙，主要發(fā)生在一主多從的環(huán)境下。

  
  

• 從庫IO線程太忙

  
  

• 人為控制（delay節(jié)點、延時節(jié)點 ）

4.2 主庫update，從庫遲遲的沒有更新

特殊情況：日志已經(jīng)傳過來了，數(shù)據(jù)并沒有同步

一般情況：

1. 沒開啟SQL線程

   
   

2. 傳的東西有問題（你要做的事情，我提前已經(jīng)做了，不想重復(fù)做了，然后他就死了）

   
   

3. SQL線程忙

   
   

4. 人為控制了【delay(從庫)節(jié)點、延時節(jié)點，一般生產(chǎn)設(shè)置為3-6小時之間，可以保證過去3-6小時之間的誤操作，可以避免】

4.3 主從復(fù)制延時配置(從庫配置)

停止從庫復(fù)制

mysql>stop slave;
Query OK, 0 rows affected (0.01 sec)

修改延時參數(shù)，MASTER_DELAY，單位位S （秒）。

mysql>CHANGE MASTER TO MASTER_DELAY = 30;
Query OK, 0 rows affected (0.07 sec)

啟動從庫復(fù)制

mysql>start slave;
Query OK, 0 rows affected (0.07 sec)

查看配置是否生效

mysql>?show slave status \G
……
??????????????SQL_Delay:?30

4.4 從庫安全配置（其他用戶只讀）

修改my.cnf配置文件，添加只讀參數(shù)

read_only?=?1?====> 控制普通用戶
innodb_read_only?=?1?====> 控制root用戶，正常情況不要加

添加完成后重啟數(shù)據(jù)庫

mysql> show variables like?'%read_only%';
+------------------+-------+
| Variable_name |?Value?|
+------------------+-------+
|?innodb_read_only?| OFF |
| read_only |?ON?|
|?tx_read_only?| OFF |
+------------------+-------+
3?rows?in?set (0.00?sec)

延時從庫:?delay節(jié)點、延時節(jié)點

4.5 主從復(fù)制故障及解決（跳過錯誤）

命令行設(shè)置

stop?slave;?#<==臨時停止同步開關(guān)。
set?global sql_slave_skip_counter =?1?;?#<==將同步指針向下移動一個，如果多次不同步，可以重復(fù)操作。
start?slave;

在配置文件修改，設(shè)置要跳過的pos

/etc/my.cnf
slave-skip-errors =?1032,1062,1007

在mysql中可以跳過某些錯誤,但是最好的解決辦法，重新搭建主從復(fù)制。

4.6 延時節(jié)點概念?--> SQL線程延時？

Last_SQL_Errno:?0
Last_SQL_Error:

原因:

1. 主庫做操作的對象，在從庫不存在

   
   

2. 主庫做操作的對象屬性不一致

   
   

3. 主庫做操作的對象，從庫已經(jīng)存在

????????????????? ……

4.7 Slave_*_Running：？

1. Slave_IO_Running I/O 線程正在運(yùn)行、未運(yùn)行還是正在運(yùn)行但尚未連接到主服務(wù)器?？赡苤捣謩e為Yes、No 或 Connecting。

   
   

2. Slave_SQL_Running SQL線程當(dāng)前正在運(yùn)行、未運(yùn)行,可能值分別為Yes、No

   
   

3. 主服務(wù)器日志坐標(biāo)：Master_Log_File 和 Read_Master_Log_Pos 標(biāo)識主服務(wù)器二進(jìn)制日志中 I/O 線程已經(jīng)傳輸?shù)淖罱录淖鴺?biāo)。

   
   

4. 如果Master_Log_File和Read_Master_Log_Pos 的值遠(yuǎn)遠(yuǎn)落后于主服務(wù)器上的那些值，這表示主服務(wù)器與從屬服務(wù)器之間事件的網(wǎng)絡(luò)傳輸可能存在延遲。

4.8 中繼日志坐標(biāo)

Relay_Log_File 和 Relay_Log_Pos 列標(biāo)識從屬服務(wù)器中繼日志中 SQL 線程已經(jīng)執(zhí)行的最近事件的坐標(biāo)。

這些坐標(biāo)對應(yīng)于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列標(biāo)識的主服務(wù)器二進(jìn)制日志中的坐標(biāo)。

如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的輸出遠(yuǎn)遠(yuǎn)落后于 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 線程的坐標(biāo)）

這表示 SQL 線程（而不是 I/O 線程）中存在延遲。即，它表示復(fù)制日志事件快于執(zhí)行這些事件。

4.9 單一主從需要改變的地方

從庫的作用

1、相當(dāng)于實時備份

2、使用從庫備份

3、一主多從應(yīng)對讀多的業(yè)務(wù)需求

? ? ??

如果，從庫只做備份服務(wù)器用，那么主庫的壓力會不減反增。因為，所有的業(yè)務(wù)都在主庫實現(xiàn)，讀和寫，dump線程讀取并投遞binlog

解決方案：

1. 可不可以挪走一部分讀業(yè)務(wù)到從庫，讀寫分離

   
   

2. 一主多從應(yīng)對讀多的業(yè)務(wù)需求，一旦發(fā)展成這個架構(gòu)，dump線程投遞binlog的壓力更大

   
   

3. 多級主從，采用中間庫緩解主庫dump的壓力，會出現(xiàn)中間庫瓶頸的問題，選擇blackhole引擎，看性能與安全的權(quán)衡

   
   

4. 雙主模型：緩解，數(shù)據(jù)一致性難保證

   
   

5. 環(huán)狀復(fù)制

6. 
   

End

作者：慘綠少年

來源：https://www.cnblogs.com/clsn/p/8150036.html#auto_id_39?

1.?顏值絕絕子的 swagger-ui
2.?CompletableFuture：讓你的代碼免受阻塞之苦
3.?昨天，開源地震：Apache Log4j2 發(fā)現(xiàn)遠(yuǎn)程代碼執(zhí)行漏洞，高危！
4.?鏈路追蹤自從用了SkyWalking，睡的真香！
最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。
獲取方式：點“在看”，關(guān)注公眾號并回復(fù)?Java?領(lǐng)取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話，在看，轉(zhuǎn)發(fā)吧。
謝謝支持喲 (*^__^*)