How to Restore a Single Database from MariaDB Backup

Lately, I’ve been asked how to restore a single database or even a single table out of a complete backup of MariaDB Server that was created with MariaDB Backup. This blog provides step-by-step guidance on how to achieve a restore of a database. Another blog post will pick up the question on how to restore a single table which has a separate set of challenges.

We will use the world sample database and a backup directory /opt/backup/<timestamp> as an example to explain the process.

Step 1 – Creating the Backup and Preparing the Database for Export

As root or user with write permission to /opt/backup issue the following commands:

# TS=`date +”%Y-%m-%d_%H-%M-%S”`
# mkdir /opt/backup/${TS}
# mariabackup –backup –user backup1 –password MariaDB –target-dir “/opt/backup/${TS}”

This created a directory /opt/backup/2018-03-28_19-02-56 with the complete backup.

To be able to restore a database or to be more precise all or some tables of a database you first need to have the tables prepared for export. This is the easiest step in the process. To prepare all tables of a database world for export issue the following command:

# mariabackup  –prepare –export –databases world
–user backup1 –password MariaDB
–target-dir “/opt/backup/${TS}”

After this step if you go to the backup directory you will find .cfg files for all tables in world.

# cd /opt/backup/2018-03-28_19-02-56
# ls -l world
total 1132
-rw-rw—- 1 root root    686 Mar 28 19:05 city.cfg
-rw-r—– 1 root root   1578 Mar 28 19:03 city.frm
-rw-r—– 1 root root 606208 Mar 28 19:03 city.ibd
-rw-r—– 1 root root    856 Mar 28 19:03 country_capital.frm
-rw-rw—- 1 root root   1228 Mar 28 19:05 country.cfg
-rw-r—– 1 root root   1618 Mar 28 19:03 country.frm
-rw-r—– 1 root root 163840 Mar 28 19:03 country.ibd
-rw-rw—- 1 root root    665 Mar 28 19:05 countrylanguage.cfg
-rw-r—– 1 root root   1542 Mar 28 19:03 countrylanguage.frm
-rw-r—– 1 root root 229376 Mar 28 19:03 countrylanguage.ibd
-rw-r—– 1 root root     61 Mar 28 19:03 db.opt

country_capital.frm is a view on country and city tables therefore it has no .cfg file since it has no tablespace.

Step 2 – Creating empty tables for the restore

Next, you’ll need to create a database you want to restore the tables to.  The database does not necessarily need to be named the same as the database in the backup. For demonstration purposes, we use a database named world2.

What you need is the CREATE DATABASE and CREATE TABLE SQL statements that you used to create the original tables. You can obtain these from your server by taking the full CREATE TABLE statements from SHOW CREATE TABLE for each table (see emphasized text).

MariaDB [world]> SHOW CREATE DATABASE worldG
************************** 1. row ***************************
Database: world
Create Database: CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */
1 row in set (0.00 sec)

MariaDB [world]> SHOW CREATE TABLE countryG
*************************** 1. row ***************************
Table: country
Create Table: CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT ”,
`Name` char(52) NOT NULL DEFAULT ”,
`Continent` enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’) NOT NULL
DEFAULT ‘Asia’,
`Region` char(26) NOT NULL DEFAULT ”,
`SurfaceArea` float(10,2) NOT NULL DEFAULT 0.00,
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT 0,
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT ”,
`GovernmentForm` char(45) NOT NULL DEFAULT ”,
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT ”,
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [world]> SHOW CREATE TABLE cityG
*************************** 1. row ***************************
      Table: city
