老男孩dba6期课件第十二mysql57复制

介 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