Setup MySQL Cluster/Load-balancing Using HAProxy

I have already explained how to setup streaming replication in MySQL in my previous posts mentioned below. I have also showed you how you can load-balance Apache web-servers using HAproxy.

How To Setup Streaming Replication In MySQL – Slave Node

How To Setup Streaming Replication In MySQL – Master Node

In this post, I will demonstrate how we can put our MySQL cluster behind an HAProxy load-balancer so that our database continues to run even if master database node crashes. This post is about master-slave load-balancing. So, data will be written to master only but retrieved from any node. I will write another post on master-master replication later. Lets start now 🙂

Lab Description : –

1 Load-balancer –

An Ubuntu 14.04 server running HAProxy 1.4.24. IP Address =

2 MySQL Database nodes (from previous posts, they are already under streaming replication)-

  • Master Node – running MySQL 5.6.17
  • Slave Node – running MySQL 5.6.17

Steps to be performed : –

1. Install HAProxy

root@haproxy-server:/home/shashank# apt-get install haproxy

This will install HAProxy, an open source load-balancer on our Ubuntu server.

2. Install MySQL client

Now we will need to install mysql-client on Ubuntu server to connect to our databases. So, issue below command to install it.

root@haproxy-server:/home/shashank# apt-get install mysql-client

Note that if you already installed MySQL on this server before, you may skip this step as client will be already present. You may issue mysql command to check.

3. Create users on MySQL servers.

Now we need to add 2 database users to connect to our MySQL databases servers from HAProxy Ubuntu server. Fail-over needs root access to database, hence one of these users will have equivalent privileges. You may continue with root but that will require more configurations and its always safe to have a user other than root. Note that below queries have to be run on both database nodes.

E:\wamp\bin\mysql\mysql5.6.17\bin>mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('','haproxy_test'); FLUSH PRIVILEGES;"
Enter password: *****

E:\wamp\bin\mysql\mysql5.6.17\bin>mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'' IDENTIFIED BY 'haproxy' WITH GRANT OPTION; FLUSH PRIVILEGES;"
Enter password: *****

You can see that haproxy_root user has root access & haproxy_test is created just to login to database.

4. Configure HAProxy

Its time to edit HAProxy’s configuration. By default, its disabled. So, edit the file /etc/default/haproxy & change ENABLED=0 to ENABLED=1. Now, backup the existing HAProxy configuration file /etc/haproxy/haproxy.cfg & edit it with below contents. I have put comments wherever necessary.

log local0 notice
user haproxy
group haproxy

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

listen mysql-cluster #name of your mysql cluster.
mode tcp
option mysql-check user haproxy_check #db user created in last step
balance roundrobin
server host_name 1 check #hostname & IP:port of DB node1
server host_name check  #hostname & IP:port of DB node2

listen #port to bind HAProxy's web UI to.
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth user1:PASSWORD #user:password for authentication while opening web UI
stats auth user2:PASSWORD

frontend LB #optional & can be left

One main point to remember is to bind HAProxy to proper host & port. Since my web-application runs on a different server, I used listen in cluster properties above. HAProxy doesn’t have special properties for MySQL unlike Web-server. So I chose tcp above. If there are errors in your HAProxy configuration, you will see errors like below on starting haproxy service.

root@haproxy-server:/home/shashank# service haproxy start
* Starting haproxy haproxy [ALERT] 243/081515 (22114) : parsing [/etc/haproxy/haproxy.cfg:22] : 'bind' expects [addr1]:port1[-end1]{,[addr]:port[-end]}... as arguments.
[ALERT] 243/081515 (22114) : Error(s) found in configuration file : /etc/haproxy/haproxy.cfg
[ALERT] 243/081515 (22114) : config : proxy '' has no listen address. Please either specify a valid address on the <listen> line, or use the <bind> keyword.
[ALERT] 243/081515 (22114) : Fatal errors found in configuration.

It was partly because I had earlier used port 8080 for web UI but it was already in use. So I used 8090. Also, I had bound cluster to but it should have been or public IP of application/web-server.

Once this is done/fixed, start the service. It will start without any error.

5. Test load-balancer.

If you performed the steps correctly, you can now see your MySQL cluster being accessed by HAProxy server in a round-robin manner i.e. one by one 🙂 You have to use here & not the public IP or hostname. You may also point your browser to IP_address of haproxy server:8090 or any port you specified in configuration above to see the web UI. Credentials will be what you mentioned in its configuration.


