Oracle 5.0, Reference Manual

Page: 1561 / 3232

MySQL Cluster Example with Tables and Data
1541
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=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.
Note
NDBCLUSTER
in MySQL 5.0 does not support autodiscovery of databases.
(See Section 17.1.5, “Known Limitations of MySQL Cluster” .) This means that,
once the
world
database and its tables have been created on one data node,
you need to issue the
CREATE DATABASE world
statement (beginning with
MySQL 5.0.2, you may use
CREATE SCHEMA world
instead), followed by