MySQL主從同步+binlog詳解
一、簡介
MySQL內建的復制功能是構建大型，高性能應用程序的基礎 通過將MySQL的某一臺主機（master）的數(shù)據(jù)復制到其他主機（slaves）上，并重新執(zhí)行一遍來執(zhí)行 復制過程中一臺服務器充當主服務器，而其他一個或多個其他服務器充當從服務器
主從復制原理
主要基于MySQL二進制日志 主要包括三個線程（2個I/O線程，1個SQL線程）
LNMP（centos7，mysql5.6）
vmware workstation pro配置了3個虛擬機，均安裝了LNMP環(huán)境：
- master： 192.168.0.105
- slave：192.168.0.106 、192.168.0.107
二、原理
（1）主數(shù)據(jù)庫master進行增刪改操作后，相應操作記錄的語句（比如 create database test）會記錄到binlog日志文件中（binlog日志文件一般和數(shù)據(jù)庫data文件夾在一起）。
（2）從數(shù)據(jù)庫Slave會請求主數(shù)據(jù)庫的binlog日志文件，拷貝到Slave的中繼日志中，然后在自己的從數(shù)據(jù)庫上自動執(zhí)行相同的操作語句，進而實現(xiàn)主從的同步。
注：這里，我們所需要配置的只是主從環(huán)境以及開啟binlog日志，其他的mysql會自動完成。
二、配置主從同步
2.1環(huán)境準備
克隆三臺沒有下過mysql的虛擬機（或恢復快照）
ip為
- master 192.168.1.112
- slave1 192.168.1.113
- slave2 192.168.1.114
下載mysql：yum install mysql-server -y
不會下載或者下載報錯：MySQL數(shù)據(jù)庫安裝---離線下載
2.2主從配置
2.2.1【master】（主服務器）
1.）選舉112為主服務器
vim /etc/my.cnf [client] user=root password=ABc1234 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld. log pid-file=/var/run/mysqld/mysqld.pid server_id=11 #可以是任意正整數(shù)，一般比slave大
然后開啟MySQL服務
2.）在數(shù)據(jù)庫中創(chuàng)建用戶，以及授權，修改master庫的密碼加密方式
- repl 你所創(chuàng)建的用戶名
- MySQL@123 用戶密碼
mysql> create user repl@'%'identified with mysql_native_password by 'MySQL@123'
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)
查看主配置狀態(tài)：show master status;
3 ）關閉防火墻，禁用SElinux
[root@openeuler-1 ~] systemctl stop firewalld [root@openeuler-1 ~] setenforce 0 setenforce: SELinux is disabled #已被禁用 [root@openeuler-1 ~] getenforce #查看SElinux狀態(tài) Disabled
2.2.2【slave】從服務器
【slave1】和【slave2】
1.）配置主配置文件，添加一個server_id（唯一標識）
vim /etc/my.cnf
- slave1的 113
- slave2的 114
配置后開啟/重啟mysql服務：systemctl start mysql.service/systemctl restart mysql.service
2.）在從mysql上進行部署，連接主庫（因為連接的是同一個主庫，兩個slave操作一樣）
ps：如果start slave后，要重新連接主庫（change maste to), 必須先stop slave
- 從庫指向主庫創(chuàng)建的host，用戶名，密碼，二進制文件，以及節(jié)點
mysql> change master to
-> master_host='192.168.1.112',
-> master_user='repl',
-> master_password='MySQL@123',
-> master_log_file='binlog.000001',
-> master_log_pos=980;
Query OK, 0 rows affected, 8 warnings (0.01 sec)3.）開啟主從
mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
4.) 關閉防火墻，禁用SElinux
[root@openeuler-1 ~] systemctl stop firewalld [root@openeuler-1 ~] setenforce 0 setenforce: SELinux is disabled #已被禁用 [root@openeuler-1 ~] getenforce #查看SElinux狀態(tài) Disabled
5.) 查看是否成功并測試
三、基于binlog的主從同步
3.1【主服務器】
1.) 開啟binlog
在id后面添加兩行
[root@openeuler-1 Mysql]# vim /etc/my.cnf server_id=11 gtid_mode=ON enforce-gtid-consistency=ON [root@openeuler-1 Mysql]# systemctl restart mysqld.service
3.2【從服務器】
1.）暫停slave，進入配置文件添加兩行，重啟服務（重啟更改后的配置才會生效）
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> \q Bye [root@openeuler-1 Mysql]# vim /etc/my.cnf server_id=113 gtid_mode=ON enforce-gtid-consistency=ON [root@openeuler-1 Mysql]# systemctl restart mysqld.service
2.）測試gid是否開啟
mysql> show variables like '%gtid%';
3.） 暫停slave，連接主庫
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='192.168.1.112',
-> master_user='repl',
-> master_password='MySQL@123',
-> master_auto_position=1; #使用 GTID 自動定位。
Query OK, 0 rows affected, 7 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)3.3 GTID 從庫誤寫入操作處理
從庫一般是不需要寫入數(shù)據(jù)的，如果不小心寫入一般會報錯
Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3 #說明gid3報錯
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
解決方法：注入空事務
暫停salve：
stop slave;
#3是報錯的ID號
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
這里的xxxxx:N 也就是你的slave sql thread報錯的GTID，或者說是你想要跳過的GTID。
最好的解決方案：重新構建主從環(huán)境
3.4 IO進程錯誤
發(fā)現(xiàn)IO進程錯誤，檢查日志，排除故障：
tail localhost.localdomain.err
常見錯誤：各個復制的虛擬機server_id不能相同
解決方法：修改id，重啟mysql
四、延時同步
4.1簡介
是我們人為配置的一種特殊同步，從庫和主庫同步延時N小時
為什么要有延時同步？
數(shù)據(jù)庫有兩種故障：
- 物理損壞：可以用主從復制解決。主庫rm刪除，直接將應用切換到從庫
- 邏輯損壞：普通主從同步無法解決。eg：主庫drop database ns，從庫也沒了
4.2配置延時同步
主庫加一個參數(shù)即可，其他不變
change master to master_delay=300; #延時300s
4.3故障模擬恢復
1.）主庫數(shù)據(jù)操作
mysql> stop slave; Query Ok, 0 rows affected, l warning(0.00 sec) mysal> change master to master_delay=300; #延時300s Query OK, 0 rows affected, 2 warnings (0.0l sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.02 sec) mysq1> show slave status \G SQL_Delay: 300 SQL_Remaining_Delay: NULL
2.）停止從庫SQL線程
stop slave sql_thread;
3.) 找relaylog的截取點和終點
起點：
Relay_Log_File:db01-relay-bin.000002 Relay_Log_Pos:482
終點：
4.) 從庫恢復relaylog
source /tmp/relay.sq1
5.) 從庫身份解除
db0l [relay]>stop slave ; db0l [relay]>reset slave all
總結
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
很多朋友都會問mysql read_buffer_size 設置多少合適，其實這個都是根據(jù)自己的內存大小等來設置的2016-05-05
這篇文章主要介紹了MySql常用操作SQL語句匯總,本文講解了增加、刪除、查詢、修改等常用SQL語句實例,需要的朋友可以參考下2015-03-03
授權就是為某個用戶賦予某些權限。MySQL 提供了 GRANT 語句來為用戶設置權限。具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-06-06
MySQL查看數(shù)據(jù)庫狀態(tài)命令詳細講解
在工作中，有時候我們需要了解MySQL服務器的狀態(tài)信息,下面這篇文章主要給大家介紹了關于MySQL查看數(shù)據(jù)庫狀態(tài)命令的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-03-03
如何解決MySQL5升級為MySQL8遇到的問題my.ini
這篇文章主要介紹了如何解決MySQL5升級為MySQL8遇到的問題my.ini，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
這篇文章主要介紹了Mysql分組查詢取max那條記錄其它字段方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
文章介紹了在使用Navicat連接MySQL 8.0數(shù)據(jù)庫時遇到連接測試失敗的問題,并提供了解決方法,即將MySQL用戶的加密規(guī)則修改為mysql_native_password,文章步驟清晰,適合初學者參考2026-03-03
這篇文章主要介紹了MySQL主從數(shù)據(jù)庫搭建方法,較為詳細的分析了MySQL主從數(shù)據(jù)庫搭建的原理、步驟與具體操作技巧,需要的朋友可以參考下2017-09-09
MySQL 8.0前InnoDB自增ID重啟會丟失,重新插入從max+1開始；8.0后持久化到磁盤,保留原有值,MyISAM在8.0及以后版本均不會丟失自增ID,差異源于存儲引擎對自增值的處理方式2025-09-09
這篇文章主要介紹了MySQL?中的權限管理及驗證流程詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-09-09