Setup an Apache Reverse Proxy Server on Ubuntu 14.04


In my last post, I demonstrated how we can set up an Apache Forward Proxy Server on Ubuntu 14.04. So, this post will talk about everything you need to set up an Apache Reverse Proxy Server.

Difference between Forward and Reverse Proxy.

Before I start this tutorial, we must first understand the difference between Forward and Reverse Proxy. Since my blog is all about simple explanation, I will keep things simple & informative 🙂

Forward Proxy server is your more obvious kind of proxy where you access a remote server, like Google or Facebook or another remote server in your network such as Jenkins, via a proxy server. It means, data packets flow like this.

Client(aka you) –> Forward Proxy server –> Remote server(aka Google).

Forward implies that your proxy server sits in front of your remote server. You need to configure your client to use Forward Proxy server. Your remote server will see IP of your proxy server & knows nothing about client’s existence.

Reverse Proxy is a different concept. Here you also access your remote server via a proxy server but you don’t specify IP or hostname of your remote server. You enter IP/hostname of your Reverse proxy server which redirects your request to the remote server, based on its configuration. Data packets flow in the similar manner, but you only invoke your Reverse Proxy server here as opposed to invoking your remote server.

Suppose you have a Jenkins server running on port 8080. You want to access this server but you don’t want to expose its port. You setup an Apache Reverse Proxy Server & tell it to redirect all requests landing on port 80 (Apache’s default) to Jenkins server which is running on port 8080. Since its default port, you just enter IP/hostname of your Reverse Proxy server in your browser & you will end up seeing Jenkins GUI 😉 You don’t need to configure any client at all here because you are entering Reverse Proxy server’s IP/hostname only.

Armed with some information, lets begin this tutorial 🙂

Lab Description : –

  • Reverse Proxy Server – An Ubuntu 14.04 Server running Apache with IP 192.168.0.51
  • Client  – An Ubuntu 14.04 Server with IP 192.168.0.50
  • Remote Server – An Ubuntu 14.04 Server with IP 192.168.0.50. This server runs my Jenkins, so I will be accessing Jenkins from my client via proxy server. Note that I am using same host for client & remote server. This is because my request will go to Reverse Proxy Server only & it will redirect that request to Jenkins which runs on the same host.

Steps to Perform : –

Configure Reverse Proxy Server (192.168.0.51).

1. Install core product.

Install Apache, if not already done. It is as simple as issuing –

root@shashank-reverse-proxy-server:/home/shashank# apt-get install-y apache2 apache2-doc apache2-utils

2. Install necessary modules.

Issue below command to install modules required for proxy server.

root@shashank-reverse-proxy-server:/home/shashank# apt-get installlibapache2-mod-proxy-html libxml2-dev

3. Enable Apache modules.

Issue below command to enable all the required Apache modules.

root@shashank-reverse-proxy-server:/home/shashank# a2enmod proxy proxy_ajp proxy_http rewrite deflate headers proxy_balancer proxy_connect proxy_html

4. Configure proxy configuration file.

We will now create an Apache proxy configuration file that will hold information required for proxying. Start with creating a file like this.

root@shashank-reverse-proxy-server:/home/shashank# vim /etc/apache2/mods-available/mod_reverse_proxy.conf

Please check my GitHub gist to see the contents of this file. HTML tags are interfering with the tags in configuration file. You can see that nothing much is happening here except enabling the proxy.

If you want to run Apache on its default port, its alright. Otherwise you may wish to edit /etc/apache2/ports.conf file. Whatever port you define here will be used to access your remote server in the form of IP/hostname of Reverse Proxy server:port#.

5. Define a Virtual Host.

Its now time to define a Virtual Host which is a separate instance of your web-server(remember you can host multiple sites on a single Apache server). We are defining it to enable more fine-grained logs & redirection for remote server. Start with backing up the original default Virtual Host.

root@shashank-reverse-proxy-server:/home/shashank# cp -p /etc/apache2/sites-enabled/000-default.conf /etc/apache2/sites-enabled/000-default.conf.orig

Now rename 000-default.conf to apache_reverse_proxy.conf to avoid confusion.

