Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner :

```
mysql> select count(*) from table_date;

+----------+
| count(*) |
+----------+
| 53217368 |
+----------+

1. Here are the min and max value for the table1:

mysql> select min(table_date),max(table_date) from table1;
+---------------------+---------------------+
| min(table_date) | max(table_date) |
+---------------------+---------------------+
| 2011-08-09 04:05:01 | 2012-01-13 04:04:16 |
+---------------------+---------------------+
row in set (0.01 sec)

1. Date and time 35 days ago from the current date & time will be:

mysql> select now(),now() - interval 35 day;
+---------------------+-------------------------+
| now() | now() - interval 35 day |
+---------------------+-------------------------+
| 2012-01-13 21:41:36 | 2011-12-09 21:41:36 |
+---------------------+-------------------------+

1. Number of rows which contains dataentry_date less than the above date is:

mysql> select count(*) from table1 where table_date delete from table1 where table_date check table table_date;
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| tabe1.table_date | check | warning | 7 clients are using or haven't closed the table properly |
| tabe1.table_date | check | error | Can't read key from filepos: 2048 |
| tabe1.table_date | check | Error | Incorrect key file for table './table1/table_date.MYI'; try to repair it

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server and UNLOCK TABLES after you copy the data back. You will need to keep the FLUSH TABLES ... session running for this duration.

So, it would look like this

server1:

# keep this session running for the duration of the repair!
mysql> FLUSH TABLES WITH READ LOCK table_data;
/var/lib/mysql# rsync -aP table_date.* server2:/somedir/

server2:

# make a backup
/somedir# tar -czvf table_data_backup.tgz table_date.*
# run the repair
/somedir# myisamchk -r table_data
# copy the files back
/somedir# rsync -aP table_date.* server1:/var/lib/mysql/

server1:

# make sure the permissions are correct on /var/lib/mysql/table_date.*
mysql> UNLOCK TABLES;

Remember, do not close the FLUSH TABLES WITH READ LOCK session.

You may want to give myisamchk additional memory for the following parameters to speed it up:
--key_buffer_size
--sort_buffer_size
--read_buffer_size
--write_buffer_size

Next, convert your table to InnoDB. There's almost no good reason to use MyISAM these days.

StackExchange Database Administrators Q#11352, answer score: 7