Recovering MySQL or MariaDB database via dump and restore

14 Aug 2020 - tsp
Last update 14 Aug 2020
Reading time 8 mins

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:

In this case keep in mind that this method:

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:

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.


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 200
 100 200
2020-08-13T20:54:30.770120Z 0 [Warning] InnoDB: New log files created, LSN=45790
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

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:

Data protection policy

Dipl.-Ing. Thomas Spielauer, Wien (

This webpage is also available via TOR at http://rh6v563nt2dnxd5h2vhhqkudmyvjaevgiv77c62xflas52d5omtkxuid.onion/

Valid HTML 4.01 Strict Powered by FreeBSD IPv6 support