Now edit this /etc/apache2/sites-enabled/apache_reverse_proxy.conf file to define logs location & port. You can see that I am redirecting all incoming HTTP requests to my remote Jenkins server which is running on port 8080.

Please check my GitHub gist to see the contents of this file.

6. Enable the Virtual Host.

Time to enable our newly created Virtual Host.

root@shashank-reverse-proxy-server:/home/shashank# a2ensiteapache_reverse_proxy.conf Enabling site reverse-proxy. To activate the new configuration, you need to run: service apache2 reload root@shashank-reverse-proxy-server:/home/shashank# service apache2 reload * Reloading web server apache2 *

7. Restart Apache.

Configuration is now done & we must restart Apache to load these new settings.

If everything is configured correctly, you will now have a working Reverse Proxy Server.

To test it, log on to your client server 192.168.0.50 & open your browser. Now enter just the IP address of your Reverse Proxy server 192.168.0.51. You will see Jenkins GUI 🙂 Since I am running Apache on its default port 80, I only used192.168.0.51. If it was running on some other port, I would have used 192.168.0.51:port#. See the screenshot below to see Reverse Proxy in action 😉

Jenkins_behind_Reverse_Proxy_server.png

If you stop Apache service & retry opening Jenkins, you will see that its not loading & asks you to check Proxy configuration 😉

As always, you are most welcome if you have suggestions/feedback or you need more information 🙂

How To Receive Emails From Your PHP OpenShift Application Using Swift Mailer & GMail


There are times when we need emails to be sent/received from our PHP applications hosted on OpenShift. My particular example is based on a feedback form that I have put on my own PHP based web-application hosted on OpenShift http://www.howmuchisaved.in. Using this form, I can receive email whenever users submit their feedback to me. If your applications is hosted on your own server, you can easily setup Postfix to start receiving mails but things start to get tricky when you have little to no control on the web-server which runs your application. OpenShift doesn’t allow you to configure Postfix or any such utilities. It even doesn’t give you root access to install & configure dependencies. So, you are left with very little options. And this is where Swift Mailer comes to rescue. In this tutorial I will explain how you can leverage Swift Mailer PHP library to be able to receive mails. Please note that this tutorial can be followed for other restrictive hosting solutions as well.

Requirements : –

For this tutorial, you will need one GMail account (more on it below) & one PHP application hosted on OpenShift or any cloud platform.

Steps to be performed : –

Configure Google Security settings

Since we are using GMail as our SMTP, all emails will be delivered/relayed via smtp.gmail.com. By default, Google doesn’t allow less-secure apps (such as your OpenShift app) to access your account. So, go to https://www.google.com/settings/security/lesssecureapps and turn it on. Its better to create a new Google account & turn this setting on for that particular account. Choice is yours, so act accordingly.

Download Swift Mailer PHP Library

Download it from here https://github.com/swiftmailer/swiftmailer & place it inside your project directory.

Or if you have git installed on your machine, you can always use git clone https://github.com/swiftmailer/swiftmailer inside your project directory. This will create a new directory inside your project. Rename it so that its easy to reference Swift Mailer inside your code. I renamed mine as swiftmailer.

Edit PHP code

Now, with Swift Mailer library in place, edit your PHP script that handles e-mail functionality. Below is the sample code snippet from my GitHub repository’s file.

https://github.com/shashank-ssriva/HowMuchISaved/blob/openshift-version/howmuchisaved/send_feedback_mail.php

You may fork or download my entire project if you so wish 🙂 This version is fully functional and is already hosted on OpenShift (as told in introduction above).

require_once 'swiftmailer/lib/swift_required.php';
      //allow less secured app in Gmail settings for this to work.
      //Also use port 465 and ssl if it doesn't work.
      $transport = Swift_SmtpTransport::newInstance('smtp.gmail.com', 587, "tls")
      ->setUsername('your gmail username')
      ->setPassword('your gmail password');

You can see in the snippet above how you need to organise your Swift Mailer directory. You also need to enter your GMail credentials there.

Test your application

After making changes to the code, fire up your application in a browser & check if you received email from this page or not. Please note the line #22 in above
script. It mentions the email-address where you will be receiving e-mails from your application. Change this to yours if not already done.

