Reset replication address in MySQL slave

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

2 comments

  • Boris

    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

  • Lela

    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

Leave a Reply

Your email address will not be published. Required fields are marked *