MySQL Replication

How To Set Up Database Replication In MySQL

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.
In this tutorial I will show how to replicate the database exampledb from the master with the IP address 192.168.0.100 to a slave. Both systems have MySQL installed, and the database exampledb (with all objects – tables with data, views etc) on the master, but not on the slave.

1 Configure The Master

1. 1. Edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking
#bind-address = 127.0.0.1

1. 2. Tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1

1. 3. Then we restart MySQL:

/etc/init.d/mysql restart

Then we log into the MySQL database as root and create a user with replication privileges:
mysql -u root –p<>

Create Users with below command - 
GRANT SUPER,REPLICATION CLIENT,RELOAD, REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)

FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
You will get status like below –

|| +----------+----------+--------------+----------------| File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------|mysql-bin | 183 | exampledb | |.006 +---------------+----------+--------------+------------- 1 row in set (0.00 sec) ||
Note the above info which be require for slave configuration and quit.
quit;

---

There are below two possibilities to get the existing tables and data from exampledb from the master to the slave. 

1. Take export Dump
2. OR - Use the LOAD DATA FROM MASTER; command on the slave.

The latter will be lock the database during this operation, 

Take export dump using below command – 

mysqldump -u root -p<password> --opt exampledb> exampledb.sql (Replace <password>

This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server.

Unlock all table and quit - 
unlock the tables in exampledb:
mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;


2 Configure The Slave
On the slave we first have to create the database exampledb:
mysql -u root -p

Enter password:

CREATE DATABASE exampledb;

quit;


1. Import the SQL dump into our newly created exampledb on the slave:
mysql -u root -p<password> exampledb </path/to/exampledb.sql>

2. Config MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb
3. Then we restart MySQL:
/etc/init.d/mysql restart

---

If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:
mysql -u root -p

Enter password:

LOAD DATA FROM MASTER;

quit;

---

1. Stop Slave

mysql -u root -p<>
SLAVE STOP;

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

• MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
• MASTER_USER is the user we granted replication privileges on the master.
• MASTER_PASSWORD is the password of MASTER_USER on the master.
• MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
• MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Restart the slave
START SLAVE;
quit;

Over and Beer !!!! exampledb is updated on the master, all changes will be replicated to exampledb on the slave.

Useful commands -
1. show slave status;
2.