Home › High Performance › Cluster › Building a High-Availability Load-Balanced MySQL Cluster with LVS+Keepalived+MySQL Cluster (1)

Building a High-Availability Load-Balanced MySQL Cluster with LVS+Keepalived+MySQL Cluster (1)

Categories: Cluster 2014-05-31 root Comments (0)

I. Introduction

MySQL Cluster is a high-availability, high-redundancy version of MySQL designed for distributed computing environments. It uses the NDB Cluster storage engine, allowing multiple MySQL servers to run within a single Cluster. The storage engine is available in MySQL 5.0 and later binary versions, as well as in RPM packages compatible with the latest Linux distributions. MySQL Cluster is a technology that enables deploying an “in-memory” database Cluster in shared-nothing systems. With a shared-nothing architecture, the system can use inexpensive hardware without special hardware or software requirements. Furthermore, since each component has its own memory and disk, there is no single point of failure. MySQL Cluster consists of a group of computers, each running multiple processes including MySQL servers, NDB Cluster data nodes, management servers, and (optionally) dedicated data access programs.

II. MySQL Cluster Basic Concepts 

NDB is an “in-memory” storage engine known for high availability and strong data consistency. MySQL Cluster can configure the NDB storage engine with various failover and load-balancing options, though doing so at the Cluster-level storage engine is the simplest approach. The NDB storage engine of MySQL Cluster contains the complete dataset, depending only on other data within the Cluster itself. 

Currently, the Cluster component of MySQL Cluster can be configured independently of the MySQL server. In MySQL Cluster, each component is treated as a node. 

Management (MGM) Node: This type of node manages other nodes within MySQL Cluster, providing configuration data, starting and stopping nodes, running backups, etc. Since this node manages the configuration of other nodes, it should be started first before any other nodes. The MGM node is started with the command “ndb_mgmd”. 

Data (NDB) Node: This type of node stores the Cluster’s data. The number of data nodes is related to the number of replicas and is a multiple of the number of fragments. For example, with two replicas and two fragments per replica, there will be 4 data nodes. However, it is not necessary to set up multiple replicas. Data nodes are started with the command “ndbd”. 

