We take db backup by stopping the database and then issuing a
tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error log reports that a number of tables have crashed with messages similar to the following:
140221 19:09:49 [ERROR] /usr/sbin/mysqld: Table './adplatform/aggregate_app' is marked as crashed and should be repaired 140221 19:09:49 [Warning] Checking table: './adplatform/aggregate_app' 140221 19:09:49 [ERROR] 1 client is using or hasn't closed the table properly
Trying to recover the table using myisamchk results in the following for a particular table:
$ myisamchk -o -f -r -v --key_buffer_size=4G --myisam_sort_buffer_size=4G --read_buffer_size=2G --write_buffer_size=2G aggregate_app Warning: option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147483647 Warning: option 'write_buffer_size': unsigned value 2147483648 adjusted to 2147483647 - recovering (with sort) MyISAM-table 'aggregate_app' Data records: 337215562 - Fixing index 1 - Searching for keys, allocating buffer for 49367435 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 252645129 keys - Last merge and dumping keys myisamchk: Unexpected eof found when reading file '/tmp/ST5BRnZ9' (Errcode: 175) myisamchk: error: 175 when fixing table MyISAM-table 'aggregate_app' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
As you can see, I'm using the -o and the -f options and not using the -q option. Is there any way to repair this table?