Skip to main content

Recovery form MySQL corruption

Background​

There are two instances of MySQL on Cloudron. One instance runs on the host and is used by the platform. Another instance is the MySQL addon which runs in a container named mysql and is shared by apps. You can use the steps below to recover the host MySQL.

danger

Always make sure to take a backup/snapshot of the server before proceeding

Soft recovery​

If MySQL is running and you can connect to it using mysql -uroot -ppassword (sic), then maybe the data is already salvagable.

Try to create a dump of existing data:

bash
mysqldump -uroot -ppassword --single-transaction --routines --triggers box > box.mysql
mysql -uroot -ppassword -e "DROP DATABASE box"
mysql -uroot -ppassword -e "CREATE DATABASE IF NOT EXISTS box CHARACTER SET utf8mb4 COLLATE utf8mb4_bin"
mysql -uroot -ppassword box < box.mysql

If one or more steps above get "stuck", it means MySQL database is corrupt. Follow the hard recovery steps below.

Hard recovery​

  1. Stop box code:
bash
systemctl stop box
  1. Create a backup of the MySQL data with:
Bash
rsync -avrlP /var/lib/mysql /root/mysql_backup_$(date +%Y%m%d-%H%M%S)
  1. Edit /etc/mysql/my.cnf to have the below. See the recovery docs for details.
/etc/mysql/my.cnf
[mysqld]
innodb_force_recovery = 1
  1. Keep increasing the above value till MySQL starts with:
Bash
systemctl start mysql
  1. Once it starts, we have to take a dump of the database:
Bash
mysqldump -uroot -ppassword --skip-lock-tables -A > /root/alldb.sql
  1. Now that we created the dump, stop MySQL:
Bash
systemctl stop mysql
  1. Remove the innodb_force_recovery in /etc/mysql/my.cnf
  2. Recreate the existing MySQL installation:
Bash
mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
mysqld --initialize   # This will dump the MySQL root password in /var/log/mysql/error.log
  1. Start MySQL:
Bash
systemctl start mysql
  1. Change the root password to password (sic) -
Bash
mysql -uroot -p<password from /var/log/mysql/error.log>  # there is no space between p and the password. e.g -pAS23kdI
SQL
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
  1. Import the database:
Bash
mysql -uroot -ppassword < /root/alldb.sql
  1. Start the platform code again:
Bash
systemctl restart box