root@haproxy-server:/home/shashank# mysql -h -u haproxy_root -p -e "SHOW DATABASES"
Enter password:
| Database           |
| information_schema |
| asset              |
| mysql              |
| performance_schema |
| test               |
| testdb             |

Great 🙂 You can access your cluster from load-balancer, as you can see above 🙂 Now, its time to see which node is being accessed. So, issue below command on load-balancer server 2-3 times & you will see server-ids in round-robbin manner 🙂

root@shashank-server:/home/shashank# mysql -h -u haproxy_root -p -e "show variables like 'server_id'"
Enter password:
| Variable_name | Value |
| server_id     | 2     |
root@haproxy-server:/home/shashank# mysql -h -u haproxy_root -p -e "show variables like 'server_id'"
Enter password:
root@haproxy-server:/home/shashank# mysql -h -u haproxy_root -p -e "show variables like 'server_id'"
Enter password:
| Variable_name | Value |
| server_id     | 1     |

6. Test your application

Now that basic testing has been done, now its time to test our setup in live scenario. To do this, stop mysql service on any database node & execute the same command(that you ran above) on load-balancer. You will see server-id of the other node every time you run this. Now, bring up the service and stop it on other node. Again run the same query & see the result 🙂

Now, change the application code where you have hard-coded database connection string & replace that name/IP with load-balancer IP 🙂 Check your application now by trying to read from the database. You will see that you can access the data even when one of your DB nodes is down 🙂

With this, you have successfully setup a MySQL cluster & load-balancing 🙂 See you soon!


How To Setup Streaming Replication In MySQL – Slave Node

In my last post, you read about configuring the master node. Here, you will learn how you can configure slave for streaming replication. Lets start this tutorial 🙂

Create the same database as on master.

As you know, our example slave node has IP & that will run our replicated database. So, create the same database here.

mysql> create database your_db_name;

Import the dump file to populate data.

Now, exit the mysql prompt & import the dump file that you copied from master node. This will populate our database with the data from master.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -p asset < C:\your_db_name.sql
Enter password: *****

Edit my.ini or my.cnf file.

Here we will edit the configuration file to indicate which one is our master & similar settings. Enter below details to your file. Read the file carefully & make changes accordingly. datadir is optional. Save the file & restart MySQL. On Windows, you need to go to Services & restart MySQL service. After that, you will see the data populated from dump file.

datadir=C:\Program Files\MySQL\MySQL Server 5.6\data

Stop slave.

Login to your MySQL & issue below to stop the slave.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

Configure slave to start replication from master. 

Issue below query to start the replication process. Please change the values accordingly. In the last 2 properties, use the values that you noted down while configuring master. Its the File & Value property that you need to enter from that output. After this, start your slave.

-> MASTER_USER='repl_user',
-> MASTER_LOG_FILE='mysql-bin.000003',
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Now you are done 🙂 Just create new tables or add record in your master database. All that will be mirrored to your slave database 🙂 Check it for yourself!


How To Setup Streaming Replication In MySQL – Master Node

Hey there 🙂 I am back today with a post on how to setup Streaming Replication In MySQL. Streaming Replication is a process in which a databases(s) is/are mirrored between 2 0r more servers. In simple language, we have a MySQL master node running the database which is in sync with one or more slaves. If master node fails or crashes, one of the slaves takes over & database is made available. Any data that is written to master is automatically mirrored to its slaves. Sounds interesting? 😉 Here is how to do it. I have performed this on Windows servers because of my application. But same steps apply to Linux servers as well. Only the file location will differ. I am explaining this taking into consideration that a database already exists on master. Lets start this tutorial 🙂

Lab Description : –

  • Master node – Windows Server running MySQL 5.6.17. IP Address
  • Slave node – Windows Server running MySQL 5.6.17. IP Address

Please ensure port 3306(default) or the port you specify in MySQL configuration needs to be opened so as to enable communication between master & slave. I have to do it on my Windows servers.

Steps to be performed : –

Edit my.ini or my.cnf file.

Add below section to your existing file. Note that server-id has to be 1 for master. Change datadir to the location of your MySQL data directory. binlog-do-db has to be set to the database that you need to replicate. After saving the file, restart MySQL.

Please note that all these properties are thoroughly explained in my.ini or my.cnf files. Read that carefully and then make changes.

server-id = 1

Create replication user & grant access.

Issue below queries on master node mysql prompt. Note that the IP mentioned is of slave node.

mysql> create USER repl_user@;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Flush tables with read lock.

Issue below sql queries on your already open mysql prompt.

mysql> use your_db_name;

Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| mysql-bin.000003 | 120      |              |                  |                   |
1 row in set (0.00 sec)

