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

AWS: Introduction to AWS (Amazon Web Services)

Image
Amazon Web Services (AWS) Considering that you don't have an account with AWS ( Amazon Web Services) this Demo is divided into 5 steps 1. Creating/Sign Up for AWS account. 2. Creat ing an IAM user . 3. Creat ing a secure key pair. 4. Creat ing a Virtual Private Cloud (VPC) 5. Creat ing a security group for access. 1. Creating/Sign Up for AWS account When we sign up for the AWS account, we get automatic  access for all the services in the AWS, which includes the Amazon EC2 and we are charged for only the services which we chose and use. With Amazon EC2 we will pay for that what we use and if we are a  new AWS customer and we can get started with Amazon EC2 for free. If we have an AWS account already, we can skip the next task or else we can continue with the account creation. You can check the AWS Free Tier information here http://aws.amazon.com/free/ To create an AWS account, we needed to access the URL a). Connect to the URL ht...
Read more

System/Server Health Status using PowerShell

Image
System/Server Health Status using PowerShell Support team or System Administrators who are willing to get the system health check status can use this script. This script has been written in Powershell and it can generate   reports   in HTML format. This script can give you below information: Hardware: To Get Manufacturer, Model, No.   Of   Logical Processors, Installed Memory.   Code:  Get-WmiObject -class Win32_ComputerSystem -ComputerName $Computername | Select-Object Name,Domain,Manufacturer, Model,NumberOfLogicalProcessors,@{ Name = "Installed Memory (GB)" ; Expression = { "{0:N0}" -f( $_.TotalPhysicalMemory / 1gb ) } }            CPU Details: To Get  CPU speed, Current CPU Usage in %,  Code: Get-WmiObject win32_processor | Select-Object SystemName,@{ Expression = { $_.MaxClockSpeed...
Read more