介 MySQL擴展能力：通過功能可以將MySQL的性能壓力分擔到一個或多個slave上。這要求所有 介 介 記錄到binlog的格式會有所不同。 binlog中完成 (rowbasedreplication)：基于主庫將每一個行數據變化的信 寫入到binlog中完成日志 MySQL基于binlog基于binarylog 是指主庫將修改操作寫入到binlog中，從庫負 主庫的binlog，并在本 份后將里面的操作在從庫執行一每個從庫會保存目 主庫日志的文件名和日志位主庫和每個從庫都必須有一個唯一ID，叫server-id配置在配置文件配置方法主庫需要開啟bin-log，并且指定一個唯一的server-id，重啟數據log-bin=mysql-binserver-在同一個 組下的所有實例的ser_都必須是唯一的而且取值必須是正整數，值范圍~21確保主庫的 f中skinon參數為非開狀態，否則會導致主從庫能通信而 失敗mysql>showvariableslike | | ||

| | MySQL基于binlog在主庫創建一個專門用來做的數據庫用戶，這樣所有從庫都可以用這個用戶來連接主庫，也可以確保這個用戶只有的權限雖然可以用任何擁有權限的MySQL用戶來建立關系，但由于被使用的用戶名和密碼會明文保存在備庫的文件中，所以為安全起見，最好是使用僅有權限mysql>CREATEUSER'repl'@'192.168.237.%'IDENTIFIEDBYmysql>GRANTREPLICATIONSLAVEON*.*TO從slave庫驗 [root@vmware1bin]#./mysql-urepl-p-P3308-h28Enter etotheMySQLmonitor. Commandsendwith;or\g.YourMySQLconnectionidis22Serverversion:5.7.17-logMySQLCommunityServerCopyright(c)2000,2016,Oracleand/oritsaffiliates.All OracleisaregisteredtrademarkofOracleCorporationand/oritsOthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputMySQL基于binlog為了確保建立的備庫能從正確的binlog位置開 ，要首先獲取主庫的binlog信息mysqlFLUSHTABLESWITHREAD ##主庫上所有表加鎖，停mysql>SHOWMASTERSTATUS; | |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set |mysql-bin.000001 619 mysql>droptableERROR1223(HY000):Can'texecutethequerybecauseyouhave ingreadmysql>createdatabaseERROR1223(HY000):Can'texecutethequerybecauseyouhavea ingreadlockmysql>insertintotempvalues(now());ERROR1223(HY000):Can'texecutethequerybecauseyouhave ingreadMySQL基于binlog主庫數據生成鏡像并上傳到從 s>bin/mysqldump--all-databases--master-data-uroot-p-P3308>dbdump.db##mysqldump方式導出所有數據庫數據到dbdump.db文件，--master-data表示導出數據直接加上changemasterto參數如果使用文件拷貝的辦法將主庫臨時關閉，并 >tarcf/tmp/db.tarsftpTheauthenticityofhost'28(28)'can'tbeECDSAkeyfingerprintis64:db:17:91:08:ca:77:29:57:ac:b1:ff:b5:81:3d:db.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded'28'(ECDSA)tothelistofknownroot@28'sConnectedtosftp>cd/usr/local/mysql/sftp>lcd/usr/local/mysqlsftp>getdbdump.db 100%785KB784.9KB/s00:01sftp>exit主庫釋放mysql>UNLOCK從庫的binlog屬性可以打開也可以不打開server- MySQL基于binlogDROPTABLEIFEXISTS/*!40101SET = /*!40101SET =utf8CREATETABLE`rcx``a`int(11)DEFAULT`b`int(11)DEFAULT`c`char(3)DEFAULT`d`int(11)DEFAULT)ENGINE=InnoDBDEFAULT/*!50500PARTITIONBYRANGE(PARTITIONp0VALUESLESSTHAN(5,10,'ggg')ENGINE=InnoDB,PARTITIONp1VALUESLESSTHAN(10,20,'mmm')ENGINE=InnoDB,PARTITIONp2VALUESLESSTHAN(15,30,'sss')ENGINE=InnoDB,PARTITIONp3VALUESLESSTHAN(MAXVALUE,MAXVALUE,MAXVALUE)ENGINE=InnoDB)/*!40101SET = --- datafortable-LOCKTABLES`rcx`/*!40000ALTERTABLE`rcx`DISABLEKEYSINSERTINTO`rcx`VALUES/*!40000ALTERTABLE`rcx`ENABLEKEYS*/;UNLOCKTABLES;MySQL基于binlogMysqldump的鏡像，通過source命令執 從庫指定主庫的日志信息 信mysql>CHANGEMASTER

