-----------------------------------------------
MASTER:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
UNLOCK TABLES;
Copy mysql dump file to server 2
(scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)
SLAVE:
STOP SLAVE;
mysql -uroot -p < /tmp/mysqldump.sql
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE IO_THREAD;
copy mysql dump from master
mysql -uroot -p < /tmp/mysqldump.sql
START SLAVE SQL_THREAD;
That should start the downloading of binary logs while you're doing the restore, so you don't miss out on any.
By restoring the binary Log
---------------------------
1) Take the Binary log necessary for the replication to work from "Show Slave Status"
2) Restore the binary logs from backup
MASTER:
a. Restore the logfiles in any location where you have space
b. Add the file names in mysql-bin.index for the master to know the binary log info
c. login to master database and see if binary logs are shown while you query "show binary logs", if you dont see the bin logs run "FLUSH LOGS" and now you should see the bin log files.
Once you see the binary log files in database
3) Stop / Reset the replication and start from the missing logfile
SLAVE:
stop slave;
reset slave;
show warnings;
change master to master_host ='10.99.3.21', master_user='replicator', master_password='pwd', master_log_file='mysql-bin.018736', master_log_pos=47904313; -- These information can be retreived from slave server /mysqldata/mysql/mysql-master.info
start slave;
show slave status \G
