MySQL - Cluster MySQL cluster-Mysql Tutorial-php.cn

MySQL Cluster MySQL Cluster

Basic concept:

"NDB" is a " "In-memory" storage engine, which has the characteristics of high availability and good data consistency.

MySQL Cluster can configure the NDB storage engine with a variety of failover and load balancing options, but it is easiest to do this on the storage engine at the Cluster level. MySQL Cluster's NDB storage engine contains a complete data set and only depends on other data within the cluster itself.

Management (MGM) node: The role of this type of node is to manage other nodes in MySQL Cluster, such as providing configuration data, starting and stopping nodes, running backups, etc. Because these nodes manage the configuration of other nodes, they should be started before other nodes. The MGM node is started with the command "ndb_mgmd".

Data node: This type of node is used to save Cluster data. The number of data nodes is related to the number of replicas, which is a multiple of the fragments. For example, with two replicas, each with two fragments, there are 4 data nodes. There is no need to set up multiple copies though. Data nodes are started with the command "ndbd".

SQL node: This is the node used to access Cluster data. For MySQL Cluster, the client node is a traditional MySQL server using the NDB Cluster storage engine. Usually, the SQL node is started using the command "mysqld -ndbcluster", or using "mysqld" after adding "ndbcluster" to "my.cnf".

The management server (MGM node) is responsible for managing Cluster configuration files and Cluster logs. Each node in the cluster retrieves configuration data from the management server and requests a way to determine where the management server is located. When new events occur within a data node, the node transmits information about such events to the management server, and then writes such information to the Cluster log.

Cluster configuration overview:

Installed version: mysql cluster 7.3.2

Operating system: centos6.3 (X64)

Software name: mysql- cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz (general version)

Management node IP:192.168.0.202

Data node-SQL node IP:192.168 .0.203

Data node-SQL node IP:192.168.0.204

Install dependency packages: yum install -y glibc perl libaio-devel

x32-bit system needs to install the compatibility library Group: yum groupinstall “Compatibility libraries”

1. Management node installation configuration

1. Install mysql-cluster

groupadd mysql useradd -g mysql -s /sbin/nologin mysql tar -zxvf mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64. tar .gz mv mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 /usr/local/mysql-cluster chown -R root.mysql /usr/local/mysql/ chown -R mysql.mysql /usr/local/mysql/data/ /usr/local/mysql/scripts/mysql_install_db --basedir= /usr/local/mysql --datadir= /usr/local/mysql/data --user=mysql & #初始化数据库 cp -rf /usr/local/mysql/bin/ndb_mgm * /usr/local/bin/ #复制ndb节点管理命令到本地，方便使用

Copy after login

2. Modify the mysql master Configuration file

vi /etc/my .cnf [MYSQLD] user = mysql socket = /tmp/mysql .sock basedir = /usr/local/mysql #安装目录 datadir = /usr/local/mysql/data #数据库存放目录 character-sets-server=UTF8 ndbcluster #运行NDB存储引擎 ndb-connectstring=192.168.0.202 lower_case_table_names=1 #表名是否区分大小写1为不区分，不然linux下表名是区分大小写的 [MYSQL_CLUSTER] ndb-connectstring=192.168.0.202 #Mysql Cluster管理节点IP

Copy after login

3. Create mysql cluster configuration file

mkdir /var/lib/mysql-cluster vi /var/lib/mysql-cluster/config .ini [ndbd default] NoOfReplicas=2 #定义在Cluster环境中相同数据的份数，最大为4 DataMemory=256M #分配的数据内存大小，根据本机服务器内存适量来分配 IndexMemory=256M #设定用于存放索引（非主键）数据的内存段大小 #一个NDB节点能存放的数据量是会受到DataMemory和IndexMemory两个参数设置的约束，两者任何一个达到限制数量后，都无法再增加能存储的数据量。如果继续存入数据系统会报错“table is full”。 [ndb_mgmd] nodeid=1 hostname =192.168.0.202 datadir= /var/lib/mysql-cluster/ [ndbd] nodeid=2 hostname =192.168.0.203 datadir= /usr/local/mysql/data [ndbd] nodeid=3 hostname =192.168.0.204 datadir= /usr/local/mysql/data [mysqld] nodeid=4 hostname =192.168.0.203 [mysqld] nodeid=5 hostname =192.168.0.204

