Database connection errors – steps to fix
Following the February 10 power outage on 123 Reg systems, some of our VPS customers may see an issue on their website similar to "Database connection error".
Chances are this happened due to a MySQL corruption. At the moment, the fastest way to resolve this would be to attempt to repair the database. if that fails you may need to rebuild the server and have it restored from your backups/
The underlying issue is most likely related to a crash related to an unclean shut down. This can be recovered, however, and after taking a backup you can resolve this by doing the following:
Open the /etc/my.cnf file (MySQL’s config file)
It should look similar to this (however options may vary):
[mysqld]
innodb_file_per_table=1
open_files_limit=2164
max_allowed_packet=268435456
Add this line to the end:
innodb_force_recovery = 1
Try starting MySQL again.
If it fails, change the line you added to my.cnf to:
innodb_force_recovery = 2
Try restarting MySQL again.
If it still fails, change the line you added to my.cnf to:
innodb_force_recovery = 3
You can keep going up to 6 if needed however this should be a last resort as anything over 3 can cause data loss
As soon as you get MySQL started, take a full backup of your databases. MySQL is now is read only recovery mode so your sites may not be online but you should take a copy of your data as soon as possible.
# mysqldump –all-databases –lock-tables
–compress –routines –comments –extended-insert > new_all_databases.sql
— Warning: Skipping the data of table
mysql.event. Specify the –events option explicitly.
Log in as the root MySQL user, check the DB, exit, stop MySQL and remove the innodb_force_recovery = […]” line from my.cnf
Start MySQL one more time. It should now be running normally.
You may need to repair corrupted tables using the following method:-
mysql> USE databasename;
The Database will be changed, so you can issue the repair operation:-
mysql> REPAIR TABLE `table1` , `table2`;
The above should work for you and will allow MySQL to start and run however once you do this get this started. There is more information regarding this here: https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
The above guide should correct any issues, however if the above fails to work or if you may need to restore from your backups.
If the recovery process fails, and you do not have backups please raise a ticket on our support page and we will attempt to recover your data. Please understand however that we have an extremely large backlog of tickets and we are handling them in chronological order.