MySQL Cluster-Backup Recovery preliminary test

MySQL Cluster-Backup Recovery preliminary test data input on the SQL node: CREATE DATABASE bg and CREATE TABLE bgt1 (id INT, 'name' VARCHAR (20 ), primary key ('id') ENGINE = NDBCLUSTER; insert into bgt1 VALUES (1, 'hangzhou1'), (2, 'hangzhou2'), (3, 'hangzhou3 '), (4, 'hangzhou4'); 8.2 On the Management node, start backing up ndb_mgm> start backupWaiting for completed, this may take several minutesNode 4: Backup 1 started from Node 1 node 4: backup 1 started from node 1 completedStart GCP: 184725 StopGCP: 184772 # Records: 722078 # LogRecords: 0 Data: 287345616 bytes Log: 0 bytesndb_mgm> 8.3 insert several Data Records again (to keep as close as possible to the official environment, the flush logs operation is interspersed with the inserted Data !) Insert into bgt1 VALUES (5, 'hang5'), (6, 'hangzhou6'); flush logs; insert into bgt1 VALUES (7, 'hangzhou7'), (8, 'hang8'); 8.4 Delete the data of the SQL node. Mysql> drop database bg; Query OK, 2 rows affected (6.16 sec) 8.5 disable the MYSQLD server. [Root @ banggo data] #/etc/rc. d/init. d/mysqld stopShutting down MySQL ...... 120718 18:58:11 mysqld_safe mysqld from pid file/usr/local/mysql/data/banggo. local. pid ended [OK] [1] + Done/usr/local/mysql/bin/mysqld_safe [root @ banggo data] # [root @ banggo data] #/etc/rc. d/init. d/mysqld stopShutting down MySQL .... [OK] [root @ banggo data] #8.6 restart the node ndb_mgm> shutdownNode 4: Cluster shutdown initiatedNode 4: Nod E shutdown completed.2 NDB Cluster node (s) have shutdown. disconnecting to allow management server to shutdown. ndb_mgm> exit [root @ banggo mysql-cluster] # ndb_mgmd-f/usr/local/mysql/cluster-conf/config. ini -- reloadMySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4 8.7 restart the data node ndbd -- initial 8.8 restore on the data node. /Home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore-e-c 10.100.200.36-n 4-B 1-m -- backup_path =/var/lib/mysql-cluster/BACKUP-1/where backup_path is under the root directory of the default data node (it has been found for a long time, initially thought it was in the configuration file) step 1-m operation [root @ test-db-20053 BACKUP-1] #/home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore-e-c 10.100.200.36-n 4-B 1-m -- backup_path =/var /lib/mysql-cluster/BACKUP-1/Nodeid = 4 Backup Id = 1 backup path =/var/lib/mysql-cluster/BACKUP-1/Opening file '/var/lib/mysql-cluster/BACKUP-1/BACKUP-1.4.ctl 'File size 61160 bytesBackup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4Stop GCP of Backup: 184771 Connected to ndb !! Successfully restored table 'test/def/t2' Successfully restored table event REPL $ test/other restored table 'bg/def/# sql-303d_2 'successfully restored table event REPL $ bg/# sql-303d_2Successfully restored table 'bg/def/# sql-51f0_3 'successfully restored table event REPL $ bg/# sql-51f0_3Successfully restored table 'test/def/t11' Successfully restored table event REPL $ test/t11Successfully restored t Able 'ndb/def/ndborder_goods 'Successfully restored table event REPL $ ndb/inclurestored table 'bg/def/bgt1' Successfully restored table event REPL $ bg/inclurestored table 'test/ def/ndborder_info_history 'successfully restored table event REPL $ test/stored restored table 'mysql/def/ndb_schema 'Successfully restored table event REPL $ mysql/ndb_sch Stored restored table 'mysql/def/dis' Successfully restored table event REPL $ mysql/stored restored table 'ndb/def/ndbtest' Successfully restored table event REPL $ ndb/stored created Index' PRIMARY 'on 'ndborder _ info_history 'Successfully created index 'uniq _ order_ OS' on 'ndborder _ goods 'Successfully created Index' is _ updat' on 'ndborder _ info_his Comment 'successfully created Index' PRIMARY 'on' # sql-51f0_3 'successfully created Index' sku _ sn 'on 'ndborder _ goods' Successfully created index'primary 'on 'bg1' Successfully created index 'exchange _ from 'on' ndborder _ goods 'Successfully created index 'addtime' on 'ndborder _ info_history' created index 'relating _ return_sn 'on 'ndborder _ info_history' created index 'primary' on 'n' Dborder_goods 'stored created Index' order _ from 'on 'ndborder _ info_history' Successfully created Index' order _ out_sn 'on 'ndborder _ info_history' Successfully created Index' PRIMARY on '# sql-303d_2 'encrypted created Index' order _ status' on 'ndborder _ info_history 'Successfully created index'user _ id' on 'ndborder _ info_history' Successfully created index'uniq _ order_ OS $ unique 'on 'dborder _ goods 'Successfully created Index' order _ sn 'on 'ndborder _ goods' NDBT_ProgramExit: 0-OK [root @ test-db-20053 BACKUP-1] #8.9 Step 2-r operation (if there are N nodes, perform N times) [root @ test-db-20053 BACKUP-1] #/home/mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_restore-e-c 10.100.200.36-n 4-B 1-r -- backup_path =/var/lib/mysql- cluster/BACKUP-1/Nodeid = 4 Backup Id = 1 backup path =/var/lib/mysql-cluster/BACKUP- 1/Opening file '/var/lib/mysql-cluster/BACKUP-1/BACKUP-1.4.ctl' File size 61160 bytesBackup version in files: ndb-6.3.11 ndb version: mysql-5.5.19 ndb-7.2.4Stop GCP of Backup: 184771 Connected to ndb !! Opening file '/var/lib/mysql-cluster/BACKUP-1/BACKUP-1-0.4.Data' File size 287834112 bytes _________________________________________________ Processing data in table: test/def/t2 (20) fragment 0 _________________________________________________________ Processing data in table: bg/def/# sql-303d_2 (34) fragment 0 _____________________________________________________ Processing data in table: bg/def/# s Ql-51f0_3 (32) fragment 0 partition Processing data in table: mysql/def/NDB $ BLOB_7_3 (8) fragment 0 _____________________________________________________ Processing data in table: test/def/t11 (11) fragment 0 _____________________________________________________ Processing data in table: ndb/def/ndborder_goods (12) fragment 0 _______________________________________ __________ Processing data in table: ndb/def/NDB $ BLOB_12_13 (13) fragment 0 contains Processing data in table: mysql/def/ndb_index_stat_head (4) fragment 0 contains Processing data in table: sys/def/NDB $ EVENTS_0 (3) fragment 0 _____________________________________________________________ Processing data in table: sys/def/S YSTAB_0 (2) fragment 0 parse Processing data in table: mysql/def/ndb_index_stat_sample (5) fragment 0 _________________________________________________ Processing data in table: bg/def/bgt1 (36) fragment 0 _________________________________________________________ Processing data in table: test/def/ndborder_info_history (21) fragment 0 Temporary error: 1220: RED O log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase failed) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log Files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase failed) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) Temporary error: 1220: REDO log files overloaded (increase FragmentLogFileSize) reports an error when executing the-r operation [OK] Feihong said it was due to REDO log Files are too small to be increased, without affecting the recovery effect. Reference http://bugs.mysql.com/bug.php?id=19651 This is also the case above. Mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | mysql | ndb | ndbinfo | performance_schema | test | + -------------------- + 6 rows in set (0.00 sec) -- no database? It seems that you have rebuilt mysql> create database bg; -- rebuilt Query OK, 1 row affected (0.06 sec) mysql> use bgDatabase changedmysql> show tables; -- OK, see the table below + -------------- + | Tables_in_bg | + ---------------- + | bgt1 | + -------------- + 1 row in set (0.00 sec) mysql> select * from bgt1; -- data has been recovered + ---- + -------- + | id | name | + ---- + -------- + | 3 | zhang3 | 1 | zhang1 | 2 | zhang2 | 4 | zhang4 | + ---- + -------- + 4 rows in set (0.01 se C) 8.10 find a mysqld node, enter the single-user mode on the Management node, start the SQL node, and start the mysqld node, log in to the largest epoch value ndb_mgm> enter single user mode 10; Single user mode enteredAccess is granted for API node 10 only. ndb_mgm> mysql> SELECT @ LASTEPOCH: = MAX (epoch) FROM mysql. ndb_apply_status; + ---------------------- + | @ LASTEPOCH: = MAX (epoch) | + ------------------------ + | 793593992183807 | + batch + 1 row in set (0.04 sec) based on epoch value, find The location of the binary log and the FILE name mysql> select position, @ FIRSTFILE: = FILE-> FROM mysql. ndb_binlog_index-> WHERE epoch> @ LASTEPOCH-> order by epoch ASC-> LIMIT 1; Empty set (0.03 sec) 8.11 The binary log File select distinct File FROM mysql, except for the first log File, used to restore Data Based on Time points. ndb_binlog_indexWHERE epoch> @ lastepoch and File <> @ firstfile order by File; then restore binary logs: mysqlbinlog-H -- set-charset = "utf8"-D -- start-positio N = 829. /mysql-bin.000012 | grep-v "reload database" | mysql bgmysqlbinlog-H -- set-charset = "utf8"-D -- stop-datetime = "13:30:00 ". /mysql-bin.0000013 | grep-v "reload database" | after mysql bg is restored, exit single user mode and start another SQL node ndb_mgm> EXIT SINGLE USER MODE; exiting single user mode in progress. use all status or SHOW to see when single user mode has been exited. ndb_mgm> enter the mysql node and check that the data has been recovered.