MySQL PXC构建一个新节点只需IST传输的方法(推荐)

2024-02-27 04:39:06 131

需求场景：原有的pxc环境数据量已经比较大，新买的服务器要加入此集群中，如何让其用IST的方式传输，而不是SST.

PXC传输数据有两种方式：

IST：IncrementalStateTransfer增量同步
SST：StateSnapshotTransfer全量同步

IST发生的条件：Thisisdoneusingcachingmechanismonnodes（即只发生在之前集群里的节点数据还有在缓存中）

判断方法：

mysql>showglobalstatuslike'wsrep_local_cached_downto';
+---------------------------+-------+
|Variable_name|Value|
+---------------------------+-------+
|wsrep_local_cached_downto|1|
+---------------------------+-------+
1rowinset(0.00sec)

此值小于新开节点的seqno：

[root@zejin241I6000]#catgrastate.dat
#GALERAsavedstate
version:2.1
uuid:6c86dc17-246f-11e6-9955-ae4d7e89eed2
seqno:4
cert_index:

所以我们的思路即通过备份一个集群中节点的数据，恢复到新节点上，并伪造一个grastate.dat，来记录备份数据的seqno,这样当新节点启动时就会自动用IST的方法来进行，而不是SST（注意gcache.size应该有足够的容量来保证在备份及恢复这段时间内新产生的数据都被缓存到）

galera.cache:Thisfileisusedasamainwritesetstore.It'simplementedasapermanentring-bufferfile
thatispreallocatedondiskwhenthenodeisinitialized.Filesizecanbecontrolledwiththevariable
gcache.size.Ifthisvalueisbigger,morewritesetsarecachedandchancesarebetterthattherejoiningnodewillgetISTinsteadofSST.Filenamecanbechangedwiththegcache.namevariable.

如下为这次的操作步骤：

node240：192.168.1.240--已经存在的集群节点
node241：192.168.1.241--将要加入的集群节点

step1：启动新的集群中的第一个节点

/usr/local/pxc_56/bin/mysqld_safe--defaults-file=/home/mysql/pxc6000.cnf--ledir=/usr/local/pxc_56/bin/--wsrep-new-cluster&

配置文件如下：

[root@zejin240I6000]#cat/home/mysql/pxc6000.cnf[client]port=6000socket=/home/mysql/I6000/mysql.sockdefault-character-set=utf8[mysqld]basedir=/usr/local/pxc_56datadir=/home/mysql/I6000pid-file=/home/mysql/I6000/mysql.pidcharacter-set-server=utf8init_connect='SETNAMESutf8'log-bin=/home/mysql/I6000/log_binserver-id=9816000innodb_buffer_pool_size=100Minnodb_data_file_path=ibdata1:10M:autoextendinnodb_data_home_dir=/home/mysql/I6000innodb_file_per_table=1skip-name-resolveport=6000socket=/home/mysql/I6000/mysql.sockuser=mysqllog_error=/home/mysql/I6000/mysql_error.log#pxcuser=mysqllog_error=error.logbinlog_format=ROW#6000formysqldport,6020forsstport,6031foristport,6030forclustercommunicationportwsrep_cluster_address='gcomm://192.168.1.240:6030,192.168.1.241:6030,192.168.1.242:6030'wsrep_provider=/usr/local/pxc_56/lib/libgalera_smm.sowsrep_sst_receive_address=192.168.1.240:6020wsrep_node_incoming_address=192.168.1.240:6000wsrep_node_address=192.168.1.240:6000wsrep_provider_options="gmcast.listen_addr=tcp://192.168.1.240:6030;ist.recv_addr=192.168.1.240:6031;"wsrep_slave_threads=2wsrep_cluster_name=pxc_zejinwsrep_sst_method=xtrabackup-v2wsrep_node_name=node6000_240innodb_autoinc_lock_mode=2wsrep_sst_auth="sstuser:123"log-slave-updates[mysql]no-auto-rehashdefault-character-set=utf8

连接进去并创造更改一些数据：

mysql>usezejin;mysql>select*fromt1;+----+-------+|id|name|+----+-------+|1|chen||2|li||3|zhang|+----+-------+3rowsinset(0.01sec)mysql>updatemysql.usersetpassword=password('123');mysql>flushprivileges;
mysql>showmasterstatus;+----------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------------+----------+--------------+------------------+-------------------+|log_bin.000003|2772||||+----------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

step2：用innobackupex备份node240数据：

[root@zejin240mysql]#innobackupex--user=root--password=123--port=6000--host=127.0.0.1--defaults-file=/home/mysql/pxc6000.cnf/home/mysql/InnoDBBackupUtilityv1.5.1-xtrabackup;Copyright2003,2009InnobaseOyandPerconaLLCand/oritsaffiliates2009-2013.AllRightsReserved.ThissoftwareispublishedundertheGNUGENERALPUBLICLICENSEVersion2,June1991.GetthelatestversionofPerconaXtraBackup,documentation,andhelpresources:http://www.percona.com/xb/p16052809:39:07innobackupex:Executingaversioncheckagainsttheserver...16052809:39:07innobackupex:ConnectingtoMySQLserverwithDSN'dbi:mysql:;mysql_read_default_file=/home/mysql/pxc6000.cnf;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=6000'as'root'(usingpassword:YES).16052809:39:07innobackupex:ConnectedtoMySQLserver16052809:39:07innobackupex:Done.16052809:39:07innobackupex:ConnectingtoMySQLserverwithDSN'dbi:mysql:;mysql_read_default_file=/home/mysql/pxc6000.cnf;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=6000'as'root'(usingpassword:YES).16052809:39:07innobackupex:ConnectedtoMySQLserver16052809:39:07innobackupex:StartingthebackupoperationIMPORTANT:Pleasecheckthatthebackupruncompletessuccessfully.Attheendofasuccessfulbackupruninnobackupexprints"completedOK!".innobackupex:Usingserverversion5.6.26-74.0-25.12-loginnobackupex:Createdbackupdirectory/home/mysql/2016-05-28_09-39-0716052809:39:07innobackupex:Startingibbackupwithcommand:xtrabackup--defaults-file="/home/mysql/pxc6000.cnf"--defaults-group="mysqld"--backup--suspend-at-end--target-dir=/home/mysql/2016-05-28_09-39-07--innodb_log_file_size="50331648"--tmpdir=/tmp--extra-lsndir='/tmp'innobackupex:Waitingforibbackup(pid=30280)tosuspendinnobackupex:Suspendfile'/home/mysql/2016-05-28_09-39-07/xtrabackup_suspended_2'xtrabackupversion2.2.11basedonMySQLserver5.6.24Linux(x86_64)(revisionid:)xtrabackup:usesposix_fadvise().xtrabackup:cdto/home/mysql/I6000xtrabackup:openfileslimitrequested0,setto1024xtrabackup:usingthefollowingInnoDBconfiguration:xtrabackup:innodb_data_home_dir=/home/mysql/I6000xtrabackup:innodb_data_file_path=ibdata1:10M:autoextendxtrabackup:innodb_log_group_home_dir=./xtrabackup:innodb_log_files_in_group=2xtrabackup:innodb_log_file_size=50331648>>logscannedupto(1637591)xtrabackup:Generatingalistoftablespaces[01]Copying/home/mysql/I6000/ibdata1to/home/mysql/2016-05-28_09-39-07/ibdata1>>logscannedupto(1637591)[01]...done[01]Copying./zejin/t1.ibdto/home/mysql/2016-05-28_09-39-07/zejin/t1.ibd[01]...done[01]Copying./mysql/innodb_table_stats.ibdto/home/mysql/2016-05-28_09-39-07/mysql/innodb_table_stats.ibd[01]...done[01]Copying./mysql/slave_master_info.ibdto/home/mysql/2016-05-28_09-39-07/mysql/slave_master_info.ibd[01]...done[01]Copying./mysql/slave_relay_log_info.ibdto/home/mysql/2016-05-28_09-39-07/mysql/slave_relay_log_info.ibd[01]...done[01]Copying./mysql/slave_worker_info.ibdto/home/mysql/2016-05-28_09-39-07/mysql/slave_worker_info.ibd[01]...done[01]Copying./mysql/innodb_index_stats.ibdto/home/mysql/2016-05-28_09-39-07/mysql/innodb_index_stats.ibd[01]...done>>logscannedupto(1637591)xtrabackup:Creatingsuspendfile'/home/mysql/2016-05-28_09-39-07/xtrabackup_suspended_2'withpid'30280'16052809:39:10innobackupex:Continuingafteribbackuphassuspended16052809:39:10innobackupex:ExecutingLOCKTABLESFORBACKUP...16052809:39:10innobackupex:Backuptableslockacquired16052809:39:10innobackupex:Startingtobackupnon-InnoDBtablesandfilesinnobackupex:insubdirectoriesof'/home/mysql/I6000/'innobackupex:Backingupfiles'/home/mysql/I6000//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53files)>>logscannedupto(1637598)innobackupex:Backingupfile'/home/mysql/I6000//zejin/db.opt'innobackupex:Backingupfile'/home/mysql/I6000//zejin/t1.frm'innobackupex:Backingupfiles'/home/mysql/I6000//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74files)16052809:39:11innobackupex:Finishedbackingupnon-InnoDBtablesandfiles16052809:39:11innobackupex:ExecutingLOCKBINLOGFORBACKUP...16052809:39:11innobackupex:ExecutingFLUSHNO_WRITE_TO_BINLOGENGINELOGS...16052809:39:11innobackupex:Waitingforlogcopyingtofinishxtrabackup:Thelatestcheckpoint(forincremental):'1637598'xtrabackup:Stoppinglogcopyingthread..>>logscannedupto(1637598)xtrabackup:Creatingsuspendfile'/home/mysql/2016-05-28_09-39-07/xtrabackup_log_copied'withpid'30280'xtrabackup:Transactionlogoflsn(1637591)to(1637598)wascopied.16052809:39:12innobackupex:ExecutingUNLOCKBINLOG16052809:39:12innobackupex:ExecutingUNLOCKTABLES16052809:39:12innobackupex:Alltablesunlockedinnobackupex:Backupcreatedindirectory'/home/mysql/2016-05-28_09-39-07'innobackupex:MySQLbinlogposition:filename'log_bin.000003',position277216052809:39:12innobackupex:Connectiontodatabaseserverclosed16052809:39:12innobackupex:completedOK!
[root@zejin2402016-05-28_09-39-07]#lltotal75812-rw-r--r--.1rootroot358May2809:39backup-my.cnf-rw-r-----.1rootroot77594624May2809:39ibdata1drwx------.2rootroot4096May2809:39mysqldrwxr-xr-x.2rootroot4096May2809:39performance_schema-rw-r--r--.1rootroot20May2809:39xtrabackup_binlog_info-rw-r-----.1rootroot89May2809:39xtrabackup_checkpoints-rw-r--r--.1rootroot635May2809:39xtrabackup_info-rw-r-----.1rootroot2560May2809:39xtrabackup_logfiledrwx------.2rootroot4096May2809:39zejin