That’s it 🙂 If everything has been configured correctly, you will start to receive e-mails from this application. You may use my repository for fully functional application & its code.

Host a PHP/MySQL Application/Dynamic Website on RedHat OpenShift


I had no idea that I can host my PHP/MySQL based application on OpenShift for free until I had to do some work on it a few weeks back. While working on some Automation with OpenShift, I came up with the idea to host my own app on it 😉

If you have a small PHP application or some dynamic PHP based website that you want to host on OpenShift for free, please read on.

So, here is how to do it.

Go to OpenShift Console

Login to your OpenShift account & go to console https://openshift.redhat.com/app/console/applications. You will see a button called “Add Application” like below. Click on it.

Applications___OpenShift_Online_by_Red_Hat.jpg

Choose PHP

Choose any of the PHP versions.

Create_a_New_Application___OpenShift_Online_by_Red_Hat.jpg

Enter a Public URL

Use a unique URL for your application. If you have not created a domain earlier, create one before this step.

Create_a_New_Application___OpenShift_Online_by_Red_Hat.png

Choose Scaling

If you want an HAProxy Loadbalancing, choose Scale with web-traffic as seen above. Please bear in mind that if your application is based on MySQL or any other Database, don’t scale it and go with default. Otherwise you will need to use HAProxy server’s IP or FQDN in your code for database connection string.

Choose Region

Free tier of OpenShift doesn’t allow to choose anything except first 2 options.

Create_a_New_Application___OpenShift_Online_by_Red_Hat.png

Create Application

Click “Create Application” button to create your application. This may take a few minutes.

Get_Started___OpenShift_Online_by_Red_Hat.png

Install Git client for your OS

Install Git client by following instructions shown there on the screen. Also, make note of the git clone command. Using these details, you will clone a repository from OpenShift to your machine. From this repository, you can push to OpenShift after making changes to code. Follow the instructions you see after clicking “Create Application” button.

Add Database gear

Add any of the Database gears. I have personally chosen MySQL 5.5. Make note of the details like database user & password. You will have to use these values in your code.

php___OpenShift_Online_by_Red_Hat.png

Push/Publish Code

Add your code project to this repository (created above). Edit your database connection string in code using the values you got from last step. Then run below commands to push it to OpenShift & restart Apache there. Whenever you make changes to code or add a new file/directory to your local repository, don’t forget to follow below steps.

git add .

git commit -am "adding project data"

git push

You are done 🙂 You will see your application/site hosted on OpenShift now 🙂

I hope you liked this post. Catch you soon with another post!

Install & Setup SonarQube on Ubuntu for Code Analysis


SonarQube is Code Quality testing solution which lets you analyse the quality of your code, detect bugs and much more to improve overall health of your code.

SonarQube comes in 2 variants. It can be accessed online using the URL https://sonarqube.com/ and it can also be hosted on your own server. In this tutorial, I am demonstrating how you can install & setup SonarQube on your own Ubuntu server to check your code’s quality 🙂

Lets start!

Lab Description : –

Ubuntu 14.04 64 bit server with 2 GB RAM.

MySQL version 5.6.33 with InnoDB storage engine.

SonarQube version 6.2.

My PHP project located at DocumentRoot. You can choose any location for code analysis.

Please note that SonarQube needs atleast 2 GB of RAM, so please make sure you have enough of it.

Steps to be followed : –

SonarQube by default uses its internal H2 database but we will be using MySQL for this. Choose any database of your choice.

1. Download SonarQube & SonarQube Scanner.

Use the links provided to download both the products.

2. Unpack them.

Unpack both of them to any location where you can locate them easily. I chose my home-directory for it. It will create 2 directories sonarqube-6.2 & sonar-scanner-2.8.

root@shashank-dbserver:/home/shashank# unzip Downloads/sonarqube-6.2.zip

root@shashank-dbserver:/home/shashank# unzip Downloads/sonar-scanner-2.8.zip

It will be good if you create aliases for above 2 directories or add them to your PATH.

3. Create MySQL Database & User.

Create a new database called sonar in MySQL (or any other DBMS of your choice). Then create a user sonarqube & grant it all privileges for sonar database.

