Monthly Archives: April 2016

  • 0

How To Set Up Master Slave Replication in MySQL

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data, a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and one of the slaves.

This section explains how to configure MySQL replication with either the Master-Slave model (M-S) or the Master-Master model in Active-Passive mode (M-M-A-P), and describes how to recover from failures in each case.

  • A lot of the following instructions assume that you are using InnoDB for all your MySQL tables. If you are using some other storage engine, other changes might be required and it would be best to verify with a DBA. InnoDB is the recommended storage engine for storing Hive metadata.
  • Assume we want to as the primary master and as the slave (or secondary passive master in the M-M-A-P case). Let’s say we have installed Hive so as to use server-A as its metastore database, as one would when installing with the HDP installer, and we’ve simply installed MySQL (using yum or otherwise) on server-B, but done nothing else.

Master-Slave Replication

  1. Shut down the mysql server process if it is running.

           /etc/init.d/mysqld stop

  1. Edit the cnffiles with the following values(vi /etc/my.cnf)






  1. Bring up the mysql server process again — this step may vary based on your installation; in a typical RHEL setup, I can use the system service startup for mysql as follows:

        /etc/init.d/mysqld start

  1. To verify that the server is now logging bin-logs, you can use the SQL command: “SHOW MASTER STATUS;”. It should show you a binlog filename and a position.
  2. Make sure that your current user is able to do a dump of the MySQL database, by running the following as a mysql-root-capable user, for example, on a default installation, “root”.

 GRANT ALL ON *.* to ‘root’@’’;

Also, create a replication user that will be used to conduct future replications:


  1. Run mysqldumpto dump all tables from the master and load them onto the slave as follows:

 mysqldump -u root -p –single-transaction –all-databases –master-data=1 > dump.out

  1. Copy this dump.out file over to the server-B.

         scp dump.out w1.hdp22:

Then, on

  1. Shut down the mysql server process if it is running.

        /etc/init.d/mysqld stop

  1. Edit the my.cnf files with the following values (vi /etc/my.cnf)

     log_bin = mysql-bin

       binlog_format = ROW

       server_id = 11

       relay_log = mysql-relay-bin

       log_slave_updates = 1

       read_only = 1

  1. Bring up the mysql server process.

       /etc/init.d/mysqld start

  1. Make sure that your current user is able to load the prepared dump of the MySQL database, by running the following as a mysql-root-capable user, for example, on a default installation, “root”.

mysql> create user ‘root’@’w1.hdp22’ identified by ‘hadoop’;

         grant all on *.* to ‘root’@’w1.hdp22’;


  1. Load the dump that was dumped out by mysqldumpby running the following:

 mysql –u root –p < dump.out

  • Verify that the metastore database was transferred over by running a ‘SHOW DATABASES’ call on MySQL.
  • Look through the MySQL dump file, and locate values for MASTER_LOG_FILE and MASTER_LOG_POS. We will need to specify values for these to start replication on the slave. Assuming these values were ‘mysql-bin.000001’ and the position was 0, then to copy new entries from the master, run the following:

mysql> CHANGE MASTER TO MASTER_HOST=’m2.hdp22′,MASTER_USER=’replication’,MASTER_PASSWORD=’hadoop’,MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=4507;

Note that these values can also be obtained from the master by running ‘SHOW MASTER STATUS’ on it.

  • Restart the mysql server.

 /etc/init.d/mysqld restart

  • Check that the replication is correctly configured by running


Note: It should show correct values as set previously for Master_User and Master_Host. If the slave is caught up to the master, then this field will show a value for Seconds_Behind_Master as being 0.


And with that, you now have M-S replication set up.

  • 0

hdfs balancer gets failed after every 30 mins when you run it through ambari

Actually there is still a bug in ambari 2.2.0, whenever you run balancer though ambari and it has to balance lots of TBs data then it fails after 30 mins due to timeout.

You can see following error in your logs:

resource_management.core.exceptions.Fail: Execution of ‘ su hdfs -l -s /bin/bash -c ‘export PATH='”‘”‘/usr/sbin:/sbin:/usr/lib/ambari-server/*:/sbin:/usr/sbin:/bin:/usr/bin:/var/lib/ambari-agent:/usr/hdp/current/hadoop-client/bin'”‘”‘ ; hdfs –config /usr/hdp/current/hadoop-client/conf balancer -threshold 10” returned 252. 16/03/08 08:42:03 INFO balancer.Balancer: Using a threshold of 10.0
16/03/08 08:42:03 INFO balancer.Balancer: namenodes = [hdfs://HDPDEVHA]
16/03/08 08:42:03 INFO balancer.Balancer: parameters = Balancer.BalancerParameters [BalancingPolicy.Node, threshold = 10.0, max idle iteration = 5, #excluded nodes = 0, #included nodes = 0, #source nodes = 0, #blockpools = 0, run during upgrade = false]
16/03/08 08:42:03 INFO balancer.Balancer: included nodes = []
16/03/08 08:42:03 INFO balancer.Balancer: excluded nodes = []
16/03/08 08:42:03 INFO balancer.Balancer: source nodes = []
Time Stamp Iteration# Bytes Already Moved Bytes Left To Move Bytes Being Moved
16/03/08 08:42:04 INFO balancer.KeyManager: Block token params received from NN: update interval=10hrs, 0sec, token lifetime=10hrs, 0sec
16/03/08 08:42:04 INFO block.BlockTokenSecretManager: Setting block keys
16/03/08 08:42:04 INFO balancer.KeyManager: Update block keys every 2hrs, 30mins, 0sec Another Balancer is running.. Exiting …
Mar 8, 2016 8:42:04 AM Balancing took 1.27 seconds
Last login: Tue Mar 8 08:12:09 EST 2016


So to resolve this error, you have to change file on ambari server node, after that you have to restart ambari server and run the balancer from ambari.

$ vi /etc/ambari-server/conf/