Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

The English version of this article is http://www.mrxuri.com/index.php/2013/11/20/install-mysql-cluster-on-ubuntu-12-04-lts.html

Www.oracle.com

Virtual Machine Group platform VMware ESXi. LTS (Precise Pangolin)-bit

 /usr/src/mysql-/usr/src/mysql-mgm

After completing this step, download the latest installation source code from the official MySQL website and decompress the software package.

 http: xvfz mysql-cluster-gpl-.-linux-glibc2.-x86_64..gz

Enter the decompressed folder and move the binary file.

cd mysql-cluster-gpl-.-linux-glibc2.- bin/ndb_mgm /usr/ bin/ndb_mgmd /usr/bin

Change the directory permission and delete the downloaded source file.

  /usr/bin/ndb_mg*/usr/ -rf /usr/src/mysql-mgm

Next, create a management node configuration file named config. ini in the/var/lib/mysql-cluster/folder.

 /var/lib/mysql-cluster

After the config. ini file is created, use your favorite text editor to edit the file. The content is similar

====/var/lib/mysql-==.==.=.= /var/lib/mysql-=.=/var/lib/mysql-

All Hosts are defined here, even if we only install the first one. Note that NodeId must be set for managing host nodes, but not for NDBD nodes.
After completing this step, you can use the following command to start the management Node

ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/

After completing this step, you can use the following command to add an entry to init. d to automatically start the program.

  > /etc/init.d/  /etc/init.d/ndb_mgmd

If everything goes well, the second management node follows the same steps and uses the same configuration. Do not change the ID in the node configuration file

You can use the ndb_mgm command to verify the running of the Management node (you only need to enter ndb_mgm in the terminal), and start the Configuration Utility by typing show. At this time, the NDBD node and MySQL node are in the disconnected state. You need to complete the configuration of all nodes to output the correct state.

3. Data Nodes
The process of creating a data node is similar to that of creating a management node. Let's start to create a mysql group and add the user mysql to the mysql group.

-g mysql mysql

Go to/usr/local, download the same compressed file used by the Configuration Management node, and decompress

cd /usr/local/ http: xvfz mysql-cluster-gpl-.-linux-glibc2.-x86_64..gz

Create a folder named mysql pointing to the decompressed folder (this will be used in the DB cluster later, so do not delete it !) . After creation, you can install the database

 -s mysql-cluster-gpl-.-linux-glibc2.--get  libaio1 libaio-/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

Modify file permissions

chown - -R mysql data

As on the Management node, we want the DataBase engine to be automatically started. Therefore, we need to create the init. d command

 support-files/mysql.server /etc/init.d/  /etc/init.d/mysql.server

Finally, copy the bin folder to the/usr/bin location and create a symbolic link to ensure correct reference.

cd /usr/local/mysql/ * /usr// -fr /usr/local/mysql/ -s /usr/bin /usr/local/mysql/bin

The MySQL configuration file does not exist currently, so we need to create it ourselves. The file is located in/etc/and named my. cnf. Use your favorite text editor and add the following lines

-connectstring=.,.-connectstring=.,.

Note that the addresses of these two management nodes are separated by commas. If you only have one management node, you only need to delete the second one from the list. Once the my. cnf file has been saved, we need to create a MySQL DATA folder

 /var/lib/mysql-cluster

After completing these steps, we need to initialize the cluster and start the service. Initialization is only required when you start the first node or when the/var/lib/mysql-cluster/config. ini file on the Management node is changed.

cd /var/lib/mysql--/etc/init.d/mysql.server start

Next, run the corresponding script to install MySQL

/usr/local/mysql/bin/mysql_secure_installation

Finally, we need NDB to start automatically

  > /etc/init.d/  /etc/init.d/ndbd

Now, we have completed the configuration of the first data node and completed the configuration of the second data node according to the same method and steps.

4. Verification and Testing

If everything is normal, execute the command ndb_mgm in the management node terminal, and then type show. The prompt that the database node has been filled should be displayed at this time.

root@MYSQL-MGM1:~>---------------------=    @.  (mysql-. ndb-., Nodegroup: , *=    @.  (mysql-. ndb-., Nodegroup: =    @.  (mysql-. ndb-.=    @.  (mysql-. ndb-.=    @.  (mysql-. ndb-.=    @.  (mysql-. ndb-.)

If you can see similar output, try some basic SQL commands. Log on to the SQL database and create a new database and table to verify data synchronization. Note that you must use the NDBCLUSTER storage engine when creating a database. If InnoDB is used, data will not be copied between cluster nodes. When using the NDBCLUSTER engine, there may be some problems. Please refer to the MySQL Official Website

Http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-limitations-unsupported.html
Http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-limitations-syntax.html

mysql -u root ->>> CREATE TABLE testtable (i INT) ENGINE=> INSERT INTO testtable () VALUES (

> SELECT *+------+| i    |+------+|     |+------+

 row  set ( sec)

Connect to the second database node. Let's take a look and get the same output.

mysql -u root ->> SELECT *+------+| i    |+------+|     |+------+ row  set ( sec)

We should see the same output. Now, if you insert a new entry table, it will be copied back to the first node

5. Server Load balancer

The last part of this article is to install the Server Load balancer server for the MySQL cluster. Server Load balancer can be easily installed using mysql-proxy. Of course, you can also use other services.

root@mysql-proxy:~# apt-get  mysql--proxy:~#  /etc/mysql--proxy:~# cd /etc/mysql--proxy:/etc/mysql-proxy# nano mysql-proxy.conf

Add the following lines to the mysql-proxy.conf File

[mysql-= -address = .:-skip-profiling = = -threads = - = /var/run/mysql-- = /var/log/mysql--level =-backend-addresses = .:,.:-lua-script=/usr/lib/mysql-proxy/lua/proxy/balance.lua

Create the following file/etc/default/mysql-proxy for the auto start additional options

ENABLED==

Then, you can start mysql-proxy by calling the following command

/etc/init.d/mysql-proxy start/stop/status

After that, you should be able to connect to the MySQL server using the proxy address. Remember this job, you need to create a new user with a specific subnet to connect to it. You also need to add the binding address for the MySQL server in the my. cnf File

SQL users do not need to copy data, so the same user has been separately added to all database nodes. Log on to SQL shell on the data node and run the following command:

CREATE USER @ IDENTIFIED BY * FROM mysql.user;

Change the newuser, IP address, and password based on your configuration requirements. % Is used as a wildcard to act on the IP address of the entire subnet. It allows remote connection to this database node. Remember to add all other databases with the same user configuration to the nodes in this cluster

This article references MySQL NDB Cluster setup on Ubuntu 12.04 LTS and makes some changes.