I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS.

Every night we do a backup of all the databases with this script:

rm -rf /var/backup/mysql/ *
for i in $(echo 'SHOW DATABASES;' | mysql -u root -p'password' |grep -v '^Database$'); do
mysqldump \
-u root -p'password' \
-Q -c -C --add-drop-table --add-locks --quick --lock-tables \
$i > /var/backup/mysql/$i.sql;
tar -cpzf /var/backup/mysql/$i.sql.tar.gz /var/backup/mysql/$i.sql;
rm -rf /var/backup/mysql/$i.sql
done;

Every time the backup is executed a particular MySQL table is broken (the MYD and MYI files are deleted, while the FRM remains).

We have many databases and tables, only this one is broken.

Any idea?

This might seem too obvious but:

rm -rf /var/backup/mysql/ *

Space between / and * means there's two seperate deletes. So you're deleting the contents of /var/back/mysql/ and the contents of *. i.e. Everything in the current location. Could that be doing it?

  • That's a good catch. No matter where this script is run, it is dangerous and wrong and something will be deleted unexpectedly... but it seems like the /var/backup/mysql directory is still there, and that command, as shown, should remove it, no? Still, +1... at any rate, a backup simply would not remove a MyISAM tablespace in any circumstance I can think of, and I'd also note that the exit status of mysqldump is not being checked as it should be. – Michael - sqlbot Mar 14 '14 at 0:17
  • rm -rf /var/backup/mysql/ * won't remove the /var/backup/mysql/ directory if the user running the rm command doesn't have write permissions in /var/backup directory. – Tero Kilkanen Mar 14 '14 at 0:38
  • 1
    Sorry, the script runs "rm -rf /var/backup/mysql/*" but I've separated this because Stackexchange was recognizing it as a comment (/* */). That's not the problem. – Dimas Mar 14 '14 at 7:53

We need an important information : table type ... (MyIsam or InnoDb)

Maybe your table is corrupted and you need to do a check over your table (and your database). It may be occur by a corrupted datafile (*.frm, *.myd or *.myi) due to a bug, a filesystem error or ???

Have you tried to backup this table alone ? If same problem occur again corruption must be investigated.

To search about this (and correct it), you have to run a "check table" command

mysql> use <database_name>
mysql> check table <table_name>

See at http://dev.mysql.com/doc/refman/5.1/en/check-table.html for more informations about this command and options.

This command may output a lot of row. You must check about the last one (msg_type). You have to be worried if msg_type value isn't equal to

Table is already up to date

If table type is InnoDb next step is use innochecksum command from command line while server is stopped.

shell> innochecksum [options] file_name

See at http://dev.mysql.com/doc/refman/5.1/en/innochecksum.html for more informations about this command and options.

Hope that this will help you

  • Thx for the comment. It's a MyIsam table. I will try to backup the table alone. The check_table after it got corrupted isn't possible. – Dimas Mar 14 '14 at 8:02
up vote 1 down vote accepted

Solved! Finally I tried to rename the table and now it stops getting corrupted every day! It seems that MySQL have some internal registry indicating a problem with the previous table name.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.