********************************************************************* ######### Source 7.0.9 # 1mgm, 4 storage and 3 api nodes ********************************************************************* # Created a table keyvalye with 15k records [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndb_mgmd --configdir=/data/ushastry/cluster/mysql-cluster-com-7_0_9/ -f config.ini --initial 2013-01-17 12:40:41 [MgmtSrvr] INFO -- NDB Cluster Management Server. mysql-5.1.39 ndb-7.0.9b 2013-01-17 12:40:41 [MgmtSrvr] INFO -- Reading cluster configuration from 'config.ini' [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndbd --ndb-nodeid=4 --initial 2013-01-17 12:40:53 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1 [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndbd --ndb-nodeid=5 --initial 2013-01-17 12:40:58 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1 [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndbd --ndb-nodeid=6 --initial 2013-01-17 12:41:03 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1 [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndbd --ndb-nodeid=7 --initial 2013-01-17 12:41:08 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1 [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=4 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master) id=5 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0) id=6 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 1) id=7 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 1) [ndb_mgmd(MGM)] 1 node(s) id=1 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9) [mysqld(API)] 3 node(s) id=8 @127.0.0.1 (mysql-5.1.39 ndb-7.0.9) id=9 (not connected, accepting connect from any host) id=10 (not connected, accepting connect from any host) ndb_mgm> # Created a table keyvalye with 15k records [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/mysql -u root -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.39-ndb-7.0.9-cluster-com MySQL Cluster Server (Commercial) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table if exists `keyvalue`; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> create table `keyvalue` (`id` int,`value1` int default null,`value2` double default null,primary key (`id`)) engine=ndbcluster; Query OK, 0 rows affected (0.86 sec) mysql> set @id:=0; insert into `keyvalue` values (@id:=@id+1,floor(rand()*1000000),rand()*1000000),(@id:=@id+1,floor(rand()*1000000),rand()*1000000),(@id:=@id+1,floor(rand()*1000000) Query OK, 0 rows affected (0.00 sec) Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into `keyvalue`(`id`,`value1`,`value2`) -> select @id:=@id+1,floor(rand()*1000000),rand()*1000000 from -> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4, -> `keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,`keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit -> 15000 ; Query OK, 15000 rows affected (0.19 sec) Records: 15000 Duplicates: 0 Warnings: 0 mysql> select count(*) from keyvalue; +----------+ | count(*) | +----------+ | 15004 | +----------+ 1 row in set (0.00 sec) mysql> ndb_mgm> all report memory; Node 4: Data usage is 12%(39 32K pages of total 320) Node 4: Index usage is 5%(35 8K pages of total 672) Node 5: Data usage is 12%(39 32K pages of total 320) Node 5: Index usage is 5%(35 8K pages of total 672) Node 6: Data usage is 12%(40 32K pages of total 320) Node 6: Index usage is 5%(36 8K pages of total 672) Node 7: Data usage is 12%(40 32K pages of total 320) Node 7: Index usage is 5%(36 8K pages of total 672) ## [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndb_show_tables id type state logging database schema name 1 0 Online - DEFAULT-HASHMAP-240-4 3 SystemTable Online Yes sys def NDB$EVENTS_0 6 UserTable Online Yes mysql def ndb_apply_status 0 IndexTrigger Online - NDB$INDEX_8_CUSTOM 7 UserTable Online Yes test def keyvalue 5 UserTable Online Yes mysql def NDB$BLOB_4_3 8 OrderedIndex Online No sys def PRIMARY 2 SystemTable Online Yes sys def SYSTAB_0 4 UserTable Online Yes mysql def ndb_schema 3 TableEvent Online - REPL$mysql/ndb_apply_status 1 TableEvent Online - REPL$mysql/ndb_schema 2 TableEvent Online - NDB$BLOBEVENT_REPL$mysql/ndb_schema_3 4 TableEvent Online - REPL$test/keyvalue NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndb_desc -p -d test keyvalue -- keyvalue -- Version: 1 Fragment type: 9 K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 3 Number of primary keys: 1 Length of frm data: 280 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 FragmentCount: 4 TableStatus: Retrieved -- Attributes -- id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY value1 Int NULL AT=FIXED ST=MEMORY value2 Double NULL AT=FIXED ST=MEMORY -- Indexes -- PRIMARY KEY(id) - UniqueHashIndex PRIMARY(id) - OrderedIndex -- Per partition info -- Partition Row count Commit count Frag fixed memory Frag varsized memory Extent_space Free extent_space 0 3789 3789 196608 0 0 0 2 3769 3769 196608 0 0 0 1 3693 3693 163840 0 0 0 3 3753 3753 196608 0 0 0 NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ # Schema dump using mysql, native backup [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/mysqldump --no-data -uroot -p --databases test --routines > /tmp/test.sql Enter password: [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ ls -l /tmp/test.sql -rw-rw-r-- 1 ushastry ushastry 1914 Jan 17 13:17 /tmp/test.sql [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/ndb_mgm -e "START BACKUP 16012013 WAIT COMPLETED SNAPSHOTSTART" Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Node 4: Backup 16012013 started from node 1 Node 4: Backup 16012013 started from node 1 completed StartGCP: 1043 StopGCP: 1046 #Records: 17059 #LogRecords: 0 Data: 471232 bytes Log: 0 bytes [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ ls -l cluster-data/BACKUP/BACKUP-16012013/ total 536 -rw-rw-r-- 1 ushastry ushastry 119076 Jan 17 13:18 BACKUP-16012013-0.4.Data -rw-rw-r-- 1 ushastry ushastry 119044 Jan 17 13:18 BACKUP-16012013-0.5.Data -rw-rw-r-- 1 ushastry ushastry 117180 Jan 17 13:18 BACKUP-16012013-0.6.Data -rw-rw-r-- 1 ushastry ushastry 116796 Jan 17 13:18 BACKUP-16012013-0.7.Data -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:18 BACKUP-16012013.4.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:18 BACKUP-16012013.4.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:18 BACKUP-16012013.5.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:18 BACKUP-16012013.5.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:18 BACKUP-16012013.6.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:18 BACKUP-16012013.6.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:18 BACKUP-16012013.7.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:18 BACKUP-16012013.7.log # Copied backup folder to /tmp for later restoring on 7.2.7 [ushastry@cluster-repo mysql-cluster-com-7_0_9]$ ls -l /tmp/BACKUP-16012013/ total 536 -rw-rw-r-- 1 ushastry ushastry 119076 Jan 17 13:26 BACKUP-16012013-0.4.Data -rw-rw-r-- 1 ushastry ushastry 119044 Jan 17 13:26 BACKUP-16012013-0.5.Data -rw-rw-r-- 1 ushastry ushastry 117180 Jan 17 13:26 BACKUP-16012013-0.6.Data -rw-rw-r-- 1 ushastry ushastry 116796 Jan 17 13:26 BACKUP-16012013-0.7.Data -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:26 BACKUP-16012013.4.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:26 BACKUP-16012013.4.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:26 BACKUP-16012013.5.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:26 BACKUP-16012013.5.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:26 BACKUP-16012013.6.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:26 BACKUP-16012013.6.log -rw-rw-r-- 1 ushastry ushastry 10100 Jan 17 13:26 BACKUP-16012013.7.ctl -rw-rw-r-- 1 ushastry ushastry 52 Jan 17 13:26 BACKUP-16012013.7.log ********************************************************************* ######### Source 7.2.7 # 1mgm, 4 storage nodes and 3 api ********************************************************************* # Start mgm, data nodes and import schema on one of the sql node and then restore the backup using ndb_restore on all nodes [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_mgmd --configdir=/data/ushastry/cluster/mysql-cluster-advanced-7_2_7/ -f config.ini --initial MySQL Cluster Management Server mysql-5.5.25 ndb-7.2.7 [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndbd --ndb-nodeid=4 --initial 2013-01-17 13:32:20 [ndbd] INFO -- Angel connected to 'localhost:1186' 2013-01-17 13:32:20 [ndbd] INFO -- Angel allocated nodeid: 4 [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndbd --ndb-nodeid=5 --initial 2013-01-17 13:32:25 [ndbd] INFO -- Angel connected to 'localhost:1186' 2013-01-17 13:32:25 [ndbd] INFO -- Angel allocated nodeid: 5 [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndbd --ndb-nodeid=6 --initial 2013-01-17 13:32:30 [ndbd] INFO -- Angel connected to 'localhost:1186' 2013-01-17 13:32:30 [ndbd] INFO -- Angel allocated nodeid: 6 [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndbd --ndb-nodeid=7 --initial 2013-01-17 13:32:36 [ndbd] INFO -- Angel connected to 'localhost:1186' 2013-01-17 13:32:36 [ndbd] INFO -- Angel allocated nodeid: 7 ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 4 node(s) id=4 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master) id=5 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0) id=6 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 1) id=7 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 1) [ndb_mgmd(MGM)] 1 node(s) id=1 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7) [mysqld(API)] 3 node(s) id=8 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7) id=9 (not connected, accepting connect from any host) id=10 (not connected, accepting connect from any host) ndb_mgm> # Before restore [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_show_tables id type state logging database schema name 8 UserTable Online Yes mysql def NDB$BLOB_7_3 5 UserTable Online Yes mysql def ndb_index_stat_sample 3 SystemTable Online Yes sys def NDB$EVENTS_0 9 UserTable Online Yes mysql def ndb_apply_status 4 UserTable Online Yes mysql def ndb_index_stat_head 6 OrderedIndex Online No sys def ndb_index_stat_sample_x1 1 0 Online - DEFAULT-HASHMAP-3840-4 2 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB$INDEX_6_CUSTOM 7 UserTable Online Yes mysql def ndb_schema 4 TableEvent Online - REPL$mysql/ndb_apply_status 1 TableEvent Online - ndb_index_stat_head_event 2 TableEvent Online - REPL$mysql/ndb_schema 3 TableEvent Online - NDB$BLOBEVENT_REPL$mysql/ndb_schema_3 NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_desc -p -d test keyvalue No such object: keyvalue NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ # Restore "test" schema backed up on 7.0.9 ( restore schema using the dump generated by mysqldump) # restore schema [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/mysql -u root -p test < /tmp/test.sql Enter password: # restore data using ndb_restore [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_restore -c localhost -n 4 -b 16012013 -r /tmp//BACKUP-16012013/ Nodeid = 4 Backup Id = 16012013 backup path = /tmp//BACKUP-16012013/ Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.4.ctl' File size 10100 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.39 ndb-7.0.9 Stop GCP of Backup: 1045 Connected to ndb!! Opening file '/tmp//BACKUP-16012013/BACKUP-16012013-0.4.Data' File size 119076 bytes _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 0 _____________________________________________________ Processing data in table: test/def/keyvalue(7) fragment 0 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 0 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 0 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 0 Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.4.log' File size 52 bytes Restored 3789 tuples and 0 log entries NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_restore -c localhost -n 5 -b 16012013 -r /tmp//BACKUP-16012013/ Nodeid = 5 Backup Id = 16012013 backup path = /tmp//BACKUP-16012013/ Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.5.ctl' File size 10100 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.39 ndb-7.0.9 Stop GCP of Backup: 1045 Connected to ndb!! Opening file '/tmp//BACKUP-16012013/BACKUP-16012013-0.5.Data' File size 119044 bytes _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 2 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 2 _____________________________________________________ Processing data in table: test/def/keyvalue(7) fragment 2 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 2 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 2 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 2 Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.5.log' File size 52 bytes Restored 3769 tuples and 0 log entries NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_restore -c localhost -n 6 -b 16012013 -r /tmp//BACKUP-16012013/ Nodeid = 6 Backup Id = 16012013 backup path = /tmp//BACKUP-16012013/ Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.6.ctl' File size 10100 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.39 ndb-7.0.9 Stop GCP of Backup: 1045 Connected to ndb!! Opening file '/tmp//BACKUP-16012013/BACKUP-16012013-0.6.Data' File size 117180 bytes _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 1 _____________________________________________________ Processing data in table: test/def/keyvalue(7) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 1 Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.6.log' File size 52 bytes Restored 3693 tuples and 0 log entries NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_restore -c localhost -n 7 -b 16012013 -r /tmp//BACKUP-16012013/ Nodeid = 7 Backup Id = 16012013 backup path = /tmp//BACKUP-16012013/ Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.7.ctl' File size 10100 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.39 ndb-7.0.9 Stop GCP of Backup: 1045 Connected to ndb!! Opening file '/tmp//BACKUP-16012013/BACKUP-16012013-0.7.Data' File size 116796 bytes _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 3 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 3 _____________________________________________________ Processing data in table: test/def/keyvalue(7) fragment 3 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 3 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 3 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 3 Opening file '/tmp//BACKUP-16012013/BACKUP-16012013.7.log' File size 52 bytes Restored 3753 tuples and 0 log entries NDBT_ProgramExit: 0 - OK # After restore [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_show_tables id type state logging database schema name 1 IndexTrigger Online - NDB$INDEX_11_CUSTOM 8 UserTable Online Yes mysql def NDB$BLOB_7_3 5 UserTable Online Yes mysql def ndb_index_stat_sample 3 SystemTable Online Yes sys def NDB$EVENTS_0 9 UserTable Online Yes mysql def ndb_apply_status 4 UserTable Online Yes mysql def ndb_index_stat_head 6 OrderedIndex Online No sys def ndb_index_stat_sample_x1 10 UserTable Online Yes test def keyvalue 11 OrderedIndex Online No sys def PRIMARY 1 0 Online - DEFAULT-HASHMAP-3840-4 2 SystemTable Online Yes sys def SYSTAB_0 0 IndexTrigger Online - NDB$INDEX_6_CUSTOM 7 UserTable Online Yes mysql def ndb_schema 4 TableEvent Online - REPL$mysql/ndb_apply_status 1 TableEvent Online - ndb_index_stat_head_event 2 TableEvent Online - REPL$mysql/ndb_schema 3 TableEvent Online - NDB$BLOBEVENT_REPL$mysql/ndb_schema_3 5 TableEvent Online - REPL$test/keyvalue NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/ndb_desc -p -d test keyvalue -- keyvalue -- Version: 1 Fragment type: 9 K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 3 Number of primary keys: 1 Length of frm data: 279 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 FragmentCount: 4 ExtraRowGciBits: 0 ExtraRowAuthorBits: 0 TableStatus: Retrieved -- Attributes -- id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY value1 Int NULL AT=FIXED ST=MEMORY value2 Double NULL AT=FIXED ST=MEMORY -- Indexes -- PRIMARY KEY(id) - UniqueHashIndex PRIMARY(id) - OrderedIndex -- Per partition info -- Partition Row count Commit count Frag fixed memory Frag varsized memory Extent_space Free extent_space 0 3789 3789 196608 0 0 0 2 3769 3769 196608 0 0 0 1 3693 3693 163840 0 0 0 3 3753 3753 196608 0 0 0 NDBT_ProgramExit: 0 - OK [ushastry@cluster-repo mysql-cluster-advanced-7_2_7]$ bin/mysql -u root -p test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.25a-ndb-7.2.7-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(*) from keyvalue; +----------+ | count(*) | +----------+ | 15004 | +----------+ 1 row in set (0.00 sec) mysql>