In this recipe, we will create a simple table in the cluster, and we will both insert and select data on this new table on two nodes in the example cluster created earlier in this chapter.
Note
A MySQL Cluster table is a table of type NDBCLUSTER accessible from your storage nodes and can be created in the normal way—with the only difference being an explicit TYPE in the CREATE TABLE statement.
In older versions of MySQL Cluster, it was necessary to create databases on all nodes. This is no longer the case.
In this example, we will create a very simple table, cluster_test on node1 and insert some data into it:
[root@node1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.34-ndb-7.0.6-cluster-gpl MySQL Cluster Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE cluster_test; Query OK, 1 row affected (0.43 sec) mysql> USE cluster_test; Database changed mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER; Query OK, 0 rows affected (0.84 sec) mysql> INSERT INTO ctest () VALUES (1); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM ctest; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec)
The next step is to select this row from the other SQL node in the cluster (node2), and then insert another row from the second node:
[root@node2 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.34-ndb-7.0.6-cluster-gpl MySQL Cluster Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | cluster_test | | mysql | | test | +--------------------+ 4 rows in set (0.03 sec) mysql> use cluster_test; 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_cluster_test | +------------------------+ | ctest | +------------------------+ 1 row in set (0.04 sec) mysql> SELECT * from ctest; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> INSERT INTO ctest () VALUES (2); Query OK, 1 row affected (0.01 sec)
Finally, we return to the first node and check that the data inserted on node2 is visible.
mysql> SELECT * FROM ctest; +------+ | i | +------+ | 2 | | 1 | +------+ 2 rows in set (0.00 sec) mysql>
Congratulations! The cluster works, and you have created a table in it. You can repeat these tests while powering off the storage nodes and watch the cluster continue to work. With a single-cluster storage node powered off, everything will continue to work (except for the SQL node that was previously running on the powered-off mode, of course!).