Skip to 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.

Fair warning: please 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:

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"
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

  • Stop box code: systemctl stop box .

  • Edit /etc/mysql/my.cnf to have the below. See the recovery docs for details.

[mysqld]
innodb_force_recovery = 1

  • Keep increasing the above value till MySQL starts with systemctl start mysql

  • Once it starts, we have to take a dump of the database:

# mysqldump -uroot -ppassword --skip-lock-tables -A > /root/alldb.sql

  • Now that we created the dump, stop MySQL - systemctl stop mysql

  • Remove the innodb_force_recovery in my.cnf

  • Recreate the existing MySQL installation:

# 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

  • Start MySQL - systemctl start mysql

  • Change the root password to password (sic) -

# mysql -uroot -p<password from /var/log/mysql/error.log>  # there is no space between p and the password. e.g -pAS23kdI
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6365
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

  • Import the database - mysql -uroot -ppassword < /root/alldb.sql

  • Start the platform code again - systemctl restart box