Recovering MySQL or MariaDB database via dump and restore

Sometimes all database recovery functions provided by InnoDB don’t help anymore and one is required to restore all data of a database from existing dumps. In case one doesn’t have a replicated setup - as was the case for the test system described in this short blog post - this might be an invasive process that also leads to some service downtime (usually). Note that the steps in this article are about the last resort except redeploying the installation and restoring from a backup.

Please keep in mind that this procedure is usually not sufficient for replicated setups since it doesn’t deal with GTIDs.

Oh and just as a reminder: Periodic backups are your friend.

Which situations might make such recovery necessary

To be fair - taking your database server offline, dumping all data, deleting the whole database and reloading data into the structured storage is nothing one really likes to do. So if there is any possibility of repairing a database then it should be preferred except for really small scale uncritical systems. However there are some situations under which it might be necessary to really destroy and recreate the datastore:

• Corrupted and inconsistent logfiles. This might happen because someone messed directly with the logfiles or in case the underlying storage system didn’t support atomic access as expected - for example in case the system has been shutdown while partially caching a write. It might also happen in case someone restores a backup and uses logfiles from two different points in time or even deletes them. In this case there is no clean recovery method available.
• The database has been partiall destroyed during an upgrade or one downgrades the database to a previous version that’s incompatible with the current file format

In this case keep in mind that this method:

• Requires one to disable access of all applications to the database
• Breaks any replication
• Interrupts service
• Might lead to loss of data of uncommited transactions or might break ACID (i.e. depending on the chosen access method one might see partial data from transactions that aren’t seen atomically any more or might have applied partial rollbacks; Data that hasn’t been commited might be partially visible and data that has already been commited a short time ago might be lost; Different transactions might interfer with each other).

Usually a database system should prevent situations like these - if they happen it’s most likely an administrative error.

Trying to get a fresh backup from an potentially inaccessible database

Sometimes such stuff happens and the original database administrator didn’t have a backup of their work. This is of course bad practice - but happens all the time, especially in setups used for playing around or in small business where IT is mostly done in an somewhat unprofessional fashion. In this case one might try to get an database dump by using InnoDB recovery functions. Note that their usage might also lead to data loss (especially uncommitted transactions, pending writes and newly modified states).

First stop the database as usual:

/usr/local/etc/rc.d/mysqld-server stop

One can choose between different checks that one might skip (these also usually lead to a read only database). These modes can be enabled during database startup by setting the innodb_force_recovery parameter inside [mysqld] section inside the /usr/local/etc/mysql/my.cnf configuration file. Depending on the error different checks and procedures might be skipped - this includes disabling the processing of the rollback journal, ignoring the transaction journal, etc. The highest level that might lead to some data loss and also lead to a read only database (one cannot drop databases in this configuration) is setting the level to 6. Refer to the handbook for the meaning of those levels but keep in mind that you will loose some data under many circumstances with any value larger than 4:

• SRV_FORCE_IGNORE_CORRUPT (1) allows queries to skip over corrupt index records and allows one to rebuild indices.
• SRV_FORCE_NO_BACKGROUND (2) prevents the master and plunge thread from running. This prevents crashes caused by running purge operations.
• SRV_FORCE_NO_TRX_UNDO (3) does not run rollbacks after crash recovery operations. Pending rollbacks are of course still enqueued in the logfile and will be executed after the option has been reset to 0.
• SRV_FORCE_NO_IBUF_MERGE (4) prevents insert buffer operations, table statistics are not recalculated. Note that this might already lead to data loss. One is really required to recreate at least all secondary indices after recovery data. The database is kept read only.
• SRV_FORCE_NO_UNDO_LOG_SCAN(5) suppresses the execution of undo operations from the undo log. Interrupted and aborted transactions are seen as committed. This of course might lead to inconsistent state. The database is kept read only.
• SRV_FORCE_NO_LOG_REDO (6) prevents the usage of the redo log. This mode might lead to inconsistent state of database pages that might introduce even more errors and corruption in core database structures. Recreating the databases is mandatory. The database is kept read only.

