MySQL Cluster configuration basic command usage and one operation process record _ MySQL

Source: Internet
Author: User
Tags node server
This article mainly introduces the use of basic commands in the MySQL Cluster configuration process. The example of two node connections is provided in the real-time recording. For more information, see the following. 1. check whether you should use a MySQL Cluster.


Reduce the pressure on the data center nodes and process large data volumes. MySQL is distributed and one or more applications correspond to one MySQL database. Share data with the common data of several MySQL databases, such as shopping cart and user objects, in data nodes. Other non-shared data is maintained in the MySQL database.


2. concept of name in cluster MySQL (for example)

1) SQL node (SQL node -- corresponding to MySQLd): distributed database. Including the data of itself and the data of the query center node.

2) Data node (Data node -- ndbd): the cluster shares Data (in memory ).

3) Management Server (Management Server-ndb_mgmd): cluster Management SQL node and Data node.

3. configuration

MySQL-max version, of course, is not supported on the MySQL Cluster System windonws platform.

I won't talk much about installing MySQL. There are a lot of online information, which is concise and concise.

  • A: 192.168.1.20.- Data node and Management Server.
  • B: 192.168.1.254-SQL node.

Of course, you can also make one machine three at the same time.

A, B my. inf plus:


[MySQLD]
ndbcluster # run NDB engine
ndb-connectstring = 192.168.1.251 # location of MGM node
  
# Options for ndbd process:
[MySQL_CLUSTER]
ndb-connectstring = 192.168.1.251 # location of MGM node
  
A: /var/lib/MySQL-cluster/config.ini
[NDBD DEFAULT]
NoOfReplicas = 1 # Number of replicas
DataMemory = 80M # How much memory to allocate for data storage
IndexMemory = 18M # How much memory to allocate for index storage
         # For DataMemory and IndexMemory, we have used the
         # default values. Since the "world" database takes up
         # only about 500KB, this should be more than enough for
         # this example Cluster setup.
# TCP / IP options:
[TCP DEFAULT]
portnumber = 2202 # This the default; however, you can use any
         # port that is free for all the hosts in cluster
         # Note: It is recommended beginning with MySQL 5.0 that
         # you do not specify the portnumber at all and simply allow
         # the default value to be used instead
# Management process options:
[NDB_MGMD]
hostname = 192.168.1.251 # Hostname or IP address of MGM node
datadir = / var / lib / MySQL-cluster # Directory for MGM node logfiles
# Options for data node "A":
[NDBD]
# (one [NDBD] section per data node)
hostname = 192.168.1.251 # Hostname or IP address
datadir = / usr / local / MySQL / data # Directory for this data node's datafiles
# SQL node options:
[MySQLD]
hostname = 192.168.1.254
# [MySQLD] #This is equivalent to 192.168.1.251



4. start the test

On the Management Server (192.168.1.20 ):


shell>ndb_mgmd -f /var/lib/MySQL-cluster/config.ini 


On the data node server (still 192.168.1.251and more ):


shell>ndbd--initial 


(Add the -- initial parameter for the first time)
On the SQL node server (192.168.1.254 ):


shell>MySQLd & 


View on 251


./ndb_mgm 




-- NDB Cluster -- Management Client -- 
ndb_mgm> show 
Connected to Management Server at: 192.168.1.251:1186 
Cluster Configuration 
--------------------- 
[ndbd(NDB)]  1 node(s) 
id=2  @192.168.1.251 (Version:5.0.22, Nodegroup: 0, Master) 
  
[ndb_mgmd(MGM)] 1 node(s) 
id=1  @192.168.1.251 (Version:5.0.22) 
  
[MySQLd(API)] 1 node(s) 
id=3  @192.168.1.254 (Version:5.0.22) 
ok


Disable cluster:


shell>ndb_mgm -e shutdown 


5. basic cluster description

1) in the MySQL Cluster. A cluster is created only when the table engine is NDBCLUSTER. Other non-NDBCLUSTER tables are the same as MySQL database tables and do not share data. NDBCLUSTER table Data is stored in the memory of the Data node server. The Data Node can be one or more servers, and shared Data is stored between them. The Data Node server can Group Data copies.

For example, is the ID of four Data Node servers. is the group 0. 4 and 5 are group 1. 2, 3 maintain the same data, and 4, 5 maintain the same data. Group 0 and group 1 maintain different data.

2) on the SQL node server, non-NDBCLUSTER Data exists in its own database. when the table engine is NDBCLUSTER, the Data is stored in the Data Node. When querying the NDBCLUSTER table, it extracts Data from the Data node cluster.

3) Manager server

Manage the status of SQL node and Data node.

