MySQL optimization--A detailed description of the cluster building steps

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46833179

1 overview

MySQL Cluster is a highly practical, scalable, high-performance, high-redundancy version of MySQL for distributed computing environments, and its design is designed to meet the toughest application requirements in many industries, which often require a database to run at 99.999% reliability. MySQL cluster allows an "in-memory" DB cluster to be deployed in a system that is not shared, with no shared architecture, the system can use inexpensive hardware, and no special requirements for hardware and software. In addition, because each component has its own memory and disk, there is no single point of failure.

In fact, the MySQL cluster is integrating a memory cluster storage engine called NDB with the standard MySQL server. It contains a set of computers, each running one or more processes, which may include a MySQL server, a data node, a Management server, and a proprietary data access program.

MySQL cluster is able to configure the NDB storage engine with multiple failover and load balancing options, but this is easiest to do on the storage engine at the cluster level. The following is the MySQL cluster structure diagram,

MySQL, from the structure, consists of 3 types of nodes (computers or processes), respectively:

Management node: Used to provide configuration, management, arbitration and other functions to other nodes of the whole cluster. It is theoretically possible to provide services through a single server.

Data node: The core of MySQL cluster, which stores data, logs, and provides various management services for data. When more than 2, the cluster can be guaranteed high availability, when the DB node increases, the processing speed of the cluster becomes slower.

SQL Node (API): Used to access MySQL cluster data and provide external application services. Adding an API node increases the concurrent access speed and overall throughput of the entire cluster, which can be deployed on a Web application server, on a dedicated server, and on the same server as the DB deployment.

2 NDB Engine

MySQL Cluster uses a dedicated memory-based storage engine--NDB engine, which has the advantage of being fast and without disk I/O bottlenecks, but because it is memory-based, the size of the database is limited by the total system memory. If running NDB MySQL server must be large enough memory, such as 4G, 8G, or even 16G. NDB engine is distributed, it can be configured on multiple servers to achieve the reliability and scalability of data, in theory, through the configuration of 2 NDB storage node can realize the redundancy of the entire database cluster and solve single point of failure problem.

2.1 Defects

• Based on memory, the size of the database is limited by the total amount of memory in the cluster

  Based on memory, data may be lost after a power outage, which needs to be verified by testing.

  Multiple nodes through the network to achieve communication and data synchronization, query and other operations, so the whole is affected by the network speed,

  So the speed is slow.

  2.2 Advantages

  • Multiple nodes can be distributed in different geographic locations, so it is also a scheme for implementing distributed databases.

    Extensibility is good, the expansion of the database cluster can be achieved by adding nodes.

    Redundancy is good, there is complete database data on multiple nodes, so any one node outage will not cause service interruption.

    The cost of achieving high availability is low, unlike traditional high-availability scenarios where shared storage devices and dedicated software can be implemented, as long as there is enough memory for NDB.

Second, cluster construction

One of the most streamlined MySQL cluster systems will be built, and all commands in the configuration method are run as root accounts. This MySQL cluster contains a management node, two data nodes, two SQL nodes, each of the five nodes installed on five virtual machines, the name and IP of the virtual machine are as follows:

management node	MYSQL-MGM	192.168.124.141
data node   1	mysql-ndbd-1	192.168.124.142
data node   2	mysql-ndbd-2	192.168.124.143
sql   node 1	mysql-sql-1	192.168.124.144
SQL knot Point 2	Mysql-sql-2	192.168.124.145

First, public configuration

Configure the configuration items here separately on three virtual machines.

1. Install the virtual machine

The virtual machine operating system installs the x86_64 version of CentOS 6.4, uses a NAT network, and also installs the Vmware-tools, which is not detailed here.

2. copy MySQL Cluster

Download the following version of Mysql-cluster:

Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz

Copy the downloaded compressed package to the/root/downloads directory of the virtual machine and run the following command in the shell:

Cd/root/downloads Tar-xvzf mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz MV Mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64/usr/local/mysql

3. Turn off security policy

Close the Iptables firewall (or open the 1186, 3306 port of the firewall) and run the following command in the shell:

Chkconfig--level iptables off

Close SELinux and run the following command in the shell:

Gedit/etc/selinux/config

To change the SELinux entry in config file to disabled, the contents of the modified config file are as follows:

# This file controls the state of the SELinux on the system. # selinux= can take one of these three values: # Enforcing-selinux security policy is enforced. # Permissive-selinux Prints warnings instead of enforcing. # disabled-no SELinux policy is loaded. Selinux=disabled # selinuxtype= can take one of these the values: # targeted-targeted processes is protected, # Mls-multi level Security protection. selinuxtype=targeted

Final reboot System

Second, Configuration Management node (192.168.124.141)

1. Configuring the Config.ini configuration file

Run the following command in the shell:

Mkdir/var/lib/mysql-cluster Cd/var/lib/mysql-cluster Gedit Config.ini

The configuration file Config.ini content is as follows:

[NDBD Default] noofreplicas=2 datamemory=80m indexmemory=18m [NDB_MGMD] Nodeid=1 hostname=192.168.124.141 Datadir=/var/lib/mysql-cluster [NDBD] nodeid=2 hostname=192.168.124.142 Datadir=/usr/local/mysql/data [NDBD] Nodeid=3 hostname=192.168.124.143 Datadir=/usr/local/mysql/data [Mysqld] Nodeid=4 hostname=192.168.124.144 [Mysqld] Nodeid=5 hostname=192.168.124.145

2. Install the Management node

Installs the Management node, does not need the mysqld binary file, only needs the MySQL Cluster Server program (NDB_MGMD) and listens the client program (NDB_MGM). Run the following command in the shell:

