Thursday, January 12, 2012

MySQL backup in time

Run mysqldump on master database (needs to have innodb)

# replication point in time
mysqldump --single-transaction --flush-logs \
--master-data=2 --all-databases > backup.sql

# Note the position and the log file from the backup.sql and insert it into the slave</>

shell# mysql -u USER -p PASS
mysql> stop slave;
shell# mysql < backup.sql
shell# mysql -u USER -p PASS 
mysql> CHANGE MASTER TO MASTER_LOG_FILE='the_log_file_written_into_dump',
mysql> MASTER_LOG_POS = xxx ;
mysql> start slave;