2

I have a MySQL master-slave replication pair, and to take backups, I run a script that stops the slave, dumps the database, and then re-starts the slave. However, after restarting, the slave's MySQL daemon (mysqld) fails due to corruption in the /var/lib/mysql/mysql.index file - namely, "^@" characters prepended to the index file name on the last line, making that index file unreadable. Simply removing these characters allows MySQL to start normally, and replication then continues correctly. There is no data corruption in the actual database tables, just in this single index file.

The relevant portions of the backup script (redacted):

mysqladmin -uroot -p${PASSWORD} stop-slave >> $LOGFILE

# Lock the database
mysql -uroot -p${PASSWORD} -e 'FLUSH TABLES WITH READ LOCK'

# Backup live accounts
mysqldump -uroot -p${PASSWORD} --routines ${LIVEDB} > ${BACKUPDIR}/${LIVEDB}.sql
mysqldump -uroot -p${PASSWORD} --routines ${OTHERDB} > ${BACKUPDIR}/${OTHERDB}.sql

# Unlock the database 
mysql -uroot -p${PASSWORD} -e 'UNLOCK TABLES' >> $LOGFILE

# Start replication
mysqladmin -uroot -p${PASSWORD} start-slave >> $LOGFILE
mysql -uroot -p${PASSWORD} -e 'SHOW SLAVE STATUS\G' >> $LOGFILE
echo `date` Database backup ends >> $LOGFILE

What is causing this corruption and how do I prevent it from happening?

  • Why stopping the slave in the first place ? mysqldump needs running server anyway, unless you're taking OS-level file backups. Don't stop it, take mysqldump backups. – drookie Feb 27 '16 at 21:02
  • I've edited the subject line because it's just wrong. Replication cannot break the index file by design, furthermore, even from your story it doesn't look so. – drookie Feb 27 '16 at 21:04
  • 1
    How do you stop the slave? If you do a mysql server after that, I assume that you run STOP SLAVE in mysql, but I don't want to guess on what you do. But, as @drookie said: you don't need to stop the slave in order to do that. Also, please post the complete (you must omit sensitive information) error that you get. It might help. – Nuno Pereira Feb 27 '16 at 22:36
  • Could you show the backup script? – banyek Feb 28 '16 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.