MySQL主從複製之非同步複製

原文： https://www.cnblogs.com/hmwh/p/9198705.html

MYSQL主從複製方式有預設的複製方式非同步複製，5.5版本之後半同步複製，5.6版本之後新增GTID複製，包括5.7版本的多源複製。

MYSQL版本：5.7.20

作業系統版本：linux 6.7 64bit

 

1、非同步複製

 

MYSQL 預設的複製方式，就是主庫寫入binlog日誌後即可成功返回客戶端，無須等待binlog日誌傳遞給從庫的過程。但這樣一旦主庫發生當機，就有可能出現資料丟失的情況。

 

1.1搭建非同步主從

1、 server-id 不一樣

2、 開啟binlog,建議開啟log_slave_updates,讓從庫也寫binlog，方便後期擴充套件架構

3、 binlog格式為row。

 

主庫操作：

建立主從複製賬號

create user 'rep'@'192.16.20.%' identified by 'mysql';

 

grant replication slave on *.* to 'rep'@'192.16.20.%';

 

 

初始化：

mysqldump -S /tmp/mysql3307.sock --single-transaction -uroot -pmysql --master-data=2 -A > salve.sql （我全庫匯入的有問題，只用的test庫）

 

注意：必須加引數 –master-data=2,讓備份出來的檔案中記錄備份這一刻binlog檔案與position號，為搭建主從環境做準備。檢視備份檔案中記錄的當前binlog檔案和position號。

 

scp salve.sql 172.16.20.21:/binlogbak

 

 

mysql -S /tmp/mysql3307.sock -uroot -pmysql < slave_test.sql

 

 

在資料庫命令列執行配置主從命令。

 

change master to

    master_host='172.16.20.32',

    master_user='rep',

    master_password='mysql',

    master_port=3307,

    master_log_file='mysql-binlog.000010',

    MASTER_LOG_POS=797;

 

start slave;

stop slave;

reset slave all; 清空從庫的所有配置資訊。

 

start slave;

 

當前從庫I/O和SQL thread都是呈現Yes狀態，代表從庫上面操作已經完成了。

 

 

Master_Log_File= Relay_Master_Log_File;

Read_Master_Log_Pos= Exec_Master_Log_Pos

證明目前沒有主從延遲狀態。

 

Slave_IO_Running：從庫上I/O thread 負責請求和接收主庫傳遞來的binlog資訊。

Slave_SQL_Running：從庫上SQL thread負責應用relay中的binlog的資訊。

 

1.2主從複製故障處理

1 、主從故障之主鍵衝突，錯誤程式碼為1062

 

原因：由於誤操作，從從庫上執行寫操作，導致再在主庫上執行相同的操作，由於主鍵衝突，主從複製狀態會報錯。所以生產環境建議在從庫上開啟read only,避免在從庫執行寫操作。

 

在主庫上建表t1;

現在從庫插入資料，後面再在主庫上插入相同的語句。

主庫:

 

 

從庫：

 

 

主庫執行相同語句：

 

 

從庫報錯如下：

 

 

報錯程式碼：

Last_Errno: 1062

處理辦法：

 

利用percona-toolkit 工具：

mount /dev/sr0 /mnt

yum -y install perl-DBD-MySQL

./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql

 

 

在檢視主從狀態，已經恢復正常：

 

 

2 、主從故障之主庫更新資料，從庫找不到而報錯，錯誤程式碼為10032

上一個錯誤是主從都有相同的資料，我們可以直接通過percona-toolkit工具跳過錯誤。但如果從庫上少資料，就不能跳過錯誤了，需要找到缺少的資料，在從庫上從新執行一遍。

故障原因：由於誤操作，在從庫上執行delete 刪除操作，導致主從資料不一致。這時再在主庫執行同條資料的更新操作，由於從庫沒有該資料，SQL無法再從庫上實現。

 

模擬故障：

先在從庫伺服器的test庫下的t表中，執行delete刪除語句操作。

 

再在主庫上執行：相同資料的update更新操作。

 

 

從庫報錯如下：

 

 

報錯程式碼1032。

解決辦法：

 

根據報錯資訊所知道Binlog檔案和position（7153）號，在主庫上，通過mysqlbinlog 命令，找到在主庫上執行的那條SQL語句導致的主從報錯。

 

/usr/local/mysql5.7/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /mydata/mysql/mysql3307/logs/mysql-binlog.000010 |grep -A 10 7153

 

 

 

insert into t2 values(1,'bbb');

生產上如果丟失數以萬計條的資料，建議重新搭建主從確保資料一致性。

 

 

從庫跳過錯誤：

./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql

 

 

重新同步成功：

show slave status\G;

 

 

1.3主從故障之主從server-id一致

由於粗心，安裝的時候用模板並沒有修改servier-id。（修改從庫server-id成不同的值）

 

1.4主從故障之跨庫操作，丟失資料

原因：在主庫中設定binlog-do-db引數，使用的binlog記錄格式為statement模式，導致在主庫上執行跨庫操作時，從庫沒有複製成功，丟失資料。

 

故障操作描述：

在主庫的引數檔案中新增binlog-do-db=test，代表只複製zs這個庫，並且主庫binlog_format 設定為statement。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改時間：2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端 掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2651414/，如需轉載，請註明出處，否則將追究法律責任。