MySQL數(shù)據(jù)庫運(yùn)維與管理（微課版） 課件 項(xiàng)目7 MYSQL復(fù)制

數(shù)據(jù)庫運(yùn)維與管理項(xiàng)目7MySQL復(fù)制教學(xué)目標(biāo)知識(shí)目標(biāo)掌握MySQL復(fù)制的概念掌握MySQL復(fù)制的過程掌握MySQL復(fù)制的表現(xiàn)形式掌握MySQL復(fù)制的常用拓?fù)浣Y(jié)構(gòu)能力目標(biāo)能在Windows系統(tǒng)下建立MySQL主從復(fù)制能在Linux系統(tǒng)下建立MySQL主從復(fù)制能配置MySQL半同步復(fù)制能配置MySQL并行復(fù)制素養(yǎng)目標(biāo)加強(qiáng)安全防范意識(shí)了解系統(tǒng)穩(wěn)定運(yùn)行的重要性提升規(guī)范操作行為思政目標(biāo)民族自豪感國產(chǎn)軟件振興的使命擔(dān)當(dāng)項(xiàng)目7

MySQL復(fù)制項(xiàng)目場景隨著天天電器商場在線業(yè)務(wù)的開展，為了時(shí)刻給用戶提供7×24小時(shí)的優(yōu)質(zhì)服務(wù)，該公司要求信息部門對(duì)現(xiàn)有的服務(wù)器軟硬件進(jìn)行升級(jí)，明確要求當(dāng)數(shù)據(jù)庫的主服務(wù)器宕機(jī)時(shí)，能夠及時(shí)啟動(dòng)備用服務(wù)器以提供不間斷服務(wù)，從而保證數(shù)據(jù)庫內(nèi)容的實(shí)時(shí)備份。在實(shí)際的生產(chǎn)環(huán)境中，為了解決MySQL的單點(diǎn)故障問題和提高M(jìn)ySQL的整體服務(wù)性能，一般會(huì)建立MySQL主從復(fù)制。項(xiàng)目知識(shí)導(dǎo)入一、MySQL復(fù)制概述1．概述

