Adding Tables

Now that you have your cluster up and running and understand the principles behind memory usage, you can start to import your existing tables into the cluster.

There are two methods for doing this: You can use a SQL CREATE TABLE statement with ENGINE=NDBCLUSTER or you can use a SQL ALTER TABLE statement, also with ENGINE=NDBCLUSTER.

Using ALTER TABLE

The easiest way to move your table across is simply to alter an existing table. The syntax for this is very simple, and you can carry out this process from any SQL node. You can use the command SHOW TABLE STATUS to check what engine a table has:

mysql> SHOW TABLE STATUS FROM dbname LIKE 'tablename';

+-------+------------+---------+------------+------+

| Name | Engine | Version | Row_format | Rows |

+-------+------------+---------+------------+------+

| ctest | MyISAM | 10 | Fixed | 0 |

+-------+------------+---------+------------+------+
1 row in set (0.01 sec)

You also get a lot more statistics, but the interesting one is Engine. To change from any other engine to NDBCLUSTER, you issue the following commands:

mysql> USE test;
mysql> ALTER TABLE tablename ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (3.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

This does not tell you very much, so it is worth checking that it has worked:

mysql> SHOW TABLE STATUS FROM dbname LIKE 'tablename';

+-------+------------+---------+------------+------+

| Name | Engine | Version | Row_format | Rows |

+-------+------------+---------+------------+------+

| ctest | ndbcluster | 10 | Fixed | 0 |

+-------+------------+---------+------------+------+

1 row in set (0.01 sec)

Notice how the engine has changed from MyISAM to ndbcluster. This means that it has worked.

If you want to be 100% sure that this has worked, you should move over to a SQL node on a different physical machine and see if the table has appeared on it. (Remember that you must create the database that the table is in first of all on all SQL nodes.) If it has appeared, you know for certain that your cluster is working and that this table is clustered.

Using CREATE TABLE

You used the second method, the CREATE TABLE command, when you created the ctest table during the MySQL Cluster installation process. To convert a database to NDBCLUSTER, you get a SQL statement of the table structure, as in this example:

CREATE TABLE `test`.`ctest` (
`field1` int( 11 ) NOT NULL default '0',
`field2` char( 11 ) NOT NULL default '0',
`whattodo` enum( 'Y', 'N' ) NOT NULL default 'Y'
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

Now, you simply replace the last line with this:

) ENGINE = NDBCLUSTER DEFAULT CHARSET = latin1;

Then you execute the query at a SQL node. You should get an empty table created in the new format. If you are trying to move a table into NDB, we recommend the following course of action:

1. For each table in the original database, dump the structure so you have a SQL query that will create the table.
2. Change ENGINE = whatever to ENGINE = NDBCLUSTER for all tables within the SQL file.
3. Move the database to dbname-old and create a new database dbname on all SQL nodes.
4. Import your newly modified SQL code so you create the same (empty) tables within the database with ENGINE=NDBCLUSTER.
5. Move the data across by running the following query for each table:

   INSERT INTO `dbname`.`tablename` SELECT * FROM `dbname-old`.`tablename` ;
   

This copies all your data from the old table to the new table.