目錄：
一瞭吃、mysql主從同步概念說(shuō)明
二、主從同步實(shí)戰(zhàn)操作
三、主從同步實(shí)戰(zhàn)相關(guān)資源
閱讀完本文你的收獲：
- mysql 主從復(fù)制原理的是啥
- 如何實(shí)現(xiàn) mysql 讀寫(xiě)分離
- 如何解決 mysql 主從同步的延時(shí)問(wèn)題
參考：
主從同步詳解：https://juejin.cn/post/6920477753368117261
docker-compose實(shí)戰(zhàn)：https://juejin.cn/post/6844904040216657927
laradock：https://github.com/laradock/laradock
一崩溪、mysql主從同步概念說(shuō)明
MySQL 主從復(fù)制原理的是啥父款？
主庫(kù)將變更寫(xiě)入 binlog 日志溢谤，然后從庫(kù)連接到主庫(kù)之后，從庫(kù)有一個(gè) IO 線程憨攒，將主庫(kù)的 binlog 日志拷貝到自己本地世杀，寫(xiě)入一個(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)垦沉，剛寫(xiě)入主庫(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)題降狠。
主從節(jié)點(diǎn)使用 binglog 文件 + position 偏移量來(lái)定位主從同步的位置，從節(jié)點(diǎn)會(huì)保存其已接收到的偏移量庇楞，如果從節(jié)點(diǎn)發(fā)生宕機(jī)重啟榜配，則會(huì)自動(dòng)從 position 的位置發(fā)起同步。
- 異步模式（默認(rèn)方式）
異步模式下吕晌，主節(jié)點(diǎn)執(zhí)行完客戶端提交的事務(wù)后立即提交事務(wù)并返回給客戶端蛋褥，并不關(guān)心 log dump 線程是否成功地將將此次事務(wù)寫(xiě)進(jìn) binglog 并且發(fā)送給從庫(kù)。假如執(zhí)行事務(wù)的主線程提交事務(wù)后睛驳，log dump 線程還未來(lái)得及寫(xiě)入 binlog烙心，此時(shí)系統(tǒng)宕機(jī)，則會(huì)造成 binglog 中沒(méi)有保存剛才提交的事務(wù)乏沸，造成主從數(shù)據(jù)不一致淫茵。
優(yōu)點(diǎn)：異步模式下，主線程不用關(guān)系同步操作蹬跃，性能最好匙瘪。
缺點(diǎn)：可能導(dǎo)致主從數(shù)據(jù)的不一致
- 半同步復(fù)制
也叫 semi-sync 復(fù)制，指的就是主庫(kù)寫(xiě)入 binlog 日志之后炬转，就會(huì)將強(qiáng)制此時(shí)立即將數(shù)據(jù)同步到從庫(kù)辆苔，從庫(kù)將日志寫(xiě)入自己本地的 relay log 之后，接著會(huì)返回一個(gè) ack 給主庫(kù)，主庫(kù)接收到至少一個(gè)從庫(kù)的 ack 之后才會(huì)認(rèn)為寫(xiě)操作完成了。
優(yōu)點(diǎn)：相比于異步模式贰逾，半同步方式一定程度上保證了數(shù)據(jù)同步的可靠性。
缺點(diǎn)：增加了主庫(kù)響應(yīng)客戶端的延時(shí)骑冗，延時(shí)至少為一個(gè) TCP/IP 的往返時(shí)間，即 binglog 發(fā)送給從庫(kù)至收到從庫(kù)的響應(yīng)時(shí)間先煎。
- 并行復(fù)制
指的是從庫(kù)開(kāi)啟多個(gè)線程贼涩，并行讀取 relay log 中不同庫(kù)的日志，然后并行重放不同庫(kù)的日志薯蝎，這是庫(kù)級(jí)別的并行遥倦。
優(yōu)點(diǎn)：對(duì)比半同步復(fù)制方式，全同步復(fù)制方式數(shù)據(jù)一致性的可靠性進(jìn)一步提高
缺點(diǎn)：執(zhí)行事務(wù)時(shí)占锯，主庫(kù)需要等待所有的從庫(kù)執(zhí)行成功后才能返回袒哥，所以會(huì)大大提高主庫(kù)的響應(yīng)時(shí)間缩筛。
mysql 主從同步的延時(shí)
如果主從延遲較為嚴(yán)重，可以嘗試以下方案
1堡称、分庫(kù)瞎抛，將一個(gè)主庫(kù)拆分為多個(gè)主庫(kù)，每個(gè)主庫(kù)的寫(xiě)并發(fā)就減少了幾倍却紧，此時(shí)主從延遲可以忽略不計(jì)桐臊。打開(kāi) MySQL 支持的并行復(fù)制，多個(gè)庫(kù)并行復(fù)制晓殊。如果說(shuō)某個(gè)庫(kù)的寫(xiě)入并發(fā)就是特別高断凶，單庫(kù)寫(xiě)并發(fā)達(dá)到了 2000/s，并行復(fù)制還是沒(méi)意義巫俺。
2懒浮、重構(gòu)代碼 : 重構(gòu)代碼，插入數(shù)據(jù)后识藤，直接更新，不查詢(xún)
3次伶、若確實(shí)存在必須先插入痴昧，立馬要求查詢(xún)，然后立馬就反過(guò)來(lái)執(zhí)行一些操作冠王，對(duì)這個(gè)查詢(xún)?cè)O(shè)置直連主庫(kù)(不推薦赶撰，這和讀寫(xiě)分離的本意不是背道而馳？)
二柱彻、主從同步實(shí)戰(zhàn)操作
參考laradock完成容器編排豪娜，因?yàn)橹鲝膸?kù)在一臺(tái)服務(wù)器上，所以配置的3310,3312,3313端口映射到容器內(nèi)部3306
ps:相關(guān)代碼和資源在第三節(jié)
- 創(chuàng)建網(wǎng)絡(luò)
因?yàn)槲沂鞘褂玫囊延衝etwork哟楷，你們還需要多一步手動(dòng)創(chuàng)建網(wǎng)絡(luò)
docker network create laradock_backend
- 構(gòu)建并啟動(dòng)容器
docker-compose up -d --build mysql-master-1 mysql-slave-1 mysql-slave-2
- 進(jìn)入master容器瘤载，登錄到mysql,查看binglog情況，確認(rèn)
log_bin為ON
# 進(jìn)入容器
docker exec -it mysql-master-1 /bin/bash
# 登錄mysql
mysql -uroot -proot
# 查看binglog, 確認(rèn) log_bin 為 ON
show variables like 'log_%';
- 創(chuàng)建同步用的賬戶并給賬戶授權(quán)
這里'slave'@'%' 讓slave 可以在任何機(jī)器都可以登錄卖擅，知道Slave節(jié)點(diǎn)IP 的話鸣奔，建議使用IP，增加賬號(hào)安全性惩阶。
# ---------- mysql5.7 ----------
grant file,select,replication slave on *.* to 'slave'@'%' identified by 'slave';
# ---------- mysql8.0 ----------
# 新版的mysql版本已經(jīng)將創(chuàng)建賬戶和賦予權(quán)限的方式分開(kāi)了挎狸，并且更換了默認(rèn)加密方式
# 創(chuàng)建賬戶
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
# 賦予權(quán)限
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%';
# 最后刷新一下權(quán)限
flush privileges;
再次確認(rèn)一下新增的用戶狀態(tài), 不對(duì)就修改一下
use mysql;
select Host,User,plugin from user;
# 已經(jīng)存在了就去修改
# ALTER user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
- 查看Master狀態(tài)
記錄下File和Position,后面會(huì)用到
show master status;
- 新開(kāi)一個(gè)命令行窗口，進(jìn)入slave節(jié)點(diǎn)断楷，登錄mysql锨匆，
# 進(jìn)入容器
docker exec -it mysql-slave-1 /bin/bash
# 登錄mysql
mysql -uroot -proot
- 配置master的信息
change master to master_host='mysql-master-1',master_user='slave',master_password='slave',master_log_file='mysql-master-bin.000005',master_log_pos=541;
注意：【參數(shù)說(shuō)明】需要修改為自己的配置：
master_host ： 主節(jié)點(diǎn)IP, 由于mysql-master 和msql-slave都在同一個(gè)backend網(wǎng)路中，所以可以通過(guò)mysql-master名稱(chēng)訪問(wèn)到冬筒。docker 幫我們做了host解析恐锣。
master_user ： 前面創(chuàng)建的用于同步的賬號(hào)
master_password ： master節(jié)點(diǎn)登錄密碼
master_log_file ： master節(jié)點(diǎn)要同步的binlog文件名茅主。
master_log_pos ： 從binlong文件的哪個(gè)位置開(kāi)始同步。
- 啟停同步
stop slave;
start slave;
# 如果遇到報(bào)錯(cuò)：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
# 重置一下slave
reset slave;
- 查看主從同步狀態(tài)
show slave status\G
Slave_IO_Running 和 Slave_SQL_Running: 兩列的值都為"Yes"侥蒙，這表明 Slave 的 I/O 和 SQL 線程都在正常運(yùn)行暗膜。
Seconds_Behind_Master:表示延遲多少，一般情況為0鞭衩。
到此主從同步已經(jīng)實(shí)現(xiàn)学搜，mysql-slave-2節(jié)點(diǎn)也是相同配置。
三论衍、主從同步實(shí)戰(zhàn)相關(guān)資源
- 文件目錄
|- docker-compose.yml
|- .env
|- mysql-master-1
| |- Dockerfile
| |- my.cnf
| |- docker-entrypoint-initdb.d
| |- createdb.sql.example
|
|- mysql-slave-1
| |- Dockerfile
| |- my.cnf
| |- docker-entrypoint-initdb.d
| |- createdb.sql.example
|- mysql-slave-2
| |- Dockerfile
| |- my.cnf
| |- docker-entrypoint-initdb.d
| |- createdb.sql.example
- docker-compose.yml
version: '3'
networks: #定義網(wǎng)路
laradock_backend:
external: true
#driver: ${NETWORKS_DRIVER}
#-------------------------------------------------------------------
services:
mysql-master-1: # Master 節(jié)點(diǎn)
build:
context: ./${MYSQL_PATH_MASTER_1} #自定義鏡像
args:
- MYSQL_VERSION=${MYSQL_VERSION}
environment:
- MYSQL_DATABASE=${MYSQL_DATABASE} #默認(rèn)創(chuàng)建數(shù)據(jù)庫(kù)
- MYSQL_USER=${MYSQL_USER} #創(chuàng)建用戶
- MYSQL_PASSWORD=${MYSQL_PASSWORD} #用戶密碼
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} #root 用戶秘密
- TZ=${WORKSPACE_TIMEZONE}
volumes:
- "${DATA_PATH_HOST}/master_1:/var/lib/mysql"
- "${MYSQL_ENTRYPOINT_INITDB}:/docker-entrypoint-initdb.d:"
ports:
- "${MYSQL_PORT_MASTER_1}:3306"
container_name: mysql-master-1
networks:
- ${NETWORK}
#-------------------------------------------------------------------
mysql-slave-1: #Slave 節(jié)點(diǎn)
build:
context: ./${MYSQL_PATH_SLAVE_1} #自定義鏡像
args:
- MYSQL_VERSION=${MYSQL_VERSION}
environment:
- MYSQL_DATABASE=${MYSQL_DATABASE} #默認(rèn)創(chuàng)建數(shù)據(jù)庫(kù)
- MYSQL_USER=${MYSQL_USER} #創(chuàng)建用戶
- MYSQL_PASSWORD=${MYSQL_PASSWORD} #用戶密碼
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} #root 用戶秘密
- TZ=${WORKSPACE_TIMEZONE}
volumes:
- "${DATA_PATH_HOST}/slave_1:/var/lib/mysql"
- "${MYSQL_ENTRYPOINT_INITDB}:/docker-entrypoint-initdb.d"
ports:
- "${MYSQL_PORT_SLAVE_1}:3306"
container_name: mysql-slave-1
networks:
- ${NETWORK}
#-------------------------------------------------------------------
mysql-slave-2: #Slave 節(jié)點(diǎn)
build:
context: ./${MYSQL_PATH_SLAVE_2} #自定義鏡像
args:
- MYSQL_VERSION=${MYSQL_VERSION}
environment:
- MYSQL_DATABASE=${MYSQL_DATABASE} #默認(rèn)創(chuàng)建數(shù)據(jù)庫(kù)
- MYSQL_USER=${MYSQL_USER} #創(chuàng)建用戶
- MYSQL_PASSWORD=${MYSQL_PASSWORD} #用戶密碼
- MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} #root 用戶秘密
- TZ=${WORKSPACE_TIMEZONE}
volumes:
- "${DATA_PATH_HOST}/slave_2:/var/lib/mysql"
- "${MYSQL_ENTRYPOINT_INITDB}:/docker-entrypoint-initdb.d"
ports:
- "${MYSQL_PORT_SLAVE_2}:3306"
container_name: mysql-slave-2
networks:
- ${NETWORK}
- .env
### DOCKER 配置 #################################################
NETWORK=laradock_backend
NETWORKS_DRIVER=bridge
WORKSPACE_TIMEZONE=UTC
DATA_PATH_HOST=./data
### MYSQL 配置 #################################################
MYSQL_VERSION=8.0
MYSQL_DATABASE=ebook
MYSQL_USER=sync
MYSQL_PASSWORD=sync
MYSQL_ROOT_PASSWORD=root
MYSQL_ENTRYPOINT_INITDB=./mysql/docker-entrypoint-initdb.d
### MYSQL-MASTER-1 配置 #################################################
MYSQL_PATH_MASTER_1=mysql-master-1
MYSQL_PORT_MASTER_1=3310
### MYSQL-SLAVE-1 配置 #################################################
MYSQL_PATH_SLAVE_1=mysql-slave-1
MYSQL_PORT_SLAVE_1=3312
### MYSQL-SLAVE-2 配置 #################################################
MYSQL_PATH_SLAVE_2=mysql-slave-2
MYSQL_PORT_SLAVE_2=3313
- Dockerfile
ARG MYSQL_VERSION=latest
FROM mysql:${MYSQL_VERSION}
#####################################
# Set Timezone
#####################################
ARG TZ=UTC
ENV TZ ${TZ}
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone && chown -R mysql:root /var/lib/mysql/
COPY my.cnf /etc/mysql/conf.d/my.cnf
CMD ["mysqld"]
EXPOSE 3306
- mysql-master-1/my.cnf
# The MySQL Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
character-set-server=utf8
## 主庫(kù)配置
## 設(shè)置server_id瑞佩，一般設(shè)置為IP,保證唯一就好
server_id=1
# 開(kāi)啟binlog，名字可以隨意取
log-bin=mysql-master-bin
## binlog日志格式,（mixed,statement,row坯台，默認(rèn)格式是statement）
binlog_format=row
## 日志自動(dòng)刪除日期,默認(rèn)值為0炬丸，表示不自動(dòng)刪除。
expire_logs_days=7
## 跳過(guò)主從復(fù)制中遇到的所有錯(cuò)誤或指定類(lèi)型的錯(cuò)誤蜒蕾，避免slave端復(fù)制中斷稠炬。
## 如：1062錯(cuò)誤是指一些主鍵重復(fù)，1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫(kù)數(shù)據(jù)不一致
slave_skip_errors=1062
## 復(fù)制過(guò)濾：也就是指定哪個(gè)數(shù)據(jù)庫(kù)不用同步（mysql庫(kù)一般不同步）
binlog-ignore-db=mysql
#控制binlog的寫(xiě)入頻率咪啡。每執(zhí)行多少次事務(wù)寫(xiě)入一次(這個(gè)參數(shù)性能消耗很大首启，但可減小MySQL崩潰造成的損失)
sync_binlog = 1
#這個(gè)參數(shù)一般用在主主同步中，用來(lái)錯(cuò)開(kāi)自增值, 防止鍵值沖突
auto_increment_offset = 1
#這個(gè)參數(shù)一般用在主主同步中撤摸，用來(lái)錯(cuò)開(kāi)自增值, 防止鍵值沖突
auto_increment_increment = 1
#通過(guò)同步產(chǎn)生的變化將記錄在自己的binlog中
log-slave-updates=ON
- mysql-slave-1/my.cnf
ps:mysql-slave-2只是server_id=3
# The MySQL Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
character-set-server=utf8
## 主庫(kù)配置
## 設(shè)置server_id毅桃，一般設(shè)置為IP,保證唯一就好
server_id=2
# 開(kāi)啟binlog，名字可以隨意取
log-bin=mysql-slave-bin
## binlog日志格式,（mixed,statement,row准夷，默認(rèn)格式是statement）
binlog_format=row
## 日志自動(dòng)刪除日期,默認(rèn)值為0钥飞，表示不自動(dòng)刪除。
expire_logs_days=7
## 跳過(guò)主從復(fù)制中遇到的所有錯(cuò)誤或指定類(lèi)型的錯(cuò)誤衫嵌，避免slave端復(fù)制中斷读宙。
## 如：1062錯(cuò)誤是指一些主鍵重復(fù)，1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫(kù)數(shù)據(jù)不一致
slave_skip_errors=1062
## 復(fù)制過(guò)濾：也就是指定哪個(gè)數(shù)據(jù)庫(kù)不用同步（mysql庫(kù)一般不同步）
binlog-ignore-db=mysql
#控制binlog的寫(xiě)入頻率渐扮。每執(zhí)行多少次事務(wù)寫(xiě)入一次(這個(gè)參數(shù)性能消耗很大论悴，但可減小MySQL崩潰造成的損失)
sync_binlog = 1
# 加快事務(wù)處理速度，犧牲了一定的可靠性墓律。由于Slave 是Master 的備份膀估，所以即使Slave 服務(wù)崩潰或者斷點(diǎn)丟失數(shù)據(jù)，也可以重新啟動(dòng)后從主庫(kù)恢復(fù)耻讽。
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
# 只讀察纯，主從同步線程具有管理員權(quán)限，不受read_only控制，所以能夠正常寫(xiě)入饼记。
read_only=1