step3：此时我们在node240上再加入一些数据：

此时我们再在node240上插入一些数据：mysql>usezejin;mysql>insertintot1(name)values('lin');QueryOK,1rowaffected(0.06sec)mysql>insertintot1(name)values('liang');QueryOK,1rowaffected(0.00sec)mysql>select*fromt1;+----+-------+|id|name|+----+-------+|1|chen||2|li||3|zhang||4|lin||5|liang|+----+-------+5rowsinset(0.00sec)

step4：将备份的数据传输到node241上并恢复：

[root@zejin241mysql]#innobackupex--apply-log2016-05-28_09-39-07/InnoDBBackupUtilityv1.5.1-xtrabackup;Copyright2003,2009InnobaseOyandPerconaLLCand/oritsaffiliates2009-2013.AllRightsReserved.ThissoftwareispublishedundertheGNUGENERALPUBLICLICENSEVersion2,June1991.GetthelatestversionofPerconaXtraBackup,documentation,andhelpresources:http://www.percona.com/xb/p16052809:54:01innobackupex:Startingtheapply-logoperationIMPORTANT:Pleasecheckthattheapply-logruncompletessuccessfully.Attheendofasuccessfulapply-logruninnobackupexprints"completedOK!".16052809:54:01innobackupex:Startingibbackupwithcommand:xtrabackup--defaults-file="/home/mysql/2016-05-28_09-39-07/backup-my.cnf"--defaults-group="mysqld"--prepare--target-dir=/home/mysql/2016-05-28_09-39-07xtrabackupversion2.2.11basedonMySQLserver5.6.24Linux(x86_64)(revisionid:)xtrabackup:cdto/home/mysql/2016-05-28_09-39-07xtrabackup:Thistargetseemstobenotpreparedyet.xtrabackup:xtrabackup_logfiledetected:size=2097152,start_lsn=(1637591)xtrabackup:usingthefollowingInnoDBconfigurationforrecovery:xtrabackup:innodb_data_home_dir=./xtrabackup:innodb_data_file_path=ibdata1:10M:autoextendxtrabackup:innodb_log_group_home_dir=./xtrabackup:innodb_log_files_in_group=1xtrabackup:innodb_log_file_size=2097152xtrabackup:usingthefollowingInnoDBconfigurationforrecovery:xtrabackup:innodb_data_home_dir=./xtrabackup:innodb_data_file_path=ibdata1:10M:autoextendxtrabackup:innodb_log_group_home_dir=./xtrabackup:innodb_log_files_in_group=1xtrabackup:innodb_log_file_size=2097152xtrabackup:StartingInnoDBinstanceforrecovery.xtrabackup:Using104857600bytesforbufferpool(setby--use-memoryparameter)InnoDB:UsingatomicstorefcountbufferpoolpagesInnoDB:TheInnoDBmemoryheapisdisabledInnoDB:Mutexesandrw_locksuseGCCatomicbuiltinsInnoDB:MemorybarrierisnotusedInnoDB:Compressedtablesusezlib1.2.3InnoDB:UsingCPUcrc32instructionsInnoDB:Initializingbufferpool,size=100.0MInnoDB:CompletedinitializationofbufferpoolInnoDB:HighestsupportedfileformatisBarracuda.InnoDB:Logscanprogressedpastthecheckpointlsn1637591InnoDB:Databasewasnotshutdownnormally!InnoDB:Startingcrashrecovery.InnoDB:Readingtablespaceinformationfromthe.ibdfiles...InnoDB:Restoringpossiblehalf-writtendatapagesInnoDB:fromthedoublewritebuffer...InnoDB:Doingrecovery:scanneduptologsequencenumber1637598(0%)InnoDB:LastMySQLbinlogfileposition02772,filenamelog_bin.000003InnoDB:128rollbacksegment(s)areactive.InnoDB:WaitingforpurgetostartInnoDB:5.6.24started;logsequencenumber1637598[notice(again)]Ifyouusebinaryloganddon'tuseanyhackofgroupcommit,thebinarylogpositionseemstobe:InnoDB:LastMySQLbinlogfileposition02772,filenamelog_bin.000003xtrabackup:RecoveredWSREPposition:6c86dc17-246f-11e6-9955-ae4d7e89eed2:8xtrabackup:startingshutdownwithinnodb_fast_shutdown=1InnoDB:FTSoptimizethreadexiting.InnoDB:Startingshutdown...InnoDB:Shutdowncompleted;logsequencenumber163760816052809:54:03innobackupex:Restartingxtrabackupwithcommand:xtrabackup--defaults-file="/home/mysql/2016-05-28_09-39-07/backup-my.cnf"--defaults-group="mysqld"--prepare--target-dir=/home/mysql/2016-05-28_09-39-07forcreatingib_logfile*xtrabackupversion2.2.11basedonMySQLserver5.6.24Linux(x86_64)(revisionid:)xtrabackup:cdto/home/mysql/2016-05-28_09-39-07xtrabackup:Thistargetseemstobealreadyprepared.xtrabackup:notice:xtrabackup_logfilewasalreadyusedto'--prepare'.xtrabackup:usingthefollowingInnoDBconfigurationforrecovery:xtrabackup:innodb_data_home_dir=./xtrabackup:innodb_data_file_path=ibdata1:10M:autoextendxtrabackup:innodb_log_group_home_dir=./xtrabackup:innodb_log_files_in_group=2xtrabackup:innodb_log_file_size=50331648xtrabackup:usingthefollowingInnoDBconfigurationforrecovery:xtrabackup:innodb_data_home_dir=./xtrabackup:innodb_data_file_path=ibdata1:10M:autoextendxtrabackup:innodb_log_group_home_dir=./xtrabackup:innodb_log_files_in_group=2xtrabackup:innodb_log_file_size=50331648xtrabackup:StartingInnoDBinstanceforrecovery.xtrabackup:Using104857600bytesforbufferpool(setby--use-memoryparameter)InnoDB:UsingatomicstorefcountbufferpoolpagesInnoDB:TheInnoDBmemoryheapisdisabledInnoDB:Mutexesandrw_locksuseGCCatomicbuiltinsInnoDB:MemorybarrierisnotusedInnoDB:Compressedtablesusezlib1.2.3InnoDB:UsingCPUcrc32instructionsInnoDB:Initializingbufferpool,size=100.0MInnoDB:CompletedinitializationofbufferpoolInnoDB:Settinglogfile./ib_logfile101sizeto48MBInnoDB:Settinglogfile./ib_logfile1sizeto48MBInnoDB:Renaminglogfile./ib_logfile101to./ib_logfile0InnoDB:Newlogfilescreated,LSN=1637608InnoDB:HighestsupportedfileformatisBarracuda.InnoDB:128rollbacksegment(s)areactive.InnoDB:WaitingforpurgetostartInnoDB:5.6.24started;logsequencenumber1637900[notice(again)]Ifyouusebinaryloganddon'tuseanyhackofgroupcommit,thebinarylogpositionseemstobe:InnoDB:LastMySQLbinlogfileposition02772,filenamelog_bin.000003xtrabackup:startingshutdownwithinnodb_fast_shutdown=1InnoDB:FTSoptimizethreadexiting.InnoDB:Startingshutdown...InnoDB:Shutdowncompleted;logsequencenumber163791016052809:54:06innobackupex:completedOK!
[root@zejin2402016-05-28_09-39-07]#lltotal176168-rw-r--r--.1rootroot358May2809:39backup-my.cnf-rw-r-----.1rootroot77594624May2809:54ibdata1-rw-r--r--.1rootroot50331648May2809:54ib_logfile0-rw-r--r--.1rootroot50331648May2809:54ib_logfile1drwx------.2rootroot4096May2809:39mysqldrwxr-xr-x.2rootroot4096May2809:39performance_schema-rw-r--r--.1rootroot20May2809:39xtrabackup_binlog_info-rw-r--r--.1rootroot20May2809:54xtrabackup_binlog_pos_innodb-rw-r-----.1rootroot89May2809:54xtrabackup_checkpoints-rw-r--r--.1rootroot38May2809:54xtrabackup_galera_info-rw-r--r--.1rootroot635May2809:39xtrabackup_info-rw-r-----.1rootroot2097152May2809:54xtrabackup_logfiledrwx------.2rootroot4096May2809:39zejin

