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 authentication trap ” is
Read more

AWS: Upgrade PV Drivers on Windows Instances

Image
Amazon Windows AMIs contain a set of drivers to permit access to Xen virtualized hardware. These drivers are used by Amazon EC2 to map instance store and Amazon EBS volumes to their devices. If your Windows instance is launched from a Windows Server 2012 R2 AMI, it uses AWS PV drivers. If your Windows instance uses RedHat drivers, you can upgrade to Citrix drivers. If you are already using Citrix drivers, you can upgrade the Citrix Xen guest agent service. To verify which driver your Windows instance uses , open  Network Connections  in Control Panel and view the  Local Area Connection . Check whether the driver is one of the following: AWS PV Network Device Citrix PV Ethernet Adapter RedHat PV NIC Driver Alternatively, you can check the output from the  pnputil -e  command. AWS PV Drivers Windows Server 2012 R2 AMIs include AWS PV drivers. The AWS PV drivers are stored in the %ProgramFiles%\Amazon\Xentools directory. This directory also contains public symbo
Read more

How to configure Incremental backups for MSSQL Database

Image
Incremental Database Backup : Create a folder for the backup in the Hardisk (Ex: C:\SQL_backup\Incrementail) 1. Open SQL Server Management Studio and connect using windows credentials. 2. Click Management   and select Maintenance Plans . 3. Right-Click on Maintenance Plans and select New Maintenance Plans, Give naming Convention as Incremental_DB_Backup . 4. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks   5. Drag and drop the Back Up Database Task and Maintenance Clean Task in the Incremental_DB_Backup   Design area.   6. Right Click on the Back Up Database Task and select Edit .   7. Select the Type of Backup you want to do as Differential 8. Select the Database In Database(s)   in these database list (Ex: TestDB.)   9. Select Create a Backup file for every database , select Create a sub-directory for each database 10. Give the path where we want to store the database backup as C:\SQL_backup\I
Read more