##主庫的日志文件位CHANGEMASTERTO從庫啟 進mysql>STARTMySQL基于binlog查看主備 是否正常在slave上執行showslavestatus\Gmysql>showslave***************************1.rowSlave_IO_State:WaitingformastertosendeventMaster_Host:28Connect_Retry:60Read_Master_Log_Pos:2667Relay_Log_Pos:2186Slave_IO_Running:YesSlave_SQL_Running:YesLast_Errno:0Last_IO_Errno:0Last_SQL_Errno:01rowinset(0.00MySQL基于binlog驗 mysql>createtabletemp(idint,nameQueryOK,0rowsaffected(0.02mysql>insertintotempQueryOK,2rowsaffected(0.01Records:2Duplicates:0Warnings:mysql>select*from? | |name? 1| 2| ? 2rowsinset(0.00MySQL基于binlogLast_IO_Errno:Last_IO_Error:Fatalerror:TheslaveI/OthreadstopsbecausemasterandslavehaveequalMySQLserverUUIDs;theseUUIDsmustbedifferentforreplicationtowork. Last_IO_Errno:Last_IO_Error:errorconnectingtomaster'repl@28:3306'-60retries: MySQL基于binlog DelimiterCreateprocedureDeclarenintdefaultwhilen<=20000Insertintotempvalues(n,SetEnddelimitermysql>showmastermysql>callMySQL基于binlog的多slave環 Slavmysql>showprocesslist; |Id|User| | | |Time|| |21|repl|30:34897|NULL|BinlogDump|2524|Masterhassentallbinlogslave;waitingformoreupdates||23|root|localhost |test|Queryshowprocesslist|

