Dedicated Server Repair MySQL Tables
If your dedicated server MySQL has crashed or MySQL tables have been corrupted somehow, there are few methods you can use to try and restore your database tables.
Note: MariaDB since version 5.1 uses myisam_recover_options that does auto-repair of crashed databases.
For Debian dedicated server:
Please replace all instances of /var/lib, with /home.
E.g., /var/lib/mysql becomes /home/mysql .
- Get DirectAdmin 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 check-box for the DB to repair , and click “Repair”
OR
- Use mysqlcheck tool to repair all databases on running mysql:
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair -A -u root -p
- Use myisamchk and repair many databases at once on stopped mysql:
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
NOTE: 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, which is not likely what you want when repairing databases/tables.
- 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
/usr/local/mysql/bin/myisamchk
- d) Start MySQL again:
Admin Level -> Services Monitor -> mysqld: start
Confirm all sites are working as intended and no further errors that would indicate corrupted/crashed databases/tables are being logged in MySQL’s error log. Read More.
Don’t have dedicated server? get your own here
