今回は、検証用に2台のノードにMySQL Clusterを導入してみました。
検証目的なので2台で設定してますが、本番ではデータノードは分けた方が良さそうです。

————————-
Node1: 192.168.56.114
Node2: 192.168.56.115
————————-

mysql> select * from ndbinfo.nodes;
+---------+--------+---------+-------------+-------------------+
| node_id | uptime | status  | start_phase | config_generation |
+---------+--------+---------+-------------+-------------------+
|       1 |   3994 | STARTED |           0 |                 1 |
|       2 |   3948 | STARTED |           0 |                 1 |
+---------+--------+---------+-------------+-------------------+
2 rows in set (0.05 sec)

mysql> 

mysql> select * from ndbinfo.arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
|       2 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> 

既に、起動済みなのでNDBストレージエンジンでTABLEを作成してみる。
cluster74_02で作成

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE NDB01 default character set utf8mb4;USE NDB01;
Query OK, 1 row affected (0.06 sec)

Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE T20150319(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndb;
Query OK, 0 rows affected (0.47 sec)

mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc T20150319;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> 

他のノードにてデータ確認
Node: cluster74_01

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| NDB01              |
| mysql              |
| ndb_1_fs           |
| ndbinfo            |
| ndbmemcache        |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> show tables from NDB01;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

ここで一度バックアップを取得
※リストアすると上記の状態に戻る。

ndb_mgm> backup start

DBに追加でデータを追加すると同時にリソースアロケーションを確認。

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-4');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319;
+----+--------------------------------------+
| id | memo                                 |
+----+--------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証-3   |
|  5 | This is MySQL Cluster 7.4.4 検証-5   |
|  1 | This is MySQL Cluster 7.4.4 検証     |
|  2 | This is MySQL Cluster 7.4.4 検証-2   |
|  4 | This is MySQL Cluster 7.4.4 検証-4   |
+----+--------------------------------------+
5 rows in set (0.01 sec)

mysql> desc ndbinfo.memory_per_fragment;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| fq_name                | varchar(512)        | YES  |     | NULL    |       |
| parent_fq_name         | varchar(512)        | YES  |     | NULL    |       |
| type                   | varchar(512)        | YES  |     | NULL    |       |
| table_id               | int(10) unsigned    | YES  |     | NULL    |       |
| node_id                | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance         | int(10) unsigned    | YES  |     | NULL    |       |
| fragment_num           | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_bytes  | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_size_bytes  | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_count       | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_count  | decimal(16,0)       | YES  |     | NULL    |       |
| var_elem_alloc_bytes   | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_free_bytes    | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_count         | bigint(20) unsigned | YES  |     | NULL    |       |
| hash_index_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32608 |
|    1 |    1 |       32768 |      32512 |
|    2 |    0 |       32768 |      32608 |
|    2 |    1 |       32768 |      32512 |
+------+------+-------------+------------+
4 rows in set (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-7');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-9');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-10');
Query OK, 1 row affected (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32480 |
|    1 |    1 |       32768 |      32480 |
|    2 |    0 |       32768 |      32480 |
|    2 |    1 |       32768 |      32480 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id,memory_type,used,used_pages,total,total_pages from ndbinfo.memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       1 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       2 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.01 sec)

mysql> 

ここからは、データのリストア
1. Clusterを停止(管理・データ)
2. 管理ノードを起動
3. データノードを–initial付きで起動
4.SQLノード停止
5. Restore作業1(1台目だけは-mオプション付き)
6. Restore作業2 (2台目は-mは不要)
7. データ確認して完了

[root@cluster74_01 BACKUP]# pwd
/usr/local/mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/data/BACKUP
[root@cluster74_01 BACKUP]# ls -l
合計 0
drwxr-x---. 2 root root 72  3月 19 22:25 BACKUP-1
[root@cluster74_01 BACKUP]# 


[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
ndb_mgm> exit
[root@cluster74_01 cluster]# 

SQLノードも停止しました。

[root@cluster74_01 cluster]# /home/admin/cluster/start_management.sh 
Start MySQL Management Node
MySQL Cluster Management Server mysql-5.6.23 ndb-7.4.4
Management Prompt
/usr/local/mysql/bin/ndb_mgm
[root@cluster74_01 cluster]# 

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:25:38 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:25:38 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 cluster]# 

[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:26:51 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:26:51 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 cluster]# 



[root@cluster74_01 BACKUP]# /usr/local/mysql/bin/ndb_restore -b 1 -n 1 -m -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 1
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Successfully restored table `NDB01/def/T20150319`
Successfully restored table event REPL$NDB01/T20150319
Successfully restored table `ndbmemcache/def/ndb_clusters`
Successfully restored table event REPL$ndbmemcache/ndb_clusters

省略....
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 0
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.log'
File size 52 bytes
Restored 15 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_01 BACKUP]# 



[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndb_restore -b 1 -n 2 -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 2
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.2.Data'
File size 31068 bytes

省略...
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table(24) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.log'
File size 52 bytes
Restored 14 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_02 cluster]# 

SQLノードを起動してデータ確認

[root@cluster74_01 cluster]# ./start_mysql.sh 
Start MySQL Node
[root@cluster74_01 cluster]# 150319 23:45:00 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150319 23:45:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 cluster]# 