0| |26|repl|31:38754|NULL|BinlogDump 33|Masterhassentallbinlogslave;waitingformoreupdates| 3rowsinset(0.00 MySQL基于binlog的多slave環 >mysqladmin拷貝從庫的文件到新的從庫，包括log文件和relaylog文件，其中如果relaylog使用[root@vmware1mysql]#tar-zcvfdata.tar.gzdata[root@vmware1mysql]#sftp30Connectedto30.sftp>getdata.tar.gztar–zxvfrm- 拷貝masterinfo和relayloginfo是1~之間，且同一個組之內不能重復server_uuid：server_uuid會在GTID中使用。當MySQL啟動之后，會首先到數據文件下的f中尋找是否有指定的server_uuid，如果沒有也記錄在slave自己的binlog中。正常情況下是不需要記錄的，但如果是想創建級聯關系，比如ABC，這其中B既要作為A的從庫，也要作 如：replicate-do-db=salesUSEUPDATEsales.januarySET USE mysql>showslavemysql>usemysql>insertintotempmysql>usemysql>insertintotemp mysql>useysqseect* ++? ++|id|name 1 | 2 |5| || 5rowsnset.00mysql>useysqseect* 1|

|ce mysql>usemysql>updatetest2.tempsetmysql>usemysql>updatetest.tempset mysql>useReadingtableinformationforcompletionoftableandcolumnYoucanturnoffthisfeaturetogetaquickerstartupwith-Databasemysqlselect*fromtemp; | |name 1|abc 2|abc 3|abc 4|abc 5|abc mysql>usemysql>select*from

| |name 10|ddd 11|ddd 12|ddd mysql>showvariableslike |Variable_name|Value |binlog_format| mysql>usemysql>updatetest2.tempsetmysql>usemysql>updatetest.tempset mysql>usemysqlselect*fromtemp; | |name 1|abc 2|abc 3|abc 4|abc 5|abc mysql>usemysqlselect*fromtemp; +| |name+ 10| 11| 12| USEUPDATEdb1.table1SETcol1=10,db2.table2SETcol2= table1USEUPDATEdb1.table1SETcol1=10,db2.table2SETcol2= create/drop/alterdatabasefoo打頭 mysql>showslavestatus\G***************************1.row***************************Slave_SQL_Running:Yesc:mysql>usemysql>insertintotempvalues(6,'f');mysql>insertintotest2.tempvalues(14,'ff');mysql>insertintotemp2values(4,'d'); mysql>usemysql>select*fromtemp;##數據 過 | |name 1|abc 2|abc 3|abc 4|abc 5|abc 6| mysqlselect*from | |name 10|ddd 11|ddd 12|dddmysqlselect*from | |name 1| 2| 3| slave-parallel-workers:該參數決定了slave上啟動多個SQLthread線程來并行應用數skip-slave-start：該參數決定了在MySQL啟動時是否先不啟動slave線程，即暫停復slave-parallel-type=type：該參數決定了當啟動了并行之后，采用什么粒度的并行方slave的SQLthread執行過程中碰到何種錯誤時可以忽略并繼續接下來的數據 sqlslaveskipcounter代表在非GTID環境下，通過設置此參數來跳過多少個 設置完該參數并非立即生效，等待下次startslave命令的執行生效，并將該參數再次設 binlog-do-db=dbname：該參數決定了哪些庫下的修改會被記錄到binlog中。其行為與replicate-do-db類型，在基于SQL語句的環境下，只記錄在當前數據庫下的修改。比如指定binlog-do-db=sales，一下語句不會被記錄到binlog中：USEprices;UPDATEsales.januarySETamount=amount而以下語句則會被記錄到binlogUSEsales;UPDATEprices.discountsSETpercentage=percentage+ USEprices;UPDATEsales.februarySETUSEsales;UPDATEprices.marchSETamount=amount-binlog-ignore-db=dbname：該參數決定了在binlog中忽略的數據庫，其行為與bogfmbn別SQL的 行 合 57是當置為混模式優先使an，只有當基于語句的 無法保證 準確時會動替換為w 通過在slave上執行showslavestatus來檢 是否正常工mysql>SHOWSLAVE***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:master1Master_User:rootMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000004Read_Master_Log_Pos:931Relay_Log_Pos:950Relay_Master_Log_File:mysql-bin.000004Slave_IO_Running:YesSlave_SQL_Running:YesLast_Errno:Skip_Counter:0Until_Condition:NoneUntil_Log_Pos:0Master_SSL_Allowed:NoSeconds_Behind_Master:0Last_IO_Errno:0Last_SQL_Errno:0 ReplicateIgnoreServerIds: Slave_IO_State：代表當前slave的狀 主庫binlog的IO線程是否是運行狀態，正常情況下應,: (Master_Log_file,Read_Master_Log_Pos)：表示IO線程在主庫binlog中的坐標位置(Relay_Master_Log_FileExec_Master_Log_Pos)：表示SQL線程在主庫binlog中的坐, 在主庫可以通過執行showprocesslist命令查看主庫的binlog日志生成進程mysql>SHOWPROCESSLIST\G;***************************4.rowId:User:Host:slave1:58371db:NULLCommand:BinlogTime:State:Hassentallbinlogtoslave;waitingforbinlogtobeupdatedInfo:NULL 格基于語 屬于比較 技術，得到廣泛使 基于語 語句中包含自定義函數或者不確定性 過update/delete語句中包含Limit語句但不包含orderby語句屬于不確定性語句一些函數比如rand(),sysdate()，version()等由于不確定性也會導致 [Warning]Statementisnotsafetologinstatement基于 的優勢所有的數據庫修改都可以 基于 的劣勢 binlog會很大，有可能會導 要 異 格基于語 的二進制文件內容binlog-#at SET mit=1/*!*/;SET SET@@session.auto_increment_increment=1, SETSET@@session.collation_database=DEFAULT/*!*/;#at SET 格 執行命令：bin/mysqlbinlogvdata/mysql-#at#1706099:42:05serverid1end_log_pos347CRC320xa130fe8fTable_map:`course`.`temp`mappedto#at#1706099:42:05serverid1end_log_pos389CRC320x8deeb544Write_rows:tableid141flags:BINLOG'###INSERTINTO`course`.`temp`###SET #at#1706099:42:05serverid1end_log_pos420CRC320x0bf24c6aXid=51 格mysql>select*from | |name 2|eee 3|eee 4|eee 5|eeemysql>select*from | |name 3|eee 2|eee 4|eee 5|eee主庫執行deletefromtemplimit 格 mysql>showvariableslike |

| | | mysql>deletefromtempwheremysql>deletefromtempwheremysql>showslave***************************1.rowSlave_IO_Running:YesSlave_SQL_Running:YesLast_Errno:Seconds_Behind_Master:0 格在 環境下實驗主從一個表數據不一致的情況 是否還能繼續mysql>showvariableslike | | | |主庫執行mysql>deletefromtempwhere查看從庫的同步狀態不正常mysql>showslave***************************1.rowSlave_SQL_Running:NoReplicate_Do_DB:testLast_Errno:Last_Error:Coordinatorstoppedbecausetherewereerror(s)intheworker(s).Themostrecentfailurebeing:Worker4failedexecutingtransaction'ANONYMOUS'atmasterlogmysql-bin.000002,end_log_pos386.Seeerrorlogand/orperformance_schema.replication_applier_status_by_workertableformoredetailsaboutthisfailureorothers,ifany.如果手工在備庫增加這條數據，則同步再次正常Insertintotempmysql>showslave***************************1.row***************************Slave_IO_Running:YesSlave_SQL_Running:Last_Errno:0Skip_Counter:0Seconds_Behind_Master: 線 binlogdumpthread：在主庫創建，用來在從庫 過來時發送binlog的slaveiothread：在備庫創建，用來連接主庫并請求發送新的binlog 主庫的binlogdump線程發送的更新內容并將此內容 本地的relaylog中Slavesqlthread：在備庫創建 線通過showprocesslistmysql>SHOW***************************1.row***************************Id:2User:Host:db:Command:BinlogDumpTime:94State:Hassentallbinlogtoslave;waitingforbinlogbeupdatedInfo:NULLmysql>SHOW***************************1.row***************************Id:10User:systemdb:Command:ConnectTime:11State:WaitingformastertosendInfo:***************************2.row***************************Id:11User:systemdb:Command:ConnectTime:11State:Hasreadallrelaylog;waitingfortheslavethreadtoupdateInfo: 線 整體停止的方法：mysqlSTOPmysql>STOPSLAVEIO_THREAD;mysql>STOPSLAVESQL_THREAD;整體開啟的方法：mysqlSTARTmysql>STARTSLAVEIO_THREAD;mysql>STARTSLAVESQL_THREAD; 使用場 件內容是SQL語句，所以可以很方便的將其中的一部分出來應用到其他數smysqladminstop-slave或者smysqle'STOP >mysqldump--all-databases>fulldb.dump >mysqladminstart-slave >mysqladmin >tarcf/tmp/dbbackup.tar 使用場MySQL可以用在主庫和從庫采用不同的引擎的情況下。這樣做的目的通常是在主庫和從庫可以分別利用不同引擎的優勢，比如在主庫使用當使用mysqldump方式來創建備庫時，改變備庫的表引擎的方式就是在應用dump文件之前先修改文件里的所有關于表引擎的地方如果是使用文件拷貝的方式來創建備庫時，則唯一修改備庫表引擎的方式就是在啟動備庫之后使用altertable命令修改mysql>STOP執行ALTERTABLEENGINE=‘engine_typemysql>START 使用場mysql>altertabletempmysql>insertintotemp mysql>select*from | |name 3|eee 4|eee 5|eee 6| 1| 2| 使用場 力分擔到多個MySQLslave實例上，這種情況適用在讀多寫少的環境中。比如 使用場 可以在每個slave上配置不同的參數來約 過來的數據，通過replicate-do-table參數或者replicate-do-db參在配置完參數之后，通過mysqldump的方式將對應數據庫在slave應用起來啟動slave線MySQL延 可以通過changemasterto命令來指定CHANGEMASTERTOMASTER_DELAY=其原理是從庫收到主庫的binlog之后，不是立即執行，而是等待指定的秒數之延 測試在從庫IO集中在恢復binlog過程中對應用程序的 showslavestatus中SQL_Delay值表明了設置的延遲時長mysql>showslave***************************1.row***************************Slave_IO_Running:YesSQL_Delay:MySQL延mysql>stopmysql>CHANGEMASTERTOMASTER_DELAY=mysql>start在主庫上執行mysql>showmaster | +

|Position|Binlog_Do_DB|Binlog_Ignore_DB| |mysql-bin.00000323

3039 |9eae8f34-47b6-11e7-8087- +1rowinset(0.00mysql>insertintotempvalues(4,'d');QueryOK,1rowaffected(0.00sec)mysql>showmaster | +

|Position|Binlog_Do_DB|Binlog_Ignore_DB| |mysql-bin.00000324

3318 |9eae8f34-47b6-11e7-8087- +MySQL延在slave上查看數 情況mysqlselect*from ##數據沒有同步過 | |name 3|eee 4|eee 5|eee 6| 1| 2| 3| mysql>showslaveMaster_Log_File:mysql-bin.000003Read_Master_Log_Pos:Relay_Log_Pos:693Slave_IO_Running:YesSlave_SQL_Running:YesExec_Master_Log_Pos:3039Seconds_Behind_Master:SQL_Delay:_Remaining_Delay:SQL_Running_State:WaitinguntilMASTER_DELAYsecondsafterexecutedeventMySQL延 mysql>select*fromtemp; | |name 3|eee 4|eee 5|eee 6| 1| 2| 3| m|>s|owslaveMaster_Log_File:mysql-bin.000003Read_Master_Log_Pos:3318Relay_Log_File:vmware1-relay-Relay_Log_Pos:972Relay_Master_Log_File:mysql-bin.000003Slave_IO_Running:YesSlave_SQL_Running:YesExec_Master_Log_Pos:Seconds_Behind_Master:SQL_Delay:60SQL_Remaining_Delay:NULLave_SQL_Running_State:Slavehasreadallrelaylog;waitingformore 主從切 當新的master產生之后，需要通過在其他slave上執行changemasterto語句致，而是直接獲取master上的二進制日志并繼續自己的功能 主從切新的master上開啟log-bin=mysql-Master上查看binlog信息mysqlshowmasterstatus; ---||+---

|Position|Binlog_Do_DB|Binlog_Ignore_DB| 154 在slave上執行mysql>resetslaveQueryOK,0rowsaffected(0.01mysql>stopslave;->->->->->MASTER_LOG_FILE='mysql--> mysql>start MySQL半同 binlog，而并不知道從庫是否獲取了這些 半同 relaylog MySQL半同 MySQL半同 事先建立好異 關INSTALLPLUGINrpl_semi_sync_masterSONAMEsemisync_master.so';INSTALLPLUGINrpl_semi_sync_slaveSONAMEmysql>SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKE'%semi%'; | |PLUGIN_STATUS |rpl_semi_sync_master| 在主庫上開啟半同 SETGLOBALrpl_semi_sync_master_enabled=SETGLOBALrpl_semi_sync_master_timeoutN;##N是毫秒，默認是10000，代表10在備庫上開啟半同 SETGLOBALrpl_semi_sync_slave_enabled1；STOPSLAVESTARTSLAVEMySQL半同半同 通過命令mysqlSHOWSTATUSLIKERpl_semi_sync%';查看各個參數的狀MySQL半同mysql>STOPSLAVEQueryOK,0rowsaffected(0.00mysql>updatetemp2setname='ddd'wheremysql>showstatuslike | |Value |Rpl_semi_sync_slave_status| mysql>STARTSLAVEQueryOK,0rowsaffected(0.00mysql>showstatuslike | |Value |Rpl_semi_sync_slave_status| MySQL半同 時，停止其中一個的slaveIO線程，再在主mysql>insertintotemp2QueryOK,1rowaffected(0.00當把第二個從庫的slaveIO線程關閉時，則主庫插入數據需要等待10秒才能mysqlinsertintotemp2values(132,‘a’);##等待10GTID(globaltransactionidentifiers) 前執行的binlog和log GTID= GTID，并記錄在主庫的binary②主庫的binarylog會被傳輸到從庫的relaylog中，從 此GTID并生成gtid_nextGTID并沒有在自己的binarylog （注：開啟log_slave_updates參數，是把relay-log里的日志內容再記錄到slave本地但在MySQL5.7里 做了調整，用一張gtid_executed系統表記錄同 （UUID:事務號），這樣就可以不用開啟log_slave_updates參數，減少了從庫的壓CREATETABLEgtid_executedsource_uuidCHAR(36NOTNULLinterval_startBIGINT(20NOTNULL,interval_endBIGINT(20NOTNULL,PRIMARYKEY(source_uuid,interval_start)mysql>showmaster ||

|Position|Binlog_Do_DB|Binlog_Ignore_DB| |mysql-bin.000004| 475|000c298d7ee3:1-25|

mysql>select*from | |interval_start|interval_end |9eae8f34-47b6-11e7-8087-000c298d7ee3|9eae8f34-47b6-11e7-8087-000c298d7ee3|9eae8f34-47b6-11e7-8087-000c298d7ee3|9eae8f34-47b6-11e7-8087-000c298d7ee3

12232425+

12232425創 流mysql>SET@@global.read_only=ON; >mysqladmin-uusername-p設置主從庫GTIDslave 機制安全的語句才會被重新設置主從庫 關mysql>CHANGEMASTERTO