Appendix: detailed process recording for MySQL Cluster configuration
1. prepare three linux servers (configure the three servers as follows)
-- Hostname configuration
192.168.9.241 sqltest01 (mysqld and storage node)
192.168.9.242 sqltest02 (mysqld and storage node)
192.168.9.243 sqltest03
Sqltest01 and sqltest02 are mysql nodes and storage nodes respectively, and sqltest03 is the management node.
-- Disable the firewall or open related ports, such as 3306 and 1186 of the management node.


[root@sqltest01 u01]# service iptables status



iptables: Firewall is not running.


If yes, use service iptables stop
-- Create corresponding users and directories


[root@sqltest01 u01]# groupadd mysql
[root@sqltest01 u01]# useradd -r -g mysql mysql
[root@sqltest01 u01]# mkdir -p /usr/local/mysql
[root@sqltest01 u01]# chown -R mysql.mysql


2. mysql cluster
Download URL: dev.mysql.com, select cluster, and select linux generic from the platform that appears on the page! Bytes
After the download is complete, use ftp to transfer it to the server and decompress the package on the three servers.


[root@sqltest01 u01]# tar -zxvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz



mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_plugin_auth.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/kill_query.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/unsafe_binlog.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_multi_ndb.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/ipv6_clients.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/setup_fake_relay_log.inc
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/wait_for_slave_sql_error_and_skip.inc
......................................................................................................


After decompression, the database files and cluster software are included.
3. configuration management node (sqltest03)
-- Copy the extracted software to the specified location/usr/local/mysql


[root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd
/u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64
[root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/


-- Create a cluster Directory


[root@sqltest03 u01]# cd /usr/local/mysql
[root@sqltest03 mysql]# mkdir mysql-cluster
[root@sqltest03 mysql]# pwd
/usr/local/mysql
[root@sqltest03 mysql]# cp bin/ndb_mgm* /usr/local/bin/
[root@sqltest03 mysql]# cd /var/lib
[root@sqltest03 mysql]# mkdir mysql-cluster
[root@sqltest03 mysql]# cd mysql-cluster
[root@sqltest03 mysql]# vi config.ini


The configuration is as follows:


[root@sqltest03 mysql-cluster]# cat config.ini 
[ndbd default]
NoOfReplicas=1
DataMemory=2048M
IndexMemory=512M
[tcp default]
[ndb_mgmd]
hostname=192.168.9.243
datadir=/var/lib/mysql-cluster
NodeId=1
[ndbd]
hostname=192.168.9.241
datadir=/u01/mysql/data
NodeId=2
[ndbd]
hostname=192.168.9.242
datadir=/u01/mysql/data
NodeId=3
[mysqld]
hostname=192.168.9.241
NodeId=4
[mysqld]
hostname=192.168.9.242
NodeId=5


Configuration instructions:
[Ndbd default]
This part is a public part and is valid for each data node. you only need to configure one copy.
NoOfReplicas = 1
Several data images (each data node backs up each other)
[Tcp default]
Which port is used for communication between each data node and the management node, portnumber = 2202 is usually configured in this place, but the new NDB software does not need to be configured here, and MySQL officially strongly recommends that you do not configure
[Ndb_mgmd]
The configuration section of the management node (usually only one ). Note: NodeId = 1 indicates that the node ID of the management node is 1. if this parameter is not specified, an error is returned when the cluster is started.
Hostname = 192.168.9.243
Specify the IP address of the management node
Datadir =/var/lib/mysql-cluster
Specify the location where cluster management logs are stored
[Ndbd]
In the data node configuration section, several [ndbd] data nodes are configured.
Hostname = 192.168.1.111
Specify the IP address of the data node
Datadir =/u01/app/mysql/data
Specifies the location where database files are stored on the data node.
NodeId = 2
Specifies the nodeid of the data node in the entire cluster (important)
[Mysqld]
In the SQL node configuration section, you can configure several [mysqld] SQL nodes.
Now you can start the cluster.


[root@sqltest03 bin]# pwd
/usr/local/bin
[root@sqltest03 bin]# ./ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.21 ndb-7.3.7


Go to execution view


[root@sqltest03 bin]# 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 (not connected, accepting connect from 192.168.9.241)
id=3 (not connected, accepting connect from 192.168.9.242)
[ndb_mgmd(MGM)] 1 node(s)
id=1  @192.168.9.243 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)]  2 node(s)
id=4 (not connected, accepting connect from 192.168.9.241)
id=5 (not connected, accepting connect from 192.168.9.242)


We can see that there are two nodes and the nodes are not connected.
4. configure the mysqld node and storage node (sqltest01, sqltest02)
-- Create a directory


[root @ sqltest01 mysql] # mkdir -p / usr / local / mysql-used to store the files that were just decompressed, such as the bin directory of mysql, etc.
[root @ sqltest01 mysql] # mkdir -p / u01 / mysql / data-used to store data files (innodb)
[root @ sqltest01 mysql] # chown -R mysql.mysql / u01


