運(yùn)維 強(qiáng)昌金 MySQL-NDB-Cluster實(shí)踐-運(yùn)維專場(chǎng)

MySQLNDBCluster的實(shí)踐=■強(qiáng)昌金目錄234567關(guān)于我MySQLHAMySQLNDBCluster介紹MySQLNDBCluster架構(gòu)MySQLNDBCluster的管理MySQLNDBCluster的復(fù)制MySQLNDBCluster■的限制關(guān)于我?陌陌DBA(2014-2015)-去哪兒高級(jí)DBA(2015-2017)?去哪兒網(wǎng)數(shù)據(jù)庫(kù)管理平臺(tái)?《MySQL運(yùn)維內(nèi)參》?CRUG(Redis中國(guó)用戶組)副主席?MySQLOCP-自由職業(yè):提供MySQL/Redis咨詢與技術(shù)支持MySQLHAMySQLHigh-Availability&Scale-OutSolutionsMySQLReplicationInnoDBCluster*0,3**/yMySQLClusterMySQLNDBCluster架構(gòu)Clients/APIsonnector/JmysqldmysqldCustomClients(NDBAPI)rNDBManagementClientLndbmgmjNDBManagementServerndb_mgmd：onnector/NDataNodesMySQLCAPISQLNodesmysqldMySQLNDBCluster概述?NetworkDataBase?In-Memory?Shared-Nothing?Auto-Sharding?High-Availability?Data-Persisnce?Read/Writeconsistency/ScalabilityMySQLNDBCluster-APIClusterJmod_ndb?REST/JSON?HTML?usingApacheSQL?Industrystandard?Joins&complexqueries?Relationalmodel....Memcached?simpletouseAPI?key/value?driverformanylanguages?idealase.g.PHPproxy?simpletouseJavaAPI?Web&telco?ObjectRelationalMapping-native&fastaccesstoMySQLClusterC++?experienceddeveloper?ultralowlatency/real-timeMySQLNDBCluster—DataNodes?DataNodes-數(shù)據(jù)存儲(chǔ)-分片?自動(dòng)復(fù)制?擴(kuò)展性Paiiion0RinoryRcplcl曲業(yè)Group0吋驀、BaJupRem*MjdelIEinn2PErtiton2PrimajRep^lc：CLUSTER點(diǎn)龍gN。*Group1Partlion16ocwpF：中cohock3=u啟VPriii^iO、、、rbrfcunh?pPurlHun3Frim^ryDataNodes(NodeGroups、-一―8:::asReplicas、Partitions)^SB■二軍貝=c?！?MySQLNDBCluster—SQLNodes?SQLNodes?SQLAccesstodata?Round-robinappaccess?GeographicReplication?ScaleoutforperformanceMySQLNDBCluster—ManagermentNodes?ManagementNodes?Distributeconfigurationdata?ControlClustermembership?Runbackup?ArbitratorincaseofnetworkpartitioningMySQLNDBCluster一安裝與配置SQLNodes?安裝MySQL實(shí)例?修改f配置文件DataNodes?安裝DataNode軟件?修改f配置文件ManagementNodes?安裝ManagementNode?修改f配置[mysql_cluster]Mndb-connectstring=.[mysqld].ndbcluster.ndb-connectstring-M[mysqld]ndbclusterndb-connectstring=:1186[mysql_cluster]ndb-connectstring-:1186[ndbddefault]NoOfReplicas-2DatcMemGny=￡0NIndexM?rory-18M[ndb_jrgrddefault]PortNunber-1186DafcoDir?/data/mysql/multi/3307[ndb-jrg^d]Ncxield=lhostname=[ndbd]ServerPort=1186HoNName.DataDir-/data/mysql/multi/3306/data[ndbd]ServerPort=1186HostNane=DataDir-/data/mysql/multi/3306/dcita[mysqld]HostNane-[mysqld]HostNane=MySQLNDBCluster一節(jié)點(diǎn)啟動(dòng)類型?啟動(dòng)類型?Initialstart?Systemrestart?Noderestart?InitialnoderestartMySQLNDBCluster—ndbinfo庫(kù)?仲裁相關(guān)?arbitrator_validity_*?參數(shù)配置?config_params與config_values?節(jié)點(diǎn)信息?config_nodesxmembership、nodes?CPU統(tǒng)計(jì)信息?cpustat_*?NDB數(shù)據(jù)字典?dict_objjnfo與dict_obj_types?磁盤寫入情況?disk_write_speed?命中率問(wèn)題?diskpagebuffer?LOG使用情況?logbuffers和logspaces?重啟信息?restartJnfo?NDB表分布?table_*MySQLNDBCluster—Programs21.4「ndbd—TtieND3CusteeDataNodeDaemon?14.7ndbinfo_select_alI—IplrrrFromndbinfoTables214.3ndtwntd—TheNDBCloserDauNaceDaemon(Mjltl-Threaced)214.4ndbmgmd—TheNDBClus^rManagerrentServerDaemor21.42ndbmgm—丨h(huán)eNDSOusterMaragementClient214.6ndb_Mob_tool—CheckandRepairBLObandTEXTcoLmnsofNJBCusterlabes21.4.7ndb.cocfig—ExtractNDBCusterContigurationIn^mnaticn214.8ndb_cpcd—AntcrnateTestir^g.rr>rNDBDp*jenprmrt214/3ndb_dcletE_all—DpImpAllRowsfremnnNDRTnblr214/0ndb.desc—DescrbeNDDTables214/1ndbdropindex—DrooIndex"romanNDDTable21,4.-2ndb_drop_table—DropanNDBTable21.4/3ndb_error_reporter—ND3Enror-RepcrtngUtility214/4ndbjrnport—InpuitCSVOnIdInto214/5ndbjndcx_stat-NDBIndexStatisticsJrflltv21.4.16ndb_move_data—NDBDa:aCopyUWty714.17ndb_perror—ObtainNDRerrormp?;s.igpinformatirn21.4.1Bndb_print_backup_file—Prin:NDUBackupFileContents21.4.1yndbjprint.fi!?—Pnn:NDUDiskDataHieContents21.4.20ndbprintfragfile—P*lrwNDDFragmentLis:FleContents214.2*ndbprintschemafile—PrintNCBSdeindFileCcntenb214.2?ndb_print_sys_file■—PrritND3S/sipinAlpContonrs214.23ndb_redo_log_reader—CheckandPrintContertn€Cii<^prRedoIeg21.4.24ndb_restor?—私torea*1NDDCluster[jackup21.4.25ndbselectall—PrlruMv/s牝manND3Tabe214.26ndbselectcount—Pri'itCuurtsforNDB"iblcs214.27ndb_setup.py—Stdr.AtiQhrUXIirfi?rNCBCluslei21.4.2Rndb_show_tatales—DisplayListofMDBTahes21.4.29ndb_sixe.pl—NDDCLUSTLRSizeReqcIrenen:[stimator21.4.30ndbtop—Vev.usageInfcnr跳IonforKDCthreads214.3*ndbwaiter—WuilFurNDBCIlt>jc^UuRcjcIicGivenStutui：MySQLNDBCluster—在線添加節(jié)點(diǎn)與節(jié)點(diǎn)組?修改(ndb_mgm)配置文件、添加新的節(jié)點(diǎn)信息?關(guān)閉所有的ManagementNodes.使用reload啟動(dòng)，加載新配置?重啟已有的DataNodes?重啟所有的SQLNodes?啟動(dòng)新的DataNodes(--initial)?創(chuàng)建新的NodeGrcnjp?重新分配集群中的數(shù)據(jù)(ALTERABLE***ALGORITHMTNPLACEREORGANIZEPARTITION)MySQLNDBCluster—BackupSTARTbackup

[bac如walt_optlon[snapshot_optionwait_option:WAIT(STARTED|COMPLETED)|NOWAITsnapshot_option:SNAPSHOTSTART|SNAPSHOTEND?BACKUP-backupJd.nodeJd.ctl?BACKUP-backupJd.nodeJd.data?BACKUP■backupJd.nodeJd.log?備份內(nèi)容?Metadata?Tablerecords?Transactionlog?備份文件MySQLNDBCluster—Restore?命令：ndb_restore?重要參數(shù)?restore_meta?restore_data?backup_path?backupjd?ndb-connectstring?nodeid?include-database?include-tables?restore_epoch[raottnto.iMOQUf*ndb.r^stcre--ntM?iBodcupid?StockMPpcHh.2817-^90?B:I2:S?[restore.ietadTU]ReadaetAdataFi.eheadferCPtnihQHUsisc22S72bytesAdk4.3.1imA7.195.7&09:U:S?(n*tore_*^odbtfl]Loadcom^vtHwCO“J?ttS26U490?C9:42:5?[rc?t?rc_^?tadbt?JGet*MbcrofYsb：??3017—9。?9.42;57J—SjMabS]Cu?N9.l?JStdb!I4PV-W40.d：w:>iLrwwejetod&ttj?：em<^iespxes...J4H7Wit9:U:51Iratore^KtadtitaJRestsr.ngweiesSucomfull>ertcctsbie'MCitsV4ef/rdb_tes4'Succttsfull)rrs^orectableevertKMlndb_te$t.*ndb_test&(9:12:51【—Ur?_i^dbtQl3Stsker.MoSucoeishjll、creffttdirdexPU>WK

on、ndb'utst'CrwoHfor*(0nkwytfnMviQHln?y</rv*?9，岫51【?^iljMo]

?4>0H?MeJBU-SOPO9:U:S1[nvtm.aaialeataHl?heodkrQwngf11<.”U/?yEZlS*9U/MaUMMOaP-；ZMOaP-I?LC0bfil?>i?*UjrUn聽17YX7C9;*Z;SI(natPHLjtotaJRirWonFfruyenti^roce^stngcatssudib:tr^tnt?Fromslngeatsint^ble:ayMt<5)?Froce^sina?>h>st^bb:sv^'dtf/MDtSlwnkJO)fraawM9mkstS

?:?ys^ly_?te?v?(9>:koo—")Feeee??iAfinW^leiayxl〃cM?A>SA?e*i?L>eW(4)/,??T9?aUin"/OcSYfOCr，pf~U?,rowtns53st<Dle:JIroce^slng8ta?nEl■:FM/def/XJgneOfrpgM<rt020Veer[retore_lod3l丐RShtoaerCMnina./duU/^yZ3*d?U/?OW7WKLP-VUOQJ?4.！?1oc.HUsixS2b^tesfQQSS[e&r?心WxvIrJyHvmJt*V1*nr網(wǎng)Ing0-OtMySQLNDBCluster—RollingRestart?滾動(dòng)重啟的原因?配置修改?NDBCluster的升級(jí)或者降級(jí)?更改節(jié)點(diǎn)所在的主機(jī)?系統(tǒng)重啟或集群重啟MySQLNDBCluster—RollingRestart?滾動(dòng)重啟的過(guò)程?關(guān)閉、修改配置、重啟所有的ManagementNode?關(guān)閉、修改配置、重啟所有的DataNode?關(guān)閉、修改配置、重啟所有的SQLNodeMySQLNDBCluster—RollingRestartMySQLNDBCluster一集群間的復(fù)制MySQLServerReplicationZ--、i/oMySQLServerMasterf*匕Slavet4■RelayBinlogNdbClusterEngineBinlogNDBKernel(Datanodes)"「ndbd:

jndbdndbd,]nd*bd「NDBKernel(Datanodes)ndbd~ndbdMySQLNDBCluster—搭建復(fù)制?備份數(shù)據(jù)?ndb_mgm(M)>STARTBACKUP?將備份數(shù)據(jù)傳送到從庫(kù)的所有NDB節(jié)點(diǎn)?創(chuàng)建數(shù)據(jù)庫(kù)?從庫(kù)上RESETSLAVE?mysql(S)>RESETSLAVE;?所有從節(jié)點(diǎn)的DataNode恢復(fù)數(shù)據(jù)?ndb_restore-c“slave_ndb_mgm:port"-n*-b*-r-m-backup_path=***?ndb_restore-c“slave_ndb_mgm:port"-n*-b*-r-e--backup_path=***?找點(diǎn)?幵啟復(fù)制MySQLNDBCluster—相關(guān)表?ndb_binlog_indexmysql>SHOWCREATETABLE'ndb_binloo_index'\G***************************1.pgyy*********************Table:ndb_binlog_indexCreateTable:CREATETABLE'ndb_binlog_index'('Position'biginKZO)unsignedNOTNULL,'File'varchor(255)NOTNULL,'epoch'bigint(20)unsignedNOTNULL,'inserts'int(10)unsignedNOTNULL,'updates'int(10)unsignedNOTNULL,'deletes'int(10)unsignedNOTNULL,'schemaops'unsignedNOTNULL,'orig_server_id'irrtr(10)unsignedNOTNULL,'orig_epoch'bigint(20)unsignedNOTNULL,'gci'unsignedNOTNULL,'next_position'bigint(20)unsignedNOTNULL,'next__file'varchar(Z55)NOTNULL,PRIMARYKEY('epoch','orig-server.id','orig_epoch'))ENGINE-InnoDBDEFAULTCHARSET-utf8STAIS_PERSISTENT=0?ndb_apply_statusmysql>SHOWCREATETABLE'ndb_apply_status'\G***************************1.*******************Table:ndb_apply_statusCreateTable:CREATETABLE'ndb_apply_status'('server_id'unsignedNOTNULL,'epoch'bigint(20)unsignedNOTNULL,'log_nane'varchar(255)CHARACTERSETlatinlCOLLATE'start_pos'bigint(20)unsignedNOTNULL,'end_pos'bigint(20)unsignedNOTNULL,PRIMARYKEY('server_id')USINGHASH)ENGINE^ndbclusterDEFAULTCHARSET=latinlMySQLNDBCluster—復(fù)制必要條件?復(fù)制需求?基于行復(fù)制(binlog-format=[ROW|MIXED])?server-id唯一?版本兼容?API使用問(wèn)題MySQLNDBCluster一集群間復(fù)制的限制?主從連接斷開、需要人工介入?主SQLNode與SQLNode斷開?主SQLNode與主集群中的DataNode斷開-主SQLNode無(wú)響應(yīng)，被集群丟棄?不支持GTID?不支持多線程復(fù)制MySQLNDBCluster—SingleReplicationChannel&TwoReplicationChannels?SingleReplicationChannel?兩個(gè)節(jié)點(diǎn)(M-S)?TwoReplicationChannels-4個(gè)節(jié)點(diǎn)(M、M'、S、S')?建立一對(duì)主從關(guān)系(M?S)MySQLNDBCluster—Fa