![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL Clusterは、データベースシステムの高可用性をサポートし、
ビジネスの機会損失を回避し、サービスの安定提供をサポートする
インメモリーデータベースです。Shared Nothing型のクラスターの為、
柔軟にスケールアウトする事が可能です。バックエンドはノード間を
ネットワークで接続する為、ノード間を跨る大量のデータを扱う処理よりも、
特定のノードからユーザーIDなどを取得してくるサービスなどに向いているようです。
GPLとCommercial共にあるので、サービスの重要度などによって
使い分けていけるのも良いかと思います。
————————————————–
MySQL Cluster (GPL)
MySQL Cluster is a real-time, open source transactional database.
MySQL Cluster is a write-scalable, real-time, ACID-compliant transactional database,
designed to deliver 99.999% availability. With a distributed, multi-master architecture
and no single point of failure, MySQL Cluster scales horizontally on commodity hardware
with auto-sharding (partitioning) to serve read and write intensive workloads,
accessed via SQL and NoSQL interfaces.
It is available as both open source and commercial editions. The Community (OSS) Edition is available under the free software/opensource GNU General Public License (commonly known as the “GPL”). MySQL Cluster is provided as a separate download so that users can have access to builds from the latest source releases of the MySQL Cluster Carrier Grade Edition (http://www.mysql.com/downloads/clustercge/).
MySQL Cluster CGE (commercial)
自動シャーディングによる書き込みのスケーラビリティ
99.999%の可用性
SQL & NoSQL API
リアルタイム・パフォーマンス
マルチサイト遠隔地レプリケーション
オンライン・スケーリング と スキーマ・アップグレード
MySQL Cluster Auto-Installer
MySQL Cluster Manager
24時間365日提供する技術サポート
————————————————–
上記、dev.mysql.com抜粋
MySQL Clusterダウンロードとインストール
基本的に、設定は役割毎に必要ですがTarを展開して利用してください。
必要に応じて、MySQLインターフェースをインストール。
基本的には、MySQLのインストールはMySQLノードだけでOK。
ここでは、検証目的のみでManageノードにインストールしいます。
[root@CLSManage01 admin]# cd /usr/local/src/ [root@CLSManage01 src]# ls -l 合計 477472 -rw-rw-r--. 1 admin admin 488928659 7月 4 08:26 mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64.tar.gz [root@CLSManage01 src]# tar zxvf mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64.tar.gz mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/sql-bench/bench-count-distinct mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/sql-bench/innotest2a mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/sql-bench/test-select [Hup....] mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/share/memcache-api/README mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/share/memcache-api/sandbox.sh mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/share/japanese/errmsg.sys [root@CLSManage01 src]# [root@CLSManage01 src]# groupadd mysql [root@CLSManage01 src]# useradd -g mysql mysql [root@CLSManage01 src]# mv mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64 /usr/local/ [root@CLSManage01 src]# cd /usr/local/ [root@CLSManage01 local]# ln -s mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/ mysql [root@CLSManage01 local]# [root@CLSManage01 local]# ls -l 合計 4 drwxr-xr-x. 2 root root 6 6月 10 09:11 bin drwxr-xr-x. 2 root root 6 6月 10 09:11 etc drwxr-xr-x. 2 root root 6 6月 10 09:11 games drwxr-xr-x. 2 root root 6 6月 10 09:11 include drwxr-xr-x. 2 root root 6 6月 10 09:11 lib drwxr-xr-x. 2 root root 6 6月 10 09:11 lib64 drwxr-xr-x. 2 root root 6 6月 10 09:11 libexec lrwxrwxrwx. 1 root root 51 10月 1 09:56 mysql -> mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/ drwxr-xr-x. 13 root root 4096 10月 1 09:52 mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64 drwxr-xr-x. 2 root root 6 6月 10 09:11 sbin drwxr-xr-x. 5 root root 46 9月 22 23:37 share drwxr-xr-x. 2 root root 70 10月 1 09:55 src [root@CLSMySQL01 local]# chown -R mysql:mysql mysql/ [root@CLSMySQL01 local]# chmod -R 755 mysql/
メモ：MySQLインストールで必要だったので、以下のパッケージもインストールしておきました。
yum install perl
yum install perl-Data-Dumper
yum install libaio
yum install libaio-devel
[root@CLSManage01 mysql]# pwd /usr/local/mysql [root@CLSManage01 mysql]# ./scripts/mysql_install_db --user=mysql WARNING: The host 'CLSManage01' could not be looked up with ./bin/resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables...2014-10-01 10:14:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-10-01 10:14:40 2081 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-10-01 10:14:40 2081 [Note] InnoDB: The InnoDB memory heap is disabled 2014-10-01 10:14:40 2081 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-10-01 10:14:40 2081 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-10-01 10:14:40 2081 [Note] InnoDB: Using Linux native AIO 2014-10-01 10:14:40 2081 [Note] InnoDB: Not using CPU crc32 instructions 2014-10-01 10:14:40 2081 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2014-10-01 10:14:40 2081 [Note] InnoDB: Completed initialization of buffer pool 2014-10-01 10:14:40 2081 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! [Hup..] To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h CLSManage01 password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file ./my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as ./my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [root@CLSManage01 mysql]# bin/mysqld_safe --user=mysql & [1] 10694 [root@CLSManage01 mysql]# 141001 10:29:16 mysqld_safe Logging to '/usr/local/mysql/data/CLSManage01.err'. 141001 10:29:16 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data [root@CLSManage01 mysql]# [root@CLSManage01 mysql]# pwd /usr/local/mysql [root@CLSManage01 mysql]# ./bin/mysqladmin -u root password 'パスワード'
MySQL Cluster用の設定
[root@CLSManage01 mysql]# mkdir /usr/local/mysql/mysql-cluster/ [root@CLSManage01 local]# vi /usr/local/mysql/mysql-cluster/ndb-config.ini [root@CLSManage01 local]# cat /usr/local/mysql/mysql-cluster/ndb-config.ini [ndbd default] NoOfReplicas= 2 MaxNoOfConcurrentOperations= 10000 DataMemory= 128M IndexMemory= 65M TimeBetweenWatchDogCheck= 30000 DataDir= /usr/local/mysql/mysql-cluster MaxNoOfOrderedIndexes= 512 [ndb_mgmd default] DataDir= /usr/local/mysql/mysql-cluster [ndb_mgmd] NodeId=1 HostName=192.168.56.103 DataDir= /usr/local/mysql/mysql-cluster [mysqld] NodeId=2 HostName=192.168.56.104 [mysqld] NodeId=3 HostName=192.168.56.105 [ndbd] NodeId=4 HostName=192.168.56.106 datadir=/usr/local/mysql/data [ndbd] NodeId=5 HostName=192.168.56.107 datadir=/usr/local/mysql/data [root@CLSManage01 local]#
Mgm Node開始
[root@CLSManage01 mysql-cluster]# /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/ndb-config.ini
MySQL Cluster Management Server mysql-5.6.19 ndb-7.3.6
[root@CLSManage01 mysql-cluster]#
ndb_mgm> help
---------------------------------------------------------------------------
NDB Cluster -- Management Client -- Help
---------------------------------------------------------------------------
HELP Print help text
HELP COMMAND Print detailed help for COMMAND(e.g. SHOW)
SHOW Print information about cluster
CREATE NODEGROUP <id>,<id>... Add a Nodegroup containing nodes
DROP NODEGROUP <NG> Drop nodegroup with id NG
START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [<backup id>] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [<backup id>] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
Start backup (default WAIT COMPLETED,SNAPSHOTEND)
ABORT BACKUP <backup id> Abort backup
SHUTDOWN Shutdown all processes in cluster
CLUSTERLOG ON [<severity>] ... Enable Cluster logging
CLUSTERLOG OFF [<severity>] ... Disable Cluster logging
CLUSTERLOG TOGGLE [<severity>] ... Toggle severity filter on/off
CLUSTERLOG INFO Print cluster log information
<id> START Start data node (started with -n)
<id> RESTART [-n] [-i] [-a] [-f] Restart data or management server node
<id> STOP [-a] [-f] Stop data or management server node
ENTER SINGLE USER MODE <id> Enter single user mode
EXIT SINGLE USER MODE Exit single user mode
<id> STATUS Print status
<id> CLUSTERLOG {<category>=<level>}+ Set log level for cluster log
PURGE STALE SESSIONS Reset reserved nodeid's in the mgmt server
CONNECT [<connectstring>] Connect to management server (reconnect if already connected)
<id> REPORT <report-type> Display report for <report-type>
QUIT Quit management client
<severity> = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG
<category> = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION | INFO | ERROR | CONGESTION | DEBUG | BACKUP | SCHEMA
<report-type> = BACKUPSTATUS | MEMORYUSAGE | EVENTLOG
<level> = 0 - 15
<id> = ALL | Any database node id
For detailed help on COMMAND, use HELP COMMAND.
ndb_mgm>
Data Node開始
[root@CLSData01 admin]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.103 Connect Data node to Management Node 2015-01-03 08:25:12 [ndbd] INFO -- Angel connected to '192.168.56.103:1186' 2015-01-03 08:25:12 [ndbd] INFO -- Angel allocated nodeid: 4
SQL Node開始
[root@CLSMySQL01 admin]# /usr/local/mysql/bin/mysqld_safe & [1] 1930 [root@CLSMySQL01 admin]# 141001 13:02:17 mysqld_safe Logging to '/usr/local/mysql/data/CLSMySQL01.err'. 141001 13:02:18 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data [root@CLSMySQL01 admin]#
その他のSQLノードとデータノードを設定後に起動して、管理ノードで接続を確認してみます。
[root@CLSManage01 mysql-cluster]# /usr/local/mysql/bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration ndb_mgm> show Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=4 @192.168.56.106 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *) id=5 @192.168.56.107 (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.56.103 (mysql-5.6.19 ndb-7.3.6) [mysqld(API)] 2 node(s) id=2 @192.168.56.104 (mysql-5.6.19 ndb-7.3.6) id=3 @192.168.56.105 (mysql-5.6.19 ndb-7.3.6) ndb_mgm>
それぞれのノードで実行されているプロセスとListenしているサービス
管理ノードX1, SQLノード X 2, Data Node X 2
[root@CLSManage01 local]# ps -ef | grep mysql root 1934 1 1 08:21 ? 00:10:41 /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/ndb-config.ini root 2407 1909 0 17:29 pts/0 00:00:00 grep --color=auto mysql [root@CLSManage01 local]# [root@CLSManage01 local]# netstat | grep mysql-cluster tcp 0 0 192.168.5:mysql-cluster 192.168.56.106:60550 ESTABLISHED tcp 0 0 192.168.5:mysql-cluster 192.168.56.104:56500 ESTABLISHED tcp 0 0 192.168.5:mysql-cluster 192.168.56.107:48671 ESTABLISHED tcp 0 0 192.168.5:mysql-cluster 192.168.56.105:35490 ESTABLISHED tcp 0 0 localhost:49167 localhost:mysql-cluster ESTABLISHED tcp 0 0 192.168.5:mysql-cluster 192.168.56.107:48670 ESTABLISHED tcp 0 0 192.168.5:mysql-cluster 192.168.56.106:60551 ESTABLISHED tcp 0 0 localhost:mysql-cluster localhost:49167 ESTABLISHED [root@CLSManage01 local]# [admin@CLSMySQL01 ~]$ ps -ef | grep mysql root 2054 1 0 08:26 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe mysql 2158 2054 1 08:26 pts/0 00:07:41 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/CLSMySQL01.err --pid-file=/usr/local/mysql/data/CLSMySQL01.pid admin 2503 1865 0 17:30 pts/0 00:00:00 grep --color=auto mysql [admin@CLSMySQL01 ~]$ ls -l /usr/local/mysql lrwxrwxrwx. 1 mysql mysql 51 10月 1 10:05 /usr/local/mysql -> mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/ [admin@CLSMySQL01 ~]$ [admin@CLSMySQL01 ~]$ netstat | grep mysql-cluster tcp 0 0 192.168.56.104:56500 192.168.5:mysql-cluster ESTABLISHED [admin@CLSMySQL01 ~]$ [root@CLSMySQL02 admin]# ps -ef | grep mysql root 1928 1916 0 08:27 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe mysql 2032 1928 1 08:27 pts/0 00:07:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/CLSMySQL02.err --pid-file=/usr/local/mysql/data/CLSMySQL02.pid root 2499 1916 0 17:31 pts/0 00:00:00 grep --color=auto mysql [root@CLSMySQL02 admin]# ls -l /usr/local/mysql lrwxrwxrwx. 1 mysql mysql 51 10月 1 10:06 /usr/local/mysql -> mysql-cluster-advanced-7.3.6-linux-glibc2.5-x86_64/ [root@CLSMySQL02 admin]# [root@CLSMySQL02 admin]# netstat | grep mysql-cluster tcp 0 0 192.168.56.105:35490 192.168.5:mysql-cluster ESTABLISHED [root@CLSMySQL02 admin]# [root@CLSData01 admin]# ps -ef | grep mysql root 1931 1 0 08:25 ? 00:00:08 /usr/local/mysql/bin/ndbd --connect-string=192.168.56.103 root 1932 1931 4 08:25 ? 00:22:51 /usr/local/mysql/bin/ndbd --connect-string=192.168.56.103 root 2390 1911 0 17:31 pts/0 00:00:00 grep --color=auto mysql [root@CLSData01 admin]# [root@CLSData01 admin]# netstat | grep mysql-cluster tcp 0 0 192.168.56.106:60550 192.168.5:mysql-cluster ESTABLISHED tcp 0 0 192.168.56.106:60551 192.168.5:mysql-cluster ESTABLISHED [root@CLSData01 admin]# [root@CLSData02 admin]# ps -ef | grep mysql root 10501 1 0 08:26 ? 00:00:06 /usr/local/mysql/bin/ndbd --connect-string=192.168.56.103 root 10502 10501 3 08:26 ? 00:16:35 /usr/local/mysql/bin/ndbd --connect-string=192.168.56.103 root 10840 10482 0 17:32 pts/0 00:00:00 grep --color=auto mysql [root@CLSData02 admin]# [root@CLSData02 admin]# netstat | grep mysql-cluster tcp 0 0 192.168.56.107:48670 192.168.5:mysql-cluster ESTABLISHED tcp 0 0 192.168.56.107:48671 192.168.5:mysql-cluster ESTABLISHED [root@CLSData02 admin]#
NDBClusterストレージエンジンとVARIABLEの確認
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 11 rows in set (0.00 sec) mysql> mysql> show variables like '%cluster%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | ndb_cluster_connection_pool | 1 | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> mysql> show variables like 'ndb%'; +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ndb_autoincrement_prefetch_sz | 1 | | ndb_batch_size | 32768 | | ndb_blob_read_batch_bytes | 65536 | | ndb_blob_write_batch_bytes | 65536 | | ndb_cache_check_time | 0 | | ndb_cluster_connection_pool | 1 | | ndb_connectstring | 192.168.56.103 | | ndb_deferred_constraints | 0 | | ndb_distribution | KEYHASH | | ndb_eventbuffer_max_alloc | 0 | | ndb_extra_logging | 1 | | ndb_force_send | ON | | ndb_index_stat_enable | ON | | ndb_index_stat_option | loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0 | | ndb_join_pushdown | ON | | ndb_log_apply_status | OFF | | ndb_log_bin | ON | | ndb_log_binlog_index | ON | | ndb_log_empty_epochs | OFF | | ndb_log_orig | OFF | | ndb_log_transaction_id | OFF | | ndb_log_update_as_write | ON | | ndb_log_updated_only | ON | | ndb_mgmd_host | 192.168.56.103 | | ndb_nodeid | 0 | | ndb_optimization_delay | 10 | | ndb_optimized_node_selection | 3 | | ndb_recv_thread_activation_threshold | 8 | | ndb_recv_thread_cpu_mask | | | ndb_report_thresh_binlog_epoch_slip | 3 | | ndb_report_thresh_binlog_mem_usage | 10 | | ndb_show_foreign_key_mock_tables | OFF | | ndb_table_no_logging | OFF | | ndb_table_temporary | OFF | | ndb_use_copying_alter_table | OFF | | ndb_use_exact_count | OFF | | ndb_use_transactions | ON | | ndb_version | 459526 | | ndb_version_string | ndb-7.3.6 | | ndb_wait_connected | 30 | | ndb_wait_setup | 30 | | ndbinfo_database | ndbinfo | | ndbinfo_max_bytes | 0 | | ndbinfo_max_rows | 10 | | ndbinfo_offline | OFF | | ndbinfo_show_hidden | OFF | | ndbinfo_table_prefix | ndb$ | | ndbinfo_version | 459526 | +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 48 rows in set (0.00 sec) mysql>
ストレージを指定したテーブル作成とデータのInsert
mysql> CREATE DATABASE Cluster_NDB01;
Query OK, 1 row affected (1.12 sec)
mysql> USE Cluster_NDB01
Database changed
mysql> CREATE TABLE TEST(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndbcluster;
Query OK, 0 rows affected (0.49 sec)
mysql> desc TEST;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| memo | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO TEST(memo) VALUES("MySQL Cluster Install Confirmation");
Query OK, 1 row affected (0.05 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| CLSMySQL01 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from TEST;
+----+------------------------------------+
| id | memo |
+----+------------------------------------+
| 1 | MySQL Cluster Install Confirmation |
+----+------------------------------------+
1 row in set (0.01 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| CLSMySQL02 |
+------------+
1 row in set (0.00 sec)
mysql> select * from TEST;
+----+------------------------------------+
| id | memo |
+----+------------------------------------+
| 1 | MySQL Cluster Install Confirmation |
+----+------------------------------------+
1 row in set (0.01 sec)
mysql>
日本語用にUTF8でDB作成
mysql> CREATE DATABASE Cluster_NDB02 default character set utf8;
Query OK, 1 row affected (0.09 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Cluster_NDB01 |
| Cluster_NDB02 |
| mysql |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> use Cluster_NDB02;
Database changed
mysql> CREATE TABLE T1019(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndbcluster;
Query OK, 0 rows affected (0.39 sec)
mysql> INSERT INTO T1019(memo) VALUES("MySQL Cluster TEST3");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO T1019(memo) VALUES("MySQL Cluster TEST3日本語");
Query OK, 1 row affected (0.01 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| CLSMySQL01 |
+------------+
1 row in set (0.00 sec)
mysql> select * from Cluster_NDB02.T1019;
+----+------------------------------+
| id | memo |
+----+------------------------------+
| 1 | MySQL Cluster TEST3 |
| 2 | MySQL Cluster TEST3日本語 |
+----+------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| CLSMySQL02 |
+------------+
1 row in set (0.00 sec)
mysql> select * from Cluster_NDB02.T1019;
+----+------------------------------+
| id | memo |
+----+------------------------------+
| 1 | MySQL Cluster TEST3 |
| 2 | MySQL Cluster TEST3日本語 |
+----+------------------------------+
2 rows in set (0.00 sec)
mysql>
マネージメントノードにてログの確認
[root@CLSManage01 mysql-cluster]# tail -n 30 /usr/local/mysql/mysql-cluster/ndb_1_cluster.log 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Nodeid 2 allocated for API at 192.168.56.104 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Node 2: mysqld --server-id=0 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Node 4: Node 2 Connected 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Node 5: Node 2 Connected 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Node 5: Node 2: API mysql-5.6.19 ndb-7.3.6 2015-01-03 08:26:47 [MgmtSrvr] INFO -- Node 4: Node 2: API mysql-5.6.19 ndb-7.3.6 2015-01-03 08:27:21 [MgmtSrvr] INFO -- Nodeid 3 allocated for API at 192.168.56.105 2015-01-03 08:27:21 [MgmtSrvr] INFO -- Node 3: mysqld --server-id=0 2015-01-03 08:27:22 [MgmtSrvr] INFO -- Node 4: Node 3 Connected 2015-01-03 08:27:22 [MgmtSrvr] INFO -- Node 5: Node 3 Connected 2015-01-03 08:27:22 [MgmtSrvr] INFO -- Node 4: Node 3: API mysql-5.6.19 ndb-7.3.6 2015-01-03 08:27:22 [MgmtSrvr] INFO -- Node 5: Node 3: API mysql-5.6.19 ndb-7.3.6 2015-01-03 09:25:12 [MgmtSrvr] INFO -- Node 4: Local checkpoint 21 started. Keep GCI = 18848 oldest restorable GCI = 18848 2015-01-03 09:25:17 [MgmtSrvr] INFO -- Node 4: Local checkpoint 21 completed 2015-01-03 10:24:05 [MgmtSrvr] INFO -- Node 4: Local checkpoint 22 started. Keep GCI = 20569 oldest restorable GCI = 18880 2015-01-03 10:24:10 [MgmtSrvr] INFO -- Node 4: Local checkpoint 22 completed 2015-01-03 11:22:54 [MgmtSrvr] INFO -- Node 4: Local checkpoint 23 started. Keep GCI = 22289 oldest restorable GCI = 20986 2015-01-03 11:22:59 [MgmtSrvr] INFO -- Node 4: Local checkpoint 23 completed 2015-01-03 12:21:39 [MgmtSrvr] INFO -- Node 4: Local checkpoint 24 started. Keep GCI = 24010 oldest restorable GCI = 20986 2015-01-03 12:21:44 [MgmtSrvr] INFO -- Node 4: Local checkpoint 24 completed 2015-01-03 13:20:28 [MgmtSrvr] INFO -- Node 4: Local checkpoint 25 started. Keep GCI = 25730 oldest restorable GCI = 20986 2015-01-03 13:20:33 [MgmtSrvr] INFO -- Node 4: Local checkpoint 25 completed 2015-01-03 14:19:19 [MgmtSrvr] INFO -- Node 4: Local checkpoint 26 started. Keep GCI = 27450 oldest restorable GCI = 20986 2015-01-03 14:19:24 [MgmtSrvr] INFO -- Node 4: Local checkpoint 26 completed 2015-01-03 15:18:10 [MgmtSrvr] INFO -- Node 4: Local checkpoint 27 started. Keep GCI = 29171 oldest restorable GCI = 20986 2015-01-03 15:18:16 [MgmtSrvr] INFO -- Node 4: Local checkpoint 27 completed 2015-01-03 16:16:59 [MgmtSrvr] INFO -- Node 4: Local checkpoint 28 started. Keep GCI = 30892 oldest restorable GCI = 20986 2015-01-03 16:17:04 [MgmtSrvr] INFO -- Node 4: Local checkpoint 28 completed 2015-01-03 17:15:48 [MgmtSrvr] INFO -- Node 4: Local checkpoint 29 started. Keep GCI = 32612 oldest restorable GCI = 20986 2015-01-03 17:15:54 [MgmtSrvr] INFO -- Node 4: Local checkpoint 29 completed [root@CLSManage01 mysql-cluster]#
基本BACKUP
ndb_mgm> START BACKUP Waiting for completed, this may take several minutes Node 4: Backup 2 started from node 1 Node 4: Backup 2 started from node 1 completed StartGCP: 43696 StopGCP: 43699 #Records: 2077 #LogRecords: 0 Data: 53752 bytes Log: 0 bytes ndb_mgm>
各データノードにバックアップファイルが作成される。
.Data → クラスタデータ
.ctl → クラスタのメタデータ
.log → クラスタのログファイル
[root@CLSData01 admin]# hostname CLSData01 [root@CLSData01 admin]# ls -l /usr/local/mysql/mysql-cluster/BACKUP/ 合計 0 drwxr-x---. 2 root root 72 10月 22 13:56 BACKUP-1 drwxr-x---. 2 root root 72 1月 3 22:35 BACKUP-2 [root@CLSData01 admin]# [root@CLSData01 admin]# ls -l /usr/local/mysql/mysql-cluster/BACKUP/BACKUP-2/ 合計 60 -rw-r--r--. 1 root root 28096 1月 3 22:35 BACKUP-2-0.4.Data -rw-r--r--. 1 root root 27492 1月 3 22:35 BACKUP-2.4.ctl -rw-r--r--. 1 root root 52 1月 3 22:35 BACKUP-2.4.log [root@CLSData01 admin]# [root@CLSData02 admin]# hostname CLSData02 [root@CLSData02 admin]# ls -l /usr/local/mysql/mysql-cluster/BACKUP/ 合計 0 drwxr-x---. 2 root root 72 10月 22 13:56 BACKUP-1 drwxr-x---. 2 root root 72 1月 3 22:35 BACKUP-2 [root@CLSData02 admin]#
MySQL Cluster全体をシャットダウン
ndb_mgm> shutdown Node 4: Cluster shutdown initiated Node 5: Cluster shutdown initiated Node 5: Node shutdown completed. Node 4: Node shutdown completed. 3 NDB Cluster node(s) have shutdown. Disconnecting to allow management server to shutdown. ndb_mgm>
Memo:
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-configuration.html
———-Data Nodeに以下のみ記載。（他はコメントオフ）
[mysqld]
ndbcluster
[mysql_cluster]
ndb-connectstring=192.168.56.103
【参考】
漢のコンピュータ道
MySQL Cluster 7.3 is now Generally Available – an overview
【その他 確認】
Eventual consistency with MySQL
18.6.11 MySQL Cluster Replication Conflict Resolution