主從復(fù)制的原理
主從復(fù)制的作用
- 數(shù)據(jù)備份和恢復(fù)
- 高可用
- 負(fù)載均衡
主從復(fù)制的原理
主從復(fù)制的原理.png
兩種復(fù)制方法
- 基于位點(diǎn)的復(fù)制
show master status\G;
- 基于GTID的復(fù)制
主從復(fù)制的搭建
基于位點(diǎn)的主從復(fù)制部署流程
- 確定主庫(kù)Binlog是否開啟
- 修改主從server_id
- 主庫(kù)導(dǎo)出數(shù)據(jù)
- 從庫(kù)導(dǎo)入數(shù)據(jù)
- 確定主庫(kù)備份時(shí)的位點(diǎn)
- 在從庫(kù)配置主庫(kù)信息
- 查看復(fù)制狀態(tài)并測(cè)試數(shù)據(jù)是否同步
實(shí)戰(zhàn)
- 確定主庫(kù)Binlog是否開啟
show global variables like "log_bin";
# 查看配置文件Binlog是否開啟
vim /data/mysql/conf/my.cnf
# 查看是否有配飾 log-bin=
- 修改主server_id
# 查看主庫(kù)的server_id
select @@global.server_id;
# 動(dòng)態(tài)修改主庫(kù)的server_id,范圍 1~2^32-1
set global server_id=12161;
# 修改配置文件
vim /data/mysql/conf/my.cnf
server-id = 12161
- 修改從server_id
# 查看主庫(kù)的server_id
select @@global.server_id;
# 動(dòng)態(tài)修改主庫(kù)的server_id
set global server_id=12162;
# 修改配置文件
vim /data/mysql/conf/my.cnf
server-id = 12162
- 關(guān)閉主和從的GTID
# 修改配置文件
vim /data/mysql/conf/my.cnf
gtid_mode=off
# 注釋掉
# enforce_gtid_consistency=on
- 重啟主從的MySQL
/etc/init.d/mysql.server restart
- 主庫(kù)創(chuàng)建復(fù)制用戶
create user 'repl'@'%' identifiled with mysql_native_password by '123456';
grant replication slave on *.* to 'repl'@'%';
- 主庫(kù)導(dǎo)出數(shù)據(jù)
# --single-transaction 開啟一個(gè)事務(wù)進(jìn)行備份窜管，可重復(fù)讀，不堵塞應(yīng)用保證數(shù)據(jù)一致性；--all-databases 備份所有庫(kù)；--master-data=2 記錄備份時(shí)的位置 =2 以注釋的方式記錄栗菜；--set-gtid-purged=off 不增加gtid的信息
mysqldump -uroot -p --single-transaction --all-databases --master-data=2 --set-gtid-purged=off > alldb_bak.sql
# 將備份傳到從庫(kù)
scp alldb_bak.sql 192.168.12.162:/data/backup
- 從庫(kù)導(dǎo)入數(shù)據(jù)
mysql -uroot -p <alldb_bak.sql
- 在從庫(kù)配置主庫(kù)信息
# 先確定備份時(shí)的位點(diǎn)
head -n 30 alldb_bak.sql
# 找到位點(diǎn)信息Binlog，MASTER_LOG_FILE='mysql-bin.000029',MASTER_LOG_POS=699;
# 查看是否已經(jīng)存在主從復(fù)制關(guān)系
show slave status\G;
# 在從庫(kù)建立主從復(fù)制的鏈接;master_host 主庫(kù)的ip地址;master_user 主庫(kù)的備份用戶；master_log_file 主庫(kù)在全備時(shí)的Binlog
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000029',master_log_pos=699;
start slave;
- 查看復(fù)制狀態(tài)
show slave status\G;
- 測(cè)試數(shù)據(jù)是否同步
... ...
使用Xtrabackup創(chuàng)建主從復(fù)制
- 主庫(kù)使用Xtrabackup進(jìn)行全備
xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream
- 將全備傳到從庫(kù)
scp xtrabackup.xbstream 192.168.12.162:/data/backup/recover
- 清空從庫(kù)
# 先停止從庫(kù)
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
- 把全備導(dǎo)入到新實(shí)例
cd /data/backup/recover
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
- 啟動(dòng)MySQL
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start
ps -ef | grep mysql
- 查看備份時(shí)的位點(diǎn)信息
cat xtrabackyp_binlog_info
- 創(chuàng)建復(fù)制關(guān)系
# 查看是否已經(jīng)存在主從復(fù)制關(guān)系
show slave status\G;
# 在從庫(kù)建立主從復(fù)制的鏈接;master_host 主庫(kù)的ip地址;master_user 主庫(kù)的備份用戶庇茫；master_log_file 主庫(kù)在全備時(shí)的Binlog
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000030',master_log_pos=196;
# 啟動(dòng)主從復(fù)制
start slave;
- 查看主從狀態(tài)
show slave status\G;
- 測(cè)試同步
... ...
基于GTID的主從復(fù)制
GTID是什么
- GTID的組成
# uuid+pid組成；pid 代表已經(jīng)提交的事務(wù)數(shù)量
show global variables like "%gtid_purged%";
# 查看MySQL的uuid,MySQL的唯一標(biāo)識(shí)
show global variables like "%server_uuid%";
# 查看uuid持久化路徑
cat /data/mysql/data/auto.cnf
# 查看控制gtid開啟和關(guān)閉的參數(shù);gtid_mode=off_permissive/on_permissive/on/off
show global variables like "gtid_mode"螃成；
- gtid_executed
用來(lái)存儲(chǔ)當(dāng)前實(shí)例上已經(jīng)執(zhí)行的事務(wù)gtid集合
show global variables like "gtid_executed";
# 或者
select * from mysql.gtid_executed;
- enforce_gtid_consistency
控制事務(wù)是不是允許違反gtid一致性旦签；off所有事務(wù)都運(yùn)行違反gtid一致性；on不允許寸宏；warn
set global enforce_gtid_consistency = on;
基于GTID復(fù)制的優(yōu)勢(shì)
- 可以知道事務(wù)在哪個(gè)實(shí)例上提交的
- 比較方便進(jìn)行復(fù)制結(jié)構(gòu)上的故障轉(zhuǎn)移
- 很方便判斷主從一致性
使用GTID的注意事項(xiàng)
- 事務(wù)和非事務(wù)引擎不能在同一個(gè)事務(wù)里
- create table ... select
- 建議設(shè)置 --enforce-gtid-consistency
位點(diǎn)復(fù)制改成GTID復(fù)制
- 查看GTID狀態(tài)
show slave status\G;
show global variables like "gtid_mode";
show global variables like "enforce_gtid_consistency";
# 恢復(fù)默認(rèn)狀態(tài)
set global enforce_gtid_consistency = off;
- 修改enforce_gtid_consistency=warn
# 主庫(kù)從庫(kù)執(zhí)行,等待一段時(shí)間后業(yè)務(wù)沒有問題再修改為on
set global enforce_gtid_consistency = warn;
- 修改enforce_gtid_consistency=on
# 主庫(kù)從庫(kù)執(zhí)行
set global enforce_gtid_consistency = on;
- 修改gtid_mode=off_permisssive
# 主庫(kù)從庫(kù)
set global gtid_mode=off_permisssive;
- 修改gtid_mode=on_permissive;
# 主庫(kù)從庫(kù)
set global gtid_mode=on_permissive;
- 查看正在執(zhí)行的匿名事務(wù)數(shù)量
# 0 沒有正在執(zhí)行的匿名事務(wù)數(shù)量就可以執(zhí)行下一步
show status like 'ongoing_anonymous_transaction_count';
- 刷新日志
# 主庫(kù)從庫(kù)
flush logs;
- 開啟GTID
# 主從庫(kù)
set global gtid_mode=on;
- 參數(shù)持久化
# 主從庫(kù)
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
- 修改復(fù)制參數(shù)
# 從庫(kù)執(zhí)行
stop slave;
change master to master_auto_position=1;
start slave;
- 查看復(fù)制狀態(tài)
show slave status\G;
- 測(cè)試
... ...
從0部署GTID復(fù)制
- 確定主庫(kù)Binlog是否開啟
show global variables like "log_bin";
# 或者查看配置文件
cat /data/mysql/conf/my.cnf
log-bin=/data/mysql/binlog/mysql-bin
- 主從修改server_id
# 主從server_id需要不一樣
select @@global.server_id;
# 修改server_id
set global server_id=xxx;
# 且配置文件也要修改
vim /data/mysql/conf/my.cnf
server-id=xxx;
- 主從開啟GTID
set global gtid_mode=off_permissive;
set global gtid_mode=on_permissive;
set global gtid_mode=on;
set global enforce_gtid_consistency=on;
# 修改配置文件
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
- 創(chuàng)建復(fù)制用戶
#主庫(kù)
create user 'repl'@'%' identifiled with mysql_native_password by '123456';
grant replication slave on *.* to 'repl'@'%';
- 主庫(kù)導(dǎo)入數(shù)據(jù)
mysqldump -uroot -p --single-transaction --all-databases --master-data=2 --set-gtid-purged=on > alldb.sql
# 將sql傳輸?shù)綇膸?kù)
scp alldb.sql 192.168.12.162:/data/backup/
- 從庫(kù)導(dǎo)入數(shù)據(jù)
# 先清空gtid集合
reset master;
mysql -uroot -p < alldb.sql
- 在從庫(kù)配置主庫(kù)信息
# 清空以前的主從關(guān)系
stop slave;
reset slave;
# 配置主從庫(kù)信息
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
- 查看復(fù)制狀態(tài)
show slave status\G;
- 測(cè)試
... ...
多線程復(fù)制
MySQL5.6的并行復(fù)制
- 支持庫(kù)級(jí)別的并行復(fù)制
- 配置方式
# 在從庫(kù)執(zhí)行
show global variables like "slave_parallel_sorkers";
# 開啟庫(kù)級(jí)別的多線程復(fù)制
set global slave_parallel_workers=4;
# 重啟主從復(fù)制
stop slave;
start slave;
# 查看復(fù)制線程
show processlist宁炫；
- 5.6并行復(fù)制的不足
基于庫(kù)
MySQL5.7的并行復(fù)制
- 5.7并行復(fù)制原理
基于組提交
- 配置方式
show global variables like "slave_parallel_type";
stop slave sql_thread;
set global slave_parallel_workers=8;
set global slave_parallel_type=logical_clock;
# 啟動(dòng)sql線程
start slave sql_thread;
flush logs;
show processlist；
MySQL5.7.22及之后版本的并行復(fù)制
- MySQL5.7.22的并行復(fù)制介紹
# 新增一個(gè)參數(shù)氮凝，在主庫(kù)配置淋淀，控制是否啟動(dòng)新策略；writeset;writeset_session;
show global variables like "binlog_transaction_dependency_tracking";
- 配置方式
# 在主庫(kù)
set global binlog_transaction_dependency_tracking=writeset;
# 在從庫(kù)
set global slave_parallel_workers=8;
stop slave sql_thread;
set global slave_parallel_type=logical_clock;
# 控制二進(jìn)制日志提交再將二進(jìn)制日志同步到磁盤之前等待的微秒數(shù)
set global binlog_group_commit_sync_delay=3000;
start slave sql_thread;
# 刷新日志
flush logs;
# 查看復(fù)制線程覆醇，查看配置前后變化
show processlist朵纷；
一主兩從和級(jí)聯(lián)的切換
部署一套基于位點(diǎn)的一主兩從架構(gòu)
- 部署三套MySQL
# 保證三臺(tái)mysql的server_id不一致
vim /etc/mysql/conf/my.cnf
# 動(dòng)態(tài)修改
set global server_id=12163;
- 修改配置文件
# 在三臺(tái)mysql修改配置參數(shù)
vim /data/mysql/conf/my.cnf
#關(guān)閉gtid
gtid_mode=off
# enforce_gtid_consistency=on
# 開啟一個(gè)參數(shù)
log-slave-updates=1
# 重啟
/etc/init.d/mysql.server restart
- 創(chuàng)建一主兩從架構(gòu)
# 主庫(kù)
# 創(chuàng)建復(fù)制用戶
create user 'repl'@'%' identifiled with mysql_native_password by '123456';
# 導(dǎo)出主庫(kù)數(shù)據(jù)
xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream
# 將備份傳到兩臺(tái)從庫(kù)
scp xtrabackup.xbstream 192.168.12.xx:/data/backup/recover
# 兩臺(tái)從庫(kù)操作
# 確認(rèn)磁盤空間是否足夠
df -TH
# 停掉mysql
/etc/init.d/mysql.server stop
# 清空從庫(kù)的數(shù)據(jù)和binlog目錄
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
# 解壓備份
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 恢復(fù)完成后，修改屬組
shown -R mysql.mysql /data/mysql
# 啟動(dòng)
/etc/init.d/mysql.server start
ps -ef | grep mysql
# 查看備份時(shí)的位點(diǎn)信息
cat xtrabackup_binlog_info
# 查看復(fù)制狀態(tài),為空
show slave status\G;
# 創(chuàng)建主從復(fù)制關(guān)系
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000050',master_log_pos=196;
start slave;
show slave status\G;
- 測(cè)試
... ...
基于位點(diǎn)的復(fù)制架構(gòu)切換
- 一主兩從改成級(jí)聯(lián)
# 停掉兩臺(tái)同步復(fù)制
stop slave;
# 在一臺(tái)從庫(kù)上執(zhí)行
show slave status\G;
# 記住兩個(gè)參數(shù)
Relay_Master_Log_File:mysql-bin.000050 當(dāng)前從庫(kù)執(zhí)行到的主庫(kù)binlog
Exec_Master_Log_Pos:733 當(dāng)前從庫(kù)執(zhí)行到的主庫(kù)位點(diǎn)
# 查看當(dāng)前從庫(kù)的位點(diǎn)
show master status\G; # 當(dāng)前從庫(kù)的位點(diǎn)和主庫(kù)的位點(diǎn)要一樣 Position=Exec_Master_Log_Pos
# 啟動(dòng)當(dāng)前復(fù)制
start slave;
# 在另一臺(tái)從庫(kù)執(zhí)行
# 啟動(dòng)復(fù)制到特定位點(diǎn),前一臺(tái)從庫(kù)的復(fù)制位點(diǎn)信息
start slave until master_log_file='mysql-bin.000050',master_log_pos=733;
# 查看復(fù)制狀態(tài)永脓，會(huì)自動(dòng)關(guān)閉sql線程Slave_SQL_Running=No
show slave status\G;
# 停掉主從復(fù)制
stop slave;
# 修改復(fù)制鏈接到前一臺(tái)從庫(kù)袍辞，位點(diǎn)信息為前一臺(tái)從庫(kù)的位點(diǎn)信息
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_log_file='mysql-bin.000051',master_log_pos=742;
start slave;
show slave status\G;
- 級(jí)聯(lián)改成一主兩從
# 在第一臺(tái)從庫(kù)操作
stop slave;
# 查看自己的當(dāng)前位點(diǎn)信息
show master status\G;
# 查看對(duì)應(yīng)的主庫(kù)binlog和位點(diǎn)
show slave status\G;
# 在第二臺(tái)從庫(kù)操作
# 對(duì)比和第一臺(tái)從庫(kù)的binlog和位點(diǎn)信息是否對(duì)應(yīng)
show slave status\G;
# 如果和第一臺(tái)是一致的，則停止
stop slave;
# 在第一臺(tái)從庫(kù)操作
start slave;
# 在第二臺(tái)從庫(kù)操作
# 重新配置復(fù)制信息;master_host 填主庫(kù)ip常摧；master_log_file和master_log_pos填寫第一臺(tái)從庫(kù)上對(duì)應(yīng)的主庫(kù)位點(diǎn)信息
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_log_file='mysql-bin.000050',master_log_pos=1073;
start slave;
基于GTID的復(fù)制架構(gòu)切換
- 基于位點(diǎn)的復(fù)制改成基于GTID的復(fù)制
# 在三臺(tái)mysql操作
# 設(shè)置warn后搅吁，查看是否有違反gtid一致性的sql語(yǔ)句，好即時(shí)調(diào)整
set global enforce_gtid_consistency=warn;
# 如果一段時(shí)間沒有問題落午，則設(shè)置為on
set global enforce_gtid_consistency=on;
set global gtid_mode=off_permissive;
set global gtid_mode=on_permissive;
# 查看正在執(zhí)行的匿名事務(wù)數(shù)量
show status like 'ongoing_anonymous_transaction_count';
flush logs;
set global gtid_mode=on;
# 修改配置文件谎懦，持久化配置
vim /data/mysql/conf/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
# 在每臺(tái)從庫(kù)
stop slave;
change master to master_auto_position=1;
start slave;
# 查看復(fù)制狀態(tài)
show slave status\G;
- GTID復(fù)制一主兩從改級(jí)聯(lián)
# 在第二臺(tái)從庫(kù)執(zhí)行
stop slave;
# master_host為第一臺(tái)從庫(kù)的ip
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
- GTID級(jí)聯(lián)改一主兩從
# 在第二臺(tái)從庫(kù)執(zhí)行
stop slave;
# master_host為主庫(kù)ip
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
show slave status\G;
- 建議生產(chǎn)環(huán)境用GTID復(fù)制
忽略某張表的復(fù)制或者只復(fù)制某張表
只復(fù)制某一個(gè)庫(kù)
- 配置方法
# 在從庫(kù)
# 停止SQL線程
stop slave sql_thread;
# 復(fù)制多個(gè)庫(kù)replicate_do_db=(db1,db2)
change replication filter replicate_do_db=(db1);
start slave sql_thread;
# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate_do_db=db1
# 查看狀態(tài)
show slave status\G;
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_do_db=();
start slave sql_thread;
忽略某個(gè)庫(kù)的復(fù)制
- 配置方法
# 在從庫(kù)
# 停止SQL線程
stop slave sql_thread;
change replication filter replicate_ignore_db=(db1);
start slave sql_thread;
# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-ignore-db=db1
# 查看復(fù)制狀態(tài)
show slave status\G;
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_ignore_db=();
start slave sql_thread;
只復(fù)制指定的表
- 配置方法
stop slave sql_thread;
change replication filter replicate_do_table=(db1.table01);
start slave sql_thread;
# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-do-table=db1.table01
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_do_table=();
start slave sql_thread;
忽略復(fù)制指定的表
- 配置方法
stop slave sql_thread;
change replication filter replicate_ignore_table=(db1.log01);
start slave sql_thread;
# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-ignore-table=db1.table01
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_ignore_table=();
start slave sql_thread;
指定同步某些表
- 配置方法
stop slave sql_thread;
# 只復(fù)制db1里面以table開頭的表
change replication filter replicate_wild_do_table=('db1.table%');
start slave sql_thread;
# 如果需要持久化,新增配置
vim /data/mysql/conf/my.cnf
replicate-wild-do-table=db1.table%
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_wild_do_table=();
start slave sql_thread;
忽略同步某些表
- 配置方法
stop slave sql_thread;
change replication filter replicate_wild_ignore_table=('db1.table%');
start slave sql_thread;
- 取消復(fù)制過濾
stop slave sql_thread;
change replication filter replicate_wild_ignore_table=();
start slave sql_thread;
復(fù)制延遲如何處理？
- 復(fù)制延遲的原因
- 主庫(kù)增刪改并發(fā)大
# 模擬主庫(kù)并發(fā)寫入導(dǎo)入從庫(kù)延遲寫入
# 主庫(kù)溃斋，使用壓測(cè)工具
# 創(chuàng)建壓測(cè)庫(kù)
create database sysbench_ab;
# --mysql-db=sysbench_db壓測(cè)的數(shù)據(jù)庫(kù)界拦；--threads=4多少個(gè)線程；--table_size=500000 每張表寫入50萬(wàn)行數(shù)據(jù)梗劫；--tables=4創(chuàng)建壓測(cè)表的個(gè)數(shù)
sysbench --db-driver=mysql --mysql-host=192.168.12.161 --mysql-port=3306 --mysql-user='repl_rw' --mysql-password='123456' --mysql-db=sysbench_db --threads=4 --table_size=500000 --tables=4 --time=100 oltp_write_only prepare
# 從庫(kù)查看是否有延遲
show slave status\G;
- 大表在做DDL
# 在測(cè)試庫(kù)里操作
use sysbench_ab;
show tables;
# after c; mysql8.0.12開始InnoDB原生支持快速加列享甸，只能在表的最后加列
alter table sbtest1 add column d char(10) after c;
# 執(zhí)行過程中查看從庫(kù)情況 Seconds_Behind_Master:xx
show slave status\G;
- 從庫(kù)備份導(dǎo)致延遲
- 大事務(wù)
# 在壓力測(cè)試表中
insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
# 等待主庫(kù)執(zhí)行完后在從庫(kù)查看延遲狀態(tài)
show slave status\G;
- 從庫(kù)機(jī)器配置差
怎么判斷延遲
- Seconds_Behind_Master
表示從庫(kù)延遲的秒數(shù)；0 可能沒有延遲梳侨，不準(zhǔn)確可能網(wǎng)絡(luò)延遲
- 對(duì)比位點(diǎn)
Master_Log_File;Relay_Master_Log_File是否相等蛉威。Read_Master_Log_Pos；Exec_Master_Log_Pos是否相等走哺。
- 對(duì)比GTID
Retrievec_Gtid_Set;Executed_Gtid_Set最后的數(shù)據(jù)是否一致（最后一個(gè)事務(wù)的id）
- 讓ChatGPT幫忙寫一個(gè)判斷數(shù)據(jù)庫(kù)延遲的腳本
幫忙寫一個(gè)MySQL復(fù)制延遲的判斷腳本
開啟GTID的蚯嫌，可以判斷show slave status結(jié)果中的 Retrieved_Gtid_Set 減去 Executed_Gtid_Set的值來(lái)計(jì)算從庫(kù)落后的事務(wù)數(shù)
并每秒輸出一次上面計(jì)算出來(lái)的落后事務(wù)數(shù)，不要通過查詢表的方式計(jì)算落后事務(wù)數(shù)
比如
Retrieved_Gtid_Set: ec992f67-a08b-1ed-9a9f-024255bd70b6:4429853-4431720
Executed Gtid Set ec992f67-a08b-11ed-9a9f-02425567066:1-4431655
就需要計(jì)算4431720-4431655
并提供腳本里MySQL用戶的創(chuàng)建命令，用戶名用delay_check择示，密碼用Yd_asdfa
需要的是Shell腳本
測(cè)試束凑，重新創(chuàng)建測(cè)試庫(kù)，壓測(cè)对妄，查看延遲信息
主從延遲處理方法
- 開啟多線程
- 調(diào)整一些MySQL參數(shù)
show global variables like "innodb_flush_log_at_trx_commit";
# sysc_binlog=0 二進(jìn)制日志從不同步到磁盤湘今；1 二進(jìn)制日志每個(gè)事務(wù)都會(huì)刷盤；100 每100個(gè)事務(wù)提交一次
show global variables like "sysc_binlog";
- 調(diào)整從庫(kù)機(jī)器配置
- 避免大事務(wù)
- 使用PT工具執(zhí)行耗時(shí)長(zhǎng)的DDL
- 調(diào)整架構(gòu)
復(fù)制常見問題(報(bào)錯(cuò))處理辦法
- server_id重復(fù)
- 端口不通
- 從庫(kù)磁盤空間滿了
- 主庫(kù)要新增的內(nèi)容在從庫(kù)已經(jīng)有了
# 比如主庫(kù)創(chuàng)建從庫(kù)已經(jīng)存在表
# 在從庫(kù)
stop slave;
show slave status\G;
set @session.gtid_next='Executed_Gtid_Set后面的gtid信息';
begin;commit;
start slave;
show slave status\G;
# 如果是基于位點(diǎn)的復(fù)制
stop slave;
set global sql_slave_skip_count=1;
start slave;
show slave status\G;
- 主庫(kù)要更新的記錄從庫(kù)沒有
# 模擬剪菱，主庫(kù)新增兩條記錄摩瞎，從庫(kù)刪除一條，主庫(kù)更新從庫(kù)刪除的那條數(shù)據(jù)
# 從庫(kù) 報(bào)錯(cuò)
show slave status\G;
cd /data/mysql/binlog/
# 解析報(bào)錯(cuò)的日志
mysqlbinlog mysql-relay-bin.000017 --start-position=1129 --base64-output=decode-rows -v > /tmp/1.sql
cat 1.sql
# 在從庫(kù)補(bǔ)全丟失的記錄
... ...
stop slave;start slave;
show slave status\G;
- 找不到主庫(kù)的binlog位點(diǎn)
# 1. 可以將報(bào)錯(cuò)的從庫(kù)轉(zhuǎn)移到正常的從庫(kù)補(bǔ)全復(fù)制孝常，主從改級(jí)聯(lián)
# 2. 重建復(fù)制（刪除從庫(kù)數(shù)據(jù)旗们，重新簡(jiǎn)歷從庫(kù)復(fù)制）
三種日志格式對(duì)復(fù)制的影響
# 修改binlog日志格式
set global binlog_format=row
- 開始支持復(fù)制時(shí)的statement格式
mysql5.1.5之前支持，這種格式下binlog日志相對(duì)較少构灸，但是涉及到跨庫(kù)更新或者使用結(jié)果不確定的函數(shù)時(shí)上渴，如使用uuid則容易出現(xiàn)主從數(shù)據(jù)不一致的情況
- 開始支持Row格式復(fù)制
主從一致性得到了保障，binlog日志量相對(duì)statement較多;生產(chǎn)環(huán)境強(qiáng)烈建議使用row格式
- 新增mixed格式
mysql5.1.8開始支持喜颁。statement和row的混合方式稠氮。
異步復(fù)制
傳統(tǒng)的MySQL復(fù)制是異步復(fù)制
-
原理
異步復(fù)制原理.png、
半同步復(fù)制
mysql5.5開始支持
-
原理
半同步復(fù)制.png 配置方法
# 主庫(kù) 在MySQL中安裝插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 查看
select * from mysql.plugin;
# 開啟半同步復(fù)制插件
set global rpl_semi_sync_master_enabled=1;
# 從庫(kù) 安裝半同步復(fù)制插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 開啟半同步復(fù)制插件
set global rpl_semi_sync_master_enabled=1;
# 主庫(kù)從庫(kù) 修改配置文件半开，新增配置
vim /data/mysql/conf/my.cnf
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so";
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
# 重啟MySQL,如果有業(yè)務(wù)在使用隔披，需要臨時(shí)開啟，可以不重啟MySQL寂拆，但要保證配置文件修改正確
/etc/init.d/mysql.server restart
# 如果需要在線修改奢米，還需要重啟io線程
stop slave io_thread;
start slave io_thread;
# 查看半同步執(zhí)行狀態(tài)，主庫(kù)
show global status like "%semi%";
增強(qiáng)半同步復(fù)制
MySQL5.7開始支持
-
原理
增強(qiáng)半同步復(fù)制.png 參數(shù)
# rpl_semi_sync_master_wait_point 默認(rèn)AFTER_SYNC 半同步復(fù)制策略纠永；after_commit 增強(qiáng)半同步復(fù)制
show global variables like "rpl_semi_sync_master_wait_point";
組復(fù)制
MySQL5.7推出
-
原理
組復(fù)制.png
復(fù)制相關(guān)的日志參數(shù)
配置雙主相關(guān)環(huán)境
雙主 A是B的從庫(kù)鬓长，B是A的從庫(kù)
# 使用之前的主從復(fù)制環(huán)境
# 在從庫(kù)
show slave status\G;
# 在主庫(kù)
show slave status\G;
stop slave;
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
start slave;
show slave status\G;
# 雙寫
# 在主庫(kù)
set global auto_increment_increment=2;
set global auto_increment_offset=1;
# 在從庫(kù)
set global auto_increment_increment=2;
set global auto_increment_offset=2;
# 雙寫，設(shè)置上列參數(shù)可以避免兩庫(kù)在同時(shí)寫入數(shù)據(jù)主鍵沖突的情況尝江；
# 生產(chǎn)環(huán)境不建議使用雙寫
復(fù)制過濾參數(shù)
| 參數(shù) | 作用 |
| :----:|:----:|
| replicate_do_db | 只同步某個(gè)庫(kù) |
| replicate_ignore_db | 忽略同步某個(gè)庫(kù) |
| replicate_do_table | 只同步某些表 |
| replicate_ignore_table | 忽略同步某些表 |
| replicate_wild_do_table | 指定同步某些表涉波，支持正則 |
| replicate_wild_ignore_table | 忽略同步某一些表，支持正則 |
| binlog_do_db | 只記錄指定數(shù)據(jù)庫(kù)的Binlog |
| binlog_ignore_db | 不記錄指定數(shù)據(jù)庫(kù)的Binlog |多線程復(fù)制相關(guān)配置
| 參數(shù) | 作用 |
| :----:|:----:|
| slave_parallel_workers | 控制復(fù)制的線程數(shù) |
| slave_parallel_type | 控制并行復(fù)制的策略 |
| binlog_transaction_dependency_tracking | 5.7.22新增的控制并發(fā)策略的參數(shù) |
| binlog_group_commit_sync_delay | 控制將二進(jìn)制日志文件同步到磁盤之前二進(jìn)制日志是交等待的微秒數(shù) |半同步復(fù)制相關(guān)配置
| 參數(shù) | 作用 |
| :----:|:----:|
| rpl_semi_sync_master_enabled | 主庫(kù)開啟半同步復(fù)制 |
| rpl_semi_sync_slave_enabled | 從庫(kù)開啟半同步復(fù)制 |
| rpl_semi_sync_master_wait_point | 是否開啟增強(qiáng)半同步復(fù)制 |
通過復(fù)制來(lái)恢復(fù)誤刪的庫(kù)
- 在新實(shí)例恢復(fù)全備數(shù)據(jù)
# 前提茂装，主從復(fù)制關(guān)系怠蹂，主全備
... ...
# 準(zhǔn)備一新實(shí)例，將全備傳輸?shù)叫聦?shí)例
scp xtrabackup.sbstream 192.168.12.164:/data/backup/recover
# 新實(shí)例
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
#解壓備份
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
# 恢復(fù)完成后少态，修改屬組
shown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start
- 將新實(shí)例配置成原實(shí)例的從庫(kù)
# 新實(shí)例
show slave status\G;
stop slave;
# 清空原實(shí)例的復(fù)制關(guān)系
reset slave;
change master to master_host='192.168.12.162',master_user='repl',master_password='123456',master_auto_position=1;
# 一定不能先啟動(dòng)復(fù)制
- 確定回檔時(shí)間點(diǎn)的位點(diǎn)
# 主庫(kù)
cd /data/mysql/binlog/
# 找到誤操作的binlog
cp mysql-bin.000069 /data/backup/
cd /etc/init.d/mysql.server stop
# 解析binlog,預(yù)估發(fā)送誤操作的時(shí)間范圍
mysqlbinlog mysql-bin.000069 --start-datetime='2023-07-31 23:30:00' --stop-datetime='2023-07-31 23:59:00‘ --base64-output=decode-rows -v > /data/backup/1.sql
# 找到誤操作的binlog
cat 2023-07-31 23:30:00'
- 新實(shí)例同步到誤操作前一個(gè)事務(wù)
# 在新實(shí)例
start slave io_thread;
start slave sql_thread until sql_before_gtids="3ebhb-ahsb3-3e3hb-000-12312eq:1243489";
show slave status\G;
- 確定誤操作前的數(shù)據(jù)
- 清空復(fù)制關(guān)系
# 新實(shí)例
stop slave;
reset slave;
- 導(dǎo)出新實(shí)例中誤刪除的數(shù)據(jù)
mysqldump -uroot -p --set-gtid-purged=off -B recover > recover.sql
scp recover.sql 192.168.12.161:/data/backup/
- 在誤操作的實(shí)例導(dǎo)入誤刪除的庫(kù)
mysql -uroot -p < recover.sql
- 確定數(shù)據(jù)是否已經(jīng)恢復(fù)
... ...
通過延遲從庫(kù)恢復(fù)數(shù)據(jù)
- 配置延遲從庫(kù)
# 主庫(kù)全量備份
xtrabackup --dafults-file=/data/mysql/conf/my.cnf -uroot -p --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream
# 備份傳到從庫(kù)
scp xtrabackup.xbstream 192.168.12.161:/data/backup/
# 從庫(kù)
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf
# 解壓
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./
# 移動(dòng)備份目錄
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start
# 查看是否存在主庫(kù)備份過來(lái)的主從信息
show slave status\G;
stop slave;
reset slave;
# master_delay=3600 從庫(kù)延遲的時(shí)間，單位秒
change master to master_host='192.168.12.161',master_user='repl',master_password='123456',master_delay=3600,master_auto_position=1;
start slave;
show slave status\G;
- 測(cè)試
... ...
恢復(fù)操作
- 確定誤操作的GTID
# 主庫(kù) 進(jìn)入到二進(jìn)制日志文件夾
cd /data/mysql/binlog
# 找到誤操作期間的binlog
cp mysql-bin.000070 /data/backup
cd /data/backup
mysqlbinlog mysql-bin.000070 --start-datetime='2023-07-31 23:30:00' --stop-datetime='2023-07-31 23:59:00‘ --base64-output=decode-rows -v > /data/backup/1.sql
cat 1.sql
- 配置同步到誤操作前一個(gè)事務(wù)
# 主庫(kù)
stop slave;
start slave sql_thread until sql_before_gtids='誤操作的事務(wù)gtid值';
show slave status\G;
# 從庫(kù)
stop slave;
# 停掉延遲復(fù)制
change master to master_delay=0;
start slave sql_thread until sql_before_gtids='誤操作的事務(wù)gtid值';
start slave io_thread;
show slave status\G;
- 導(dǎo)出誤操作的數(shù)據(jù)在主庫(kù)恢復(fù)
mysqldump -u'root' -p --set-gtid-purged=off -B recover1 > recover.sql
scp recover.sql 192.168.12.161:/data/backup/
# 主庫(kù)
mysql -uroot -p < recover.sql
通過ChatGPT編寫復(fù)制創(chuàng)建腳本
編寫一個(gè)自動(dòng)創(chuàng)建主從復(fù)制的Shel腳本
復(fù)制是基于GTID的
腳本是在從庫(kù)執(zhí)行
主庫(kù)的IP是192168.12.161
主庫(kù)的備份用戶是u_bak易遣，密碼是ljid71Gcd_a
需要在從庫(kù)通過mysgldump遠(yuǎn)程備份主庫(kù)的數(shù)據(jù)彼妻，再導(dǎo)入到從庫(kù)
主庫(kù)的復(fù)制用戶是repl，密碼是Uid_dQc63
從庫(kù)的用戶是root，密碼是Auca@502846303
主從復(fù)制的原理.png
