Repairing MySQL tables


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

Repairing MySQL tablesLast Modified: Apr 4, 2012, 1:39 am
If your system has crashed or MySQL tables have been corrupted somehow, there are few methods you can use to try and restore your database tables.

Note: For FreeBSD or Debian, please replace all instances of /var/lib, with /home.
Eg: /var/lib/mysql becomes /home/mysql.

1) Get DA to do it for you.


DirectAdmin has repair commands built in, which make use of the sql "repair table" options.
To use them (per-User basis), go to:
User Level -> MySQL Management -> Select the checkbox for the DB to repair, and click "Repair"



2) Use myisamchk and repair many databases at once


If you have many corrupted databases, and you want to repair them all in one shot, then login to ssh as root, and do the following:

a) Shut down mysqld
Admin Level -> Services Monitor -> mysqld: stop

shutting down mysqld through DA is important.
If you don't, then the services.status won't be set, and the dataskq will end up starting it again.. not likely what you want.

b) Make a backup

cd /home
cp -Rp mysql mysql.backup


c) Repair the tables

/usr/bin/myisamchk --silent --force --fast --update-state \
         --key_buffer_size=64M --sort_buffer_size=64M \
         --read_buffer_size=1M --write_buffer_size=1M \
         */*.MYI

Note, on FreeBSD/Debian, use this path instead:

/usr/local/mysql/bin/myisamchk


d) Start MySQL again
Admin Level -> Services Monitor -> mysqld: start

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST