Haproxy+mysql cluster (MySQL cluster) configuration

First, prepare

1. Prepare the server

Build a MySQL cluster system with 2 nodes and build haproxy+mysql cluster (MySQL cluster) system using 6 servers

node Configuration Instructions

Node	The corresponding IP and port
Haproxy 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 node (2) CentOS 6.3	1.1.1.31
	1.1.1.32

Second,Haproxy Load Balancer installation configuration

[email protected] ~]# Yum install haproxy*

Create the haproxy.cfg profile in the/etc/haproxy/directory

--------------------------------------------------------------------------------

[email protected] ~]# vim/etc/haproxy/haproxy.cfg

# This config needs haproxy-1.1.28 or haproxy-1.2.1

Global

Log 127.0.0.1 local0 Info #日志相关

Log 127.0.0.1 Local1 Notice

Maxconn 4096

Chroot/usr/local/haproxy

UID Root

GID Root

Daemon

#debug

#quiet

Pidfile/usr/local/haproxy/haproxy.pid

Defaults

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 #代理端口

Mode TCP #模式 TCP

Option Mysql-check #user root #mysql健康检查 root for MySQL login username

Balance Roundrobin #调度算法

Server MYSQL1 1.1.1.21:3306 Check Port 3306#weight 1 check inter 1s rise 2 fall 2 #健康检查加上check

Server MYSQL2 1.1.1.22:3306 Check Port 3306#weight 1 check inter 1s rise 2 fall 2

Listen stats #监控

Mode http

Bind 0.0.0.0:8888

Stats enable

Stats Uri/dbs

Stats Realm global\ Statistics

Stats Auth admin:hello123

--------------------------------------------------------------------------------

Starting service:/usr/sbin/haproxy-f/etc/haproxy/haproxy.cfg

Login Browser View: Enter admin hello123 Login

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/54/37/wKioL1R8RuOwLCEWAAYmE9A0L_4625.jpg "title=" Haproxy.jpg "alt=" Wkiol1r8ruowlcewaayme9a0l_4625.jpg "/>

Third, MySQL cluster installation

1,mysql cluster Each node installation, take the management node as an example

[Email protected] ~]# wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/ Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz

[[email protected] ~]# Groupadd MySQL

[[email protected] ~]# useradd mysql-g MySQL

[Email protected] ~]# TAR-XVF mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz

[Email protected] ~]# MV Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/usr/local/mysql

[Email protected] ~]# cd/usr/local/

[Email protected] ~]# chown-r mysql:mysql mysql/

[Email protected] ~]# CD mysql/

[Email protected] ~]# scripts/mysql_install_db--user=mysql

2, node configuration

Management node:

[Email protected] ~]# Vi/var/lib/mysql-cluster/config.ini

--------------------------------------------------------------------------------

[NDBD DEFAULT]

Noofreplicas=2 #副本数量, it is recommended to use the default 2

maxnoofconcurrentoperations=10000

datamemory=1024m #每个数据节点中给数据分配的内存

indexmemory=500m #每个数据节点中给索引分配的内存

backupmemory:50m

[NDB_MGMD]

#管理节点配置项

Nodeid= 1

hostname=1.1.1.30 #管理节点IP

Datadir=/usr/local/mysql/data #管理节点日志, configuration directory

Arbitrationrank:1 #该节点的优先级别等级

[NDBD]

#数据节点配置项

Nodeid = 2 #第一个数据节点

hostname=1.1.1.31 #数据节点IP

Datadir=/usr/local/mysql/data #数据节点存放数据的目录

[NDBD]

Nodeid = 3 #第二个数据节点

hostname=1.1.1.32

Datadir=/usr/local/mysql/data

[Mysqld]

#SQL节点配置项

Nodeid = 4 #第一个SQL节点

hostname=1.1.1.21

[Mysqld]

Nodeid = 5 #第二个SQL节点

hostname=1.1.1.22

[Mysqld]