Create Table: CREATE TABLE `city` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` char(35) NOT NULL DEFAULT ”,
 `CountryCode` char(3) NOT NULL DEFAULT ”,
 `District` char(20) NOT NULL DEFAULT ”,
 `Population` int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY (`ID`),
 KEY `CountryCode` (`CountryCode`),
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4100 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [world]> SHOW CREATE TABLE countrylanguageG
*************************** 1. row ***************************
      Table: countrylanguage
Create Table: CREATE TABLE `countrylanguage` (
 `CountryCode` char(3) NOT NULL DEFAULT ”,
 `Language` char(30) NOT NULL DEFAULT ”,
 `IsOfficial` enum(‘T’,’F’) NOT NULL DEFAULT ‘F’,
 `Percentage` float(4,1) NOT NULL DEFAULT 0.0,
 PRIMARY KEY (`CountryCode`,`Language`),
 KEY `CountryCode` (`CountryCode`),
 CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

You need to remove any referential integrity constraints from the CREATE TABLE statements (see text in red) and recreate them after successfully importing the tablespaces because this can cause problems when you try to discard the tablespace in the next step.

MariaDB [world2]> ALTER TABLE country DISCARD TABLESPACE;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In this case drop the FOREIGN KEY CONSTRAINT by issuing:

MariaDB [world2]> ALTER TABLE city DROP FOREIGN KEY city_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

In the CREATE DATABASE statement replace world with world2.

If you do not have your original database or tables anymore you need to get your latest CREATE statements from your application. So it is always a good idea to get the CREATE statements from every database you have on your servers whenever a change to the schema has occurred and store them in a safe place.

If your original database schema still exists you can also use the following statements to prepare the database for restore:

MariaDB [(none)]> CREATE DATABASE world2;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> use world2
Database changed

MariaDB [world2]> CREATE TABLE country LIKE world.country;
Query OK, 0 rows affected (0.05 sec)

MariaDB [world2]> CREATE TABLE city LIKE world.city;
Query OK, 0 rows affected (0.04 sec)

MariaDB [world2]> CREATE TABLE countrylanguage LIKE world.countrylanguage;
Query OK, 0 rows affected (0.04 sec)

Referential integrity constraints are not copied into the new schema.

Step 3 – Discard the tablespaces

MariaDB [world2]> ALTER TABLE country DISCARD TABLESPACE;
Query OK, 0 rows affected (0.02 sec)
MariaDB [world2]> ALTER TABLE city DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
MariaDB [world2]> ALTER TABLE countrylanguage DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

After this step the database directory for world2 only contains the .frm files and the db.opt file.

Step 4 – Copy the tables to restore to the new database directory

# cp /opt/backup/2018-03-28_19-02-56/world/*.* /var/lib/mysql/world2

If you look into the database directory world2 now you will see the following:

# ls -l
total 1008
-rw-r—– 1 root  root 686 Mar 28 19:25 city.cfg
-rw-rw—- 1 mysql mysql   1578 Mar 28 19:25 city.frm
-rw-r—– 1 root  root 606208 Mar 28 19:25 city.ibd
-rw-r—– 1 root  root 856 Mar 28 19:25 country_capital.frm
-rw-r—– 1 root  root 1228 Mar 28 19:25 country.cfg
-rw-rw—- 1 mysql mysql   1618 Mar 28 19:25 country.frm
-rw-r—– 1 root  root 163840 Mar 28 19:25 country.ibd
-rw-r—– 1 root  root 665 Mar 28 19:25 countrylanguage.cfg
-rw-rw—- 1 mysql mysql   1542 Mar 28 19:25 countrylanguage.frm
-rw-r—– 1 root  root 229376 Mar 28 19:25 countrylanguage.ibd
-rw-rw—- 1 mysql mysql     61 Mar 28 19:25 db.opt

The form files are owned by user and group mysql but the tablespace and export files (.cfg files) are not. To be able to import the tablespaces you need to change the ownership.

chown -R mysql:mysql /var/lib/mysql/world2

Step 5 – Import the tablespaces

To complete the process you now need to import the restored tablespaces.

MariaDB [world2]> ALTER TABLE country IMPORT TABLESPACE;
Query OK, 0 rows affected (0.09 sec)
MariaDB [world2]> ALTER TABLE city IMPORT TABLESPACE;
Query OK, 0 rows affected (0.10 sec)
MariaDB [world2]> ALTER TABLE countrylanguage IMPORT TABLESPACE;
Query OK, 0 rows affected (0.06 sec)

After importing the tablespace the database is fully restored. A SELECT against the imported tables shows that they have all the data expected:

MariaDB [world]> select count(id) from world.city;
+———–+
| count(id) |
+———–+
|      4081 |
+———–+
1 row in set (0.01 sec)

MariaDB [world2]> select count(id) from world2.city;
+———–+
| count(id) |
+———–+
|      4081 |
+———–+
1 row in set (0.01 sec)

If all went well we only need to add the FOREIGN KEY CONSTRAINTS again which is done in Step 6.

During import of the tablespaces you might get an error saying that the flags of the tablespaces to be imported do not match with the flags of the newly created tables in the new database.

MariaDB [world3]> ALTER TABLE country IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don’t match, sserver table has 0x21 and the meta-data file has 0x1)

See Step 7 on how to get around this error.

Step 6 – Recreate FOREIGN KEY Constraint

Recreate FOREIGN KEY constraint on table city:

MariaDB [world2]> ALTER TABLE city ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`);
Query OK, 4081 rows affected (0.15 sec)
Records: 4081  Duplicates: 0 Warnings: 0

Recreate FOREIGN KEY constraint on table countrylanguage:

MariaDB [world2]> ALTER TABLE countrylanguage ADD CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`);
Query OK, 984 rows affected (0.12 sec)             
Records: 984  Duplicates: 0 Warnings: 0

Step 7 – Identifying the InnoDB File Formats

If the world database was created with a version prior to that the Antelope file format you have ROW_FORMAT=COMPACT which corresponds to FLAG: 1 (0x1). If the version you are restoring to uses Barracuda file format the ROW_FORMAT will be Dynamic which corresponds to FLAG: 33 (0x21).

So check the row format of the original tables if you still have access to them.

MariaDB [(none)]> select * from information_schema.innodb_sys_tables where name like ‘world/%’G
*************************** 1. row **************************
    TABLE_ID: 125
        NAME: world/city
        FLAG: 1
      N_COLS: 12
       SPACE: 131
 FILE_FORMAT: Antelope
  ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
  SPACE_TYPE: Single

This will also be the format of the tablespace files in
/opt/backup/2018-03-28_19-02-56/world .

Do the same check on the new tables. If it looks like in the example below you must proceed with Step 8.

MariaDB [world]> select * from information_schema.innodb_sys_tables where name like ‘world2/%’G
*************************** 1. row ***************************
    TABLE_ID: 159
        NAME: world/city
        FLAG: 33
      N_COLS: 8
       SPACE: 108
 FILE_FORMAT: Barracuda
  ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
  SPACE_TYPE: Single

If you still have access to a working copy of the original database and tables like in the example above you can do the FILE_FORMAT check before trying to import the tablespaces and do Step 8 before.

Step 8 – Adjust FILE_FORMAT and ROW_FORMAT

In this case you need to change the ROW_FORMAT of the new empty tables to COMPACT by issuing:

ALTER TABLE country ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE city ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)
ALTER TABLE countrylanguage ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

Now retry from Step 5.

Restoring a database to a MariaDB Galera Cluster

Generally speaking, this same procedure can be used to restore a single database to a MariaDB Galera Cluster. The imported tablespaces will only be available on the node where the restore has been executed since tablespace imports are not replicated to the other nodes. We will cover the whole how to restore a single database to a MariaDB Galera Cluster including samples in a later blog. Stay tuned!