step5：找出Xid的位置

有两种方法

方法一：查看文件xtrabackup_galera_info

[root@zejin241I6000]#catxtrabackup_galera_info6c86dc17-246f-11e6-9955-ae4d7e89eed2:8

方法二：通过binlog位置来查找：

[root@zejin241I6000]#catxtrabackup_binlog_infolog_bin.0000032772
即备份时的备份点位置为log_bin.0000032772

我们再去主库查找此binlog对应的xid

[root@zejin240I6000]#mysqlbinlog-vvlog_bin.000003|grepXid#1605289:02:59serverid9816000end_log_pos947CRC320x37371b3eXid=7#16052810:03:04serverid9816000end_log_pos2969CRC320xf7973829Xid=9#16052810:03:13serverid9816000end_log_pos3168CRC320x8db2aa41Xid=10

发现竟然没有此位置的xid，原因在于我们在最上面更新mysql.user表了，而此表是myisam表，不会有Xid的记录，这种情况时我们根据上下位置把Xid设置为8

step6：在node241上伪造一个grastate.dat文件（从node240上复制过来，更改下seqno即可）：

#GALERAsavedstateversion:2.1uuid:6c86dc17-246f-11e6-9955-ae4d7e89eed2seqno:8cert_index:

step7：启动新节点：