cp/usr/local/mysql/bin/ndb_mgm*/usr/local/bin Cd/usr/local/bin chmod +x ndb_mgm*

Third, configuration Data node (192.168.124.142, 192.168.124.143)

1. Adding MySQL groups and users

Run the following command in the shell:

Groupadd MySQL useradd-g MySQL MySQL

2. Configuring the MY.CNF configuration file

Run the following command in the shell:

Gedit/etc/my.cnf

The contents of the configuration file my.cnf are as follows:

[Mysqld] Basedir=/usr/local/mysql Datadir=/usr/local/mysql/data Socket=/usr/local/mysql/sock/mysql.sock User=mysql # Disabling Symbolic-links is recommended to prevent assorted security risks Symbolic-links=0 [Mysqld_safe] Log-error=/var/log/mysqld.log Pid-file=/var/run/mysqld/mysqld.pid [Mysql_cluster] ndb-connectstring=192.168.124.141

3. Create a system database

Run the following command in the shell:

Cd/usr/local/mysql MkDir Sock scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data

4. Set the Data directory

Run the following command in the shell:

Chown-r Root. Chown-r Mysql.mysql/usr/local/mysql/data Chown-r Mysql.mysql/usr/local/mysql/sock Chgrp-r MySQL.

5. Configure the MySQL service

Run the following command in the shell:

CP support-files/mysql.server/etc/rc.d/init.d/ chmod +x/etc/rc.d/init.d/mysql.server Chkconfig--add Mysql.server

iv. Configuring SQL nodes ( 192.168.124.144 , 192.168.124.145)

1. Adding MySQL groups and users

Run the following command in the shell:

Groupadd MySQL useradd-g MySQL MySQL

2. Configuring the MY.CNF configuration file

Run the following command in the shell:

Gedit/etc/my.cnf

The contents of the configuration file my.cnf are as follows:

[Client] Socket=/usr/local/mysql/sock/mysql.sock [Mysqld] Ndbcluster Datadir=/usr/local/mysql/data Socket=/usr/local/mysql/sock/mysql.sock ndb-connectstring=192.168.124.141 [Mysql_cluster] ndb-connectstring=192.168.124.141

3. Create a system database

Run the following command in the shell:

Cd/usr/local/mysql MkDir Sock scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data

4. Set the Data directory

Run the following command in the shell:

Chown-r Root. Chown-r Mysql.mysql/usr/local/mysql/data Chown-r Mysql.mysql/usr/local/mysql/sock Chgrp-r MySQL.

5. Configure the MySQL service

Run the following command in the shell:

CP support-files/mysql.server/etc/rc.d/init.d/ chmod +x/etc/rc.d/init.d/mysql.server Chkconfig--add Mysql.server

Five, cluster environment start

Note The boot order: the first is the management node, then the data node, and finally the SQL node.

1. Start the Management node

Run the following command in the shell:

Ndb_mgmd-f/var/lib/mysql-cluster/config.ini

You can also use NDB_MGM to listen to clients, as follows:

Ndb_mgm

2. Start the Data node

The first time you start, you need to add the--initial parameter for the initialization of the NDB node. You cannot add this parameter during a later boot process, otherwise the NDBD program clears all data files and log files that were previously established for recovery.

/USR/LOCAL/MYSQL/BIN/NDBD--initial

If it is not the first time, execute the following command.

/usr/local/mysql/bin/ndbd

3. Start the SQL node

If the MySQL service is not running, run the following command in the shell:

/usr/local/mysql/bin/mysqld_safe--user=mysql &

4. Start the test

To view the management node, start successfully:

VI. Cluster testing

1. Test A

Now we create the relevant database on one of the SQL nodes and then go to another SQL node to see if the data is synchronized.

Execute on SQL Node 1 (192.168.124.144):

Shell>/usr/local/mysql/bin/mysql-u Root-p Mysql>show databases; Mysql>create database AA; Mysql>use AA; Mysql>create TABLE ctest2 (i INT) engine=ndb; This must specify that the database table engine is NDB, otherwise the synchronization fails Mysql> INSERT into Ctest2 () VALUES (1); Mysql> SELECT * from Ctest2;

And then on SQL Node 2, see if the data is synchronized.

Tested to create data on non-master and can be synced to master

The engine that looks at the table is not the ndb,>show create table name;

2. Test Two

Close a data node, write input on another node, turn on the closed node, and see if the data is synchronized.

First, the Data node 1 is restarted, and then the data is added on Node 2.

operation on SQL Node 2 (192.168.124.145) is as follows:

mysql> CREATE DATABASE BB; mysql> use BB; Mysql> CREATE TABLE ctest3 (i INT) engine=ndb; mysql> use AA; Mysql> INSERT into Ctest2 () VALUES (3333); Mysql> SELECT * from Ctest2;

Data node 1 start, Start Data node 1 service

#/usr/local/mysql/bin/ndbd--initial#service mysqld Start

Then log in to view the data

#/usr/local/mysql/bin/mysql-u Root–p

You can see that the data has been synced, indicating that the data can be synchronized in two directions.

Vii. shutting down the cluster

1. To close the management node and the data node, simply execute it in the Management node (clustermgm--134):

Shell>/usr/local/mysql/bin/ndb_mgm-e shutdown

Show

Connected to Management Server at:localhost:1186 2 NDB Cluster node (s) has shutdown. Disconnecting to allow Management Server to shutdown.

2. Then close the SQL node (135,136) and run it in 2 nodes, respectively:

Shell>/etc/init.d/mysql.server Stop Shutting down MySQL ... success!

Note: To start the cluster again, follow the start step of part five, but do not add the "-initial" parameter when starting the data node.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL optimization--A detailed description of the cluster building steps