MySQL Replication

Replication is a process where we will be having a Master and a Slave Server. The master server should be connected to slave where the binary logs are send to the slave server on the fly and it sync with the slave.

f the primary server goes down then we have to manually bring the slave server up and making the changes in application to connect to the slave server.

Step 1:On Master Server. Perform the below steps.

1. Edit My.cnf in Master Server

Please add the below command on the Master Server of MySQL.

server-id=1

log_bin=/var/log/mysql/mysql_bin.log

2. Refresh MySQL

sudo service mysql restart;

3. Connect to MySQL Server – Master.

# mysql -u root -p

Enter the password:******

Now we are connected to the MySQL.

Give the permissions to the ‘slave_user’.

Mysql>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’192.168.1.104′ IDENTIFIED BY ‘Coffee$123′;

mysql>Flush privileges;

mysql>USE Manish;

mysql>Flush Tables with Read Lock;

mysql> show master status;

Now we have to take the note of the Master_Log_File and Master_Log_Pos values that are used later to connect to the slave server.

On another terminal connect to the server. And now we are going to take the backup of the database.

# mysqldump -u root -p –opt Manish > /data/Manish_backup.sql

On the Mysql 1 Terminal. Issue the below command.

mysql> UNLOCK TABLES;

Mysql> exit

Step 2: On Slave Server. Perform the below steps.

Connect to the slave server

# mysql -u root -p

Enter the password: 

Now we are connected to the MySQL.

mysql> Create database Manish;

mysql> exit.

Now we are going to restore the database with the backup.

# mysql -u root -p Manish <  /data/Manish_backup.sql

Enter Password:

Please enter the password to connect to the server and after that the database will be restored on the serve.

Now Edit the My.cnf on Slave Server.

# sudo nano /etc/mysql/my.cnf

Please add the below lines.

server-id=2

Now restart the MySQL on Slave server.

sudo service mysql restart

Now connect to the slave server by

# mysql -u root -p 

Issue the below commands;

mysql> change master to MASTER_HOST=’192.168.1.103′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’Coffee$1234′, MASTER_LOG_FILE=’mysql_bin.000023′, MASTER_LOG_POS=107;

mysql> START SLAVE;

mysql>SHOW SLAVE STATUS;

In the slave status output. Please check the SLAVE_IO_RUNNING and SLAVE_SQL_THREAD should be to Yes and Seconds_Behind_Master to 0.

If the above both thread are running on the slave then the replication is configured correctly.

If the Seconds_Behind_to_master is 0 then the slave is sync with the master server.

 Now we can enjoy with the replication configuration and slave will be connecting to the master and always be in sync with it.