Setup HA Load Balance with HAProxy for MySQL



HAproxy is a High Availabilty for MySQL server, It is a free opensource application. In my previous artical we used HAproxy is as a TCP/Http Load Balancer now we are using for database server in to multiple servers which improvies performance and reliablity of the server.

My Demo setup I am showing how to setup HA proxy for database server MySQL

The load balancer HA proxy server is 192.167.57.150

HAproxy Server details
OS :  Centos 6.7
IP Address          :  192.168.57.150

MySQL Servers details

Database1
OS                          :  Centos 6.7
IP Address          :  192.168.57.151

Database2
OS                          :  Centos 6.7
IP Address          :  192.168.57.152


For demo purpose we needed to install the MySQL on both the servers
# yum install mysql-server*
# service mysql start

After server of mysql please set password to the mysql server
# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n]Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]  ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n  ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database... ... Success!
 - Removing privileges on test database... ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y  ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Prepare MySQL Servers
We need to prepare the MySQL servers by creating two additional users for HAProxy. The first user will be used by HAProxy to check the status of a server.

# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('192.168.87.150','haproxy_check'); FLUSH PRIVILEGES;"

A MySQL user is needed with root privileges when accessing the MySQL cluster from HAProxy. The default root user on all the servers are allowed to login only locally. While this can be fixed by granting additional privileges to the root user, it is better to have a separate user with root privileges.

# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'192.168.87.150' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES"

Replace haproxy_root and password with your own secure values. It is enough to execute these queries on one MySQL master as changes will replicate to others.

Install MySQL Client

MySQL client has to be installed on the HAProxy server to test connectivity.

# yum install mysql*

Now try executing a query on one of the masters as the haproxy_root user.

# mysql -h 192.168.87.151 -u haproxy_root -p -e "SHOW DATABASES"

Installing HAProxy Server
# yum install haproxy

open the host file and add the server details

192.168.85.150      haproxy.demo.org     haproxy
192.168.85.147      dbserver1.demo.org    haproxy
192.168.85.148      dbserver2.demo.org    haproxy

# Vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.87.150  haserver.demo.com haserver
192.168.87.151  dbserver1.demo.com ha2
192.168.87.152  dbserver2.demo.com ha1

Configuring HAProxy
Edit the configuration file
#vi /etc/haproxy/haproxy.cfg

The first block is the global and defaults configuration block.
global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

defaults
    log global
    retries 2
    timeout connect 3000
    timeout server 5000
    timeout client 5000

More information about each of these options are covered in this article. As I told told HAProxy to send log messages to 127.0.0.1 we have to configure rsyslog to listen on it. This has too been covered in the same article under Configure Logging for HAProxy.
Moving to the main configuration part.
listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.0.0.1:3306 check
    server mysql-2 10.0.0.2:3306 check

Unlike HTTP load balancing HAProxy does not need to have a specific "mode" for MySQL so we use tcp. To set HAProxy to listen only on the loopback address (assuming that application is on the same server) however if your application resides on a different droplet make it listen on 0.0.0.0 or the private IP address of the application server.
We need one more configuration block to see the statistics of load balancing. This is completely optional and can be omitted if you don't want stats.
listen 0.0.0.0:8080
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth A_Username:YourPassword
    stats auth Another_User:passwd
Replace the usernames and passwords in "stats auth". This will make HAProxy listen on port 8080 for HTTP requests and the statistics will be protected with HTTP Basic Authentication. So you can access stats at http://<yourserver-ip-address>:8080/
# service haproxy restart
To made this service to start automatically
# chkconfig haproxy on
To check the autostart
# chkconfig --list haproxy

Before going to test the HAproxy on MySQL to differentiate with each servers dbaserver1 is with default databases and dbserver2 has default databases and one more database with name glpi_inventory

Checking the HAproxy run this query HAProxy server.

# mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+

by default it will show the database on the dbserver1
To test the the HAproxy go to the dbaserver1 and stop the service
# service mysqld stop

Then check the database on the HAproxy server this time the database will point to dbaserver2
# mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| glpi_inventory     |
| mysql              |
+--------------------+


If your want to test the HAproxy as a load server then you can run a simple test

Go to the dbserver1 and start the mysql services
# service mysqld start

Go to the HAproxy server and run the below code
Query for the server_id multiple times.
# for i in `seq 1 6`; do mysql -h 127.0.0.1 -u haproxy_root -ppassword -e "SHOW DATABASES"; done
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| glpi_inventory     |
| mysql              |
+--------------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| glpi_inventory     |
| mysql              |
+--------------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| glpi_inventory     |
| mysql              |
+--------------------+


This will show that alternatively this will show the databases on the both servers,

Please give comments and suggestions are always welcome a

Keywords: HAproxy, HAproxy database, database server load balance, Load balance on database server, Load balance MySQL, apache proxy web load balance, Apache on load balance 



Comments

Post a Comment

Popular posts from this blog

Observium: Configuring Microsoft Windows 2008 Server SNMP Agent

Image
1 . Open Control Pannel. 2. Right click on “ Programs & Features ” and click on “ Turn Windows Features on or off" 3 . Expand “ SNMP Services ” and select “ SNMP Service ” click Next 4. Click Install 5. Close  Programs & Features ” 6. Open Ädministraive Tools and clicket Services 7. Expand Configuration and select Services,  Select SNMP Service , right click and select properties 8 . Select the “ Agent ” tab 9. Type the contact name responsible for this server or email address 10 . Type the location eg, Hyderabad,India. 11 . Tick the following  Physical Datalink and subnetwork  SNMP Trap Service - Agent 12 . Select the “ Traps ” tab 13 . Type in the community string you are using in your environment and select “ Add to list ” 14 . Click “ Add ” 15 . Type the IP of the Observium ServerSNMP Trap Service - Traps 16 . Select “ Security ” tab 17 . Ensure “ Send ...
Read more

Setup Backups using Deltacopy Tools (Open Source)

Image
Over View of DeltaCopy: In general terms, DeltaCopy is an open source, fast incremental backup program. Let's say you have to backup one file that is 500 MB every night. A normal file copy would copy the entire file, even if a few bytes have changed. DeltaCopy , on the other hand, would only copy the part of the file that has actually been modified. This reduces the data transfer to just a small fraction of 500 MB saving time and network bandwidth. In technical terms, DeltaCopy is a "Windows Friendly" wrapper around the Rsync program, currently maintained by Wayne Davison. "rsync" is primarily designed for Unix/Linux/BSD systems. Although ports are available for Windows, they typically require downloading Cygwin libraries and manual configuration. Unlike "rsync", DeltaCopy is a only available for Windows and is tightly integrated with services available only on Microsoft platforms (XP, 2000, 2003, Vista, Windows 7 & 2008). Here is...
Read more

What is Kubernetes?

Image
  What is Kubernetes? Introduction In 2017, the container orchestration space was overcome by Kubernetes (k8s). Docker Swarm and Mesos have been presenting container orchestration instruments for a long time, for example, by Kubernetes components. They two helped the Kubernetes in their surroundings. Kubernetes has declared reconciliations to its individual cloud stadiums, for example, AWS, Microsoft Azure and Oracle Cloud. Every developer and the dev-ops engineer would thus benefit by learning Kubernetes nuts and bolts. In fact, this is what we are going to do in this article. What is Kubernetes? Kubernetes (K8s) is one of the most stimulating developments in the Dev-Ops world today. The aim is to produce powerful containers behind its immediate reputation. Kubernetes is a basic framework at which container applications can be operated and arranged on a group of machines. The strategy aims to fully address the containerized applications and administration lifecycle of existence. I...
Read more