MySQL Database Replication

Today Database system are very important to business and organization which provide an efficient method to handle multiple types of data such as employee records, student information, accounting information, inventory details and library books. It is important to prevent total data loss. One solution is database replication which keeps a copy of database on a remote server. It will be easy to retrieve database from remote server if the primary database server fail.

In this blog I’m going to show simple step by step guide for setting up MySQL Database Replication (Master-Slave).

For that we need two servers Master and Slave, with IPs 10.0.0.1 and 10.0.0.2 respectively. Let’s find out how the stuff will work.

Configuring the master
———————

Edit the /etc/my.cnf on the Master server
server-id=1

#skip-networking

#bind-address = 127.0.0.1

log-bin = /var/lib/mysql/mysql-bin

binlog-do-db=DATABASE_TO_BE_REPLICATED

now restart MySQL
service mysqld restart

Login to MySQL as root user and run following command.
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘replication_password’;

mysql> FLUSH PRIVILEGES;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;
+——————-+———-+———————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————-+———-+———————–+——————+

| mysql-bin.000010 | 237874 | DATABASE_TO_BE_REPLICATED | |

+——————-+———-+———————–+——————+

*FLUSH TABLES WITH READ LOCK which prevent any new changes ie, nothing can write to the database during dump process

You must dumb the database which want needs to be replicated and will be copied to slave

mysqldump -u root -p DATABASE_TO_BE_REPLICATED > DATABASE_TO_BE_REPLICATED.sql

Now unlock the database

mysql> UNLOCK TABLES;

mysql> QUIT

Copy dump file to the slave server.
scp DATABASE_TO_BE_REPLICATED.sql root@10.0.0.2:/usr/local/src/

Configuring the slave
———————

Edit the /etc/my.cnf on the Slave server
server-id=2

log_bin=/var/lib/mysql/mysql-bin

master-host= 10.0.0.1

master-user= slave-user

master-password=replication_password

master-connect-retry= 60

Restore the imported file.
mysql –u root –p < /usr/local/src/ DATABASE_TO_BE_REPLICATED.sql now restart MySQL service mysqld restart Login to MySQL as root user and run following command. mysql> SLAVE STOP;

mysql> CHANGE MASTER TO MASTER_HOST=’10.0.0.1′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’replication_password’, MASTER_LOG_FILE=’master-bin.000010′, MASTER_LOG_POS=237874;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

On Master server create a table and insert some value in it and verify that it in replicated to slave.

That’s it.