Thursday, September 16, 2010

How to repair mysql database or tables of the database?

Guys,

I usually use following command to repair all the tables of the databases of a user.

=========
[root@server /var/lib/mysql]#for i in `ll |grep bigchakr|awk '{print $9}' |cut -d/ -f1`; do for j in `mysql -e "use $i; show tables;"|grep -v -`; do mysql -e "use $i; repair table $j;"; done done;
=========

User : bigchakr, please replace this user. It'll repair all the tables of all the DB of that user.

You can use myisamchk command to repair the DB. Here is the comamnd :

========
[root@server /var/lib/mysql]# myisamchk -r /var/lib/mysql/bigchakr_content/*.MYI
========

Note : bigchakr_content is the name of the DB. You can use following command.

========
[root@server /var/lib/mysql]# for i in `ll |grep bigchakr|awk '{print $9}'`; do myisamchk -r $i/*.MYI ; done
========

I don't prefer myisamchk since sometime it doesn't give good results.

Try. :)

2 comments:

  1. Hi,

    I am not sure whether this will work because in /var/lib/mysql the ownership of databases will be mysql:mysql .So, will it possible to grep out with username?
    ==
    ll |grep bigchakr|awk '{print $9}'
    ====

    ReplyDelete
  2. To repair all the corrupt databases and restore the data from unreadable table and table spaces. Go through Stellar Phoenix Database Repair for MySQL software that can fix and recover corrupt MySQL databases and restore all the unreadable database objects safely, such as views, primary keys, triggers and more.

    ReplyDelete