Mysql
在沒有額外磁碟空間和表損壞時修復 Myisam 表
我有一張表，其中 MyIsam 作為儲存引擎包含數百萬行。有一個清除任務，需要刪除 35 天前的數據。我用以下方式測量了行：
mysql> select count(*) from table_date; +----------+ | count(*) | +----------+ | 53217368 | +----------+ 2. 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) 3. 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 | +---------------------+-------------------------+ 4. Number of rows which contains dataentry_date less than the above date is: mysql> select count(*) from table1 where table_date < '2011-12-09 21:41:36'; +----------+ | count(*) | +----------+ | 30729315 | +----------+ 1 row in set (3 min 3.34 sec) Now purged the 3 million rows using : mysql> delete from table1 where table_date < (now() - interval 35 day); After running the delete query has been terminated with the below error message and mysql has been restarted. Tried to Repair using : mysql> 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 | | tabe1.table_date | check | error | Corrupt | +----------------------------+-------+----------+-----------------------------------------------------------------------------------+ 4 rows in set (0.01 sec) No luck & then tried with repair table : mysql> repair table table_date; +----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tabe1.table_date | repair | error | 28 when writing to datafile | | tabe1.table_date | repair | Error | Disk is full writing './table/tabl1.TMD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) | | tabe1.table_date | repair | Error | Retry in 60 secs. Message reprinted in 600 secs | | tabe1.table_date | repair | Error | Retry in 60 secs. Message reprinted in 600 secs | | tabe1.table_date | repair | Error | Error writing file './table/table_data.TMD' (Errcode: 28) | | tabe1.table_date | repair | status | Operation failed | +----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (43 min 5.53 sec)又沒有運氣了。
Error Log : -------------------- Version: '5.1.45-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 120120 4:38:05 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './tabl1/table_data.MYI'; try to repair it 120120 4:39:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './table1/table_date.MYI'; try to repair it 120120 5:05:13 [ERROR] /usr/sbin/mysqld: Disk is full writing './table1/table_date.TMD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) 120120 5:05:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs 120120 5:15:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Messagemysql> I tried with Myisamchk -r & -o also. But no luck. Here is my present table status : mysql> show table status like 'table_date'\G *************************** 1. row *************************** Name: table_date Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: Table './table1/table_date' is marked as crashed and last (automatic?) 1 row in set (0.01 sec)Q1。當我使用 myisamchk 時，創建了 .TMD。這是一個我可以理解的中間數據文件。如果我可以刪除它，它會起作用。?
Q2。該表佔用 17GB 空間。並在執行刪除後崩潰。嘗試修復磁碟空間問題時。使該表恢復工作狀態的任何棘手解決方案。解決方案將不勝感激。
如果您無法釋放或添加任何磁碟空間，您可以將 table_data.* 文件（MYD、MYI、frm 等）複製到另一台具有大量可用磁碟的機器上，使用 myisamchk 在那裡執行修復，然後將文件複製回原機。如果伺服器需要保持執行，請
FLUSH TABLES WITH READ LOCK table_data在將數據複製到另一台伺服器之前和UNLOCK TABLES將數據複製回來之後執行。您需要在此期間保持 FLUSH TABLES … 會話執行。所以，它看起來像這樣
伺服器1：
# 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/伺服器2：
# 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/伺服器1：
# make sure the permissions are correct on /var/lib/mysql/table_date.* mysql> UNLOCK TABLES;請記住，不要關閉 FLUSH TABLES WITH READ LOCK 會話。
您可能希望為以下參數提供 myisamchk 額外記憶體以加快速度： –key_buffer_size –sort_buffer_size –read_buffer_size –write_buffer_size
接下來，將您的表轉換為 InnoDB。這些天幾乎沒有充分的理由使用 MyISAM。
我自己也嘗試過這樣的自由空間麻煩。我在 Windows 上，我只是在另一個硬碟上創建了 table.TMD 文件（一個空的文本文件）。然後我在數據庫目錄中創建了一個指向它的符號連結。
mklink E:\table.TMD C:\sql\data\table.TMD第一個路徑是空的 txt 文件，第二個路徑是 myisamcheck 將創建 .TMD 的位置。它會欺騙它最終寫入外部硬碟驅動器。
我不知道 Linux 中的等價物是什麼，否則。但它必須存在。