Basic deployment tutorial for MySQLCluster cluster _ MySQL

This article mainly introduces the basic deployment tutorial of MySQLCluster. MySqlCluster can make full use of the multi-process of the node server to achieve high availability. For more information, see Mysql Cluster overview

The most significant advantage of MySql Cluster is high availability, high real-time performance, high redundancy, and high scalability.

It allows you to deploy the Cluster of the "in memory" database in a non-shared system. without a shared architecture, the system can use cheap hardware. in addition, since each component has its own memory and disk, there is no single point of failure.

It consists of a group of computers with various processes running on each computer, including mysql servers, NDB cluster data nodes, management service initiation, and specialized data access programs.

All these nodes constitute a complete mysql Cluster System. the data is stored in the storage engine of "NDB storage server", and the table (structure) is saved in "mysql server. the application accesses these data tables through the "mysql server", and the cluster management server manages the "NDB storage server" through the management tool (ndb_mgmd ".

Basic concepts

"NDB" is a "in-memory" storage engine with high availability and good data consistency. the following describes the process of a mysql cluster node. A single computer can have any number of nodes.

Management nodes (MGM): These Nodes manage other nodes in the mysql cluster, such as configuration files and cluster logs, start and stop nodes, and run backups. each node in the cluster retrieves configuration data from the management server and requests the location of the management server. when a new event occurs in the data node, the node transmits the information about the event to the management server, and then writes the information to the cluster log. Because these nodes are responsible for managing the configurations of other nodes, you should first start these nodes before starting other nodes. The MGM node is started with the command "ndb_mgmd ".

Data nodes (NDB): These nodes are used to save cluster data. the number of data nodes is related to the number of copies, which is a multiple of fragments. suppose there are two copies, each of which has two fragments, then there are four data nodes. however, there is no need to set multiple copies. the data node is started with the "ndbd" command.

SQL node: this is the node used to access cluster data. for MYSQL cluster, the client node is a traditional Mysql server that uses the NDB cluster Storage Engine. generally, SQL nodes add "ndbcluster" to "my. use "mysqld" to start cnf

In addition, there can be any number of cluster client processes or programs. they are divided into two types: Standard mysql client and management client.

Standard mysql client: access mysql cluster from existing mysql applications written in php, perl, c, c ++, java, python, ruby, etc.

Management clients: These clients are connected to management servers and provide commands such as starting and stopping nodes, starting and stopping message tracing, displaying node versions and statuses, and starting and stopping backups.

The following is the mysql cluster architecture:

Because Mysql Cluster uses TCP/IP connection and data transmission between nodes is not encrypted, it uses a separate subnet.

Next we will deploy

For convenience, I put the management nodes, data nodes, and SQL nodes on one machine.

Management Node 1 10.1.6.205

Data node 1 10.1.6.203

Data node 2 10.1.6.205

SQL node 1 10.1.6.203

SQL node 2 10.1.6.205

1. install (7.2.6 is installed here)

Download the mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz binary package (including ndb and mysql)

Root@10.1.6.205 :~ # Tar-C/usr/local-xzvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gzroot@10.1.6.205:/usr/local # ln-s/usr/local/mysql-cluster-gpl-7.2.8-linux2.6-i686/usr/local/mysqlroot@10.1.6.205: /usr/local # cd mysqlroot@10.1.6.205:/usr/local/mysql # scripts/mysql_install_db -- user = mysqlroot@10.1.6.205:/usr/local/mysql # chown-R mysql: mysql/usr/local/mysql similarly 10.1.6.203

2. configure SQL nodes and NDB nodes

root@10.1.6.205:/usr/local/mysql# vim /etc/my.cnf [mysqld]basedir=/usr/local/mysql/datadir=/usr/local/mysql/data/user=mysqlport=3306socket=/tmp/mysql.sock ndbclustermax_connect_errors=10000ndb-connectstring=10.1.6.205connect_timeout = 300 [mysql_cluster]ndb-connectstring=10.1.6.205

Similarly, 10.1.6.203
3. configuration management node

Root@10.1.6.205:/usr/local/mysql # vim/opt/cluster/config. ini [ndbd default] NoOfReplicas = 2 DataMemory = 80 M # allocate the memory used by data storage. each ndb occupies IndexMemory = 18 M # allocate the memory used by index storage. each ndb occupies [tcp default] portnumber = 2205 # ndb listening port # set management node [ndb_mgmd] NodeId = 1 hostname = 10.1.6.205datadir =/opt/cluster # directory for storing logs on MGM # set storage node NDB1 [ndbd] NodeId = 2 hostname = 10.1.6.203datadir =/usr/local/mysql/data # set the storage node NDB2 [ndbd] NodeId = 3 hostname = 10.1.6.205datadir =/usr/local/mysql/data # set SQL node 1 [mysqld] NodeId = 4 hostname = 10.1.6.203 # set SQL node 2 [mysqld] NodeId = 5 hostname = 10.1.6.205 [mysqld] # run any ip connection [mysqld]

