2. Configuring and Using Replication

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.

2.1. Disclaimer and Assumptions

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 use server-A.my.example.com as the primary master and server-B.my.example.com 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.

2.2. M-S Replication

2.2.1. Setup of Master-Slave Replication

First, on server-A.my.example.com:

Shut down the mysql server process if it is running.
```
> mysqladmin shutdown
```

Edit the my.cnf files with the following values:

log_bin=mysql-bin
binlog_format=ROW
server_id=10
innodb_flush_logs_at_trx_commit=1
innodb_support_xa=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:
```
> service mysql start
```
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.

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

CREATE USER ‘root’@’server-A.my.example.com’ identified by ‘p4ssw0rd’;
GRANT ALL ON *.* to ‘root’@’server-A.my.example.com’;

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

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'server-B.my.example.com'\
  IDENTIFIED BY 'r3plpwd';

Run mysqldump to dump all tables from the master and load them onto the slave as follows:
```
> mysqldump --single-transaction --all-databases --master-data=1 --host=server-A.my.example.com > dump.out
```
(You may need to specify -pp4ssw0rd to specify the password.)
Copy this dump.out file over to the server-B.

Then, on server-B.my.example.com:

Shut down the mysql server process if it is running.

Edit the my.cnf files with the following values:

log_bin = mysql-bin
binlog_format = ROW
server_id = 11
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

Bring up the mysql server process.
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".
```
create user 'root'@'server-B.my.example.com' identified by 'p4ssw0rd';
grant all on *.* to 'root'@'server-B.my.example.com';
```
Load the dump that was dumped out by mysqldump by running the following:
```
> mysql --host=server-B.my.example.com -pp4ssw0rd < dump.out
```
Verify that the metastore database was transferred over by running a 'SHOW DATABASES' call on MySQL.
Look through the MySQL dump, 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:
```
CHANGE MASTER TO MASTER_HOST='server-A.my.example.com', MASTER_USER='repl',\
  MASTER_PASSWORD='r3plpwd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
```
Note that these values can also be obtained from the master by running 'SHOW MASTER STATUS' on it.
Restart the mysql server.
Check that the replication is correctly configured by running
```
SHOW SLAVE STATUS;
```
or, for increased readability:
```
SHOW SLAVE STATUS\G
```
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.

2.2.2. What To Do in the Case of a Master Failure

Stop all writes on the existing master — for example, shut down the Hive metastore server and stop any Hive processes that connect directly to the database.
If the master is still accessible, you can flush the tables to make sure that everything has been written out to logs with the command 'FLUSH TABLES WITH READ LOCK' and set the master to read_only.
Make sure that replication in the slave is caught up to the transactions recorded by the master by checking 'SHOW SLAVE STATUS'.
Run 'STOP SLAVE' on the slave.
Remove existing master settings for replication by running:
```
CHANGE MASTER TO MASTER_HOST=''
```
Note the new master's binary log coordinates with 'SHOW MASTER STATUS'.
Configure a new slave to replicate from this new master in a manner similar to how we configured the M-S setup before.
Change hive-site.xml to point to the new master.
If you intend to bring back the old master in this rotation, it can easily be configured to be a slave of the new master.
Turn off the read-only flag on the new master (old slave).

2.3. M-M-A-P Replication

2.3.1. Configuration for Master-Master-Active-Passive

Follow the same M-S setup instructions as above, including log_bin, binlog_format and log_slave_updates as recommended. While these settings were not essential for a typical M-S setup, they are needed for an M-M-A-P setup.
Verify that both machines are in sync, that one-way replication is working.
Configure the server-A's master as server-B, in a manner similar to how we configured server-B's master as server-A. To wit, as follows:
On server-B, after verifying sync:
```
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'server-A.my.example.com' IDENTIFIED BY 'r3plpwd';
SHOW MASTER STATUS;
```
Let’s say, for example, that we see that the replication file is mysql-bin.000004 and the position is 296. Then, we go to server-A, and run the following:
```
CHANGE MASTER TO MASTER_HOST='server-B.my.example.com', MASTER_USER='repl', MASTER_PASSWORD='r3plpwd',\
MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=296;
START SLAVE;
```

That's all there is to it — you're set. If you still don’t see a slave process running, you might need to restart the slave database as a means of ensuring all settings being flushed and loaded.

2.3.2. Switching Active and Passive Roles in the M-M-A-P Setup

This is a simple matter of doing the following:

Shut down the applications (metastore server) so as to not perform any new writes to the database.
Switch the "active" master to read-only.
Wait for the "passive" master to be caught up to replicating all updates from the old "active" master.
Switch the passive master's read-only flag to off.
Switch config in the applications (metastore server) to point to the old passive/new active master and restart the application.

That's it — this switches which master is active and which is passive.

2.3.3. Recovering from Failure When the Active Master in an M-M-A-P Fails

If the old master still has not had any data corruption, it should recover gracefully when its replication from the new master is caught up.
If there was data loss/data corruption, proceed to set up a database as if it were a fresh slave in the M-S case, and/or the secondary master in the M-M-A-P case, and continue.

Legal notices