启动node241:[root@zejin241mysql]#/usr/local/pxc_56/bin/mysqld_safe--defaults-file=/home/mysql/pxc6000.cnf--ledir=/usr/local/pxc_56/bin/&[1]56425[root@zejin241mysql]#16052811:06:03mysqld_safeLoggingto'/home/mysql/I6000/error.log'.16052811:06:03mysqld_safeStartingmysqlddaemonwithdatabasesfrom/home/mysql/I600016052811:06:03mysqld_safeSkippingwsrep-recoverfor6c86dc17-246f-11e6-9955-ae4d7e89eed2:8pair16052811:06:03mysqld_safeAssigning6c86dc17-246f-11e6-9955-ae4d7e89eed2:8towsrep_start_position查看下启动过程的日志：[root@zejin241I6000]#caterror.log16052811:06:03mysqld_safeStartingmysqlddaemonwithdatabasesfrom/home/mysql/I600016052811:06:03mysqld_safeSkippingwsrep-recoverfor6c86dc17-246f-11e6-9955-ae4d7e89eed2:8pair16052811:06:03mysqld_safeAssigning6c86dc17-246f-11e6-9955-ae4d7e89eed2:8towsrep_start_position2016-05-2811:06:190[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).2016-05-2811:06:190[Note]/usr/local/pxc_56/bin//mysqld(mysqld5.6.26-74.0-25.12-log)startingasprocess56931...2016-05-2811:06:1956931[Note]WSREP:ReadnilXIDfromstorageengines,skippingpositioninit2016-05-2811:06:1956931[Note]WSREP:wsrep_load():loadingproviderlibrary'/usr/local/pxc_56/lib/libgalera_smm.so'2016-05-2811:06:2056931[Note]WSREP:wsrep_load():Galera3.12.2(rf3e626d)byCodershipOyloadedsuccessfully.2016-05-2811:06:2056931[Note]WSREP:CRC-32C:usinghardwareacceleration.2016-05-2811:06:2056931[Note]WSREP:Foundsavedstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:82016-05-2811:06:2056931[Note]WSREP:PassingconfigtoGCS:base_dir=/home/mysql/I6000/;base_host=192.168.1.241;base_port=6000;cert.log_conflicts=no;debug=no;evs.auto_evict=0;evs.delay_margin=PT1S;evs.delayed_keep_period=PT30S;evs.inactive_check_period=PT0.5S;evs.inactive_timeout=PT15S;evs.join_retrans_period=PT1S;evs.max_install_timeouts=3;evs.send_window=4;evs.stats_report_period=PT1M;evs.suspect_timeout=PT5S;evs.user_send_window=2;evs.view_forget_timeout=PT24H;gcache.dir=/home/mysql/I6000/;gcache.keep_pages_count=0;gcache.keep_pages_size=0;gcache.mem_size=0;gcache.name=/home/mysql/I6000//galera.cache;gcache.page_size=128M;gcache.size=128M;gcs.fc_debug=0;gcs.fc_factor=1.0;gcs.fc_limit=16;gcs.fc_master_slave=no;gcs.max_packet_size=64500;gcs.max_throttle=0.25;gcs.recv_q_hard_limit=9223372036854775807;gcs.recv_q_soft_limit=0.25;gcs.sync_donor=no;gmcast.listen_addr=tcp://192.168.1.241:6030;gmcast.segment=0;gmcast.version=0;ist.recv_a2016-05-2811:06:2156931[Note]WSREP:Servicethreadqueueflushed.2016-05-2811:06:2156931[Note]WSREP:Assigninitialpositionforcertification:8,protocolversion:-12016-05-2811:06:2156931[Note]WSREP:wsrep_sst_grab()2016-05-2811:06:2156931[Note]WSREP:Startreplication2016-05-2811:06:2156931[Note]WSREP:Settinginitialpositionto6c86dc17-246f-11e6-9955-ae4d7e89eed2:82016-05-2811:06:2156931[Note]WSREP:protonetasioversion02016-05-2811:06:2156931[Note]WSREP:UsingCRC-32Cformessagechecksums.2016-05-2811:06:2156931[Note]WSREP:backend:asio2016-05-2811:06:2156931[Warning]WSREP:accessfile(/home/mysql/I6000//gvwstate.dat)failed(Nosuchfileordirectory)2016-05-2811:06:2156931[Note]WSREP:restorepcfromdiskfailed2016-05-2811:06:2156931[Note]WSREP:GMCastversion02016-05-2811:06:2156931[Note]WSREP:(275a2ebd,'tcp://192.168.1.241:6030')listeningattcp://192.168.1.241:60302016-05-2811:06:2156931[Note]WSREP:(275a2ebd,'tcp://192.168.1.241:6030')multicast:,ttl:12016-05-2811:06:2156931[Note]WSREP:EVSversion02016-05-2811:06:2156931[Note]WSREP:gcomm:connectingtogroup'pxc_zejin',peer'192.168.1.240:6030,192.168.1.241:6030,192.168.1.242:6030'2016-05-2811:06:2156931[Note]WSREP:(275a2ebd,'tcp://192.168.1.241:6030')turningmessagerelayrequestingon,nonlivepeers:2016-05-2811:06:2156931[Note]WSREP:declaring6c48c86cattcp://192.168.1.240:6030stable2016-05-2811:06:2156931[Note]WSREP:Node6c48c86cstateprim2016-05-2811:06:2156931[Note]WSREP:view(view_id(PRIM,275a2ebd,2)memb{275a2ebd,06c48c86c,0}joined{}left{}partitioned{})2016-05-2811:06:2156931[Note]WSREP:savepcintodisk2016-05-2811:06:2156931[Note]WSREP:discardingpendingaddrwithoutUUID:tcp://192.168.1.242:60302016-05-2811:06:2256931[Note]WSREP:gcomm:connected2016-05-2811:06:2256931[Note]WSREP:Changingmaximumpacketsizeto64500,resultingmsgsize:326362016-05-2811:06:2256931[Note]WSREP:ShiftingCLOSED->OPEN(TO:0)2016-05-2811:06:2256931[Note]WSREP:Openedchannel'pxc_zejin'2016-05-2811:06:2256931[Note]WSREP:WaitingforSSTtocomplete.2016-05-2811:06:2256931[Note]WSREP:NewCOMPONENT:primary=yes,bootstrap=no,my_idx=0,memb_num=22016-05-2811:06:2256931[Note]WSREP:STATE_EXCHANGE:sentstateUUID:2800468f-2481-11e6-8c7c-de1ade51709a2016-05-2811:06:2256931[Note]WSREP:STATEEXCHANGE:sentstatemsg:2800468f-2481-11e6-8c7c-de1ade51709a2016-05-2811:06:2256931[Note]WSREP:STATEEXCHANGE:gotstatemsg:2800468f-2481-11e6-8c7c-de1ade51709afrom0(node6000_241)2016-05-2811:06:2256931[Note]WSREP:STATEEXCHANGE:gotstatemsg:2800468f-2481-11e6-8c7c-de1ade51709afrom1(node6000_240)2016-05-2811:06:2256931[Note]WSREP:Quorumresults:version=3,component=PRIMARY,conf_id=1,members=1/2(joined/total),act_id=10,last_appl.=-1,protocols=0/7/3(gcs/repl/appl),groupUUID=6c86dc17-246f-11e6-9955-ae4d7e89eed22016-05-2811:06:2256931[Note]WSREP:Flow-controlinterval:[23,23]2016-05-2811:06:2256931[Note]WSREP:ShiftingOPEN->PRIMARY(TO:10)2016-05-2811:06:2256931[Note]WSREP:Statetransferrequired:Groupstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:10Localstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:82016-05-2811:06:2256931[Note]WSREP:Newclusterview:globalstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:10,view#2:Primary,numberofnodes:2,myindex:0,protocolversion32016-05-2811:06:2256931[Warning]WSREP:Gapinstatesequence.Needstatetransfer.2016-05-2811:06:2256931[Note]WSREP:Running:'wsrep_sst_xtrabackup-v2--role'joiner'--address'192.168.1.241:6020'--datadir'/home/mysql/I6000/'--defaults-file'/home/mysql/pxc6000.cnf'--defaults-group-suffix''--parent'56931''''2016-05-2811:06:2456931[Note]WSREP:(275a2ebd,'tcp://192.168.1.241:6030')turningmessagerelayrequestingoffWSREP_SST:[INFO]Streamingwithxbstream(2016052811:06:25.322)WSREP_SST:[INFO]Usingsocatasstreamer(2016052811:06:25.324)WSREP_SST:[INFO]Evaluatingtimeout-s9100socat-uTCP-LISTEN:6020,reuseaddrstdio|xbstream-x;RC=(${PIPESTATUS[@]})(2016052811:06:25.891)2016-05-2811:06:2756931[Note]WSREP:PreparedSSTrequest:xtrabackup-v2|192.168.1.241:6020/xtrabackup_sst//12016-05-2811:06:2756931[Note]WSREP:wsrep_notify_cmdisnotdefined,skippingnotification.2016-05-2811:06:2756931[Note]WSREP:REPLProtocols:7(3,2)2016-05-2811:06:2756931[Note]WSREP:Servicethreadqueueflushed.2016-05-2811:06:2756931[Note]WSREP:Assigninitialpositionforcertification:10,protocolversion:32016-05-2811:06:2756931[Note]WSREP:Servicethreadqueueflushed.2016-05-2811:06:2756931[Note]WSREP:PreparedISTreceiver,listeningat:tcp://192.168.1.241:60312016-05-2811:06:2756931[Note]WSREP:Member0.0(node6000_241)requestedstatetransferfrom'*any*'.Selected1.0(node6000_240)(SYNCED)asdonor.2016-05-2811:06:2756931[Note]WSREP:ShiftingPRIMARY->JOINER(TO:10)2016-05-2811:06:2756931[Note]WSREP:Requestingstatetransfer:success,donor:12016-05-2811:06:3156931[Note]WSREP:1.0(node6000_240):Statetransferto0.0(node6000_241)complete.2016-05-2811:06:3156931[Note]WSREP:Member1.0(node6000_240)syncedwithgroup.WSREP_SST:[INFO]xtrabackup_istreceivedfromdonor:RunningIST(2016052811:06:31.257)WSREP_SST:[INFO]Galeraco-ordsfromrecovery:6c86dc17-246f-11e6-9955-ae4d7e89eed2:8(2016052811:06:31.261)WSREP_SST:[INFO]Totaltimeonjoiner:0seconds(2016052811:06:31.265)WSREP_SST:[INFO]Removingthesst_in_progressfile(2016052811:06:31.268)2016-05-2811:06:3156931[Note]WSREP:SSTcomplete,seqno:82016-05-2811:06:3156931[Note]Plugin'FEDERATED'isdisabled.2016-05-2811:06:3156931[Note]InnoDB:Usingatomicstorefcountbufferpoolpages2016-05-2811:06:3156931[Note]InnoDB:TheInnoDBmemoryheapisdisabled2016-05-2811:06:3156931[Note]InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins2016-05-2811:06:3156931[Note]InnoDB:Memorybarrierisnotused2016-05-2811:06:3156931[Note]InnoDB:Compressedtablesusezlib1.2.32016-05-2811:06:3156931[Note]InnoDB:UsingLinuxnativeAIO2016-05-2811:06:3156931[Note]InnoDB:UsingCPUcrc32instructions2016-05-2811:06:3156931[Note]InnoDB:Initializingbufferpool,size=100.0M2016-05-2811:06:3156931[Note]InnoDB:Completedinitializationofbufferpool2016-05-2811:06:3256931[Note]InnoDB:HighestsupportedfileformatisBarracuda.2016-05-2811:06:3256931[Note]InnoDB:128rollbacksegment(s)areactive.2016-05-2811:06:3256931[Note]InnoDB:Waitingforpurgetostart2016-05-2811:06:3356931[Note]InnoDB:PerconaXtraDB(http://www.percona.com)5.6.26-74.0started;logsequencenumber16379102016-05-2811:06:3356931[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:2e7b3e12-2481-11e6-8fe4-000c2973aa48.2016-05-2811:06:3356931[Note]RSAprivatekeyfilenotfound:/home/mysql/I6000//private_key.pem.Someauthenticationpluginswillnotwork.2016-05-2811:06:3356931[Note]RSApublickeyfilenotfound:/home/mysql/I6000//public_key.pem.Someauthenticationpluginswillnotwork.2016-05-2811:06:3356931[Note]Serverhostname(bind-address):'*';port:60002016-05-2811:06:3356931[Note]IPv6isavailable.2016-05-2811:06:3356931[Note]-'::'resolvesto'::';2016-05-2811:06:3356931[Note]ServersocketcreatedonIP:'::'.2016-05-2811:06:3356931[Warning]'user'entry'root@zejin240'ignoredin--skip-name-resolvemode.2016-05-2811:06:3356931[Warning]'user'entry'@zejin240'ignoredin--skip-name-resolvemode.2016-05-2811:06:3356931[Warning]'proxies_priv'entry'@root@zejin240'ignoredin--skip-name-resolvemode.2016-05-2811:06:3356931[Note]EventScheduler:Loaded0events2016-05-2811:06:3356931[Note]WSREP:Signallingprovidertocontinue.2016-05-2811:06:3356931[Note]WSREP:Initializedwsrepsidno22016-05-2811:06:3356931[Note]WSREP:SSTreceived:6c86dc17-246f-11e6-9955-ae4d7e89eed2:82016-05-2811:06:3356931[Note]WSREP:ReceivingIST:2writesets,seqnos8-102016-05-2811:06:3356931[Note]/usr/local/pxc_56/bin//mysqld:readyforconnections.Version:'5.6.26-74.0-25.12-log'socket:'/home/mysql/I6000/mysql.sock'port:6000PerconaXtraDBClusterbinary(GPL)5.6.26-25.12,Revision624ef81,wsrep_25.122016-05-2811:06:3356931[Note]WSREP:ISTreceived:6c86dc17-246f-11e6-9955-ae4d7e89eed2:102016-05-2811:06:3356931[Note]WSREP:0.0(node6000_241):Statetransferfrom1.0(node6000_240)complete.2016-05-2811:06:3356931[Note]WSREP:ShiftingJOINER->JOINED(TO:10)2016-05-2811:06:3356931[Note]WSREP:Member0.0(node6000_241)syncedwithgroup.2016-05-2811:06:3356931[Note]WSREP:ShiftingJOINED->SYNCED(TO:10)2016-05-2811:06:3356931[Note]WSREP:Synchronizedwithgroup,readyforconnections2016-05-2811:06:3356931[Note]WSREP:wsrep_notify_cmdisnotdefined,skippingnotification.

