Re-syncing broken MySQL databases replication

How do I configure a slave if the master is running and I do not want to stop it?
There are several possibilities. If you have taken a snapshot backup of the master at some point and recorded the binary log filename and offset (from the output of SHOW MASTER STATUS) corresponding to the snapshot, use the following procedure:

1. 
   Make sure that the slave is assigned a unique server ID.
   
2. 
   Execute the following statement on the slave, filling in appropriate values for each option:
   

   mysql> CHANGE MASTER TO
   ->     MASTER_HOST='master_host_name',
   ->     MASTER_USER='master_user_name',
   ->     MASTER_PASSWORD='master_pass',
   ->     MASTER_LOG_FILE='recorded_log_file_name',
   ->     MASTER_LOG_POS=recorded_log_position; 

3. 
   Execute START SLAVE on the slave.
   

If you do not have a backup of the master server, here is a quick procedure for creating one. All steps should be performed on the master host.

1. 
   Issue this statement to acquire a global read lock:
   

   mysql> FLUSH TABLES WITH READ LOCK; 

2. 
   With the lock still in place, execute this command (or a variation of it), you will have to use another login console to execute command below, as once you left the mysql console, the 'READ LOCK' is unlocked:
   

   shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql 

3. 
   Issue this statement and record the output, which you will need later:
   

   mysql> SHOW MASTER STATUS; 

4. 
   Release the lock:
   

   mysql> UNLOCK TABLES; 

An alternative to using the preceding procedure to make a binary copy is to make an SQL dump of the master. To do this, you can use mysqldump --master-data on your master and later load the SQL dump into your slave. However, this is slower than making a binary copy.
Regardless of which of the two methods you use, afterward follow the instructions for the case when you have a snapshot and have recorded the log filename and offset. You can use the same snapshot to set up several slaves. Once you have the snapshot of the master, you can wait to set up a slave as long as the binary logs of the master are left intact. The two practical limitations on the length of time you can wait are the amount of disk space available to retain binary logs on the master and the length of time it takes the slave to catch up.

Case Study:

In this case study, I assume that you have the replication being done correctly before. User permission and mysql settings has been properly configured.

I have a single master-slave database replication, and the slave's data was not synchronized with the master data. To solve this, we need to perform steps below to re-sync the data.

Step 1. ( on master server )

# mysql -p -u root
> use exampledb;
> flush tables with read lock;
> show master status;
( you need to write down the 'File' and "Position' displayed for later usage. )

( now open another console to dump the database out. Make sure the bin file and the position was the same before and after you dumped the database out. )
# mysqldump - u root -p exampledb > exampledb.sql

> unlock tables;
> quit;

Step 2. ( on slave server )

( you will need to copy over the exampledb.sql file from master to this slave server first. )

# mysql -u root -p
> slave stop;
> show slave status;
> quit;

# mysqladmin -u root -p create exampledb
# mysql -u root -p exampledb <> 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=1123;
> start slave;
> show slave status;
> quit;

Now you need to try to update some data from the master and check whether it was replicated to the slave server. The easiest way is to create a new test database.

Step 3. ( on master server )
> mysqladmin -u root -p create test1

Step 4. ( on slave server )
You need to check on the data directory for the newly created database and check the mysql error log.

If everything replicated, then your job has done!!


Reference:
http://homepage.mac.com/kelleherk/iblog/C711669388/E226281480/index.html
http://www.howtoforge.com/mysql_database_replication_p2
http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html