Haproxy + mysqlcluster (MySQL cluster) Configuration
I. Preparation
1. Prepare the server
Establish a MySQL CLuster system with two nodes and build a Haproxy + mysql cluster (MySQL CLuster) system with six servers
Node configuration instructions
Node |
Corresponding IP address and port |
Haproxy Server Load balancer (1) centos 6.3 |
1.1.1.11 |
Management node (1) centos 6.3 |
1.1.1.30 |
SQL node (2) centos 6.3 |
1.1.1.21 |
1.1.1.22 |
Data nodes (2) centos 6.3 |
1.1.1.31 |
1.1.1.32 |
Ii. Haproxy Server Load balancer installation and configuration
[Root @ HAPROXY001 ~] # Yum install haproxy *
Create the haproxy. cfg configuration file in the/etc/haproxy/directory.
--------------------------------------------------------------------------------
[Root @ HAPROXY001 ~] # Vim/etc/haproxy. cfg
# This config needs haproxy-1.1.28 or haproxy-1.2.1
Global
Log 127.0.0.1 local0 info # log-related
Log 127.0.0.1 local1 notice
Maxconn 4096
Chroot/usr/local/haproxy
Uid root
Gid root
Daemon
# Debug
# Quiet
Pidfile/usr/local/haproxy. pid
Ults
Log global
Mode http
# Option httplog
Option dontlognull
Retries 3
Option redispatch
Maxconn 8000
Contimeout 3000
Clitimeout 30000
Srvtimeout 30000
Listen mysql
Bind 0.0.0.0: 3306 # proxy Port
Mode tcp # mode TCP
Option mysql-check # user root # mysql health check root is the mysql login Username
Balance roundrobin # Scheduling Algorithm
Server mysql1 1.1.1.21: 3306 check port 3306 # weight 1 check inter 1 s rise 2 fall 2 # health check plus check
Server mysql2 1.1.1.22: 3306 check port 3306 # weight 1 check inter 1 s rise 2 fall 2
Listen stats # monitoring
Mode http
Bind 0.0.0.0: 8888
Stats enable
Stats uri/dbs
Stats realm Global \ statistics
Stats auth admin: hello123
--------------------------------------------------------------------------------
Start the service:/usr/sbin/haproxy-f/etc/haproxy. cfg
Log on to the browser and enter admin hello123 to log on.
Iii. Install mysql cluster
1. Install mysql cluster nodes. Take management nodes as an example.
[Root @ MYSQLMANAGER ~] # Wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
[Root @ MYSQLMANAGER ~] # Groupadd mysql
[Root @ MYSQLMANAGER ~] # Useradd mysql-g mysql
[Root @ MYSQLMANAGER ~] # Tar-xvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
[Root @ MYSQLMANAGER ~] # Music mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/usr/local/mysql
[Root @ MYSQLMANAGER ~] # Cd/usr/local/
[Root @ MYSQLMANAGER ~] # Chown-R mysql: mysql/
[Root @ MYSQLMANAGER ~] # Cd mysql/
[Root @ MYSQLMANAGER ~] # Scripts/mysql_install_db -- user = mysql
2. node configuration
Management node:
[Root @ MYSQLMANAGER ~] # Vi/var/lib/mysql-cluster/config. ini
--------------------------------------------------------------------------------
[Ndbd default]
NoOfReplicas = 2 # Number of replicas. The default value is 2.
MaxNoOfConcurrentOperations = 10000
DataMemory = 1024 M # Memory allocated to data in each data node
IndexMemory = 500 M # Memory allocated to the index for each data node
BackupMemory: 50 M
[NDB_MGMD]
# Manage node configuration items
Nodeid = 1
HostName = 1.1.1.30 # management node IP
DataDir =/usr/local/mysql/data # manage node logs and configure Directories
ArbitrationRank: 1 # priority level of the node
[NDBD]
# Data node configuration items
Nodeid = 2 # First Data Node
HostName = 1.1.1.31 # data node IP Address
DataDir =/usr/local/mysql/data # directory where data nodes store data
[NDBD]
Nodeid = 3 # second data node
HostName = 1.1.1.32
DataDir =/usr/local/mysql/data
[Mysqld]
# SQL node configuration items
Nodeid = 4 # first SQL Node
HostName = 1.1.1.21
[Mysqld]
Nodeid = 5 # second SQL Node
HostName = 1.1.1.22
[Mysqld]
Nodeid = 6 # Third SQL Node
HostName = 1.1.1.254
[Mysqld]
# We recommend that you retain an SQL node configuration port.
--------------------------------------------------------------------------------
Data Node: the two data nodes have the same configuration.
[Root @ MYSQLDB001 ~] # Vim/etc/my. cnf
--------------------------------------------------------------------------------
[Mysqld]
Datadir =/usr/local/mysql/data
Socket =/usr/local // mysql/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0
# Run the NDB storage engine
Ndbcluster
# Specify a management Node
Ndb-connectstring = 1.1.1.30: 1186
[MYSQL_CLUSTER]
Ndb-connectstring = 1.1.1.30: 1186
[NDB_MGM]
Connect-string = 1.1.1.30
[Mysqld_safe]
Log-error =/usr/local/mysql/log/mysqld. log
Pid-file =/var/run/mysqld. pid
--------------------------------------------------------------------------------
SQL node configuration:
[Root @ MYSQL001 ~] # Cd/usr/local/mysql/
[Root @ MYSQL001 ~] # Cp support-files/mysql. server/etc/rc. d/init. d/mysqld
[Root @ MYSQL001 ~] # Chmod + x/etc/rc. d/init. d/mysqld
[Root @ MYSQL001 ~] # Chkconfig -- add mysqld
[Root @ MYSQL001 ~] # Vi/etc/my. cnf
--------------------------------------------------------------------------------
[Mysqld]
Server-id = 4
# Different server IDS
Datadir =/usr/local/mysql/data
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0
Log-bin =/usr/local/mysql/log/mysql-bin.log
Max_connections = 1000
Skip-name-resolve
# The following is the configuration file of the mysql master mode.
# Ignore mysql Database Replication
Binlog-ignore-db = mysql
#2 increase each time
Auto-increment = 2
# Set the offset of the automatically increasing field, that is, the initial value is 2
Auto-increment-offset = 1
[Mysqld_safe]
Log-error =/usr/local/mysql/log/mysqld. log
Pid-file =/usr/local/mysql/mysqld. pid
[MYSQLD]
Ndbcluster
Ndb-connectstring = 1.1.1.30
[MYSQL_CLUSTER]
Ndb-connectstring = 1.1.1.30
[NDB_MGM]
Connect-string = 1.1.1.30
3. Start the service
The CLUSTER startup sequence is: Management node-data node-SQL node shutdown in reverse order
Management node startup:
[Root @ MYSQLMANAGER ~] #/Usr/local/mysql/bin/ndb_mgmd-f/var/lib/mysql-cluster/config. ini
After adding a new node, reload the management node configuration information:
[Root @ MYSQLMANAGER ~] #/Usr/local/mysql/bin/ndb_mgmd-f/etc/config. ini -- initial
Or
[Root @ MYSQLMANAGER ~] #/Usr/local/mysql/bin/ndb_mgmd-f/etc/config. ini -- reload
Start a data node:
[Root @ MYSQLDB001 ~] #/Usr/local/mysql/bin/ndbd -- initial
Note: Only the -- initial option is used for the first mysql node startup. this parameter is not required for subsequent startup.
SQL node startup:
[Root @ MYSQL001 ~] # Service mysqld start
Iv. mysql cluster Test
1. view cluster Information
[Root @ MYSQLMANAGER ~] # Ndb_mgm-e show
2. Test SQL data synchronization
Log On from SQL Node 1, create a database and a table, and perform a simple test.
[Root @ MYSQL001 ~] # Mysql-uroot-p
Mysql> create database test;
Mysql> use test;
Database changed
Mysql> create table test1 (id int, name varchar (10) engine = ndb;
Mysql> insert into test1 values (1, 'test ');
Mysql> select * from test1;
+ ------ + --------- +
| Id | name |
+ ------ + --------- +
| 1 | test |
+ ------ + --------- +
Log On from SQL Node 2 and check that the results, databases, tables, and data have been synchronized.
Insert a piece of data from SQL Node 2 and log on to SQL Node 1 to see that the data has been synchronized.
3. Test the HA service.
Stop SQL Node 2 Service View
[Root @ MYSQL002 ~] #/Etc/init. d/mysqld stop
Shutting down MySQL... [OK]
Mysql service 1.1.1.11: 3306 can still be used normally.