Primary deployment tutorial for MySQL cluster cluster _mysql

Mysql Cluster Overview

The most notable advantages of MYSQL cluster are high availability, high real-time, high redundancy, and strong scalability.

It allows the cluster of an in-memory database to be deployed on a system that is not shared. With no shared architecture, the system is able to use inexpensive hardware. In addition, because each component has its own memory and disk, there is no single point of failure.

It consists of a group of computers, each running a variety of processes, including MySQL server, NDB Cluster data node, management Service, and specialized data access program

All of these nodes constitute a complete MySQL cluster system. The data is stored in the storage engine of the NDB storage server, and the table (structure) is saved in the MySQL server. The application accesses these data tables through the MySQL server, and the cluster Management Server passes through the management tools (ndb_ MGMD) to manage the "NDB storage server."

Basic concepts

"NDB" is an "in-memory" storage engine that features high availability and good data consistency. The following describes the MySQL cluster node, which represents the process. You can have any number of nodes on a single computer.

Management node (MGM): The role of such nodes is to manage other nodes within the MySQL cluster, such as configuration files and cluster logs, to start and stop nodes, to run backups, and so on. Each node in cluster retrieves configuration data from the Management server. and request a way to manage the location of the server. When new events occur within the data node, the node transmits information about such events to the Management Server, and then writes such information to the cluster log. Since such nodes are responsible for managing the configuration of other nodes, you should start these nodes first before starting other nodes. The MGM node is started with the command "NDB_MGMD"

Data nodes (NDB): This type of node is used to hold cluster data. The number of data nodes is related to the number of replicas, which is a multiple of fragments. Assuming there are 2 replicas, each with 2 fragments, then there are 4 data nodes. However, there is no need to set up multiple replicas. Data node is using the command "NDBD" To start.

SQL node: This is the node that is used to access the cluster data. For MySQL cluster, the client node is a traditional MySQL server that uses the NDB cluster storage engine. Typically, SQL nodes add "ndbcluster" to "MY.CNF" Start with "Mysqld" after

In addition, you can have any number of cluster client processes or should be programs. They are divided into two types, the standard MySQL client and the management client.

Standard MySQL client: Ability to access MySQL cluster from existing MySQL applications written in Php,perl,c,c++,java,python,ruby, etc.

Managing clients: This type of client is connected to the Management Server and provides commands for starting and stopping nodes, starting and stopping message tracking, displaying node versions and status, starting and stopping backups, and so on.

The following is a diagram of the MySQL cluster architecture:

Because the MySQL cluster uses TCP/IP connection, and the data transfer between the nodes is not encrypted, the last use of a separate subnet.

To implement the deployment below

For convenience I put the management node, the data node, the SQL node on a 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. Installation (7.2.6 version installed here)

Download mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz binary package (contains Ndb,mysql)

root@10.1.6.205:~# tar-c/usr/local-xzvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
root@10.1.6.205:/usr/ local# ln-s/usr/local/mysql-cluster-gpl-7.2.8-linux2.6-i686/usr/local/mysql
root@10.1.6.205:/usr/local# CD MySQL
root@10.1.6.205:/usr/local/mysql# scripts/mysql_install_db--user=mysql
root@10.1.6.205:/usr/local /mysql# chown-r Mysql:mysql/usr/local/mysql of
empathy 10.1.6.203

2. Configure SQL nodes and storage 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=mysql
port=3306
socket=/tmp/mysql.sock
 
ndbcluster
max_connect_ errors=10000
ndb-connectstring=10.1.6.205
connect_timeout = +
 
[Mysql_cluster]
ndb-connectstring=10.1.6.205

Empathy 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=80m #分配data Storage uses memory per NDB to occupy
indexmemory=18m #分配index storage use of memory per NDB occupy
 
[TCP default]
portnumber=2205 #ndb监听端口
 
