Had to resync master-slave replication setup. Here are my notes on how it’s done.
Binary bin-log files are kept for 7 days on DB1. If the replication is stopped for more than a week DB2 replication cannot start again due to the binary log files is no longer available. therefor a fresh dump is needed and DB2 replication can be started again from the master log position.
Procedure:
Parameters:
- Single-transaction, makes it possible to do the dump without locking the database, very useful when having to dump from a production database. But while not locking the DB you may not create or alter table schema. Mysql documentation link
- master-data, is very useful because it records the master position when doing the dump and putting it into the output of the dump file. Therefore it is much easier to start the slave from the correct position. The number 2 is for only printing it to the output as a comment. Mysql documentation link
- event and routines, if there are any stored procedures or like in the old server we take them with us. Mysql documentation link
mysqldump --single-transaction --quick --master-data=2 --events --routines <DATABASE> | gzip > /data/<DATABASE> _`date +%F`.sql.gz
When the dump is done we move the dump file over to the other server. Here we import it to the MySQL server if there already were an old database in place, drop it and create it again. zcat <DATABASE>.sql.gz | mysql <database>
Also, have a look at the head of the dump file where we will find the master position data that we need to start the replication again.
gzip -cd <DATABASE>.sql.gz | head -n24
Now we have the position and need the user for replication. I did it on an older 5.5 database, in newer MySql servers it is done differently.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'happyS3ed99'';
Or if the user is in place and you just need to reset the password:SET PASSWORD FOR ‘repl’@‘192.168.10.11’ = PASSWORD('happyS3ed99'); FLUSH PRIVILEGES;
When it is imported we need to setup the master to master(slave) replication again. Remember to have a user on DB1 that is allowing replication from the DB2 server and have the user and password ready CHANGE MASTER TO MASTER_HOST='<IP>',MASTER_USER='repl', MASTER_PASSWORD='happyS3ed99', MASTER_LOG_FILE='mysql-bin.000849', MASTER_LOG_POS=758329777 ;
It will now start to replicate from the master, now you can do a “mysql -e ‘show slave status\G'” and see if the slave IO is running as it should.
