How to Restore a Single Database from MariaDB Backup ...

ZPK HowTo ¨C Wissenswertes f¨¹r Ihren Erfolg

HOW TO RESTORE A SINGLE DATABASE FROM

MARIADB BACKUP - REVISITED

MAY 07, 2018 BY ULRICH MOSER

Lately, I¡¯ve been asked how to restore a single database / schema 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. Since

restoring a single schema means restoring all tables in that schema this guide could also be used to restore a single table although I

would not recommend that because you will most likely end up with some inconsistencies. Therefore my recommendation is to

always restore the whole schema even though only a single table might have been lost or corrupted.

We will use the world sample schema and a backup directory /opt/mariadb/backup/ as an example to explain

the process.

Step 1 ¨C Creating the Backup and Preparing the Database for Export

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

# TS=`date +¡±%Y-%m-%d_%H-%M-%S¡±`

# mkdir /opt/mariadb/backup/${TS}

# mariabackup --backup --user backup1 --password MariaDB --target-dir /

¡°/opt/mariadb/backup/${TS}¡±

This creates a directory /opt/mariadb/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. Before preparing the tables for export I recommend to copy the backup generation 1 you want to restore from in case you

later might need it for a disaster recovery. I personnally use a script to automate my database backups and this script packs the

backup into a compressed TAR archive at the end. To restore from that backup I need to unpack the TAR archive and prepare it but

this leaves the archive untouched. This way I can reuse it as often as I need it provided it is still on my backup device.

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/mariadb/backup/${TS}¡±

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

# cd /opt/mariadb/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 tablespace and hence no .cfg file.

Step 2 ¨C 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.

1

A backup generation is a full backup and all incremental or differential backups based on it.

? 2019, Ulrich Moser, ZPK Moser UG (haftungsbeschr?nkt)

ZPK HowTo ¨C Wissenswertes f¨¹r Ihren Erfolg

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 world\G

************************** 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 country\G

*************************** 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 city\G

*************************** 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 countrylanguage\G

*************************** 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)

If you do not have the CREATE statements you need to first restore the backup to an empty server and then get the statements from

there.

? 2019, Ulrich Moser, ZPK Moser UG (haftungsbeschr?nkt)

ZPK HowTo ¨C Wissenswertes f¨¹r Ihren Erfolg

Hint: I would recommend to take a mysqldump with option --no-data whenever the data model changes and store it with the

backups.

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.

In the CREATE DATABASE and the USE statement we replace world with world2.

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)

With this commands referential integrity constraints are not copied into the new schema.

Step 3 ¨C Discard the tablespaces

Since we want to import the tablespaces from the backup we prepare previously we first need to get rid of the tablespaces created

by the CREATE TABLE statements in Step 2 ¨C Creating the Backup and Preparing the Database for Export

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.

If you get an error like this

MariaDB [world2]> ALTER TABLE country DISCARD TABLESPACE;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

you still have the FOREIGN KEY CONSTRAINTS enabled. In this case drop them 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

Step 4 ¨C Copy the tables to restore to the new database directory

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

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

# ls -l

total 1008

-rw-r--- 1

-rw-rw¡ª- 1

-rw-r--- 1

-rw-r--- 1

-rw-r--- 1

-rw-rw¡ª- 1

-rw-r--- 1

-rw-r--- 1

-rw-rw¡ª- 1

root

mysql

root

root

root

mysql

root

root

mysql

root

686 Mar 28 19:25 city.cfg

mysql

1578 Mar 28 19:25 city.frm

root 606208 Mar 28 19:25 city.ibd

root

856 Mar 28 19:25 country_capital.frm

root

1228 Mar 28 19:25 country.cfg

mysql

1618 Mar 28 19:25 country.frm

root 163840 Mar 28 19:25 country.ibd

root

665 Mar 28 19:25 countrylanguage.cfg

mysql

1542 Mar 28 19:25 countrylanguage.frm

? 2019, Ulrich Moser, ZPK Moser UG (haftungsbeschr?nkt)

ZPK HowTo ¨C Wissenswertes f¨¹r Ihren Erfolg

-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) might not. To be able to import the

tablespaces you need to adjust the ownership.

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

Step 5 ¨C Import the tablespaces

To complete the process you now 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. So you can proceed to Step 7 ¨C Recreate FOREIGN

KEY Constraint and skip the next step.

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, server table has 0x21 and the

meta-data file has 0x1)

Step 6 ¨C Adjust FILE_FORMAT and ROW_FORMAT

If the world database was created with a version that used the Antelope file format you have ROW_FORMAT=COMPACT which

corresponds to FLAG: 1 (0x1). Since the backup is a physical copy of the data directory the tablespaces in the backup directory

will be of that same format. If the version you are restoring to uses Barracuda file format the ROW_FORMAT will be DYNAMIC which

corresponds to FLAG: 33 (0x21). In this case you need to adjust the file format of the new tables.

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

ALTER

Query

ALTER

Query

ALTER

Query

TABLE

OK, 0

TABLE

OK, 0

TABLE

OK, 0

country ROW_FORMAT=COMPACT;

rows affected (0.01 sec)

city ROW_FORMAT=COMPACT;

rows affected (0.01 sec)

countrylanguage ROW_FORMAT=COMPACT;

rows affected (0.01 sec)

Step 7 ¨C 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:

? 2019, Ulrich Moser, ZPK Moser UG (haftungsbeschr?nkt)

ZPK HowTo ¨C Wissenswertes f¨¹r Ihren Erfolg

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

At this point we are done with the restore of the database to the Recovery Point that is marked by the backup we chose for the

restore.

Step 8 ¨C Point-in-Time-Recovery for the Recovered Database

If recovery of a single database was done because of a data error or an accidentally dropped table or database you would probably

want to roll forward to the last transaction that was committed before the error occured or the accident happened.To perform a

point-in-time-recovery on a server or a single database you need to have all the binlogs starting with the one that is referenced in

the backups xtrabackup_binlog_info file.

There are two scenarios for a point-in-time-recovery. First scenario is reconstructing the tables up to the last successful transaction.

This is a typical case if a database has accidentally been dropped. The second on is restoring up to an errorneous statement

excluding that statement and optionally restore the following transactions up to the latest one.

In a follow up HowTo I will discuss both scenarios.

Step 9 ¨C 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

separate HowTo. Stay tuned!

Tags: #backup, #MariaDB_Backup, #MariaDB_Server, #restore, #tablespace, #tablespaces, #troubleshooting, #point-in-timerecovery

? 2019, Ulrich Moser, ZPK Moser UG (haftungsbeschr?nkt)

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download