Note down these values i.e “File name” & “Position”. You will need these to configure slave.

Dump the database data to a file.

Now, its time to dump all the database data to a file. This file will be used to generate data on slave.

E:\wamp\bin\mysql\mysql5.6.17\bin>mysqldump -uroot -p --optyour_db_name >your_db_name.sql
Enter password: *****

Copy this file to the slave node. Use below PowerShell command for Windows 🙂

PS C:\Windows\system32> copy E:\wamp\bin\mysql\mysql5.6.17\bin\your_db_name.sql \\\c$\your_db_name.sql

Unlock the tables. Its time to unlock the tables.

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

Here, we are done with configuration of master node. I will explain slave configuration in my next post. Stay tuned 🙂

Install Postgres Advanced Server on Linux(RHEL/CentOS)

In this post, I had explained how we can install PostgreSQL on a Linux server using the source code. I also had shown ho we can automate the installation & create multiple instances. This time I am going to demonstrate how we can install Postgres Plus Advanced Server on a Linux server(RHEL/CentOS). Postgres Plus Advanced Server is an advanced version of PostgreSQL that offers many features as compared to the open source variant. You can read about it here.

Lets start with this tutorial. Please note that you will need root privileges to perform the installation.

Download the installer. First, download the installer from its official site. Make sure that you choose the installer according to your CPU architecture. You will need to create your account on that site to download it. This is a binary installer.

Unpack the tarball. Issue below commands to unpack the installer tarball. You can see a Java installer here 😉 Postgres Plus Advanced Server or PPA needs Java for some functionalities like Postgre Client (a web-based UI). So, Java has to be installed otherwise PPA will disable Java based components. I installed Java 1.8 (OpenJDK) & it installed a few dependencies as well. And when I executed the installer, it threw a “segmentation fault” error. So, I uninstalled Java & all its dependencies(that got installed alongwith Java) and then installed Java 1.7(the pre-compiled one, not using YUM since that would have installed dependencies again) & then I was able to run installer. If you face the same issue, follow what I did 😉

[root@server shashank]# ll
total 335956
-rw-rw-r-- 1 shashank shashank 160872482 Dec 9 10:22 jdk-8u25-linux-x64.tar.gz
-rw-r--r-- 1 root root 0 Dec 9 10:26 postgresql.txt
-rw-rw-r-- 1 shashank shashank 183143348 Dec 8 16:23 ppasmeta-
[root@server shashank]# tar -xf ppasmeta-
[root@server shashank]# ll
total 335960
-rw-rw-r-- 1 shashank shashank 160872482 Dec 9 10:22 jdk-8u25-linux-x64.tar.gz
-rw-r--r-- 1 root root 0 Dec 9 10:26 postgresql.txt
drwxrwxr-x 2 500 500 4096 Feb 10 2015 ppasmeta-
-rw-rw-r-- 1 shashank shashank 183143348 Dec 8 16:23 ppasmeta-
[root@server shashank]# cd ppasmeta-
[root@server ppasmeta-]# ll
total 180960
-rwxr-xr-x 1 500 500 185293474 Feb 10 2015
-rw-rw-r-- 1 500 500 7836 Feb 10 2015 README_FIRST_Linux64.txt

Run the installer.

[root@server ppasmeta-]# ./
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : ^C

You can see I interrupted the installation 😀 Why? Because its better if we add a user called postgres without assigning any password before installing. PostgreSQL uses “postgres” user for its operation. Only “postgres” user can start/stop/restart database. So, add this is advance.

[root@psltddpdb01 ppasmeta-]# useradd postgres
[root@psltddpdb01 ppasmeta-]# ./
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
Welcome to the Postgres Plus Advanced Server Setup Wizard.

Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.

Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9


Press [Enter] to continue:

Do you accept this license? [y/n]: y

Please specify the directory where Postgres Plus Advanced Server will be

Installation Directory [/opt/PostgresPlus]: /opt/app/PostgresPlus

Select the components you want to install.

Database Server [Y/n] :y

Connectors [Y/n] :y

Infinite Cache [Y/n] :y

Migration Toolkit [Y/n] :y

Postgres Enterprise Manager Client [Y/n] :y

pgpool-II [Y/n] :y

pgpool-II Extensions [Y/n] :y

EDB*Plus [Y/n] :y

Slony Replication [Y/n] :y

PgBouncer [Y/n] :y

Is the selection above correct? [Y/n]: y

Additional Directories

Please select a directory under which to store your data.

