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"
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:
bash
```
systemctl stop box
```

Create a backup of the MySQL data with:

Bash

rsync -avrlP /var/lib/mysql /root/mysql_backup_$(date +%Y%m%d-%H%M%S)

Edit /etc/mysql/my.cnf to have the below. See the recovery docs for details.
/etc/mysql/my.cnf
```
[mysqld]
innodb_force_recovery = 1
```
Keep increasing the above value till MySQL starts with:
Bash
```
systemctl start mysql
```

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

Bash

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

Now that we created the dump, stop MySQL:
Bash
```
systemctl stop mysql
```
Remove the innodb_force_recovery in /etc/mysql/my.cnf

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

Start MySQL:
Bash
```
systemctl start mysql
```

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';

Import the database:

Bash

mysql -uroot -ppassword < /root/alldb.sql

Start the platform code again:
Bash
```
systemctl restart box
```