The basic commands used for MySQL cluster configuration and the real-time operation process,
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.
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 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 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=mysql141224 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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
