MySQL復制以及調優(yōu)原理的示例分析

這篇文章主要介紹復制以及調優(yōu)原理的示例分析，文中介紹的非常詳細，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

一. 簡(jiǎn)介

MySQL自帶復制方案，帶來(lái)好處有：

數據備份。

。

分布式數據。

概念介紹：

主機（master）：被復制的數據庫。

從機（slave）：復制主機數據的數據庫。

復制步驟：
(1). master記錄更改的明細，存入到二進(jìn)制日志（binary log）。
(2). master發(fā)送同步消息給slave。
(3). slave收到消息后，將master的二進(jìn)制日志復制到本地的中繼日志（relay log）。
(4). slave重現中繼日志中的消息，從而改變數據庫的數據。

下面放一張經(jīng)典的圖片來(lái)說(shuō)明這一過(guò)程：

二. 實(shí)現復制

實(shí)現復制有以下步驟：

1.設置MySQL主庫的二進(jìn)制日志以及server-id

MySQL配置文件一般存放在/etc/my.cnf

# 在[mysqld]下面添加配置選項
[mysqld]
server-id=1
log-bin=mysql-bin.log

server-id是數據庫在整個(gè)數據庫集群中的唯一標示，必須保持唯一。
重啟MySQL。

注：如果MySQL配置文件中已經(jīng)配置過(guò)此文件，則可以跳過(guò)此步。

2.新建復制賬號

在主庫里面新建用于從庫復制主庫數據的賬號，并授予復制權限。

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user_name@'host' IDENTIFIED BY 'password';

3.設置MySQL主庫server-id

和第二步配置一樣，要注意的地方有兩點(diǎn)：

如果不需要從庫作為別的從庫的主庫的話(huà)，則不需要配置二進(jìn)制日志。很多時(shí)候復制并不需要復制主庫的全部數據庫（特別是mysql的信息配置庫）。因此可以配置replicate_do_db來(lái)指定復制的數據庫 4.從庫初始化主庫的數據

如果數據量不算大的情況下，可以使用mysqldump工具導出主庫數據，然后導入到從庫里面。

mysqldump --single-transaction --triggers --master-data databasename > data.sql

如果數據量大的情況下應該使用Xtrabackup去進(jìn)行數據庫的導出，此處不做介紹。
可能會(huì )有同學(xué)問(wèn)，為什么不直接使用二進(jìn)制日志進(jìn)行初始化呢？

如果我們主庫運行了比較長(cháng)的一段時(shí)間，并不太適合使用從庫根據二進(jìn)制日志進(jìn)行復制數據，直接使用二進(jìn)制日志去初始化從庫會(huì )比較耗費時(shí)間和性能。更多的情況下，主庫的二進(jìn)制日志的配置項沒(méi)有打開(kāi)，因此也就不存在以前操作的二進(jìn)制日志。 5.開(kāi)啟復制

從庫執行下面命令

mysql> CHANGE MASTER TO MASTER_HOST='host',
-> MASTER_USER='user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;

注意最后的兩個(gè)命令：MASTER_LOG_FILE和MASTER_LOG_POS，表示從庫的從哪個(gè)二進(jìn)制文件開(kāi)始讀取，偏移量從那里開(kāi)始，這兩個(gè)參數可以從我們導入的SQL里面找到。

開(kāi)啟復制

start slave;

這時(shí)候就完成了復制，在主庫更新一個(gè)數據或者新增數據在從庫都可以查詢(xún)到結果。

在主庫上也可以查詢(xún)的到復制線(xiàn)程的狀態(tài)。

三. 復制的日志格式

MySQL復制的日志格式有三種，根據主庫存放數據的方式不同有以下三種：

mysql5.7以前默認使用statement格式。

設置方式，可以在配置文件設置（首選）：

binlog_format=ROW

或臨時(shí)設置全局變量（當前mysql連接有效）：

查看日志格式
mysql > show variables like 'binlog_format';
設置日志格式
mysql > set binlog_format='row';

由于兩個(gè)主從一般都會(huì )放在同一個(gè)機房里面，兩者之間同步的速度會(huì )會(huì )比較快，為保證強一致性，應該首選行的日志格式記錄（row），保證傳輸素速度可以選擇混合方式（mixed）。
而行的日志格式有下面三種記錄方式：

mysql默認是full，最好修改成minimal。

binlog_row_image=minimal

四. 主從復制延遲

由于主庫和從庫之間不在同一個(gè)主機上，數據同步之間不可以避免地具有延遲，解決的方法有添加緩存，業(yè)務(wù)層的跳轉等待，如果非得從數據庫層面去減緩延遲問(wèn)題，可以從復制時(shí)候的三大步驟（主庫產(chǎn)生日志，主從傳輸日志，從庫還原日志內容）入手：

1.主庫寫(xiě)入到日志的速度

控制主庫的事務(wù)大小，分割大事務(wù)為多個(gè)小事務(wù)。

如插入20w的數據，改成插入多次5000行（可以利用分頁(yè)的思路）

2.二進(jìn)制日志在主從之間傳輸時(shí)間

主從之間盡量在同一個(gè)機房或地域。

日志格式改用MIXED，且設置行的日志格式未minimal，原理詳見(jiàn)上面的日志格式介紹。

3.減少從庫還原日志的時(shí)間

在MySQL5.7版本后可以利用邏輯時(shí)鐘方式分配SQL多線(xiàn)程。

設置邏輯時(shí)鐘：slave_parallel_type=‘logical_clock';

設置復制線(xiàn)程個(gè)數：slave_parallel_workers=4;

五. 需要注意的地方

重啟MySQL最好切換未MySQL用戶(hù)再進(jìn)行操作，不然文件啟動(dòng)后會(huì )有權限問(wèn)題。搭建好MySQL的環(huán)境后就設置好配置里的log-bin選項，這樣以后如果數據庫需要從庫的復制，就不需要重啟數據庫，打斷業(yè)務(wù)的進(jìn)行。需要打開(kāi)主庫的防火墻的對應的mysql端口。由于從庫同步主庫的方式，監聽(tīng)主庫發(fā)送的信息，而不是輪詢(xún)，因此如果出現通信出現了故障，重新連接后如果主庫沒(méi)有進(jìn)行數據更改的操作，從庫不會(huì )同步數據，因此可以通過(guò)插入空事務(wù)的方式同步數據。