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 🙂

Steps to be performed : –

1. 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.

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 0.0.0.0:3306 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.

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 127.0.0.1 but it should have been 0.0.0.0 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 127.0.0.1 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.

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 🙂

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!

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.

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.

Like this:

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 : –

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.

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 😉

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.

Like this:

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 http://www.unixodbc.org/. 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 http://www.freetds.org/. 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 : –

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.

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 sqlserver.shashank.com 1433 to ensure proper connectivity. Change it to IP address/hostname of your SQL server.

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!

Like this:

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.... doneserver stoppedwaiting for server to start........ stopped waitingpg_ctl: could not start serverExamine 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 accessDETAIL: Permissions should be u=rwx (0700)

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