4. start mysql cluster

1) start the management node server first. 2) start the NDB storage node server. 3) start the SQL node server.

1) execute the process of starting the MGM node

root@10.1.6.205:/usr/local/mysql/bin# /usr/local/mysql/bin/ndb_mgmd -f /opt/cluster/config.iniMySQL Cluster Management Server mysql-5.5.22 ndb-7.2.6

The parameter-f or -- config-file must be used to tell the location of the config. ini file in the ndb_mgm configuration file.
2) on two storage node servers, if the NDB process is started for the first time, you must first execute the following command:

root@10.1.6.205:/usr/local/mysql/bin# /usr/local/mysql/bin/ndbd --initial2013-08-28 23:40:36 [ndbd] INFO  -- Angel connected to '10.1.6.205:1186'2013-08-28 23:40:36 [ndbd] INFO  -- Angel allocated nodeid: 2

Note: The-initial parameter is used only when NDB is started for the first time, or when the backup/recovery or configuration file changes and NDB is restarted. this parameter causes the node to delete any files created by an earlier NDB instance for restoration, including log files for restoration.
If it is not the first time, use the following command

root@10.1.6.205:/usr/local/mysql/bin# /usr/local/mysql/bin/ndbd

3) start the SQL node server

root@10.1.6.203:/usr/local/mysql/bin# /usr/local/mysql/bin/mysqld_safe /etc/my.cnf &

5. view the status of each node

root@10.1.6.205:/usr/local/mysql# /usr/local/mysql/bin/ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showCluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @10.1.6.203 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0, Master)id=3 @10.1.6.205 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s)id=1 @10.1.6.205 (mysql-5.5.22 ndb-7.2.6) [mysqld(API)] 4 node(s)id=4 @10.1.6.203 (mysql-5.5.22 ndb-7.2.6)id=5 @10.1.6.205 (mysql-5.5.22 ndb-7.2.6)id=6 (not connected, accepting connect from any host)id=7 (not connected, accepting connect from any host) ndb_mgm>

6. test

Note: compared with Mysql that does not use the Cluster, the data operation method in the mysql cluster is not much different.

1) the table must be created using the engine = NDB or engine = NDBCLUSTER option.

2) each NDB table must have a primary key. if the user does not define the primary key when creating the table, the NDB Cluster Storage Engine will automatically generate an implicit primary key.

This implicit key also occupies space, just like any other table index. because there is not enough memory to accommodate these automatically created keys, it is easy to see problems.

Create a table on 203 SQL node 1

root@10.1.6.203:/usr/local/mysql/bin# /usr/local/mysql/bin/mysql -uroot -pmysql> use test;mysql> create table dave (num int(10)) engine=ndb;mysql> show create table dave\G;*************************** 1. row ***************************  Table: daveCreate Table: CREATE TABLE `dave` ( `num` int(10) DEFAULT NULL) ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.00 sec) mysql> insert into dave -> values -> (100);Query OK, 1 row affected (0.01 sec)mysql> select * from dave;+------+| num |+------+| 100 |+------+

View the table on 205 SQL node 2

root@10.1.6.205:/usr/local/mysql# /usr/local/mysql/bin/mysql -uroot -pmysql> use testmysql> select * from dave;+------+| num |+------+| 100 |+------+

Test OK

Follow the table

mysql> select * from ndbinfo.memoryusage;+---------+--------------+--------+------------+----------+-------------+| node_id | memory_type | used | used_pages | total | total_pages |+---------+--------------+--------+------------+----------+-------------+|  2 | Data memory | 851968 |   26 | 83886080 |  2560 ||  2 | Index memory | 212992 |   26 | 19136512 |  2336 ||  3 | Data memory | 851968 |   26 | 83886080 |  2560 ||  3 | Index memory | 212992 |   26 | 19136512 |  2336 |

Note: When the usage is full, the access will fail. in this case, you need to adjust the DataMemory and IndexMemory parameters. all configuration files must be adjusted and restarted to take effect.

7. disable cluster

root@10.1.6.205:/usr/local/mysql/bin# /usr/local/mysql/bin/ndb_mgm -e shutdownConnected to Management Server at: 10.1.6.205:11863 NDB Cluster node(s) have shutdown.Disconnecting to allow management server to shutdown.

Disable the SQL node mysqld service.

root@10.1.6.203:/usr/local/mysql/bin# /usr/local/mysql/bin/mysqladmin -uroot -p shutdownEnter password: 130829 02:19:57 mysqld_safe mysqld from pid file /usr/local/mysql/data//debian.pid ended[1]+ Done     /usr/local/mysql/bin/mysqld_safe /etc/my.cnf

The above is the initial deployment of mysql cluster. For more information, see PHP Chinese network (www.php1.cn )!