Mysql Replication - Master and Slave

Master Server (Primary) 192.168.1.1

Slave Server (Secondary) 192.168.1.2

Now we have to enable logging of the data on the Master, update the slave to copy the log updates and apply it on to slave.

1) Create user on primary for replication, i.e the credential utilized by slave to connect to the primary copy the log updates.

On Master

mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replica@'192.168.1.2' IDENTIFIED BY 'replica';

Stop the master. and include the following in the configuration file my.cnf

log-bin=mysql-bin // Enable Binary Log file.

server-id=1 //ID of the master server.

Configuration on the Slave Server

server-id=2 //ID of the Slave server

Start Both the Server Master and Slave.

Taking backup from master and restoring on to slave

mysqldump -uroot -p<password> -hlocalhsot --all-database --master-data=2 > "D:\db.sql"

Copy the file on the Slave and Import the backup.

mysql -uroot -p<password> < "E:\db.sql"

Now To start the replication process.

Take the information from the Master.

mysql>Show master status;

It will information about the File, Position.

Now logon to the Slave Server.

issue the following command.

mysql>change master to master_host='192.168.1.1', master_user='replica', master_password='replica', master_log_file='mysql-bin.000002', master_log_pos=106;

After above command, the following files get created on the slave server.

#master_relay.index

Then issue the last command to start the replciation.

mysql>start slave;

If you face any issue after issuing the second-last command issue, reset slave i.e

mysql>reset slave;

then try again.