Mysqlcluster initial experience _ MySQL

Mysqlcluster first experience bitsCN.com

1. prepare the server
Now we plan to establish a MySQL CLuster system with five nodes, so we need five machines for the following purposes:

Node (purpose) IP address (host name)
Management Node (MGM) 192.168.0.1 (db1)
SQL node 1 (SQL1) 192.168.0.2 (db2)
SQL node 2 (SQL2) 192.168.0.3 (db3)
Data node 1 (NDBD1) 192.168.0.4 (db4)
Data node 2 (NDBD2) 192.168.0.5 (db5)

2. precautions and other
The operating system of each node is Linux. the host name is used in the following description, and IP addresses are no longer used. Because MySQL Cluster uses TCP/IP connection and data transmission between nodes is not encrypted, it is best to run this system only in a separate subnet, and considering the transmission rate, we strongly recommend that you do not use this system across the public network. The required MySQL software should be downloaded in http://dev.mysql.com/downloads beforehand.
In fact, the entire system can run successfully on a separate physical computer. of course, you must set different directories and ports and use them only for testing.
Start the installation phase:
1. Assumptions
The nobody user is used on each node computer to run the Cluster. Therefore, execute the following command to add the relevant user (if the user already exists, it will be skipped and run with the root user ):

Root #/usr/sbin/groupadd nobody
Root #/usr/sbin/useradd nobody-g nobody

Assume that you have downloaded the binary installation package that mysql can use directly and put it under/tmp.
2. install SQL nodes and storage nodes (NDB nodes) (that is, repeat the following steps on four machines)

Root # cd/tmp/
Root # tar zxf mysql-max-5.0.24-linux-i686.tar.gz
Root # mv mysql-max-5.0.24-linux-i686/usr/local/mysql/
Root # cd/usr/local/mysql/
Root #./configure -- prefix =/usr/local/mysql
Root #./script_s/mysql_install_db
Root # chown-R nobody: nobody/usr/local/mysql/

3. configure an SQL node

Root # vi/usr/local/mysql/my. cnf

Enter the following content:

[Mysqld]
Basedir =/usr/local/mysql/
Datadir =/usr/local/mysql/data
User = nobody
Port = 3306
Socket =/tmp/mysql. sock
Ndbcluster
Ndb-connectstring = db1
[MYSQL_CLUSTER]
Ndb-connectstring = db1

4. configure the storage node (NDB node)

Root # vi/usr/local/mysql/my. cnf

Enter the following content:

[Mysqld]
Ndbcluster
Ndb-connectstring = db1
[MYSQL_CLUSTER]
Ndb-connectstring = db1

5. install the management node

Root # cd/tmp/
Root # tar zxf mysql-max-5.0.24-linux-i686.tar.gz
Root # mkdir/usr/local/mysql/
Root # mkdir/usr/local/mysql/data/
Root # mysql-max-5.0.24-linux-i686/bin/cd/
Root # cp ndb_mgm */usr/local/mysql/
Root # chown-R nobody: nobody/usr/local/mysql

6. configuration management node

Root # vi/usr/local/mysql/config. ini

Enter the following content:

[Ndbd default]
NoOfReplicas = 1
[Tcp default]
Portnumber = 3306
# Set a management node server
[NDB_MGMD]
Hostname = db1
# Log storage directory on MGM
Datadir =/usr/local/mysql/data/
# Set storage node server (NDB node)
[NDBD]
Hostname = db4
Datadir =/usr/local/mysql/data/
# Second NDB node
[NDBD]
Hostname = db5
Datadir =/usr/local/mysql/data/
# Setting an SQL node server
[MYSQLD]
Hostname = db2
# Second SQL node
[MYSQLD]
Hostname = db3

Note: the default port of the Cluster management node is 1186, and the default port of the data node is 2202. This ** has been relaxed since MySQL 5.0.3, and the Cluster can automatically allocate ports to data nodes based on idle ports. If your version is earlier than 5.0.22, pay attention to this details.
V. start MySQL Cluster
A reasonable sequence of startup is to first start the management node server, then start the storage node server, and finally start the SQL node server:

On the management node server, run the following command to start the MGM node process:
Root #/usr/local/mysql/ndb_mgmd-f/usr/local/mysql/config. ini

The "-f" or "-- config-file" parameter must be used to tell the location of the ndb_mgm configuration file, which is in the same directory as ndb_mgmd by default.

On each storage node server, if the ndbd process is started for the first time, you must first execute the following command:
Root #/usr/local/mysql/bin/ndbd -- initial

Note that the "-- initial" parameter should be used only when ndbd is started for the first time or when data is backed up/restored or ndbd is restarted after the configuration file changes. This parameter causes the node to delete any files created by an earlier ndbd instance for restoration, including log files for restoration.
If it is not the first time, run the following command:

Root #/usr/local/mysql/bin/ndbd

Finally, run the following command to start the SQL node server:
Root #/usr/local/mysql/bin/mysqld_safe -- defaults-file =/usr/local/mysql/my. cnf &

If everything goes well, that is, there is no error message during the startup process, run the following command on the management node server:

Root #/usr/local/mysql/ndb_mgm
-- NDB Cluster -- Management Client --
Ndb_mgm> SHOW
Connected to Management Server at: localhost: 1186
Cluster Configuration
---------------------
[Ndbd (NDB)] 2 node (s)
Id = 2 @ 192.168.0.4 (Version: 5.0.22 Nodegroup: 0 Master)
Id = 3 @ 192.168.0.5 (Version: 5.0.22 Nodegroup: 0)
[Ndb_mgmd (MGM)] 1 node (s)
Id = 1 @ 192.168.0.1 (Version: 5.0.22)
[Mysqld (SQL)] 1 node (s)
Id = 2 (Version: 5.0.22)
Id = 3 (Version: 5.0.22)

The specific output content may be slightly different, depending on the MySQL version you are using.
Note: If you are using an earlier MySQL version, you may see the SQL node referenced as '[mysqld (API. This is an early usage and has been abandoned.
Now, you should be able to process databases, tables, and data in the MySQL Cluster.
6. create a database table
Compared with MySQL that does not use the Cluster, there is no big difference in the way data is operated in the MySQL Cluster. When performing such operations, remember two points:

Tables must be created with the ENGINE = NDB or ENGINE = NDBCLUSTER option, or changed with the alter table option to use the NDB Cluster Storage ENGINE to copy them within the Cluster. If you use mysqldump output to import tables from an existing database, you can open the SQL script in the text editor and add this option to any table creation statement, alternatively, replace any existing ENGINE (or TYPE) options with one of these options.
Remember that each NDB table must have a primary key. If you do not define a primary key when creating a table, the NDB Cluster Storage Engine automatically generates an implicit primary key. (Note: This implicit key also occupies space, just like any other table index. The problem is not uncommon because there is not enough memory to hold these automatically created keys ).
The following is an example:
On db2, create a data table and insert data:

[Db2 ~] Root # mysql-uroot test
[Db2 ~] MbitsCN.com