As one sees this options disable quite everything required to satisfy the ACID constraints and thus might introduce invalid state. Use them with care. On the system described herein the ibdata0 and ibdata1 logfiles had been damaged due to playing around with internal state - so the redo and undo log had been corrupted. This required the usage of recovery mode 6 - if unsure start with 1 and check for any errors during operation.

[mysqld]
innodb_force_recovery=6

Now one can start the database again - from this moment on some data might already be lost or in an inconsistent state. In case consistency is important rather use a full backup that has been made while transaction safety was still guaranteed.

/usr/local/etc/rc.d/mysqld-server start

Then try to dump all data using the mysqldump utility. This utility simply creates a stream of SQL commands that is capable of loading all information contained inside the database at the given moment:

mysqldump -u root -p --all-databases --flush-privileges --add-drop-database --add-drop-table --events --triggers --set-gtid-purged=OFF --add-locks	> dbdump.sql

In case you’re in the lucky situation of fetching this dump from an operating and online database server you can also add --single-transaction so the restore is happening in a consistent manner. It’s also faster than using a non transactional (auto commit) restore.

Now shutdown the database server, carefully inspect your dump to be complete.

Recreating the database from the dump

Now it’s time to drop the filesystem containing your MySQL server data and recreate if from scratch. On this setup it was assumed that all database files had been stored on a ZFS filesystem called tank/mysql and mounted ad /var/db/mysql. In this case one can simply drop the ZFS filesystem and recreate a new one:

zfs destroy tank/mysql
zfs create -o setuid=off -o exec=off -o devices=off -o quota=1024G -o mountpoint=/var/db/mysql tank/mysql

Tune the parameters according to your own needs - especially the quota parameter or remove them from the command.

Then initialize the database structure - it’s assumed that the user to be used for the service is called mysql:

/usr/local/libexec/mysqld --initialize --user=mysql --basedir=/usr/local --datadir=/var/db/mysql

This creates initial database tables required for basic operations and returns a temporary root password that will be used later on:

dbdevtst# /usr/local/libexec/mysqld --initialize --user=mysql --basedir=/usr/local --datadir=/usr/local/mysql
 100
 100 200
 100 200
2020-08-13T20:54:30.770120Z 0 [Warning] InnoDB: New log files created, LSN=45790
 100
2020-08-13T20:54:53.878619Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
mysqld: Error on delete of './auto.cnf' (Errcode: 2 - No such file or directory)
2020-08-13T20:54:54.195750Z 0 [Warning] World-writable config file './auto.cnf' has been removed.

2020-08-13T20:54:54.197850Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3d105191-dda7-11ea-b0a6-00248c64da36.
2020-08-13T20:54:54.211362Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-08-13T20:55:06.103846Z 0 [Warning] CA certificate ca.pem is self signed.
2020-08-13T20:55:07.779649Z 1 [Note] A temporary password is generated for root@localhost: URywlzhae4?w

Now launch the database server as usual

/usr/local/etc/rc.d/mysqld-server start

After that one has to change the root password since this temporary password is marked obsolete and no other queries can be executed. Use the temporary root password generated previously to login and execute the alter user statement:

mysql -u root -p
root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPasswordReadInCleartext';
Query OK, 0 rows affected (0.09 sec)

root@localhost [(none)]> exit
Bye

Because restore with drop table statements for the mysql database that contains internal data will lead to some problems one should disable this single drop table statement by removing the following line from the SQL file using an utility like sed or an simple text editor. This bug might be resolved in later database versions.

/*!40000 DROP DATABASE IF EXISTS `mysql`*/;

Now the interactive part nearly has finished - one can simply start importing by executing the MySQL command file:

mysql -u root -p < dbdump.sql

This may take a while - on an older development test system this took about 4 hours to restore a 270 GByte database when not using transactions as well as only 20 minutes when using a single transaction.

And keep in mind: Do periodic backups.

This article is tagged:

• System administration
• FreeBSD