14 Aug 2020 - tsp
Last update 14 Aug 2020
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.
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.
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:
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
/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
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
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.
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.
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
/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
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
/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
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
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
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:
Dipl.-Ing. Thomas Spielauer, Wien (email@example.com)
This webpage is also available via TOR at http://rh6v563nt2dnxd5h2vhhqkudmyvjaevgiv77c62xflas52d5omtkxuid.onion/