Thursday, March 21, 2013

Check Repair & Optimize mysql Databases

Check Repair & Optimize mysql Databases:

You can use either Mysqlcheck or Myisamchk to Check and/or Repair database tables. Mysqlcheck and Myisamchk are similar in purpose, there are some essential differences. Mysqlcheck as well as Myisamchk can Check, Repair and Analyze MyISAM tablesMysqlcheck can also check InnoDB tables, so if database engine used for the databases is other than MyISAM, i.e InnoDB then try to use Mysqlcheck cmd.
++  Check, Repair and Optimize Using mysqlcheck cmd:

+  Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

# mysqlcheck –all-databases -r   #repair databases
# mysqlcheck –all-databases -a   #analyze databases
# mysqlcheck –all-databases -o   #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name

 Check, Repair and Optimize Using mysqlcheck myisamchk cmd:
=> For All tables in All Databases:

Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI

=> For Single Database:
myisamchk -r /var/lib/mysql/[CpanelUsername_Databasename]/*

cd /var/lib/mysql/[CpanelUsername_Databasename]/
To check the tables:
# myisamchk *.MYI
To repair tables:
# myisamchk -r *.MYI

Note: You can use Mysqlcheck or Myisamchk cmd line options as per your requirenemt.

No comments:

Post a Comment