MARIADB cluster building with load balancing

Cluster Introduction:

Galera is a MySQL (also supported Mariadb,percona) synchronous multi-master cluster software that currently only supports the InnoDB engine.

Main functions:

• Synchronous replication
• True multi-master that all nodes can read and write to the database at the same time
• Automatic node member control, failed nodes are automatically cleared
• New node joins data automatic replication
• True parallel replication, row-level
• Users can connect directly to the cluster, using the same feeling on MySQL exactly

Advantage:

• Because it is multi-master, there is no slave lag (delay)
• There is no loss of transactions
• Ability to expand both read and write
• Smaller client latency
• Data between nodes is synchronous, and Master/slave mode is asynchronous, and binlog on different slave may be different

Implementation technology:

The replication capabilities of the Galera cluster are based on the Galera library, and in order to enable MySQL to communicate with the Galera library, the Wsrep API has been developed specifically for MySQL.

Practical operation: Load Balancing cluster setup

1. Download MARIADB cluster

wget http://mirrors.opencas.cn/mariadb/mariadb-galera-10.0.17/bintar-linux-x86_64/ Mariadb-galera-10.0.17-linux-x86_64.tar.gz

2. Add MARIADB Users and Groups

Groupadd mariadb

Useradd-g mariadb mariadb

3. Copy the downloaded file to the directory/usr/src

4. Unzip and copy the extracted files to/usr/local/mysql

[Email protected]:/usr/src# tar-zxvf mariadb-galera-10.0.17-linux-x86_64.tar.gz

[Email protected]:/usr/local# cp-r Mariadb-galera-10.0.17-linux-x86_64/usr/local/mysql

5. Modify the folder's owning users and groups

[Email protected]:/usr/local# chown-r mariadb:mariadb mysql/

6. Install the MARIADB dictionary database to ensure that the database is installed successfully

[Email protected]:/usr/local/mysql$./scripts/mysql_install_db

Create a node in the database that is specifically designed to replicate between nodes

7. Configure the startup parameter file for node 1 in the cluster, MY1.CNF

Mainly under the [mysqld] option, add the following configuration:

Log-bin=mysql-bin

Binlog_format = ROW

Server-id = 1

Innodb_autoinc_lock_mode = 2

Wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so

Wsrep_cluster_name = "My_mariadb_cluster"

Wsrep_cluster_address= "gcomm://"

Wsrep_node_name = "Cluster_node1"

Wsrep_node_address = 127.0.0.1:4406

Start Node 1:

./bin/mysqld--defaults-file=my1.cnf--wsrep-new-cluster

8. Configure the startup parameter file for node 2 in the cluster, MY2.CNF

Mainly under the [mysqld] option, add the following configuration:

Log-bin=mysql-bin

Binlog_format = ROW

DataDir =/usr/local/mysql/data1

Server-id = 2

Innodb_autoinc_lock_mode = 2

Wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so

Wsrep_cluster_name = "My_mariadb_cluster"

Wsrep_cluster_address= "gcomm://127.0.0.1:4406"

Wsrep_node_name = "Cluster_node2"

Wsrep_node_address = 127.0.0.1:4407

9. Configure the startup parameter file for node 3 in the cluster, MY3.CNF

Mainly under the [mysqld] option, add the following configuration:

Log-bin=mysql-bin

Binlog_format = ROW

DataDir =/usr/local/mysql/data2

Server-id = 3

Innodb_autoinc_lock_mode = 2

Wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so

Wsrep_cluster_name = "My_mariadb_cluster"

Wsrep_cluster_address= "gcomm://127.0.0.1:4407"

Wsrep_node_name = "Cluster_node3"

Wsrep_node_address = 127.0.0.1:4408

10. Check and test if the cluster is started successfully

Observe the command show status like ' wsrep% '; Output information to see if there are 3 nodes.

Connect to any one of the nodes in the cluster:

./bin/mysql--host 127.0.0.1--port 3307-umycluster-p123456

Create a table under the test database goods

CREATE TABLE goods (ID int (one) not NULL, name varchar (20));

Insert a record:

INSERT into goods (id,name) value (1, ' Iphone7 ');

Then log on to another node to check if the table and record you just created exist:

./bin/mysql--host 127.0.0.1--port 3306-umycluster-p12345

MariaDB [test]> SELECT * from goods;

+----+---------+

| ID | name |

+----+---------+

| 1 | Iphone7 |

+----+---------+

We see that the record already exists and once again proves that the cluster is well-matched.

11. Configure Haproxy

Download: wget http://down1.chinaunix.net/distfiles/haproxy-1.4.21.tar.gz

Compiling: Make target=linux26 prefix=/usr/local/haproxy

Install: Make install Prefix=/usr/local/haproxy

Add a configuration file under/usr/local/haproxy haproxy.cfg

Modify the contents of the configuration file as follows:

Global

Log 127.0.0.1 Local0

Log 127.0.0.1 Local1 Notice

#log Loghost local0 Info

Maxconn 4096

User Haproxy #所属运行的用户

Group Haproxy #所属运行的组

Nbproc 1

Pidfile/usr/local/haproxy/haproxy1.pid

#debug

#quiet

Defaults

Log Global

Option Tcplog

Option Dontlognull

Retries 3

Option Redispatch

Maxconn 4096

Timeout Connect 50000ms

Timeout Client 50000ms

Timeout Server 50000ms

Listen Mariadb-galera

Bind 127.0.0.1:3399 # Client Listening Port

Mode TCP

Balance Leastconn #最少连接的负载均衡算法

Server DB1 127.0.0.1:3306 Check

Server DB1 127.0.0.1:3307 Check

Server DB1 127.0.0.1:3308 Check

Start Haproxy:

./sbin/haproxy-f Haproxy.cfg

Based on the least-connected load-balancing algorithm, Haproxy redirects read-write requests to the least-connected database server.

Check that the Haproxy is configured correctly:

Connect First: [email protected]:/usr/local/mysql$./bin/mysql--host 127.0.0.1--port 3399 -umycluster-p123456 # Note The connection port is 3399 and is the listening port of the Haproxy server

Insert a record try: INSERT into goods (id,name) value (2, ' Lumia ');

Query OK, 1 row affected (0.19 sec)

Success, description haproxy configuration OK

MARIADB cluster building with load balancing