The information in this section applies to NDB Cluster running on both Unix and Windows platforms.
Working with database tables and data in NDB 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 theENGINE=NDBCLUSTER
orENGINE=NDB
option when creating the table:CREATE TABLE tbl_name (col_name column_definitions) ENGINE=NDBCLUSTER;
Alternatively, for an existing table that uses a different storage engine, use
ALTER TABLE
to change the table to useNDBCLUSTER
: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, theNDBCLUSTER
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 NDB Cluster, and you want to export the
City
table:
$> mysqldump --add-drop-table world City > city_table.sql
The resulting city_table.sql
file contains
this table creation statement (and the
INSERT
statements necessary to
import the table data):
DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);
(remaining INSERT statements omitted)
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:
DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);
(remaining INSERT statements omitted)
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=
or
engine_name
ENGINE=
with engine_name
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:
$> 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
198.51.100.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:
$> 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):
$> mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.7.44-ndb-7.6.31
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
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
Section 2.9.4, “Securing the Initial MySQL Account”.
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 21.6.18.2, “NDB 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:
mysql> USE world;
mysql> ALTER TABLE City ENGINE=NDBCLUSTER;
mysql> ALTER TABLE Country ENGINE=NDBCLUSTER;
mysql> ALTER TABLE CountryLanguage ENGINE=NDBCLUSTER;
Selecting a database and running a SELECT query against a table in that database is also accomplished in the usual manner, as is exiting the MySQL Monitor:
mysql> USE world;
mysql> SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5;
+-----------+------------+
| Name | Population |
+-----------+------------+
| Bombay | 10500000 |
| Seoul | 9981619 |
| São Paulo | 9968485 |
| Shanghai | 9696300 |
| Jakarta | 9604900 |
+-----------+------------+
5 rows in set (0.34 sec)
mysql> \q
Bye
$>
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:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<title>SIMPLE mysqli SELECT</title>
</head>
<body>
<?php
# connect to SQL node:
$link = new mysqli('198.51.100.20', 'root', 'root_password', 'world');
# parameters for mysqli constructor are:
# host, user, password, database
if( mysqli_connect_errno() )
die("Connect failed: " . mysqli_connect_error());
$query = "SELECT Name, Population
FROM City
ORDER BY Population DESC
LIMIT 5";
# if no errors...
if( $result = $link->query($query) )
{
?>
<table border="1" width="40%" cellpadding="4" cellspacing ="1">
<tbody>
<tr>
<th width="10%">City</th>
<th>Population</th>
</tr>
<?
# then display the results...
while($row = $result->fetch_object())
printf("<tr>\n <td align=\"center\">%s</td><td>%d</td>\n</tr>\n",
$row->Name, $row->Population);
?>
</tbody
</table>
<?
# ...and verify the number of rows that were retrieved
printf("<p>Affected rows: %d</p>\n", $link->affected_rows);
}
else
# otherwise, tell us what went wrong
echo mysqli_error();
# free the result set and the mysqli connection object
$result->close();
$link->close();
?>
</body>
</html>
We assume that the process running on the Web server can reach the IP address of the SQL node.
In a similar fashion, you can use the MySQL C API, Perl-DBI, Python-mysql, or MySQL Connectors to perform the tasks of data definition and manipulation just as you would normally with MySQL.