进阶1课件复制

复制from‘HighPerformanceMySQL’意义在线热备，解决单点(但它不是备份)高可用扩展读写分离测试新版本常见拓扑对应配置-MasterMaster:server_id=1log_bin=../binlog/mysql-binmysql>createuser'rep'@'%'identifiedby'rep';mysql>grantreplicationslaveon*.*to'rep'@'%';获取快照-MyISAM千万不要忘记获取快照-InnoDBMVCCREPEATABLEREADSTARTTRANSACTIONALTERTABLE,CREATETABLE,DROPTABLE,RENAMETABLE,TRUNCATETABLE--quick获取快照-Raw获取快照-Online

对应配置-Slavesslave:server_id=2skip_slave_start新建Slave常见拓扑对应配置-Slavesslave2:log-slave-updates常见拓扑对应配置-Slavesmaster1:auto_increment_increment=2auto_increment_offset=1master2(slave1):auto_increment_increment=2auto_increment_offset=2Optionsmaster_info_repository={FILE|TABLE}sync_master_info=10000relay_log_info_repository={FILE|TABLE}sync_relay_log_info=10000sync_relay_log=10000slave_parallel_workersFilesTables复制模式SBR:statement-basedreplication(default)RBR:row-basedreplicationmixed-basedreplication(defaultinNDB)SBRRBR适用版本>=3.23>=5.1.5日志大小少多审计方便不太方便UDFload_file()uuid()…可能不同步同步更新时锁数据的行多少复杂语句效率低高MyISAM开销小大GTIDgtid_mode=ONlog-slave-updatesenforce-gtid-consistency跳过错误setglobalsql_slave_skip_counter=1;startslave;Last_SQL_Error:CouldnotexecuteDelete_rowseventontabletest.test_inc;Can'tfindrecordin'test_inc',Error_code:1032;handlererrorHA_ERR_KEY_NOT_FOUND;theevent'smasterlogmysql-bin.000022,end_log_pos5317Semi-Sync-MasterSemi-Sync-SlavefMasterrpl_semi_sync_master_enabled={0|1}rpl_semi_sync_master_timeout=10000(ms)Slaverpl_semi_sync_slave_enabled={0|1}从库stopslave从库startslave主库errorlogFilterreplicate-do-dbreplicate-do-tablereplicate-wild-do-tablereplicate-ignore-dbreplicate-ignore-tablereplicate-wild-ignore-tableDelayedReplication重现主从不一致从