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

OR
# 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]/*
OR


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