Copy after login

Save and exit!

chown mysql.mysql /var/lib/mysql-cluster/config .ini

Copy after login

2. The two data nodes and SQL nodes have the same configuration

1. Install mysql-cluster

tar -zxvf mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64. tar .gz mv mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 /usr/local/mysql groupadd mysql useradd -g mysql -s /sbin/nologin mysql chown -R root.mysql /usr/local/mysql chown -R mysql.mysql /usr/local/mysql/data /usr/local/mysql/scripts/mysql_install_db --basedir= /usr/local/mysql --datadir= /usr/local/mysql/data --user=mysql & #初始化数据库 cp /usr/local/mysql/support-files/mysql .server /etc/init .d /mysqld cp /usr/local/mysql/support-files/medium .cnf /etc/my .cnf chmod +x /etc/init .d /mysqld

Copy after login

2. Modify the mysql configuration file

vi /etc/my .cnf [MYSQLD] user=mysql character_set_server=utf8 ndbcluster ndb-connectstring=192.168.0.202 default-storage-engine=ndbcluster #设置默认是NDB存储引擎 datadir= /usr/local/mysql/data basedir= /usr/local/mysql [MYSQL_CLUSTER] ndb-connectstring=192.168.0.202 #mysql cluster 管理节点IP

Copy after login

3. Test (first turn off the firewall (IPTABLES) and Selinux of the three servers)

1. Start the management node

ndb_mgmd -f /var/lib/mysql-cluster/config .ini --initial netstat -tuplna | grep 1186 #默认连接端口1186，启动成功 tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 1369 /ndb_mgmd #--initial：第一次启动时加上，其它时候不要加，不然会数据清空，除非是在备份、恢复或配置变化后重启时。

Copy after login

If the startup appears Error: Just delete all the nodeids set in config.ini!

2. Start two data nodes and SQL nodes

Data node:/usr/local/mysql/bin/ndbd --initial

SQL node: bin/mysqld_safe --user=mysql & or service mysqld start

3. Check the cluster status

ndb_mgm -e show #If the management node and data node are displayed, the configuration is successful

4. Create a database to verify synchronization

mysql -u root -p

Mysql>create database test ;

Mysql>use test ;

Mysql>create table abc (id int) engine=ndbcluster; #Specify the engine of the database table as NDB, otherwise the synchronization fails

Mysql>Insert into abc ()values (1);

Mysql> select * from abc;

#At this time, check whether the two data are consistent. If they are consistent, the cluster has been successful!

Notes:

1. When creating a table, be sure to use ENGINE=NDB or ENGINE=NDBCLUSTER to specify the NDB cluster storage engine, or use the ALTER TABLE option to change the storage engine of the table.

2. The NDB table must have a primary key, so the primary key must be defined when creating the table, otherwise the NDB storage engine will automatically generate an implicit primary key.

3. The user permission table of the Sql node is still saved by the MYISAM storage engine, so the MySql user created in a Sql node can only access this node. If you want to use the same user to access other Sql nodes, you need to Add users to the corresponding Sql node.

4. Management and maintenance commands

Close the mysql cluster: ndb_mgm -e shutdown

Restart the mysql cluster: ndb_mgmd -f /var/lib/ mysql-cluster/config.ini

Restart the data node:/usr/local/mysql/bin/ndbd

Start the SQL node:/usr/local/mysql/bin/mysqld_safe --user =mysql & or service mysqld restart

View mysql status: ndb_mgm -e show

Startup sequence:

Management node-> Data node-> SQL node

Close sequence:

SQL node-> Data node-> Management node