#设置管理节点
[NDB_MGMD]
nodeid=1
hostname=10.1.6.205
datadir=/opt/ Cluster #在MGM上保存日志的目录
 
#设置存储节点NDB1
[ndbd]
nodeid=2
hostname=10.1.6.203
datadir=/usr/ Local/mysql/data
 
#设置存储节点NDB2
[ndbd]
nodeid=3
hostname=10.1.6.205
datadir=/usr/local/ Mysql/data
 
#设置SQL节点1
[mysqld]
nodeid=4
hostname=10.1.6.203
 
#设置SQL节点2
[mysqld]
nodeid=5
hostname=10.1.6.205
[mysqld] #运行任意ip连接
[mysqld]

4. Start MySQL Cluster

1 Start the Management node server. 2 Start the NDB storage node server. 3 Start the SQL node server.

1 Execute startup MGM node process

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

The location of the NDB_MGM configuration file Config.ini file must be told with the parameter-F or--config-file.
2 on 2 storage node servers, if this is the first time you start the NDB process, you must first execute the following command:

root@10.1.6.205:/usr/local/mysql/bin#/usr/local/mysql/bin/ndbd--initial
2013-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

Note: Use the-initial parameter only when the NDB is first started, or when the backup/restore or profile changes and the NDB is restarted. Because this parameter causes the node to delete any files that were created by the earlier NDB instance and used for recovery, including the log files for recovery.
If it is not the first time to start, use the command

root@10.1.6.205:/usr/local/mysql/bin#/USR/LOCAL/MYSQL/BIN/NDBD

3) Start SQL node server

root@10.1.6.203:/usr/local/mysql/bin#/USR/LOCAL/MYSQL/BIN/MYSQLD_SAFE/ETC/MY.CNF &

5. View each node situation

root@10.1.6.205:/usr/local/mysql#/USR/LOCAL/MYSQL/BIN/NDB_MGM--
NDB Cluster--Management Client--
NDB_MGM > Show
Cluster 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 to any host)
id=7 (not Connec Ted, accepting connect from the any host)
 
ndb_mgm>

6. Test

Note: There is not much difference in how data is manipulated within MySQL cluster compared to MySQL without using cluster. Note When operating

1) The table must be created with the ENGINE=NDB or Engine=ndbcluster option

2 Each NDB table must have a primary key. If the user does not define a primary key when creating the table, the NDB cluster storage engine automatically generates an implied primary key.

The implied key also takes up space, just like any other table index. Because there is not enough memory to accommodate these automatically created keys, the problem is very easy.

Create a table on 203 SQL Node 1

root@10.1.6.203:/usr/local/mysql/bin#/usr/local/mysql/bin/mysql-uroot-p
mysql> use test;
Mysql> CREATE TABLE dave (num int) engine=ndb;
Mysql> Show CREATE table dave\g;
1. Row ***************************
  table:dave
Create table:create Table ' Dave ' (
 ' num ' int () DEFAULT null< c8/>) Engine=ndbcluster DEFAULT charset=latin1
1 row in Set (0.00 sec)
 
mysql> insert into Dave
 -> Valu Es
 -> (m);
Query OK, 1 row affected (0.01 sec)
mysql> select * from Dave;
+------+
| num |
+------+ |
+------+

Then view the table on 205 SQL Node 2

root@10.1.6.205:/usr/local/mysql#/usr/local/mysql/bin/mysql-uroot-p
mysql> use Test
mysql> SELECT * From Dave;
+------+
| num |
+------+ |
+------+

Test OK

Look at 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: The use of a full fill will not be accessible, you need to adjust the configuration datamemory,indexmemory parameters. Each configuration file needs to be adjusted for reboot to take effect.

7. Close Cluster

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

Then shut down the SQL node mysqld service

root@10.1.6.203:/usr/local/mysql/bin#/usr/local/mysql/bin/mysqladmin-uroot-p shutdown
Enter 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, after which will write haproxy+keepalive dual-machine high availability.