Shulou(Shulou.com)05/31 Report--

This article introduces how to use cluster in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Prepare the server

Now, we plan to build a MySQL CLuster system with five nodes, so we need to use five machines for the following purposes:

Node (purpose) IP address (hostname)

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. Matters needing attention and others

The operating system of each node is Linux, and the hostname will be used in the following description instead of the IP address. Because MySQL Cluster is connected by TCP/IP, and the data transmission between nodes is not encrypted, it is best to run in a separate subnet, and considering the transmission rate, it is strongly recommended not to use this system across the public network. The required MySQL software is available in advance.

In fact, the whole system can run successfully on a single physical computer, of course, different directories and ports must be set, and can only be used for testing.

Start the installation phase:

1. Assumptions

The nobody user is used to run Cluster on each node computer, so execute the following command to add the relevant users (skip if they already exist, and execute with the root user):

Root# / usr/sbin/groupadd nobody

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

Suppose you have downloaded the binary installation package that mysql can use directly, and put it under / tmp.

2. SQL node and storage node (NDB node) installation (that is, 4 machines repeat the following steps)

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 the SQL node

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

Then enter the following:

[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

Then enter the following:

[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# cd mysql-max-5.0.24-linux-i686/bin/

Root# cp ndb_mgm* / usr/local/mysql/

Root# chown-R nobody:nobody / usr/local/mysql

6. Configure the management node

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

Then enter the following:

[NDBD DEFAULT]

NoOfReplicas=1

[TCP DEFAULT]

Portnumber=3306

# set the management node server

[NDB_MGMD]

Hostname=db1

# directory where logs are saved 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/

# set SQL node server

[MYSQLD]

Hostname=db2

# second SQL node

[MYSQLD]

Hostname=db3

Note: the default port for the Cluster management node is 1186 and the default port for the data node is 2202. Since MySQL 5.0.3, the * * has been relaxed, and Cluster can automatically assign ports to data nodes based on idle ports. If your version is less than 5.0.22, please pay attention to this detail.

5. Start MySQL Cluster

A reasonable startup sequence is to start the management node server first, then the storage node server, and finally the SQL node server:

On the management node server, execute the following command to start the MGM node process:

Root# / usr/local/mysql/ndb_mgmd-f / usr/local/mysql/config.ini

You must tell ndb_mgm the location of the configuration file with the parameter "- f" or "--config-file". By default, it is in the same directory as ndb_mgmd.

"on each storage node server, if you start the ndbd process 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 ndbd is restarted after backup / recovery data or configuration file changes. Because this parameter causes the node to delete any files created by earlier ndbd instances for recovery, including log files for recovery.

If it is not the first time to start, simply 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 all goes well, that is, without any error messages during startup, 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 may vary slightly, depending on the version of MySQL you are using.

Note: if you are using an earlier version of MySQL, you may see the SQL node referenced as'[mysqld (API)]'. This is an early usage that has been abandoned.

You should now be able to process tables and data in MySQL Cluster.

Create database tables

Compared with MySQL that does not use Cluster, there is not much difference in the way data is manipulated within MySQL Cluster. There are two things to keep in mind when performing such operations:

Tables must be created with the ENGINE=NDB or ENGINE=NDBCLUSTER option, or changed with the ALTER TABLE option to replicate them within Cluster using the NDB Cluster storage engine. If you use the output of dump to import tables from an existing database, you can open the SQL script in a text editor and add this option to any table creation statement, or replace any existing ENGINE (or TYPE) options with one of these options.

Also remember that each NDB table must have a primary key. If the user does not define the primary key when the table is created, the NDB Cluster storage engine automatically generates the implied primary key. Note: this implicit key will also take up space, just like any other table index. Problems are not uncommon because there is not enough memory to hold these automatically created keys.

Here is an example:

On db2, create a data table and insert the data:

[db2~] root# mysql-uroot test

[db2~] m

On how to use cluster in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.