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 = 192.168.0.50

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

  • Master Node – 192.168.0.31 running MySQL 5.6.17
  • Slave Node – 192.168.0.32 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 ('192.168.0.50','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'@'192.168.0.50' 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.

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

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

listen 0.0.0.0:8090 #port to bind HAProxy's web UI to.
bind 192.168.0.50:8090
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
bind 192.168.0.50:8090

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.

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 '192.168.0.50:8080' 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 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.

mysql_cluster

root@haproxy-server:/home/shashank# mysql -h 127.0.0.1 -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 127.0.0.1 -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 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"
Enter password:
root@haproxy-server:/home/shashank# mysql -h 127.0.0.1 -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 192.168.0.32 & 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.

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=C:\Program Files\MySQL\MySQL Server 5.6\data
replicate-do-db=your_db_name
master-host=192.168.0.31
master-user=repl_user
master-password=PASSWORD

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.

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.31',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='PASSWORD',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=120;
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 192.168.0.31
  • Slave node – Windows Server running MySQL 5.6.17. IP Address 192.168.0.32

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.

[replication]
server-id = 1
datadir=e:/wamp/bin/mysql/mysql5.6.17/data
log-bin=mysql-bin
binlog-do-db=your_db_name
innodb_flush_log_at_trx_commit=1
sync_binlog=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@192.168.0.32;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.32 IDENTIFIED BY 'PASSWORD';
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
mysql> FLUSH TABLES WITH READ LOCK;
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 \\192.168.0.32\c$\your_db_name.sql

Unlock the tables. Its time to unlock the tables.

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

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

Create a database in Oracle11g on Linux (RHEL6)


In the previous tutorial I explained how to Install Oracle 11g Database on a Linux machine. In this tutorial, I will explain how to create a new database. Last time we had created & configured a default database (orcl) that comes with Oracle. This time we will create our own database & then we can create tables there. Let’s start with this tutorial 🙂 Make sure you have at least 2GB of free space where your Oracle is installed.

Declare new shell variable. This will be the name of your new database. In this tutorial, I chose the name db_new as I had run out of nice names 😉 Please note that Oracle accepts database names of maximum 8 characters. So don’t go for longer names. Open your bash shell or terminal & issue : – ORACLE_SID=db_new (make sure you use your own name that you have chosen for your database). Then issue : – export ORACLE_SID. This will export ORACLE_SID variable to your profile. It also means, your Oracle will now run targeting this database.

Create a directory for your database. Issue this command: – mkdir -p $ORACLE_BASE/oradata/db_new. This is where control & data-files for your database will be kept. Refer to previous post to know about ORACLE_BASE & HOME.

Create an Initialisation Parameter file. This parameter file or pfile determines the database name, location of control files & a variety of parameters. Its a text file, so easiest way to create it is to copy it from init.ora file found in $ORACLE_HOME/dbs location. Copy it to the same location & give it the name initdb_new.ora (name of your database, just make sure you prefix init as it’s an initialisation file). Now edit with the contents shown below. Make sure to comment the lines mentioned in screenshot (open the image in new tab if you find text too small) because we are preparing a minimal file. Anything extra will result into failure. Be extra cautions about the control files location. Control files are the files that determine the number & location of database data files. Data files are actual files what store the actual data.

pflie contents.

pflie contents.

Create Server Parameter File. Server parameter file or spfile is a binary file based on our initialisation parameter file(pfile) & can’t be read using a text editor. This is what Oracle reads before mounting the database. So, start Oracle by issuing sqlplus /nolog command. And then connect using your credentials as sysdba (for eg. sqlplus / as sysdba). This will connect you to an idle instance. From this point, we can create our spfile or server parameter file. Issue command : – create spfile from pfile. This will create spfile. Now armed with spfile, database can now be started with nomount stage. Nomount stage is where Oracle reads the spfile & starts the Oracle instance. Issue startup nomount command to do this.

spfile created & database mounted to nomount stage.

spfile created & database mounted to nomount stage.

Issue Create Database script. Below is the database creation script. I am not posting the text here as I want you to this by hand to know what you are actually doing to create database. This is the most important step, so be extra cautious here. This is the step that got me exhausted the most 😦 I failed almost 8-10 times going past this step. Potential pitfalls are : – lack of adequate storage space for data-files, wrong tablespace names in pfile, specifying inadequate size to various parameters, etc. I suggest following the values strictly that are mentioned in screenshot, if you are a novice in Oracle.

Database creation script executed.

Database creation script executed.

Mount database. Now issue alter database mount command. Mount is the stage where Oracle read the contents on control-files to know about size, number & location of data-files. Your database is now mounted.

Open database. Now issue alter database open command to open the database. This is the stage where Oracles reads from & writes to actual database files & database becomes ready for transactions.

Run Oracle scripts to build database dictionary views. This is a time-taking step (script #2), so its better to execute these scripts & then go for a cup of tea or coffee or some snacks 😉

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql

scripts executed1 scripts executed

Test database. Now that database has been created, we can create our tables here. You can also test it by querying database dictionary views. Below are two such queries.

querying database dictionary view

querying database dictionary view

So, we have successfully created our new database to play with. In next tutorial, I will explain you how to back-up & restore a database 🙂 See you later 😉