mysql> create database sonar;
Query OK, 1 row affected (0,01 sec)

mysql> use sonar;
Database changed
mysql> CREATE USER 'sonarqube'@'localhost' IDENTIFIED BY 'sonarqube';
Query OK, 0 rows affected (0,02 sec)

mysql> GRANT ALL PRIVILEGES ON sonar.* to 'sonarqube'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

4. Edit sonar.properties file.

Edit this file inside sonarqube-6.2/conf directory to enter database details. Make sure to put user-name & password you created in last step. Below is the snippet. Edit values accordingly.

# DATABASE
#
# IMPORTANT: the embedded H2 database is used by default. It is recommended for tests but not for
# production use. Supported databases are MySQL, Oracle, PostgreSQL and Microsoft SQLServer.

# User credentials.
# Permissions to create tables, indices and triggers must be granted to JDBC user.
# The schema must be created first.
sonar.jdbc.username=sonarqube
sonar.jdbc.password=sonarqube

#----- Embedded Database (default)
# H2 embedded database server listening port, defaults to 9092
#sonar.embeddedDatabase.port=9092
#----- MySQL 5.6 or greater
# Only InnoDB storage engine is supported (not myISAM).
# Only the bundled driver is supported. It can not be changed.
sonar.jdbc.url=jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useConfigs=maxPerformance

5. Create & Edit sonar-project.properties file.

Create this file inside your code’s project & enter values accordingly. See below snippet. Give your project a unique Project Key for SonarQube to uniquely identify it.

# must be unique in a given SonarQube instance
sonar.projectKey=exclaimadeasy
# this is the name and version displayed in the SonarQube UI. Was mandatory prior to SonarQube 6.1.
sonar.projectName=ExClaiMadEasy
sonar.projectVersion=1.0

# Path is relative to the sonar-project.properties file. Replace "\" by "/" on Windows.
# Since SonarQube 4.2, this property is optional if sonar.modules is set.
# If not set, SonarQube starts looking for source code from the directory containing
# the sonar-project.properties file.
sonar.sources=.

# Encoding of the source code. Default is default system encoding
#sonar.sourceEncoding=UTF-8

6. Start SonarQube.

Start it by executing sonarqube-6.2/bin/sonar.sh start command.

root@shashank-dbserver:/home/shashank/sonarqube-6.2/bin/linux-x86-64# ./sonar.sh start
Starting SonarQube...
Started SonarQube.

7. Access SonarQube via browser.

Open your browser & enter localhost:9000. Then click login at the top-right corner. Credentials are admin/admin.

8. Start SonarQube Scanner to analyse your code.

Execute below command to start SonarQube scanner from within your project directory.

root@shashank-dbserver:/var/www/bills/html/CabBIlls# /home/shashank/sonar-scanner-2.8/bin/sonar-scanner

It will start scanning your project’s code. Once its done scanning, you will see output similar to below. Click the link provided there to see your report.

INFO: Analysis report uploaded in 240ms
INFO: ANALYSIS SUCCESSFUL, you can browse http://localhost:9000/dashboard/index/exclaimadeasy
INFO: Note that you will be able to access the updated dashboard once the server has processed the submitted analysis report
INFO: More about the report processing at http://localhost:9000/api/ce/task?id=AVqskPfd6DjWymbXBiOQ
INFO: Task total time: 18.806 s
INFO: ------------------------------------------------------------------------
INFO: EXECUTION SUCCESS
INFO: ------------------------------------------------------------------------

Video Tutorial : –

I have also posted a video on my YouTube channel to demonstrate all the steps. You can watch it below.

Caveats : –

Video shown above only demonstrates the basic code analysis, even though my project is PHP based. For PHP projects (or any other non-default languages), please download the plugin(s) and place that in SonarQube_HOME/extensions/plugins directory. After that restart SonarQube by executing sonar.sh restart command. PHP plugin can be downloaded from https://docs.sonarqube.org/display/PLUG/SonarPHP

Also, in the video above, I missed to uncomment MySQL jdbc conncection URL but same can be seen uncommented in snippet I pasted in step 3 😉

I hope you liked this post. See you later 🙂

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 🙂