Mysqlcheck

image_pdfimage_print

I am getting this error in wordpress phpMyadmin

#145 - Table './DB_NAME/wp_posts' is marked as crashed and should be repaired 

When I login to phpMyadmin, it says wp_posts is “in use”

My website is currently down because of this.

———

Run this from your server’s command line:

 mysqlcheck --repair --all-databases

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Mysqlcheck : Quick guide to Sys-admins

mysqldump: Error 1194: Table ” is marked as crashed and should be repaired when dumping table `db_table` at row :

Most of us are getting this type of errors when we used “MyISM” storage engine for huge tables. Inodb tables also were affect sometimes. We can skip this errors, if we have uses high I/O operation supported drives like RAID 10. Most of the disk related/write related issues can be solved by empowering more stable drive mechanism.

Solution :

Use “repair table table name ;” This will repair the table automatically.

 mysql> repair table adserver.jos_users;
+——————–+——–+———-+———-+
| Table              | Op     | Msg_type | Msg_text |
+——————–+——–+———-+———-+
| adserver.jos_users | repair | status   | OK       |
+——————–+——–+———-+———-+
1 row in set (0.05 sec)
mysql>

a. How to find corrupted tables for a database 
# mysqlcheck -uroot -p –databases database name

[root@u15382543 ~]# mysqlcheck –databases adserver;
adserver.jos_ad_agency_advertis                    OK
adserver.jos_ad_agency_banners                     OK
adserver.jos_ad_agency_campaign                    OK
adserver.jos_ad_agency_campaign_banner             OK
adserver.jos_ad_agency_channel_set                 OK

b. Checking whole the database for any damage/corruption
#mysqlcheck –all-databases

c. Analyzing and repairing corrupted database/s Recommended

 [root@u15382543 ~]# mysqlcheck  –auto-repair –check –optimize –all-databases –extended

For a particular table,

# mysqlcheck –auto-repair –check –optimize database name table name

[root@rc-025 vmachines-img]#  mysqlcheck  –auto-repair –check –optimize wetcenter jos_menu
wetcenter.jos_menu                                 OK

If you want to see the verbose
#mysqlcheck -uroot -p –databases database_name –tables table_name –extended –verbose

 [root@rc-025 vmachines-img]# mysqlcheck  –databases  wetcenter  –tables jos_messages  –auto-repair –check –optimize –extended –verbose
# Connecting to localhost…
wetcenter.jos_messages                             OK
# Disconnecting from localhost…

5. Repairing MyISM tables in offline mode for huge GB tables

myisamchk –key_buffer_size=512M –sort_buffer_size=512M –read_buffer_size=8M –write_buffer_size=8M path/to/table/file/.MYI

-read_buffer_size/–write_buffer_size : Used to read and write data from the table and to the temporary file.
–sort_buffer_size : When the keys are repaired by sorting keys and this option would useful when you use “–recover”
–key_buffer_size : When you check the table with –extend-check option.

 [root@u15382543 ~]# myisamchk –key_buffer_size=512M –sort_buffer_size=512M –read_buffer_size=256M –write_buffer_size=256M /var/lib/mysql/lists/phplist_usermessage.MYI
Checking MyISAM file: /var/lib/mysql/lists/phplist_usermessage.MYI
Data records: 1872964   Deleted blocks:       0
– check file-size
– check record delete-chain
– check key delete-chain
– check index reference
– check data record references index: 1
– check data record references index: 2
– check data record references index: 3
– check data record references index: 4
– check record links
[root@u15382543 ~]#

6. How to optimize tables

to all databases
# mysqlcheck -o –all-databases;

For a particular table

mysqlcheck -o database_name table-name

 [root@rc-025/]# mysqlcheck -o selfcare customers
selfcare.customers                                 OK

7. How to check all the maintenance operations at a time

 #mysqlcheck -uroot -p  –auto-repair  -o –all-databases

Useful params in mysqlcheck

1. -r : Repair the table
2. -o : optimize the table
3. –auto-repair : Repair the table automatically
4. -c : Check table errors
5. -C : Check only the table changed since from the last check.
6. –fix-db-names/–fix-table-names : Check database name/table name if corrupted.
7. -e : Extended operations even check or repairing.
8. -q : Quick checking
9. -o : optimize the table
10. -B : This option keeping a a file copy before repairing it.

Sometime you got the following errors when try to repair a myISAM table where index file is missing. So you need to use the extra parms “USE_FRM” to solve this.

ERROR : Can’t find file: ‘table’ (errno: 2) Corrupt

 mysql> repair table userlogs;
+——————-+——–+———-+—————————————-+
| Table             | Op     | Msg_type | Msg_text                               |
+——————-+——–+———-+—————————————-+
| selfcare.userlogs | repair | Error    | Can’t find file: ‘userlogs’ (errno: 2) |
| selfcare.userlogs | repair | error    | Corrupt                                |
+——————-+——–+———-+—————————————-+
2 rows in set (0.00 sec)
mysql> repair table userlogs USE_FRM ;
+——————-+——–+———-+————————————+
| Table             | Op     | Msg_type | Msg_text                           |
+——————-+——–+———-+————————————+
| selfcare.userlogs | repair | warning  | Number of rows changed from 0 to 1 |
| selfcare.userlogs | repair | status   | OK                                 |
+——————-+——–+———-+————————————+
2 rows in set (0.07 sec)

One of my previous post would help you to familiar with some sort of MySQL permissions. Check this out

7. Mysqldump : taking Data and Schema separately

In some case you would need to extract schema or data separately if there any changes in table structure.

Backup the database schema 
For extracting schema just add the switch “-d” in mysqldump statement.

#mysqldump -d  -B mission -r mission_schema.sql -uroot -p

b. Backup the data only

Here you need to use –no-create switch in mysqldump statement.

[root@cellularpoint installation]# mysqldump –skip-extended-insert –skip-comments –no-create-info –no-create-db -B database_name -r db.data.sql -uroot -p

7. Mysqldump : Backing up Only Stored Procedure

 mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt -r procedure.sql
Author: Liju Mathew
Visit Liju’s Website – Email Liju
I’m Liju, one linux enthusiastic who have been playing with Linux for more than 7 years. I’m curious about to read blog, learning and implementing new technologies from my personal experience. Like to be play with burning head on busy schedule 🙂 This is a bookmark of all challenges that I’d faced which would be helpful to others sometimes as I’d learn it from the same way 🙂 Nothing more, I have to go miles, before I sleep
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Website Reference::

Leave a Reply