Mysql's cluster Configuration Basic command usage and one operation process record _mysql

1. First understand whether you should use MySQL cluster.

To reduce the pressure on the data center nodes and deal with large amounts of data, MySQL is distributed, and one or more applications correspond to a MySQL database. Make shared data of several MySQL databases common, such as shopping carts, user objects, etc., stored in the data node. Other unshared data is also maintained in the MySQL database itself distributed.


2. The concept of name in cluster MySQL. (As shown above)

1) Sql node (SQL node-corresponding to MySQLd in the above figure): distributed database. Including self data and query center node data.

2) Data node (Data node-ndbd): cluster shared data (in memory).

3) Management server (Management Server – ndb_mgmd): cluster management SQL node, Data node.

3. Configuration

The MySQL-max version, of course, is currently not supported on the MySQL cluster system windonws platform.

Not much to say about installing MySQL, there are a lot of online, concise.

A: 192.168.1.251 – Data node and Management Server.
B: 192.168.1.254 – SQL node.
Of course, you can also make a machine for 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 (here is 192.168.1.251):

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 --initial parameter 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
Shut down the cluster:

shell> ndb_mgm -e shutdown
5. Basic cluster description

1) In a MySQL cluster. Only when the table engine is NDBCLUSTER, it will be clustered. Other non-NDBCLUSTER tables are the same as general MySQL database tables and will not share data. NDBCLUSTER table data is stored in the memory of the Data node server. Or multiple servers, where shared data is stored. Data Node server can group data copy.

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

2) In the sql node server, non-NDBCLUSTER data exists in its own database, and when the table engine is NDBCLUSTER, the data is stored in the Data Node. When querying the NDBCLUSTER table, it will lift data from the Data node cluster.

3) Manager server

Manage SQl node and Data node status.

Attachment: Detailed process recording of MySQL cluster configuration
1. Prepare three Linux servers (three machines are configured 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
Among them, sqltest01 and sqltest02 are mysql node and storage node, and sqltest03 is the management node
-At the same time, turn off the firewall or open the relevant ports, such as 3306, 1186 management node, etc.
[root @ sqltest01 u01] # service iptables status
iptables: Firewall is not running.
If enabled, please 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, then select cluster, and then select linux generic in the selection platform that appears on the webpage! Select the required tar package here, I used mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
After the download is complete, use ftp to transfer to the server, and then decompress on three machines
[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, it includes database files and cluster software
3. Configure the management node (sqltest03)
-Copy the software you just unzipped 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 content 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, valid for every data node, only need to configure one
NoOfReplicas = 1
Several copies of data mirror (backup each other between data nodes)
[tcp default]
According to which port is used for communication between each data node and management node, in the old version of the NDB cluster software configuration, this place is usually configured portnumber = 2202 but the new version of the NDB software does not need to be configured here, and MySQL officials strongly recommend not Configuration
[ndb_mgmd]
The configuration part of the management node (usually only one). Note that NodeId = 1 indicates that the node ID of the management node is 1, if not specified, an error will be reported when starting the cluster
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]
Data node configuration part, there are several data nodes to configure several [ndbd]
hostname = 192.168.1.111
Specify the IP address of the data node
datadir = / u01 / app / mysql / data
Specify the location of the database file on the data node
NodeId = 2
Specify the nodeid number of the data node in the entire cluster (very important)
[mysqld]
SQL node configuration part, there are several SQL nodes, just configure several [mysqld]
At this point, 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
Enter the 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)
You can see there are two nodes, the nodes are not connected
4. Configure mysqld node and storage node (sqltest01, sqltest02)
--Create the corresponding directory
[root @ sqltest01 mysql] # mkdir -p / usr / local / mysql-used to store the files just unzipped, 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 unzipped 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 support-files] # pwd
/ usr / local / mysql / support-files
[root @ sqltest01 support-files] # ls -lrt
total 32
-rw-r--r--. 1 mysql mysql 773 Oct 9 21:46 magic
-rwxr-xr-x. 1 mysql mysql 10880 Oct 9 22:42 mysql.server
-rwxr-xr-x. 1 mysql mysql 894 Oct 9 22:42 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 1061 Oct 9 22:42 mysqld_multi.server
-rw-r--r--. 1 mysql mysql 1126 Oct 9 22:42 my-default.cnf
-rwxr-xr-x. 1 mysql mysql 1153 Oct 9 22:42 binary-configure
[root @ sqltest01 support-files] # cp mysql.server /etc/rc.d/init.d/mysqld
--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 effective
--Configure my.cnf
[root @ sqltest01 support-files] # cp my-default.cnf /etc/my.cnf
And configure my.cnf, the specific configuration is 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) are installed to the corresponding location and can be used
Perform the above 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
The first time you start, you need to add --initial to initialize the data node. The second time you start, you do n’t need this parameter.
View on the management node, you can see that the first node is 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)-indicates that it is connected
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 first time to use the root, and this time to use mysql, you need to configure the / u01 / mysql / data permission, otherwise the report is not readable and writable
Check again on 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 (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 it is connected
Finally, start the second node, 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 at the second node
[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. The cluster stops
To shut down the Cluster, simply enter the following command in the 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 shut down the mysqld service of the SQL node:
[root @ sqltest01 bin] # / usr / local / mysql / bin / mysqladmin -uroot shutdown