(mysql.info) multi-load-data-queries
Info Catalog
(mysql.info) multi-initial
(mysql.info) multi-computer
(mysql.info) multi-shutdown-restart
15.3.5 Loading Sample Data and Performing Queries
-------------------------------------------------
Working with data in MySQL Cluster is not much different from doing so
in MySQL without Cluster. There are two points to keep in mind:
* For a table to be replicated in the cluster, it must use the `NDB
Cluster' storage engine. To specify this, use the ENGINE=NDB or
ENGINE=NDBCLUSTER table option. You can add this option when
creating the table:
CREATE TABLE TBL_NAME ( ... ) ENGINE=NDBCLUSTER;
Alternatively, for an existing table that uses a different storage
engine, use `ALTER TABLE' to change the table to use `NDB Cluster':
ALTER TABLE TBL_NAME ENGINE=NDBCLUSTER;
* Each `NDB' table _must_ have a primary key. If no primary key is
defined by the user when a table is created, the `NDB Cluster'
storage engine automatically generates a hidden one. (*
This hidden 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' (or `TYPE') 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):
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 will need to make sure that MySQL uses the NDB 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=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 non-cluster 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.
It is important to note that `NDB Cluster' in MySQL 5.0 does not
support autodiscovery of databases. (See
mysql-cluster-limitations.) This means that, once the `world'
database and its tables have been created on one data node, you need to
issue the `CREATE SCHEMA world' statement (beginning with MySQL 5.0.2,
you may use `CREATE SCHEMA world' instead), followed by `FLUSH TABLES'
on each SQL node in the cluster. This will cause the node to recognize
the database and read its table definitions.
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):
shell> 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.0.19
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 default-privileges.
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.
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 |
| Sa~o Paulo | 9968485 |
| Shanghai | 9696300 |
| Jakarta | 9604900 |
+-----------+------------+
5 rows in set (0.34 sec)
mysql> \q
Bye
shell>
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 MGM or storage nodes. This brief example
shows how we might execute the `SELECT' statement just shown by using
PHP 5's `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('192.168.0.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 AB's own Connectors to perform the tasks of data
definition and manipulation just as you would normally with MySQL.
Info Catalog
(mysql.info) multi-initial
(mysql.info) multi-computer
(mysql.info) multi-shutdown-restart
automatically generated byinfo2html