Haproxy + mysql cluster (MySQL cluster) configuration, haproxycluster

Haproxy + mysql cluster (MySQL cluster) configuration, haproxycluster

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.