How To Set Up Master Slave Replication in MySQL
Category : Bigdata
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 useserver-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.
Master-Slave Replication
- Shut down the mysql server process if it is running.
/etc/init.d/mysqld stop
- Edit the cnffiles with the following values(vi /etc/my.cnf)
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:
/etc/init.d/mysqld 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”.
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 ‘replication’@’server-B.my.example.com’ IDENTIFIED BY ‘r3plpwd’;
- 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
- Copy this dump.out file over to the server-B.
scp dump.out w1.hdp22:
Then, on server-B.my.example.com:
- Shut down the mysql server process if it is running.
/etc/init.d/mysqld stop
- 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
- Bring up the mysql server process.
/etc/init.d/mysqld start
- 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’;
- 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
SHOW SLAVE STATUS;
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.