Client (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. Typically, SQL nodes are started with the command “mysqld -ndbcluster”, or by adding “ndbcluster” to “my.cnf” and then starting with “mysqld”. 

Note: In many contexts, the term “node” refers to a computer, but when discussing MySQL Cluster, it refers to a process. There can be any number of nodes on a single computer, for which we use the term “Cluster host”. 

The management server (MGM node) is responsible for managing the Cluster configuration file and Cluster logs. Each node in the Cluster retrieves configuration data from the management server and requests the method for determining the management server’s location. When new events occur within a data node, the node transmits information about such events to the management server, which then writes this information to the Cluster log.

III. Environment

System: CentOS 6.4 32-bit   9 machines

Software packages (downloadable from MySQL official site: http://dev.mysql.com/downloads/cluster/):

MGM:

MySQL-Cluster-gpl-management-7.1.31-1.el6.i686.rpm

MySQL-Cluster-gpl-tools-7.1.31-1.el6.i686.rpm

SQL Node:

MySQL-Cluster-gpl-client-7.1.31-1.el6.i686.rpm

MySQL-Cluster-gpl-server-7.1.31-1.el6.i686.rpm

NDB Node:

MySQL-Cluster-gpl-storage-7.1.31-1.el6.i686.rpm

Topology Diagram:

wKiom1OFwSTzvSc9AAGGtl899xs417.jpg

IP Planning:

wKioL1OFw-Gwy2L8AAGxWiLB-iU586.jpg

IV. Configuration (Please uninstall all MySQL-related RPM packages first)

1. LB-Master and LB-Backup Configuration

(1) Install keepalived and ipvsadm on LB-Master and LB-Backup

  1. # yum groupinstall “Additional Development” // Install development tools  
  2. # yum groupinstall “Development tools”  
  3. # tar -zxvf keepalived-1.2.1.tar.gz -C /usr/local/src/  
  4. # cd /usr/local/src/keepalived-1.2.1  
  5. # ./configure  
  6. Keepalived configuration  
  7. ————————  
  8. Keepalived version       : 1.2.1  
  9. Compiler                 : gcc  
  10. Compiler flags           : -g -O2  
  11. Extra Lib                : -lpopt -lssl -lcrypto   
  12. Use IPVS Framework       : No     // Configuration error occurred  
  13. IPVS sync daemon support : No  
  14. Use VRRP Framework       : Yes  
  15. Use Debug flags          : No  
  16. Solution:  
  17. # yum install kernel-devel ipvsadm  
  18. # ln -s /usr/src/kernels/2.6.32-358.el6.i686/ /usr/src/linux  
  19. # ./configure                     // Configure environment again  
  20. # make                           // Compile  
  21. # make install                   // Install  
  22.       
  23. # cd /usr/local/etc              // Default keepalived installation path  
  24. # ll  
  25. drwxr-xr-x. 3 root root 4096 May 24 00:37 keepalived  
  26. drwxr-xr-x. 3 root root 4096 May 24 00:29 rc.d  
  27. drwxr-xr-x. 2 root root 4096 May 24 00:29 sysconfig  
  28.        
  29. Configure for system service startup  
  30. # cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/   
  31. # cp /usr/local/etc/sysconfig/keepalived  /etc/sysconfig/  
  32. # mkdir /etc/keepalived  
  33. # cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/  
  34. # cp /usr/local/sbin/keepalived /usr/sbin/ 

(2) keepalived main configuration file for LB-Master and LB-Backup

  1. # cat /etc/keepalived/keepalived.conf  
  2. # Configuration File for keepalived  
  3. # global define  
  4. global_defs {  
  5.         router_id HaMySQL_1  
  6.         }  
  7. vrrp_sync_group VGM {  
  8.         group {  
  9.         VI_MYSQL  
  10.         }  
  11. }  
  12. vrrp_instance VI_MYSQL {  
  13.         state MASTER             // Set to BACKUP on LB-Backup  
  14.         interface eth0  
  15.         lvs_sync_daemon_interface eth0  
  16.         virtual_router_id 55  
  17.         priority 100             // Set to 90 on LB-Backup  
  18.         advert_int 5  
  19.         authentication {  
  20.                 auth_type PASS  
  21.                 auth_pass 123456  
  22.         }  
  23.         virtual_ipaddress {  
  24.                 192.168.2.200/24 dev eth0  
  25.         }  
  26. }  
  27. ##########  LVS  MySQL Start   ###########  
  28. virtual_server 192.168.2.200 3306 {  
  29.         delay_loop 6  
  30.         lb_algo rr  
  31.         lb_kind DR  
  32.         persistence_timeout 6  
  33.         protocol TCP  
  34.     
  35.         real_server 192.168.2.50 3306 {  
  36.                 weight 100  
  37.                 TCP_CHECK {  
  38.                         connect_timeout 3  
  39.                         nb_get_retry 3  
  40.                         delay_before_retry 3  
  41.                         connect_port 3306  
  42.                 }  
  43.         }  
  44.         real_server 192.168.2.60 3306 {  
  45.                 weight 100  
  46.                 TCP_CHECK {  
  47.                         connect_timeout 3  
  48.                         nb_get_retry 3  
  49.                         delay_before_retry 3  
  50.                         connect_port 3306  
  51.                 }  
  52.         }  
  53. }  
  54. ##########  LVS MySQL END   ############# 

2. MGM Configuration

(1) Install Management Node

  1. # rpm -ivh MySQL-Cluster-gpl-management-7.1.31-1.el6.i686.rpm  
  2. # rpm -ivh MySQL-Cluster-gpl-tools-7.1.31-1.el6.i686.rpm   
  3. # mkdir /etc/mysql-cluster 

(2) Configure Management Node

  1. # vim /etc/mysql-cluster/config.ini      // Add the following content  
  2. [ndbd default]  
  3. NoOfReplicas=2  
  4. DataMemory=80M  
  5. IndexMemory=18M  
  6. # TCP/IP options:  
  7. [tcp default]  
  8. portnumber=2202  
  9. # Management process options:  
  10. [ndb_mgmd]  
  11. id=1  
  12. hostname=192.168.2.10  
  13. datadir=/var/lib/mysql-cluster  
  14. # Options for data node  
  15. [ndbd]  
  16. id=2  
  17. hostname=192.168.2.30  
  18. datadir=/var/lib/mysql  
  19. [ndbd]  
  20. id=3  
  21. hostname=192.168.2.40  
  22. datadir=/var/lib/mysql  
  23. [mysqld]  
  24. id=4  
  25. hostname=192.168.2.50  
  26. [mysqld]  
  27. id=5  
  28. hostname=192.168.2.60  

(3) Start Management Node

  1. # mkdir /var/lib/mysql-cluster  
  2. # ndb_mgmd -f /etc/mysql-cluster/config.ini   
  3. MySQL Cluster Management Server mysql-5.1.73 ndb-7.1.31  
  4. # netstat -tupln  
  5. tcp     0     0    0.0.0.0:1186   0.0.0.0:*   LISTEN     17629/ndb_mgmd

3. Data Node (NDB1 and NDB2) Configuration

(1) Install Data Node

  1. # rpm -ivh MySQL-Cluster-gpl-storage-7.1.31-1.el6.i686.rpm  
  2. # mkdir /var/lib/mysql 

(2) Configure Data Node

  1. # vim /etc/my.cnf  
  2. [mysqld]  
  3. datadir=/var/lib/mysql  
  4. socket=/var/lib/mysql/mysql.sock  
  5. user=mysql  
  6. # Disabling symbolic-links is recommended to prevent assorted security risks  
  7. symbolic-links=0  
  8.     
  9. [mysqld_safe]  
  10. log-error=/var/log/mysqld.log  
  11. pid-file=/var/run/mysqld/mysqld.pid  
  12.     
  13. [mysql_cluster]  
  14. ndb-connectstring=192.168.2.10  

(3) Start Data Node

  1. # ndbd –initial        // NDB1  
  2. 2014–05–28 00:32:17 [ndbd] INFO     — Angel connected to ‘192.168.2.10:1186’
  3. 2014–05–28 00:32:17 [ndbd] INFO     — Angel allocated nodeid: 2  
  4. # ndbd –initial        // NDB2  
  5. 2014–05–28 00:33:08 [ndbd] INFO     — Angel connected to ‘192.168.2.10:1186’
  6. 2014–05–28 00:33:08 [ndbd] INFO     — Angel allocated nodeid: 3

 

4. SQL Node (SQL1 and SQL2) Configuration

Network Interface and ARP Suppression Settings

Add a lo:0 interface on top of the existing network interface

wKioL1OF0K-jcWeNAADMjF9kAk4318.jpg

  1. # vim /etc/sysctl.conf                   // Add the following two lines  
  2. net.ipv4.conf.all.arp_announce = 2  
  3. net.ipv4.conf.all.arp_ignore = 1  
  4. # sysctl -p 

(1) Install SQL Node

  1. # rpm -ivh MySQL-Cluster-gpl-client-7.1.31-1.el6.i686.rpm  
  2. # rpm -ivh MySQL-Cluster-gpl-server-7.1.31-1.el6.i686.rpm // If error occurs, uninstall MySQL-related rpm packages 

(2) Configure SQL Node

  1. # The MySQL server   
  2. [mysqld]  
  3. port = 3306  
  4. socket=/var/lib/mysql/mysql.sock  
  5. ndbcluster  
  6. default-storage-engine=NDBCLUSTER  
  7. skip-name-resolve  
  8. [mysql_cluster]  
  9. ndb-connectstring=192.168.2.10  

(3) Start SQL Node

  1. # service mysql start         // SQL1  
  2. Starting MySQL.. SUCCESS!   
  3. # service mysql start         // SQL2, if unable to start, run pkill -9 mysql then restart  
  4. Starting MySQL SUCCESS!   
  5. # netstat -tupln |grep mysql  
  6. tcp     0     0   0.0.0.0:3306        0.0.0.0:*     LISTEN      3475/mysqld

(4) Configure Remote Connection (Same configuration for SQL1 and SQL2)

  1. # mysql  
  2. mysql> grant all on *.* to ‘nuo’@‘%’ identified by ‘123’;  
  3. mysql> flush privileges;

Startup order must be followed: MGM → NDB → SQL

V. Check Status

1. Check MGM Status

  1. # ndb_mgm  
  2. — NDB Cluster — Management Client —  
  3. ndb_mgm> show  
  4. Connected to Management Server at: localhost:1186  
  5. Cluster Configuration  
  6. ———————  
  7. [ndbd(NDB)] 2 node(s)  
  8. id=2    @192.168.2.30  (mysql-5.1.73 ndb-7.1.31, Nodegroup: 0, *)  
  9. id=3    @192.168.2.40  (mysql-5.1.73 ndb-7.1.31, Nodegroup: 0)  
  10.     
  11. [ndb_mgmd(MGM)] 1 node(s)  
  12. id=1    @192.168.2.10  (mysql-5.1.73 ndb-7.1.31)  
  13.     
  14. [mysqld(API)]   2 node(s)  
  15. id=4    @192.168.2.50  (mysql-5.1.73 ndb-7.1.31)  
  16. id=5    @192.168.2.60  (mysql-5.1.73 ndb-7.1.31)

2. Check LB-Master Status

  1. # service keepalived start  
  2. Starting keepalived:                                       [  OK  ]  
  3. # ip addr  
  4. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN   
  5.     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00  
  6.     inet 127.0.0.1/8 scope host lo  
  7.     inet6 ::1/128 scope host   
  8.        valid_lft forever preferred_lft forever  
  9. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000  
  10.     link/ether 00:0c:29:22:3d:01 brd ff:ff:ff:ff:ff:ff  
  11.     inet 192.168.2.20/24 brd 192.168.2.255 scope global eth0  
  12.     inet 192.168.2.200/24 scope global secondary eth0  
  13.     inet6 fe80::20c:29ff:fe22:3d01/64 scope link   
  14.        valid_lft forever preferred_lft forever  
  15. # ipvsadm  
  16. IP Virtual Server version 1.2.1 (size=4096)  
  17. Prot LocalAddress:Port Scheduler Flags  
  18.   -> RemoteAddress:Port           Forward Weight ActiveConn InActConn  
  19. TCP  192.168.2.200:mysql rr persistent 6  
  20.   -> 192.168.2.50:mysql           Route   100    0          0            
  21.   -> 192.168.2.60:mysql           Route   100    0          0

3. Check LB-Backup Status

  1. # service keepalived start  
  2. Starting keepalived:                                       [  OK  ]  
  3. # ip addr  
  4. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN   
  5.     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00  
  6.     inet 127.0.0.1/8 scope host lo  
  7.     inet6 ::1/128 scope host   
  8.        valid_lft forever preferred_lft forever  
  9. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000  
  10.     link/ether 00:0c:29:27:e1:98 brd ff:ff:ff:ff:ff:ff  
  11.     inet 192.168.2.21/24 brd 192.168.2.255 scope global eth0  
  12.     inet6 fe80::20c:29ff:fe27:e198/64 scope link   
  13.        valid_lft forever preferred_lft forever  
  14. # ipvsadm  
  15. IP Virtual Server version 1.2.1 (size=4096)  
  16. Prot LocalAddress:Port Scheduler Flags  
  17.   -> RemoteAddress:Port           Forward Weight ActiveConn InActConn  
  18. TCP  192.168.2.200:mysql rr persistent 6  
  19.   -> 192.168.2.50:mysql           Route   100    0          0            
  20.   -> 192.168.2.60:mysql           Route   100    0          0

VI. Testing

1. Client 1 (CentOS 6.4 32-bit, IP: 192.168.2.80/24)

  1. # yum install mysql  
  2. # mysql -h 192.168.2.200 -u nuo -p  
  3. Enter password:  
  4. mysql> show databases;  
  5. +——————–+  
  6. | Database           |  
  7. +——————–+  
  8. | information_schema |  
  9. | mysql              |  
  10. | ndbinfo            |  
  11. | test               |  
  12. +——————–+  
  13. mysql> create database t;  
  14. mysql> use t;  
  15. mysql> create table t2(id int);  
  16. mysql> insert into t2 values(10);  
  17. mysql> insert into t2 values(20);

2. LB-Master Connection Status

  1. # ipvsadm  
  2. IP Virtual Server version 1.2.1 (size=4096)  
  3. Prot LocalAddress:Port Scheduler Flags  
  4.   -> RemoteAddress:Port           Forward Weight ActiveConn InActConn  
  5. TCP  192.168.2.200:mysql rr persistent 6  
  6.   -> 192.168.2.50:mysql           Route   100    1          0            
  7.   -> 192.168.2.60:mysql           Route   100    0          0            
  8. # ipvsadm -lcn  
  9. IPVS connection entries  
  10. pro expire state       source             virtual            destination  
  11. TCP 14:48  ESTABLISHED 192.168.2.80:49993 192.168.2.200:3306 192.168.2.50:3306  
  12. TCP 00:54  NONE        192.168.2.80:0     192.168.2.200:3306 192.168.2.50:3306

3. Client 2 (CentOS 6.4 32-bit, IP: 192.168.2.81/24)

  1. # yum install mysql  
  2. # mysql -h 192.168.2.200 -u nuo -p  
  3. Enter password:  
  4. mysql> show databases;  
  5. +——————–+  
  6. | Database           |  
  7. +——————–+  
  8. | information_schema |  
  9. | mysql              |  
  10. | ndbinfo            |  
  11. | t                  |  
  12. | test               |  
  13. +——————–+  
  14. mysql> use t;  
  15. mysql> select * from t2;  
  16. +——+  
  17. | id   |  
  18. +——+  
  19. |   20 |  
  20. |   10 |  
  21. +——+

4. LB-Master Connection Status

  1. # ipvsadm -lcn  
  2. IPVS connection entries  
  3. pro expire state       source             virtual            destination  
  4. TCP 00:36  NONE        192.168.2.81:0     192.168.2.200:3306 192.168.2.60:3306  
  5. TCP 14:18  ESTABLISHED 192.168.2.81:42435 192.168.2.200:3306 192.168.2.60:3306

Summary: After inserting data on Client 1, the server shows Client 1 is connected to SQL1 (192.168.2.50). When querying on Client 2, it can retrieve the data inserted by Client 1, and the server shows Client 2 is connected to SQL2 (192.168.2.60). Therefore, the data is synchronized and consistent.

51CTO Blog: http://yinuoqianjin.blog.51cto.com/8360868/1418711

Related Posts

Leave a Comment

Your email address will not be published.