今回は、検証用に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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Post Navigation