-- Copy the previously decompressed file


[root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd
/u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64
[root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/
[root@sqltest01 mysql]# chown -R mysql.mysql /usr/local/mysql/


-- Copy mysql. server


[root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd
/u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64
[root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/
[root@sqltest01 mysql]# chown -R mysql.mysql /usr/local/mysql/


-- Edit environment variables


[root@sqltest01 tmp]# vi /etc/profile


Add as follows:


PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH



export PATH



[root@sqltest01 tmp]# source /etc/profile 


-- Make the modification take effect
-- Configure my. cnf


[root@sqltest01 support-files]# cp my-default.cnf /etc/my.cnf


Configure my. cnf as follows:


[mysqld]
ndbcluster
basedir=/usr/local/mysql
datadir=/u01/mysql/data
port=3306
[mysql_cluster]
ndb-connectstring=192.168.9.243


-- Initialize the node database


[root@sqltest01 mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/u01/mysql/data


After executing this command, the database data files (including mysql, test, performance_schema, and other databases) will be installed to the corresponding location.
Perform the preceding steps on both nodes.
5. start at two points


[root@sqltest01 mysql]# ndbd --initial



2014-12-24 17:55:57 [ndbd] INFO   -- Angel connected to '192.168.9.243:1186'2014-12-24 17:55:57 [ndbd] INFO   -- Angel allocated nodeid: 2


During the first startup, -- initial is required to initialize the data node. during the second startup, this parameter is not required.
In the management node view, you can see that the first node has been connected


ndb_mgm> show



Cluster Configuration
---------------------
[ndbd(NDB)]   2 node(s)
id=2  @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, starting, Nodegroup: 0)  --表明已经连接上了
id=3 (not connected, accepting connect from 192.168.9.242)
[ndb_mgmd(MGM)] 1 node(s)
id=1  @192.168.9.243 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)]  2 node(s)
id=4 (not connected, accepting connect from 192.168.9.241)
id=5 (not connected, accepting connect from 192.168.9.242)


Start mysqld


[root@sqltest01 mysql]# cd /usr/local/mysql/bin[root@sqltest01 bin]# ./mysqld_safe --user=mysql



141224 17:59:50 mysqld_safe Logging to '/u01/mysql/data/sqltest01.err'.141224 17:59:51 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data


When starting the database, the root used for the first initialization, and this time using mysql, you need to configure the/u01/mysql/data permission, otherwise it is reported that the database cannot be read or written.
View on the management node again


ndb_mgm> show



Cluster Configuration
---------------------
[ndbd(NDB)]   2 node(s)
id=2  @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *)
id=3 (not connected, accepting connect from 192.168.9.242)
[ndb_mgmd(MGM)] 1 node(s)
id=1  @192.168.9.243 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)]  2 node(s)
id=4  @192.168.9.241 (mysql-5.6.21 ndb-7.3.7)   


-- Indicates that the connection has been established.
Finally, start the second node and check again from the management node.


ndb_mgm> show



Cluster Configuration
---------------------
[ndbd(NDB)]   2 node(s)
id=2  @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *)
id=3  @192.168.9.242 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=1  @192.168.9.243 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)]  2 node(s)
id=4  @192.168.9.241 (mysql-5.6.21 ndb-7.3.7)
id=5  @192.168.9.242 (mysql-5.6.21 ndb-7.3.7)


6. test on two nodes


[root@sqltest01 ~]# mysql -uroot -p



Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database mydb1;
Query OK, 1 row affected (0.07 sec)
mysql> use mydb1;
Database changed
mysql> create table mytb1(id int,birthdate datetime,pername char(10)) engine=ndbcluster;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into mytb1(id,birthdate,pername) values(1,'2013-01-23 09:45:10','pengzitong');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mytb1(id,birthdate,pername) values(2,'2007-07-09 09:45:10','pengzixin');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Check in section 2


[root@sqltest02 ~]# mysql -uroot -p



Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mydb1
mysql> select * from mytb1;
+------+---------------------+------------+
| id  | birthdate      | pername  |
+------+---------------------+------------+
|  1 | 2013-01-23 09:45:10 | pengzitong |
|  2 | 2007-07-09 09:45:10 | pengzixin |
+------+---------------------+------------+


7. stop the cluster
To disable Cluster, you can simply enter the following command in Shell on the machine where the MGM node is located:


[root@sqltest03 bin]# /usr/local/mysql/ndb_mgm -e shutdown


Run the following command to disable the mysqld service of the SQL node:


[root@sqltest01 bin]# /usr/local/mysql/bin/mysqladmin -uroot shutdown
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.