Data Directory [/opt/app/PostgresPlus/9.4AS/data]:

Please select a directory under which to store your Write-Ahead Logs.

Write-Ahead Log (WAL) Directory [/opt/app/PostgresPlus/9.4AS/data/pg_xlog]:

Configuration Mode

Postgres Plus Advanced Server always installs with Oracle(R) compatibility features and maintains full PostgreSQL compliance. Select your style preference for installation defaults and samples.

The Oracle configuration will cause the use of certain objects (e.g. DATE data types, string operations, etc.) to produce Oracle compatible results, create the same Oracle sample tables, and have the database match Oracle examples used in the documentation.

Configuration Mode

[1] Oracle Compatible
[2] PostgreSQL Compatible
Please choose an option [1] : 2

Please provide a password for the database superuser (postgres). A locked Unix
user account (postgres) will be created if not present.

Password :
Retype Password :
Additional Configuration

Please select the port number the server should listen on.

Port [5432]:

Select the locale to be used by the new database cluster.


[1] [Default locale]

Please choose an option [1] :

Install sample tables and procedures. [Y/n]:
Dynatune Dynamic Tuning:
Server Utilization

Please select the type of server to determine the amount of system resources
that may be utilized:

[1] Development (e.g. a developer's laptop)
[2] General Purpose (e.g. a web or application server)
[3] Dedicated (a server running only Postgres Plus)
Please choose an option [2] : 3

Dynatune Dynamic Tuning:
Workload Profile

Please select the type of workload this server will be used for:

[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] : 1

Advanced Configuration

PgBouncer Listening Port [6432]:

Service Configuration

Autostart PgBouncer Service [Y/n]: y
Autostart pgAgent Service [Y/n]: y
Update Notification Service [Y/n]: y
The Update Notification Service informs, downloads and installs whenever
security patches and other updates are available for your Postgres Plus Advanced
Server installation.

Pre Installation Summary

Following settings will be used for installation:

Installation Directory: /opt/app/PostgresPlus
Data Directory: /opt/app/PostgresPlus/9.4AS/data
WAL Directory: /opt/app/PostgresPlus/9.4AS/data/pg_xlog
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: ppas-9.4
PgBouncer Listening Port: 6432

Press [Enter] to continue:

Setup is now ready to begin installing Postgres Plus Advanced Server on your

Do you want to continue? [Y/n]: y

Please wait while Setup installs Postgres Plus Advanced Server on your computer.

Installing Postgres Plus Advanced Server
0% ______________ 50% ______________ 100%
Installing Database Server ...
Installing pgAgent ...
Installing Connectors ...
Installing Migration Toolkit ...
Installing EDB*Plus ...
Installing Infinite Cache ...
Installing Postgres Enterprise Manager Client ...
Installing Slony Replication ...
Installing pgpool-II ...
Installing pgpool-II Extensions ...
Installing PgBouncer ...
Installing StackBuilder Plus ...

Setup has finished installing Postgres Plus Advanced Server on your computer.

Change ownership to “postgres” user on data directory.

[root@server ppasmeta-]# chown postgres.postgres /opt/app/PostgresPlus/9.4AS/data

Switch to postgres user & login to database.

[root@server ppasmeta-]# su - postgres
[postgres@psltddpdb01 ~]$ /opt/app/PostgresPlus/9.4AS/bin/psql -p5432
psql.bin (
Type "help" for help.

postgres=# \q

That’s all from this post 🙂 I hope this was informative & fun to read this 🙂 Thanks for visiting my blog 🙂

How To See Contents Of A Stored Procedure In Oracle?

Ever found yourself in a situation when you need to see the code/content of a Stored Procedure in Oracle? We need to check it for troubleshooting & database optimisation.

So, below is how to do it? 😉 Make sure to use set lines 500 & set pages 500 before executing this query, so as to fit the output in a more readable format.

SELECT line, text FROM dba_source WHERE owner = 'SHASHANK' AND name = 'MY_SP' AND type = 'PROCEDURE' ORDER BY line;

How To Login to Microsoft SQL Server through SQL Server Client on Linux (RedHat/Fedora/CentOS/Ubuntu)

At times, we need to interact with Microsoft SQL server from Linux servers & run SQL queries. This tutorial will show you how to do it. But before we kick-off, lets first understand a few terms 🙂

DSN = Data Source Name. Think of it as the target MS SQL Server database you want to connect to from your Linux machine.

Driver = ODBC drivers to help establish connection. We are using two packages FreeTDS & unixODBC that will help achieve our task.

FreeTDS = From their official web-site, “FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases”.

unixODBC = From their official web-site, “The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms. This is to include GUI support for both KDE and GNOME”.

Now that we know the terms, lets start with the tutorial 😉

An important note – This tutorial is based on default SQL Server port 1433. If you are using a different port, please specify that one. I am demonstrating with default one.

1. Download unixODBC from You will need to click the “download” button for it. If it is already installed using YUM, I’d suggest uninstalling it. Better approach here will be to install from source. Untar it to any location of your choice. Then go to the unpacked directory & issue command : –

[root@server ~]# ./configure --prefix=/usr/local/unixODBC

It will configure the build process. Once it’s done, issue this command to make the installer.

[root@server ~]# make

Then issue this command to install it.

[root@server ~]# make install

It must have been installed in /usr/local/unixODBC location. Make note of this location.

2. Download FreeTDS from . Click the Software link to go its FTP site. Its a bit slow 😛 We need to install it by telling about the location of unixODBC package. For this, untar it & move to its unpacked directory. Then issue this command. You may skip --with-tdsver part : –

[root@server ~]# ./configure --with-unixodbc=/usr/local/unixODBC --with-tdsver=8.0

Now issue make & make install commands to install it.

3. Define target SQL Server. Edit the file –


Make sure this contains below text. Make sure the host value matches the text in square brackets. As you might have guessed it, this is the name of SQL server we will connect to. Make changes accordingly.

# A typical Microsoft server
host =
port = 1433
tds version = 8.0
client charset = UTF-8>/pre>

4. Define DSN. Edit the file –


Make sure it has below text. Text in square brackets defines DSN name. Enter anything but ensure it is single word only. Driver value is the driver to be used. We will define FreeTDS driver location in a separate file. Database value must be set to the database to be connected to. Its recommended to do a telnet 1433 to ensure proper connectivity. Change it to IP address/hostname of your SQL server.


5. Define FreeTDS driver location. Edit /usr/local/etc/odbcinst.ini
file and enter below text. Make sure file exists at this location /usr/local/lib

Description=TDS driver (Sybase/MS SQL)

6. Export a few variables.

  • export ODBCSYSINI=/usr/local/etc
  • export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/usr/local/lib:
  • export ODBCINI=/usr/local/etc/odbc.ini

7. Test settings. Issue the below commands.

[root@server ~]# tsql -S -U db_user
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
[root@server ~]# odbcinst -j
unixODBC 2.3.2
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
[root@server ~]# odbcinst -q -d
[root@server ~]# odbcinst -q -s

8. Connect to SQL server now. Issue below command. TEST_DB is DSN name. Next is user name & last is password.

[root@server ~]# isql -v TEST_DB db_user password
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |

9. Diagnosis & Troubleshooting. Issue below command & check errors, if any. This dumps a lot of useful information to screen that will help you find the problems & resolve them. I got a lot of help from this command.

[root@server ~]# osql -S TEST_DB -U db_user -P passwd

Thats it 🙂 We can now connect to our Windows counter part, SQL Server from Linux server 😉 Hope, you found this post useful!

Troubleshooting basic PostgreSQL Problems In Linux (RHEL)

Encountered 2 PostgreSQL problems today. One was about the wrong permission to opt/PostgreSQL/9.3/data directory.

[root@server pg_log]# /etc/rc.d/init.d/postgresql-9.3 restart
Restarting PostgreSQL 9.3:
waiting for server to shut down.... done
server stopped
waiting for server to start........ stopped waiting
pg_ctl: could not start server
Examine the log output.
PostgreSQL 9.3 did not start in a timely fashion, please see /opt/PostgreSQL/9.3/data/pg_log/startup.log for details

Examining the log file pointed to below.

FATAL:  data directory "/opt/PostgreSQL/9.3/data" has group or world access

DETAIL:  Permissions should be u=rwx (0700)

So, the trick was to chmod 0700 to opt/PostgreSQL/9.3/data directory 😉 And it solved it 🙂

[root@server pg_log]# /etc/rc.d/init.d/postgresql-9.3 restart
Restarting PostgreSQL 9.3:
pg_ctl: PID file "/opt/PostgreSQL/9.3/data/" does not exist
Is server running?
starting server anyway
waiting for server to start.... done
server started
PostgreSQL 9.3 restarted successfully

Other problem was with a normal user trying to restart PostgreSQL server. So I made below changes to the /etc/sudoers file. 

shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 restart
 shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 start
 shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 stop

prefixing sudo to /etc/rc.d/init.d/postgresql-9.3 restart did the trick 😉