Nodeid = 6 #第三个SQL节点

hostname=1.1.1.254

[Mysqld]

#建议保留一个SQL节点配置口

--------------------------------------------------------------------------------

Data node: Same as two data node configurations

[Email protected] ~]# 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

#运行NDB存储引擎

Ndbcluster

#指定管理节点

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/mysqld.pid

--------------------------------------------------------------------------------

SQL Node Configuration:

[Email protected] ~]# cd/usr/local/mysql/

[email protected] ~]# CP support-files/mysql.server/etc/rc.d/init.d/mysqld

[Email protected] ~]# chmod +x/etc/rc.d/init.d/mysqld

[Email protected] ~]# chkconfig--add mysqld

[Email protected] ~]# VI/ETC/MY.CNF

--------------------------------------------------------------------------------

[Mysqld]

Server-id=4

#每个服务器的id不一样

Datadir=/usr/local/mysql/data

Socket=/var/lib/mysql/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

#以下为mysql The main main mode configuration file

# Ignore MySQL database replication

Binlog-ignore-db=mysql

# 2 Increase each time

Auto-increment-increment = 2

# Set the offset of the automatically growing field, which is the initial value of 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

CLUSTERThe Order of Initiation is: Management node-Data Node-sqlnode Close in reverse order

Management node Startup:

[Email protected] ~]#/usr/local/mysql/bin/ndb_mgmd-f/var/lib/mysql-cluster/config.ini

After adding a new node, reload the Management node configuration information:

[email protected] ~]# /USR/LOCAL/MYSQL/BIN/NDB_MGMD -f/etc/config.ini--initial
Or
[email protected] ~]# /USR/LOCAL/MYSQL/BIN/NDB_MGMD -f/etc/config.ini--reload

Data node Start:

[Email protected] ~]#/USR/LOCAL/MYSQL/BIN/NDBD--initial

Note: This parameter is not required for the first MySQL node to start using –-initial, and later to start

SQL node Start:

[[Email protected] ~]# service mysqld start

Iv. MySQL cluster test

1. View cluster information

[email protected] ~]#ndb_mgm-e Show

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/54/3E/wKiom1R9M53gIIoEAAFvaL4okmM280.jpg "title=" Mysql-cluster.jpg "alt=" Wkiom1r9m53giioeaafval4okmm280.jpg "/>

2. Test SQL Data Synchronization

Log in from SQL Node 1, create databases and tables, and perform simple tests.

[email protected] ~]# mysql-uroot-p

mysql> CREATE DATABASE test;

mysql> use test;
Database changed

Mysql> CREATE TABLE test1 (ID int,name varchar (ten)) engine=ndb;

mysql> INSERT INTO test1 values (1, ' Test ');

Mysql> select * from Test1;

+------+---------+
| ID | name |
+------+---------+
| 1 | Test |
+------+---------+

log in from SQL Node 2 to see the effects, libraries, tables, and data already synchronized.

Insert a piece of data from the SQL Node 2 node and also log in to SQL Node 1to see that the data is synchronized

3, testing the HA service

Stop SQL Node 2 service view

[[email protected] ~]#/etc/init.d/mysqld stop

Shutting down MySQL .... [OK]

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/54/3F/wKioL1R9OILyn6L2AAXs559Vej8749.jpg "title=" Ha001.jpg "alt=" wkiol1r9oilyn6l2aaxs559vej8749.jpg "/>650" this.width=650; "src=" http://s3.51cto.com/wyfs02/M02 /54/41/wkiom1r9oefyn9azaaf8fyagkuk795.jpg "title=" ha002.jpg "alt=" Wkiom1r9oefyn9azaaf8fyagkuk795.jpg "/>

MySQL service 1.1.1.11:3306 can still be used normally.

This article is from the "two-day Love" blog, please be sure to keep this source http://408407.blog.51cto.com/398407/1585424

Haproxy+mysql cluster (MySQL cluster) configuration