Movatterモバイル変換

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:

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

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

The resultingcity_table.sql file contains this table creation statement (and theINSERT 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;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 theNDBCLUSTER storage engine for this table. There are two ways that this can be accomplished. One of these is to modify the table definitionbefore importing it into the Cluster database. Using theCity table as an example, modify theENGINE 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;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 ofENGINE=engine_name withENGINE=NDBCLUSTER. If you do not want to modify the file, you can use the unmodified file to create the tables, and then useALTER TABLE to change their storage engine. The particulars are given later in this section.

Assuming that you have already created a database namedworld on the SQL node of the cluster, you can then use themysql command-line client to readcity_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 address198.51.100.20).

To create a copy of the entireworld database on the SQL node, usemysqldump on the noncluster server to export the database to a file namedworld.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.

RunningSELECT 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 theroot password at theEnter password: prompt):

$> mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1 to server version: 9.5.0-ndb-9.5.0Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql>

We simply use the MySQL server'sroot account and assume that you have followed the standard security precautions for installing a MySQL server, including setting a strongroot password. For more information, seeSection 2.9.4, “Securing the Initial MySQL Account”.

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

If you did not modify theENGINE 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 aSELECT 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> \qBye$>

Applications that use MySQL can employ standard APIs to accessNDB 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 theSELECT statement just shown by using the PHP 5.Xmysqli 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.