Activities

February 2012
M T W T F S S
« Jan   Mar »
 12345
6789101112
13141516171819
20212223242526
272829  

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

7. How to backup the structure of certain tables

syntax : mysqldump -d database1 table1 table2 table3 table3 -r table_schema.sql

[root@rc-025 ~]# mysqldump -d sample1 user_export user_flag_details venue_details -r tables_stucture.sql

8. How do I exclude certain huge/unwanted tables from the backup.
You may use –ignore-table to exclude the tables from the backup
Eg : mysqldump databas1 –ignore-table=database1.table1 –ignore-table=database1.table2

[root@rc-025 ~]# mysqldump -B yentha --ignore-table=yentha.whatsnew_details --ignore-table=yentha.web_pages --ignore-table=yentha.web_page_content --ignore-table=yentha.voices_details -r yentha.sql
[root@rc-025 ~]#

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>