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?
