So I come across an issue where I had to reset replication master in MySQL 5.1 (but it is the same I believe in later 5.X versions as well)
There is great blog here and below is the core of the steps needed:
Login on the slave as administrator
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST="master_name_or_address", MASTER_USER="administrator_username", MASTER_PASSWORD="administrator_password", MASTER_LOG_FILE="logfile_from_master", MASTER_LOG_POS=position_from_master;
START SLAVE;
and a bit later verify using
SHOW SLAVE STATUS\G
To get the position and filename for the CHANGE MASTER command use the followin on the master server:
SHOW MASTER STATUS;
Any more details see the blog mentioned earlier.
Cheers
I found this of use to resync databases after hostname change
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.
Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:
mysqldump -uroot -p --all-databases > /a/path/mysqldump.sql
Now you can release the lock, even if the dump hasn’t end. To do it perform the following command in the mysql client:
UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.
At the slave:
Open a connection to mysql and type:
STOP SLAVE;
Load master’s data dump with this console command:
mysql -uroot -p < mysqldump.sql
Sync slave and master logs:
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.
Finally type
START SLAVE;
And to check that everything is working again, if you type
SHOW SLAVE STATUS;
you should see:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Just keep in mind that changing passwords for the account used can cause problems if it is forgotten. I found that doing this on the slave will set the password to something known:
GRANT REPLICATION SLAVE ON *.* TO replicant@<> IDENTIFIED BY '<>';
L8tr