[root@cluster74_02 cluster]# 150319 23:45:12 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150319 23:45:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 cluster]# 


ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 



mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |           0 |          0 |
|    1 |    1 |       32768 |      32608 |
|    2 |    0 |           0 |          0 |
|    2 |    1 |       32768 |      32608 |
+------+------+-------------+------------+
4 rows in set (0.06 sec)

mysql> 

restore

参照:
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbinfo.html


リストア中は当然、ロックがかかり他のユーザーは利用出来ませんが、
特定テーブルのみのリストアで済めば最小限の影響範囲に留める事が出来るかもしれません。

■テストテーブルを作成してデータを登録

root@localhost > CREATE TABLE `BR_TEST` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(32) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

root@localhost > insert into BR_TEST(name) value("NAME-1");
Query OK, 1 row affected (0.02 sec)

root@localhost > insert into BR_TEST(name) value("NAME-2");
Query OK, 1 row affected (0.02 sec)

root@localhost > insert into BR_TEST(name) value("NAME-3");
Query OK, 1 row affected (0.01 sec)

root@localhost > 

■特定データベース全体のバックアップ

[admin@CentOS02 bin]$ mysqldump --databases --single-transaction test > /home/admin/testDB20140925.sql -u root -p
Enter password: 
[admin@CentOS02 bin]$ ls -l /home/admin/
合計 4
-rw-rw-r--. 1 admin admin 2877  9月 25 19:46 testDB20140925.sql
[admin@CentOS02 bin]$ 

[admin@CentOS02 bin]$ cat /home/admin/testDB20140925.sql | grep CREATE
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `BR_TEST` (
CREATE TABLE `federated_table` (
[admin@CentOS02 bin]$ 

■特定テーブルのみリストアするため、csplitでテーブル単位にバックアップを分割する。

[admin@CentOS02 ~]$ csplit testDB20140925.sql '/DROP TABLE IF EXISTS/' {*}
973
687
1217
[admin@CentOS02 ~]$ 

[admin@CentOS02 ~]$ ls -l
合計 16
-rw-rw-r--. 1 admin admin 2877  9月 25 19:46 testDB20140925.sql
-rw-rw-r--. 1 admin admin  973  9月 25 19:57 xx00
-rw-rw-r--. 1 admin admin  687  9月 25 19:57 xx01
-rw-rw-r--. 1 admin admin 1217  9月 25 19:57 xx02
[admin@CentOS02 ~]$ 

■ リストア対象テーブル用Scriptを抜粋

[admin@CentOS02 ~]$ cat xx01
DROP TABLE IF EXISTS `BR_TEST`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `BR_TEST` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `BR_TEST`
--

LOCK TABLES `BR_TEST` WRITE;
/*!40000 ALTER TABLE `BR_TEST` DISABLE KEYS */;
INSERT INTO `BR_TEST` VALUES (1,'NAME-1'),(2,'NAME-2'),(3,'NAME-3');
/*!40000 ALTER TABLE `BR_TEST` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `federated_table`
--

[admin@CentOS02 ~]$ 

■対象テーブルを間違えてTruncateしたと仮定してデータ削除

root@localhost > truncate table BR_TEST;
Query OK, 0 rows affected (0.09 sec)

root@localhost > select count(*) from BR_TEST;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

root@localhost > 

■特定テーブルのリストア処理とデータが戻っていることを確認

[admin@CentOS02 ~]$ mysql -u root -p test < /home/admin/xx01
Enter password: 
[admin@CentOS02 ~]$ 


root@localhost > select count(*) from BR_TEST;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

root@localhost > 

余談:
■対象テーブルがトランザクション中だと、リストアは待たされます。
 反対にリストア中はテーブルは参照出来ません。

root@localhost > lock table BR_TEST READ;
Query OK, 0 rows affected (0.00 sec)
                                      <----この間はリストアも待ちになります。
root@localhost > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

root@localhost > select * from BR_TEST;
+----+--------+
| id | name   |
+----+--------+
|  1 | NAME-1 |
|  2 | NAME-2 |
|  3 | NAME-3 |
+----+--------+
3 rows in set (0.00 sec)

root@localhost >


MY ADMINを利用してMYSQLのFULL BACKUPから、
特定テーブルのリストアを行う方法。
普段は、mysqldumpでバックアップして、mysqlコマンドでリストア
しているので良い勉強になりました。

① まずはテストの為にBACKUPを作成してみます。
BACKUPプロジェクトを作成して「EXECUTE BACKUP NOW」で
バックアップを取得してみます。


BACKUP


② テストの為にBACKUPを取得した、DBにあるテーブルから
データを削除してみます。


DELETE


③ MYSQL AdministratorにてRESTOREを選択して、
①にて作成したBACKUPファイルを開き、「Analyze Backup File」を選択
しリストアしたいテーブルのみをチェックします。
※ FULLでリストアしたいときは、特に特定のテーブルのみ選択しないで
全てリストアしてください。


RESTORE


④ リストアがきちんと行われたかテーブルをselectして確認してみて下さい。
データが戻っていれば成功です。


CONFIRMATION


⑤ データが戻っているので成功ですね。