4.5 MySQL Cluster Example with Tables and Data

The information in this section applies to MySQL Cluster running
on both Unix and Windows platforms.

Working with database tables and data in MySQL Cluster is not much
different from doing so in standard MySQL. There are two key
points to keep in mind:

For a table to be replicated in the cluster, it must use the
NDBCLUSTER storage engine. To
specify this, use the ENGINE=NDBCLUSTER or
ENGINE=NDB option when creating the table:

CREATE TABLE tbl_name (col_namecolumn_definitions) ENGINE=NDBCLUSTER;

Alternatively, for an existing table that uses a different
storage engine, use ALTER TABLE
to change the table to use
NDBCLUSTER:

ALTER TABLE tbl_name ENGINE=NDBCLUSTER;

Every NDBCLUSTER table has a
primary key. If no primary key is defined by the user when a
table is created, the NDBCLUSTER
storage engine automatically generates a hidden one. Such a
key takes up space just as does any other table index. (It is
not uncommon to encounter problems due to insufficient memory
for accommodating these automatically created indexes.)

If you are importing tables from an existing database using the
output of mysqldump, you can open the SQL
script in a text editor and add the ENGINE
option to any table creation statements, or replace any existing
ENGINE options. Suppose that you have the
world sample database on another MySQL server
that does not support MySQL Cluster, and you want to export the
City table:

shell> mysqldump --add-drop-table world City > city_table.sql

The resulting city_table.sql file will
contain this table creation statement (and the
INSERT statements necessary to
import the table data):

You need to make sure that MySQL uses the
NDBCLUSTER storage engine for this
table. There are two ways that this can be accomplished. One of
these is to modify the table definition
before importing it into the Cluster
database. Using the City table as an example,
modify the ENGINE option of the definition as
follows:

This must be done for the definition of each table that is to be
part of the clustered database. The easiest way to accomplish this
is to do a search-and-replace on the file that contains the
definitions and replace all instances of
TYPE=engine_name or
ENGINE=engine_name
with ENGINE=NDBCLUSTER. If you do not want to
modify the file, you can use the unmodified file to create the
tables, and then use ALTER TABLE to
change their storage engine. The particulars are given later in
this section.

Assuming that you have already created a database named
world on the SQL node of the cluster, you can
then use the mysql command-line client to read
city_table.sql, and create and populate the
corresponding table in the usual manner:

shell> mysql world < city_table.sql

It is very important to keep in mind that the preceding command
must be executed on the host where the SQL node is running (in
this case, on the machine with the IP address
192.168.0.20).

To create a copy of the entire world database
on the SQL node, use mysqldump on the
noncluster server to export the database to a file named
world.sql; for example, in the
/tmp directory. Then modify the table
definitions as just described and import the file into the SQL
node of the cluster like this:

shell> mysql world < /tmp/world.sql

If you save the file to a different location, adjust the preceding
instructions accordingly.

Running SELECT queries on the SQL
node is no different from running them on any other instance of a
MySQL server. To run queries from the command line, you first need
to log in to the MySQL Monitor in the usual way (specify the
root password at the Enter
password: prompt):

We simply use the MySQL server's root
account and assume that you have followed the standard security
precautions for installing a MySQL server, including setting a
strong root password. For more information, see
Securing the Initial MySQL Accounts.

It is worth taking into account that Cluster nodes do
not make use of the MySQL privilege system
when accessing one another. Setting or changing MySQL user
accounts (including the root account) effects
only applications that access the SQL node, not interaction
between nodes. See
Section 7.11.2, “MySQL Cluster and MySQL Privileges”, for
more information.

If you did not modify the ENGINE clauses in the
table definitions prior to importing the SQL script, you should
run the following statements at this point:

Applications that use MySQL can employ standard APIs to access
NDB tables. It is important to
remember that your application must access the SQL node, and not
the management or data nodes. This brief example shows how we
might execute the SELECT statement
just shown by using the PHP 5.X mysqli
extension running on a Web server elsewhere on the network: