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

[Pages:5]ZPK HowTo ? 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 ? 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 generation1 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 ? 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 ? 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 ? 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 ? 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 ? 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 ? 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 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

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

ZPK HowTo ? 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 ? 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 ? 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 ? 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 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)

Step 7 ? 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 ? 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 ? 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 ? 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