MySQL復(fù)制是指從一臺(tái)MySQL主服務(wù)器將數(shù)據(jù)復(fù)制到另一臺(tái)或多臺(tái)MySQL備用服務(wù)器的過程（主服務(wù)器與備用服務(wù)器數(shù)據(jù)同步）2．復(fù)制方式（1）MySQL支持多種復(fù)制方式。基于主庫二進(jìn)制日志中的事件與基于全局事務(wù)標(biāo)識(shí)事件，并且要求二進(jìn)制日志文件及其位置在主庫和從庫之間同步；這種復(fù)制方式是事務(wù)性的，因此不需要使用二進(jìn)制日志文件和這些文件的位置，從而大幅簡化了復(fù)制任務(wù)。（2）MySQL復(fù)制格式有兩種核心類型。基于語句的復(fù)制，它可以復(fù)制整個(gè)SQL語句；基于行的復(fù)制，它僅復(fù)制更改的行。項(xiàng)目知識(shí)導(dǎo)入3．MySQL復(fù)制可以解決以下常見問題(1）服務(wù)的高可用性。從庫可以提升為主庫，減少宕機(jī)時(shí)間，雙主時(shí)承擔(dān)高可用角色。（2）服務(wù)的高性能。從庫可以用于查詢、統(tǒng)計(jì)分析，分擔(dān)主庫負(fù)載，承擔(dān)負(fù)載均衡角色。（3）數(shù)據(jù)安全性。使用延遲從庫作為數(shù)據(jù)備份以應(yīng)對(duì)誤刪除帶來的數(shù)據(jù)不可恢復(fù)性。（4）分析。實(shí)時(shí)數(shù)據(jù)可以在主庫上創(chuàng)建，而信息分析可以在從庫上進(jìn)行，從而不影響主庫的性能。（5）遠(yuǎn)程數(shù)據(jù)分發(fā)。可以使用復(fù)制來創(chuàng)建數(shù)據(jù)的本地副本以供遠(yuǎn)程站點(diǎn)使用，而無須永久訪問項(xiàng)目知識(shí)導(dǎo)入二、MySQL復(fù)制過程1.傳統(tǒng)復(fù)制：基于二進(jìn)制日志文件的傳統(tǒng)復(fù)制過程分為以下3步。（1）主庫將數(shù)據(jù)改變記錄到二進(jìn)制日志中（這些記錄叫作二進(jìn)制日志事件）；（2）從庫將主庫的二進(jìn)制日志事件復(fù)制到它的中繼日志（RelayLog）中；（3）從庫重做中繼日志中的事件（即數(shù)據(jù)重演），將改變反映到從庫中。項(xiàng)目知識(shí)導(dǎo)入2、GTID復(fù)制（1）概述GTID是MySQL5.6版本開始在主從復(fù)制方面推出的重要特性。GTID是一個(gè)已提交事務(wù)的編號(hào)，并且是全局唯一編號(hào)，不僅是在主庫上，在給定的復(fù)制設(shè)置中的所有數(shù)據(jù)庫上它都是唯一的。GTID格式：GTID=server_uuid:transaction_id。GTID由server_uuid和transaction_id組成，其中server_uuid在數(shù)據(jù)庫啟動(dòng)時(shí)自動(dòng)生成，存放在數(shù)據(jù)庫目錄下的f文件中；transaction_id即事務(wù)id，是事務(wù)提交時(shí)由系統(tǒng)按順序分配的序列號(hào)。在數(shù)據(jù)庫事務(wù)提交時(shí)，二進(jìn)制日志中會(huì)產(chǎn)生一個(gè)對(duì)應(yīng)的GTID。主從復(fù)制時(shí)，從庫會(huì)通過GTID來確定同步的位置，不用再去找二進(jìn)制日志文件的復(fù)制位置，即不再使用master_log_file和master_log_pos開啟復(fù)制，而使用master_auto_postion=1的方式自動(dòng)匹配GTID斷點(diǎn)進(jìn)行復(fù)制。項(xiàng)目知識(shí)導(dǎo)入（2）GTID相關(guān)參數(shù)項(xiàng)目知識(shí)導(dǎo)入（3）GTID相關(guān)參數(shù)說明binlog_gtid_simple_recovery：設(shè)置MySQL服務(wù)啟動(dòng)時(shí)是否自動(dòng)尋找GTID集合值。enforce_gtid_consistency：強(qiáng)制要求只復(fù)制事務(wù)安全的事務(wù)。gtid_executed：表示在當(dāng)前實(shí)例上執(zhí)行過的GTID集合；實(shí)際上包含了所有記錄到二進(jìn)制日志中的事務(wù)。執(zhí)行RESETMASTER命令可以將該變量置空。gtid_executed_compression_period：啟用GTID時(shí)，服務(wù)器會(huì)定期在mysql.gtid_executed表上執(zhí)行壓縮。此參數(shù)可以控制壓縮表之前允許的事務(wù)數(shù)，從而控制壓縮率。設(shè)置為0時(shí)，則不進(jìn)行壓縮。gtid_mode：是否開啟GTID模式。項(xiàng)目知識(shí)導(dǎo)入gtid_next：會(huì)話級(jí)參數(shù)，指示如何產(chǎn)生下一個(gè)GTID。該參數(shù)可能的取值如下。（1）AUTOMATIC：自動(dòng)生成下一個(gè)GTID。（2）ANONYMOUS：執(zhí)行事務(wù)不會(huì)產(chǎn)生GTID。（3）顯式指定GTID：可以指定任意形式合法的GTID值，但不能是當(dāng)前gtid_executed中已經(jīng)包含的GTID，

否則，下次執(zhí)行事務(wù)時(shí)會(huì)報(bào)錯(cuò)。gtid_owned：表示正在執(zhí)行的事務(wù)GTID及對(duì)應(yīng)的線程ID。gtid_purged：由于二進(jìn)制日志需要定期進(jìn)行清理，gtid_purged用于記錄已經(jīng)被清除的二進(jìn)制日志事務(wù)的GTID集合，清除的GTID集合會(huì)包括在gtid_executed中。執(zhí)行RESETMASTER命令時(shí)會(huì)把gtid_purged置空，即始終保持gtid_purged是gtid_executed的子集。session_track_gtids：是否開啟跟蹤查詢所有MySQL客戶端的GTID執(zhí)行情況。項(xiàng)目知識(shí)導(dǎo)入（4）GTID復(fù)制的工作過程（1）主庫更新數(shù)據(jù)時(shí)，會(huì)在事務(wù)提交前產(chǎn)生GTID，GTID會(huì)被記錄到二進(jìn)制日志中。（2）從庫的I/O線程將變更的二進(jìn)制日志寫入本地的中繼日志中。（3）SQL線程從中繼日志中獲取GTID，然后查詢從庫的二進(jìn)制日志中是否有相應(yīng)記錄。（4）如果有記錄，則說明該GTID的事務(wù)已經(jīng)執(zhí)行，從庫會(huì)忽略。（5）如果沒有記錄，從庫就會(huì)從中繼日志中執(zhí)行該GTID的事務(wù)，并將其記錄到二進(jìn)制日志中。（6）從庫在復(fù)制主庫的二進(jìn)制日志時(shí)，會(huì)校驗(yàn)主庫的GTID的事務(wù)是否已經(jīng)執(zhí)行過（一個(gè)GTID的事務(wù)只能執(zhí)行一次）。（7）為了保證主、從庫中數(shù)據(jù)的一致性，多線程只能同時(shí)執(zhí)行一個(gè)GTID的事務(wù)。項(xiàng)目知識(shí)導(dǎo)入三、復(fù)制的表現(xiàn)形式

（1）同步復(fù)制（SynchronousReplication）：事務(wù)必須在主庫和從庫同時(shí)提交成功。（2）異步復(fù)制（AsynchronousReplication）：MySQL默認(rèn)采用異步復(fù)制，主庫提交的事務(wù)不需要等從庫接收到或者提交成功。（3）半同步復(fù)制（SemisynchronousReplication）：主庫在執(zhí)行完客戶端提交的事務(wù)后不是立刻返回給客戶端，而是等待至少一個(gè)從庫接收到并寫到其中繼日志中后才返回給客戶端。相對(duì)于異步復(fù)制，半同步復(fù)制提高了數(shù)據(jù)的安全性，同時(shí)它也造成了一定程度的延遲，這個(gè)延遲最少是一個(gè)TCP/IP往返的時(shí)間。所以，半同步復(fù)制最好在低延遲的網(wǎng)絡(luò)中使用。項(xiàng)目任務(wù)分解任務(wù)7-1在Windows系統(tǒng)下建立MySQL主從復(fù)制任務(wù)說明

任務(wù)開始前，需要準(zhǔn)備好兩個(gè)基于Windows系統(tǒng)的MySQL實(shí)例。由于MySQL主從復(fù)制基于二進(jìn)制日志進(jìn)行，所以這兩個(gè)MySQL實(shí)例需要開啟二進(jìn)制日志。本任務(wù)要求在Windows系統(tǒng)下建立MySQL主從復(fù)制并進(jìn)行數(shù)據(jù)同步測試。任務(wù)實(shí)施過程項(xiàng)目任務(wù)分解任務(wù)7-2在Linux系統(tǒng)下建立MySQL主從復(fù)制任務(wù)說明

企業(yè)搭建具有高可用性的數(shù)據(jù)庫系統(tǒng)時(shí)，復(fù)制是一種常用的技術(shù)方法。本任務(wù)要求在Linux系統(tǒng)下，基于二進(jìn)制日志事件，建立MySQL主從復(fù)制并測試其同步情況。任務(wù)實(shí)施過程項(xiàng)目任務(wù)分解任務(wù)7-3配置MySQL半同步復(fù)制任務(wù)說明

MySQL復(fù)制的表現(xiàn)形式默認(rèn)為異步復(fù)制，這種方式對(duì)數(shù)據(jù)完整性的保護(hù)有限。MySQL復(fù)制的半同步機(jī)制是指主庫節(jié)點(diǎn)只要確認(rèn)有至少一個(gè)從庫節(jié)點(diǎn)接收到事務(wù)，即可向發(fā)起請(qǐng)求的客戶端返回操作成功的消息。本任務(wù)要求配置MySQL半同步復(fù)制。任務(wù)實(shí)施過程項(xiàng)目任務(wù)分解任務(wù)7-4配置MySQL并行復(fù)制任務(wù)說明

MySQL的復(fù)制是基于二進(jìn)制日志的。MySQL復(fù)制包括兩個(gè)部分，即I/O線程和SQL線程。I/O線程主要用于拉取、接收主庫傳遞過來的二進(jìn)制日志，并將其寫入中繼日志。SQL線程主要負(fù)責(zé)解析中繼日志，并將其應(yīng)用到從庫中。I/O線程和SQL線程都是單線程的，但是SQL線程往往是復(fù)制的瓶頸，并行復(fù)制可以有效減少延遲。本任務(wù)要求配置MySQL并行復(fù)制。任務(wù)實(shí)施過程項(xiàng)目任務(wù)分解任務(wù)7-5基于GTID建立MySQL主從復(fù)制任務(wù)說明

GTID復(fù)制比傳統(tǒng)復(fù)制的搭建過程更簡單、復(fù)制過程更安全、故障轉(zhuǎn)移更優(yōu)。本任務(wù)要求基于GTID建立MySQL主從復(fù)制并對(duì)其進(jìn)行測試。任務(wù)實(shí)施過程常見問題解決問題1：出現(xiàn)“Last_IO_Error:errorconnectingtomaster‘repl@7:33068’-retry-time:60retries:4message:Authenticationplugin‘caching_sha2_password’reportederror:Authenticationrequiressecureconnection.”錯(cuò)誤提示。原因分析：

MySQL8.0默認(rèn)使用caching_sha2_password身份驗(yàn)證插件，在從庫連接主庫時(shí)，如果使用了不被caching_sha2_password插件認(rèn)可的RSA公鑰，主庫就會(huì)拒絕從庫的連接請(qǐng)求。這時(shí)要從服務(wù)器請(qǐng)求RSA公鑰。解決方案：配置從庫端到主庫端的連接時(shí)，添加get_master_public_key=1參數(shù)，命令如下。mysql>changemastertomaster_host='7',->master_user='repl',->master_port=33068,->master_password='repl',->master_log_file='binlog.000106',->master_log_pos=1269;->get_master_public_key=1;常見問題解決問題2：：執(zhí)行showslavestatus命令時(shí)出現(xiàn)“Last_IO_Error:Fatalerror:TheslaveI/OthreadstopsbecausemasterandslavehaveequalMySQLserverUUIDs;theseUUIDsmustbedifferentforreplicationtowork.”錯(cuò)誤提示。原因分析：

UUID相同導(dǎo)致出現(xiàn)該錯(cuò)誤。解決方案：把UUID的配置文件刪除，然后把該從庫節(jié)點(diǎn)的server_id修改成與其他節(jié)點(diǎn)不同，再重新啟動(dòng)MySQL服務(wù)，自動(dòng)生成UUID即可。UUID配置文件名為f，存放在MySQL的data目錄下。常見問題解決問題3：登錄時(shí)出現(xiàn)“Last_IO_Error:Gotfatalerror