成功用IST来恢复了。

step8登陆新节点node241，查看数据是否正常：

[root@zejin241I6000]#/usr/local/pxc_56/bin/mysql-uroot-p-h127.0.0.1-P6000Enterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis4Serverversion:5.6.26-74.0-25.12-logPerconaXtraDBClusterbinary(GPL)5.6.26-25.12,Revision624ef81,wsrep_25.12Copyright(c)2009-2015PerconaLLCand/oritsaffiliatesCopyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||zejin|+--------------------+4rowsinset(0.12sec)mysql>usezejin;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromt1;+----+-------+|id|name|+----+-------+|1|chen||2|li||3|zhang||4|lin||5|liang|+----+-------+5rowsinset(0.01sec)

发现数据也已经全部同步完成。

step9：对比SST时的日志输出：

在数据复制到node241上我们不进行伪造grastate.dat操作，直接启动节点node241
[root@zejin241I6000]#/usr/local/pxc_56/bin/mysqld_safe--defaults-file=/home/mysql/pxc6000.cnf--ledir=/usr/local/pxc_56/bin&[1]3861[root@zejin241I6000]#16052815:32:57mysqld_safeLoggingto'/home/mysql/I6000/error.log'.16052815:32:57mysqld_safeStartingmysqlddaemonwithdatabasesfrom/home/mysql/I600016052815:32:57mysqld_safeSkippingwsrep-recoverforemptydatadir:/home/mysql/I600016052815:32:57mysqld_safeAssigning00000000-0000-0000-0000-000000000000:-1towsrep_start_position查看错误日志：[root@zejin241I6000]#caterror.log16052815:32:57mysqld_safeStartingmysqlddaemonwithdatabasesfrom/home/mysql/I600016052815:32:57mysqld_safeSkippingwsrep-recoverforemptydatadir:/home/mysql/I600016052815:32:57mysqld_safeAssigning00000000-0000-0000-0000-000000000000:-1towsrep_start_position2016-05-2815:32:580[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).2016-05-2815:32:580[Note]/usr/local/pxc_56/bin/mysqld(mysqld5.6.26-74.0-25.12-log)startingasprocess4359...2016-05-2815:32:584359[Note]WSREP:ReadnilXIDfromstorageengines,skippingpositioninit2016-05-2815:32:584359[Note]WSREP:wsrep_load():loadingproviderlibrary'/usr/local/pxc_56/lib/libgalera_smm.so'2016-05-2815:32:584359[Note]WSREP:wsrep_load():Galera3.12.2(rf3e626d)byCodershipOyloadedsuccessfully.2016-05-2815:32:584359[Note]WSREP:CRC-32C:usinghardwareacceleration.2016-05-2815:32:584359[Warning]WSREP:Couldnotopenstatefileforreading:'/home/mysql/I6000//grastate.dat'2016-05-2815:32:584359[Note]WSREP:Foundsavedstate:00000000-0000-0000-0000-000000000000:-12016-05-2815:32:584359[Note]WSREP:PassingconfigtoGCS:base_dir=/home/mysql/I6000/;base_host=192.168.1.241;base_port=6000;cert.log_conflicts=no;debug=no;evs.auto_evict=0;evs.delay_margin=PT1S;evs.delayed_keep_period=PT30S;evs.inactive_check_period=PT0.5S;evs.inactive_timeout=PT15S;evs.join_retrans_period=PT1S;evs.max_install_timeouts=3;evs.send_window=4;evs.stats_report_period=PT1M;evs.suspect_timeout=PT5S;evs.user_send_window=2;evs.view_forget_timeout=PT24H;gcache.dir=/home/mysql/I6000/;gcache.keep_pages_count=0;gcache.keep_pages_size=0;gcache.mem_size=0;gcache.name=/home/mysql/I6000//galera.cache;gcache.page_size=128M;gcache.size=128M;gcs.fc_debug=0;gcs.fc_factor=1.0;gcs.fc_limit=16;gcs.fc_master_slave=no;gcs.max_packet_size=64500;gcs.max_throttle=0.25;gcs.recv_q_hard_limit=9223372036854775807;gcs.recv_q_soft_limit=0.25;gcs.sync_donor=no;gmcast.listen_addr=tcp://192.168.1.241:6030;gmcast.segment=0;gmcast.version=0;ist.recv_a2016-05-2815:32:584359[Note]WSREP:Servicethreadqueueflushed.2016-05-2815:32:584359[Note]WSREP:Assigninitialpositionforcertification:-1,protocolversion:-12016-05-2815:32:584359[Note]WSREP:wsrep_sst_grab()2016-05-2815:32:584359[Note]WSREP:Startreplication2016-05-2815:32:584359[Note]WSREP:Settinginitialpositionto00000000-0000-0000-0000-000000000000:-12016-05-2815:32:584359[Note]WSREP:protonetasioversion02016-05-2815:32:584359[Note]WSREP:UsingCRC-32Cformessagechecksums.2016-05-2815:32:584359[Note]WSREP:backend:asio2016-05-2815:32:584359[Warning]WSREP:accessfile(/home/mysql/I6000//gvwstate.dat)failed(Nosuchfileordirectory)2016-05-2815:32:584359[Note]WSREP:restorepcfromdiskfailed2016-05-2815:32:584359[Note]WSREP:GMCastversion02016-05-2815:32:584359[Note]WSREP:(666e7891,'tcp://192.168.1.241:6030')listeningattcp://192.168.1.241:60302016-05-2815:32:584359[Note]WSREP:(666e7891,'tcp://192.168.1.241:6030')multicast:,ttl:12016-05-2815:32:584359[Note]WSREP:EVSversion02016-05-2815:32:584359[Note]WSREP:gcomm:connectingtogroup'pxc_zejin',peer'192.168.1.240:6030,192.168.1.241:6030,192.168.1.242:6030'2016-05-2815:32:584359[Note]WSREP:(666e7891,'tcp://192.168.1.241:6030')turningmessagerelayrequestingon,nonlivepeers:2016-05-2815:32:584359[Note]WSREP:declaring2cadc8a9attcp://192.168.1.240:6030stable2016-05-2815:32:584359[Note]WSREP:Node2cadc8a9stateprim2016-05-2815:32:584359[Note]WSREP:view(view_id(PRIM,2cadc8a9,2)memb{2cadc8a9,0666e7891,0}joined{}left{}partitioned{})2016-05-2815:32:584359[Note]WSREP:savepcintodisk2016-05-2815:32:584359[Note]WSREP:discardingpendingaddrwithoutUUID:tcp://192.168.1.242:60302016-05-2815:32:584359[Note]WSREP:discardingpendingaddrprotoentry0x30f95602016-05-2815:32:594359[Note]WSREP:gcomm:connected2016-05-2815:32:594359[Note]WSREP:Changingmaximumpacketsizeto64500,resultingmsgsize:326362016-05-2815:32:594359[Note]WSREP:ShiftingCLOSED->OPEN(TO:0)2016-05-2815:32:594359[Note]WSREP:Openedchannel'pxc_zejin'2016-05-2815:32:594359[Note]WSREP:WaitingforSSTtocomplete.2016-05-2815:32:594359[Note]WSREP:NewCOMPONENT:primary=yes,bootstrap=no,my_idx=1,memb_num=22016-05-2815:32:594359[Note]WSREP:STATEEXCHANGE:WaitingforstateUUID.2016-05-2815:32:594359[Note]WSREP:STATEEXCHANGE:sentstatemsg:66ae3372-24a6-11e6-8d13-c77d57a8c6322016-05-2815:32:594359[Note]WSREP:STATEEXCHANGE:gotstatemsg:66ae3372-24a6-11e6-8d13-c77d57a8c632from0(node6000_240)2016-05-2815:32:594359[Note]WSREP:STATEEXCHANGE:gotstatemsg:66ae3372-24a6-11e6-8d13-c77d57a8c632from1(node6000_241)2016-05-2815:32:594359[Note]WSREP:Quorumresults:version=3,component=PRIMARY,conf_id=1,members=1/2(joined/total),act_id=12,last_appl.=-1,protocols=0/7/3(gcs/repl/appl),groupUUID=6c86dc17-246f-11e6-9955-ae4d7e89eed22016-05-2815:32:594359[Note]WSREP:Flow-controlinterval:[23,23]2016-05-2815:32:594359[Note]WSREP:ShiftingOPEN->PRIMARY(TO:12)2016-05-2815:32:594359[Note]WSREP:Statetransferrequired:Groupstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:12Localstate:00000000-0000-0000-0000-000000000000:-12016-05-2815:32:594359[Note]WSREP:Newclusterview:globalstate:6c86dc17-246f-11e6-9955-ae4d7e89eed2:12,view#2:Primary,numberofnodes:2,myindex:1,protocolversion32016-05-2815:32:594359[Warning]WSREP:Gapinstatesequence.Needstatetransfer.2016-05-2815:32:594359[Note]WSREP:Running:'wsrep_sst_xtrabackup-v2--role'joiner'--address'192.168.1.241:6020'--datadir'/home/mysql/I6000/'--defaults-file'/home/mysql/pxc6000.cnf'--defaults-group-suffix''--parent'4359''''WSREP_SST:[INFO]Streamingwithxbstream(2016052815:32:59.795)WSREP_SST:[INFO]Usingsocatasstreamer(2016052815:32:59.797)WSREP_SST:[INFO]Evaluatingtimeout-s9100socat-uTCP-LISTEN:6020,reuseaddrstdio|xbstream-x;RC=(${PIPESTATUS[@]})(2016052815:32:59.840)2016-05-2815:33:004359[Note]WSREP:PreparedSSTrequest:xtrabackup-v2|192.168.1.241:6020/xtrabackup_sst//12016-05-2815:33:004359[Note]WSREP:wsrep_notify_cmdisnotdefined,skippingnotification.2016-05-2815:33:004359[Note]WSREP:REPLProtocols:7(3,2)2016-05-2815:33:004359[Note]WSREP:Servicethreadqueueflushed.2016-05-2815:33:004359[Note]WSREP:Assigninitialpositionforcertification:12,protocolversion:32016-05-2815:33:004359[Note]WSREP:Servicethreadqueueflushed.2016-05-2815:33:004359[Warning]WSREP:Failedtoprepareforincrementalstatetransfer:LocalstateUUID(00000000-0000-0000-0000-000000000000)doesnotmatchgroupstateUUID(6c86dc17-246f-11e6-9955-ae4d7e89eed2):1(Operationnotpermitted)atgalera/src/replicator_str.cpp:prepare_for_IST():482.ISTwillbeunavailable.2016-05-2815:33:004359[Note]WSREP:Member1.0(node6000_241)requestedstatetransferfrom'*any*'.Selected0.0(node6000_240)(SYNCED)asdonor.2016-05-2815:33:004359[Note]WSREP:ShiftingPRIMARY->JOINER(TO:12)2016-05-2815:33:004359[Note]WSREP:Requestingstatetransfer:success,donor:0WSREP_SST:[INFO]ProceedingwithSST(2016052815:33:00.512)WSREP_SST:[INFO]Evaluatingsocat-uTCP-LISTEN:6020,reuseaddrstdio|xbstream-x;RC=(${PIPESTATUS[@]})(2016052815:33:00.515)WSREP_SST:[INFO]Cleaningtheexistingdatadirandinnodb-data/logdirectories(2016052815:33:00.517)removed`/home/mysql/I6000/ib_logfile0'removed`/home/mysql/I6000/ib_logfile1'removed`/home/mysql/I6000/xtrabackup_info'removed`/home/mysql/I6000/xtrabackup_checkpoints'removed`/home/mysql/I6000/performance_schema/rwlock_instances.frm'removed`/home/mysql/I6000/performance_schema/db.opt'removed`/home/mysql/I6000/performance_schema/session_account_connect_attrs.frm'removed`/home/mysql/I6000/performance_schema/performance_timers.frm'removed`/home/mysql/I6000/performance_schema/setup_objects.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_by_user_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_by_digest.frm'removed`/home/mysql/I6000/performance_schema/events_stages_history.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_by_user_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/setup_timers.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_by_account_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_by_thread_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/session_connect_attrs.frm'removed`/home/mysql/I6000/performance_schema/events_stages_history_long.frm'removed`/home/mysql/I6000/performance_schema/table_io_waits_summary_by_index_usage.frm'removed`/home/mysql/I6000/performance_schema/table_io_waits_summary_by_table.frm'removed`/home/mysql/I6000/performance_schema/host_cache.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_global_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_by_host_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_statements_current.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_by_thread_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/mutex_instances.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_by_host_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/objects_summary_global_by_type.frm'removed`/home/mysql/I6000/performance_schema/events_waits_current.frm'removed`/home/mysql/I6000/performance_schema/events_stages_summary_global_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/threads.frm'removed`/home/mysql/I6000/performance_schema/events_statements_history.frm'removed`/home/mysql/I6000/performance_schema/file_summary_by_instance.frm'removed`/home/mysql/I6000/performance_schema/table_lock_waits_summary_by_table.frm'removed`/home/mysql/I6000/performance_schema/setup_instruments.frm'removed`/home/mysql/I6000/performance_schema/events_stages_summary_by_thread_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/users.frm'removed`/home/mysql/I6000/performance_schema/socket_summary_by_instance.frm'removed`/home/mysql/I6000/performance_schema/socket_instances.frm'removed`/home/mysql/I6000/performance_schema/setup_consumers.frm'removed`/home/mysql/I6000/performance_schema/events_stages_summary_by_host_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_waits_summary_by_instance.frm'removed`/home/mysql/I6000/performance_schema/events_stages_current.frm'removed`/home/mysql/I6000/performance_schema/socket_summary_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_statements_history_long.frm'removed`/home/mysql/I6000/performance_schema/cond_instances.frm'removed`/home/mysql/I6000/performance_schema/events_waits_history_long.frm'removed`/home/mysql/I6000/performance_schema/events_waits_history.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_by_account_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_statements_summary_global_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/events_stages_summary_by_account_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/file_instances.frm'removed`/home/mysql/I6000/performance_schema/hosts.frm'removed`/home/mysql/I6000/performance_schema/setup_actors.frm'removed`/home/mysql/I6000/performance_schema/events_stages_summary_by_user_by_event_name.frm'removed`/home/mysql/I6000/performance_schema/accounts.frm'removed`/home/mysql/I6000/performance_schema/file_summary_by_event_name.frm'removeddirectory:`/home/mysql/I6000/performance_schema'removed`/home/mysql/I6000/zejin/db.opt'removed`/home/mysql/I6000/zejin/t1.ibd'removed`/home/mysql/I6000/zejin/t1.frm'removeddirectory:`/home/mysql/I6000/zejin'removed`/home/mysql/I6000/xtrabackup_binlog_info'removed`/home/mysql/I6000/backup-my.cnf'removed`/home/mysql/I6000/xtrabackup_binlog_pos_innodb'removed`/home/mysql/I6000/mysql/time_zone_transition_type.MYI'removed`/home/mysql/I6000/mysql/time_zone_transition.MYI'removed`/home/mysql/I6000/mysql/servers.MYD'removed`/home/mysql/I6000/mysql/ndb_binlog_index.frm'removed`/home/mysql/I6000/mysql/servers.MYI'removed`/home/mysql/I6000/mysql/plugin.frm'removed`/home/mysql/I6000/mysql/time_zone_transition_type.MYD'removed`/home/mysql/I6000/mysql/proxies_priv.MYD'removed`/home/mysql/I6000/mysql/proxies_priv.frm'removed`/home/mysql/I6000/mysql/columns_priv.frm'removed`/home/mysql/I6000/mysql/help_keyword.MYI'removed`/home/mysql/I6000/mysql/func.frm'removed`/home/mysql/I6000/mysql/proc.frm'removed`/home/mysql/I6000/mysql/innodb_table_stats.ibd'removed`/home/mysql/I6000/mysql/help_topic.frm'removed`/home/mysql/I6000/mysql/slow_log.CSM'removed`/home/mysql/I6000/mysql/help_relation.MYD'removed`/home/mysql/I6000/mysql/proc.MYD'removed`/home/mysql/I6000/mysql/help_category.MYD'removed`/home/mysql/I6000/mysql/help_category.frm'removed`/home/mysql/I6000/mysql/columns_priv.MYI'removed`/home/mysql/I6000/mysql/help_topic.MYI'removed`/home/mysql/I6000/mysql/event.frm'removed`/home/mysql/I6000/mysql/db.frm'removed`/home/mysql/I6000/mysql/columns_priv.MYD'removed`/home/mysql/I6000/mysql/time_zone_transition.MYD'removed`/home/mysql/I6000/mysql/innodb_index_stats.frm'removed`/home/mysql/I6000/mysql/time_zone.MYI'removed`/home/mysql/I6000/mysql/event.MYD'removed`/home/mysql/I6000/mysql/slave_master_info.ibd'removed`/home/mysql/I6000/mysql/help_topic.MYD'removed`/home/mysql/I6000/mysql/help_relation.MYI'removed`/home/mysql/I6000/mysql/db.MYD'removed`/home/mysql/I6000/mysql/slave_relay_log_info.ibd'removed`/home/mysql/I6000/mysql/user.MYI'removed`/home/mysql/I6000/mysql/plugin.MYI'removed`/home/mysql/I6000/mysql/proc.MYI'removed`/home/mysql/I6000/mysql/help_relation.frm'removed`/home/mysql/I6000/mysql/time_zone_name.MYD'removed`/home/mysql/I6000/mysql/innodb_table_stats.frm'removed`/home/mysql/I6000/mysql/time_zone.frm'removed`/home/mysql/I6000/mysql/servers.frm'removed`/home/mysql/I6000/mysql/general_log.CSV'removed`/home/mysql/I6000/mysql/ndb_binlog_index.MYI'removed`/home/mysql/I6000/mysql/func.MYD'removed`/home/mysql/I6000/mysql/slave_worker_info.ibd'removed`/home/mysql/I6000/mysql/innodb_index_stats.ibd'removed`/home/mysql/I6000/mysql/time_zone_name.frm'removed`/home/mysql/I6000/mysql/proxies_priv.MYI'removed`/home/mysql/I6000/mysql/slow_log.frm'removed`/home/mysql/I6000/mysql/tables_priv.MYI'removed`/home/mysql/I6000/mysql/tables_priv.MYD'removed`/home/mysql/I6000/mysql/procs_priv.frm'removed`/home/mysql/I6000/mysql/help_keyword.MYD'removed`/home/mysql/I6000/mysql/time_zone_leap_second.MYD'removed`/home/mysql/I6000/mysql/tables_priv.frm'removed`/home/mysql/I6000/mysql/ndb_binlog_index.MYD'removed`/home/mysql/I6000/mysql/slow_log.CSV'removed`/home/mysql/I6000/mysql/time_zone.MYD'removed`/home/mysql/I6000/mysql/procs_priv.MYD'removed`/home/mysql/I6000/mysql/slave_master_info.frm'removed`/home/mysql/I6000/mysql/general_log.frm'removed`/home/mysql/I6000/mysql/help_keyword.frm'removed`/home/mysql/I6000/mysql/time_zone_transition_type.frm'removed`/home/mysql/I6000/mysql/general_log.CSM'removed`/home/mysql/I6000/mysql/time_zone_transition.frm'removed`/home/mysql/I6000/mysql/time_zone_name.MYI'removed`/home/mysql/I6000/mysql/event.MYI'removed`/home/mysql/I6000/mysql/time_zone_leap_second.frm'removed`/home/mysql/I6000/mysql/plugin.MYD'removed`/home/mysql/I6000/mysql/slave_relay_log_info.frm'removed`/home/mysql/I6000/mysql/help_category.MYI'removed`/home/mysql/I6000/mysql/user.frm'removed`/home/mysql/I6000/mysql/user.MYD'removed`/home/mysql/I6000/mysql/procs_priv.MYI'removed`/home/mysql/I6000/mysql/db.MYI'removed`/home/mysql/I6000/mysql/time_zone_leap_second.MYI'removed`/home/mysql/I6000/mysql/slave_worker_info.frm'removed`/home/mysql/I6000/mysql/func.MYI'removeddirectory:`/home/mysql/I6000/mysql'removed`/home/mysql/I6000/ibdata1'WSREP_SST:[INFO]Cleaningthebinlogdirectory/home/mysql/I6000aswell(2016052815:33:00.540)rm:cannotremove`/home/mysql/I6000/*.index':NosuchfileordirectoryWSREP_SST:[INFO]WaitingforSSTstreamingtocomplete!(2016052815:33:00.547)2016-05-2815:33:014359[Note]WSREP:(666e7891,'tcp://192.168.1.241:6030')turningmessagerelayrequestingoff2016-05-2815:33:154359[Note]WSREP:0.0(node6000_240):Statetransferto1.0(node6000_241)complete.2016-05-2815:33:154359[Note]WSREP:Member0.0(node6000_240)syncedwithgroup.WSREP_SST:[INFO]Preparingthebackupat/home/mysql/I6000//.sst(2016052815:33:15.588)WSREP_SST:[INFO]Evaluatinginnobackupex--no-version-check--apply-log$rebuildcmd${DATA}&>${DATA}/innobackup.prepare.log(2016052815:33:15.591)rm:cannotremove`/home/mysql/I6000//innobackup.prepare.log':Nosuchfileordirectoryrm:cannotremove`/home/mysql/I6000//innobackup.move.log':NosuchfileordirectoryWSREP_SST:[INFO]Movingthebackupto/home/mysql/I6000/(2016052815:33:20.551)WSREP_SST:[INFO]Evaluatinginnobackupex--defaults-file=/home/mysql/pxc6000.cnf--defaults-group=mysqld--no-version-check--move-back--force-non-empty-directories${DATA}&>${DATA}/innobackup.move.log(2016052815:33:20.554)WSREP_SST:[INFO]Movesuccessful,removing/home/mysql/I6000//.sst(2016052815:33:20.848)WSREP_SST:[INFO]Galeraco-ordsfromrecovery:6c86dc17-246f-11e6-9955-ae4d7e89eed2:12(2016052815:33:20.854)WSREP_SST:[INFO]Totaltimeonjoiner:0seconds(2016052815:33:20.857)WSREP_SST:[INFO]Removingthesst_in_progressfile(2016052815:33:20.860)2016-05-2815:33:204359[Note]WSREP:SSTcomplete,seqno:122016-05-2815:33:204359[Note]Plugin'FEDERATED'isdisabled.2016-05-2815:33:204359[Note]InnoDB:Usingatomicstorefcountbufferpoolpages2016-05-2815:33:204359[Note]InnoDB:TheInnoDBmemoryheapisdisabled2016-05-2815:33:204359[Note]InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins2016-05-2815:33:204359[Note]InnoDB:Memorybarrierisnotused2016-05-2815:33:204359[Note]InnoDB:Compressedtablesusezlib1.2.32016-05-2815:33:204359[Note]InnoDB:UsingLinuxnativeAIO2016-05-2815:33:204359[Note]InnoDB:UsingCPUcrc32instructions2016-05-2815:33:204359[Note]InnoDB:Initializingbufferpool,size=100.0M2016-05-2815:33:204359[Note]InnoDB:Completedinitializationofbufferpool2016-05-2815:33:204359[Note]InnoDB:HighestsupportedfileformatisBarracuda.2016-05-2815:33:204359[Note]InnoDB:128rollbacksegment(s)areactive.2016-05-2815:33:204359[Note]InnoDB:Waitingforpurgetostart2016-05-2815:33:214359[Note]InnoDB:PerconaXtraDB(http://www.percona.com)5.6.26-74.0started;logsequencenumber16501982016-05-2815:33:214359[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:73f9a99b-24a6-11e6-a163-000c2973aa48.2016-05-2815:33:214359[Note]RSAprivatekeyfilenotfound:/home/mysql/I6000//private_key.pem.Someauthenticationpluginswillnotwork.2016-05-2815:33:214359[Note]RSApublickeyfilenotfound:/home/mysql/I6000//public_key.pem.Someauthenticationpluginswillnotwork.2016-05-2815:33:214359[Note]Serverhostname(bind-address):'*';port:60002016-05-2815:33:214359[Note]IPv6isavailable.2016-05-2815:33:214359[Note]-'::'resolvesto'::';2016-05-2815:33:214359[Note]ServersocketcreatedonIP:'::'.2016-05-2815:33:214359[Warning]'user'entry'root@zejin240'ignoredin--skip-name-resolvemode.2016-05-2815:33:214359[Warning]'user'entry'@zejin240'ignoredin--skip-name-resolvemode.2016-05-2815:33:214359[Warning]'proxies_priv'entry'@root@zejin240'ignoredin--skip-name-resolvemode.2016-05-2815:33:214359[Note]EventScheduler:Loaded0events2016-05-2815:33:214359[Note]WSREP:Signallingprovidertocontinue.2016-05-2815:33:214359[Note]WSREP:Initializedwsrepsidno22016-05-2815:33:214359[Note]WSREP:SSTreceived:6c86dc17-246f-11e6-9955-ae4d7e89eed2:122016-05-2815:33:214359[Note]/usr/local/pxc_56/bin/mysqld:readyforconnections.Version:'5.6.26-74.0-25.12-log'socket:'/home/mysql/I6000/mysql.sock'port:6000PerconaXtraDBClusterbinary(GPL)5.6.26-25.12,Revision624ef81,wsrep_25.122016-05-2815:33:214359[Note]WSREP:1.0(node6000_241):Statetransferfrom0.0(node6000_240)complete.2016-05-2815:33:214359[Note]WSREP:ShiftingJOINER->JOINED(TO:12)2016-05-2815:33:214359[Note]WSREP:Member1.0(node6000_241)syncedwithgroup.2016-05-2815:33:214359[Note]WSREP:ShiftingJOINED->SYNCED(TO:12)2016-05-2815:33:214359[Note]WSREP:Synchronizedwithgroup,readyforconnections2016-05-2815:33:214359[Note]WSREP:wsrep_notify_cmdisnotdefined,skippingnotification.

可以看到里面有大量的removed操作，它会删除数据文件夹里面的所有文件，再从主节点那里通过SST获取数据官方说明：

XtraBackupSSTworksintwostages:•StageIonjoinerchecksifitisSSTorISTbasedonpresenceofxtrabackup_istfile.•InStageIIitstartsthedatatransfer,ifit'sSST,itemptiesthedatadirectorysansfewfiles(galera.cache,sst_in_progress,grastate.dat)andthenproceedwiththeSSTorifit'sIST,proceedsasbefore.

至此我们完成了pxc环境下避免新节点加入时需要进行SST的方法，在数据量大时SST将会占用大量的资源，配置的gcache.size足够容纳从备份到恢复此时间段内主节点产生的数据量，建议通过业务低峰期时通过脚本来实现此新节点加入操作。

以上这篇MySQLPXC构建一个新节点只需IST传输的方法(推荐)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。

MySQL PXC构建一个新节点只需IST传输的方法(推荐)

热门推荐

随机推荐