How to restore a database?

Asked by usef

I am testing how to make an incremental backups for a single innodb database and how to restore it. I am having difficulies with restoring the database. Here is my steps:

1 - Creating full backup ./xtrabackup --backup --target-dir=/home/backup/base --databases=db1

2 - creating incremental backup after changing some rows ./xtrabackup --backup --target-dir=/home/backup/inc --incremental-basedir=/home/backup/base --databases=db1

3 - Preparing :
./xtrabackup --prepare --apply-log-only --target-dir==/home/backup/base

./xtrabackup --prepare --apply-log-only --target-dir==/home/backup/base --incremental-dir==/home/backup/inc

Now after running these commands, I found a new folder called backup inside the data folder. The folder contains the following files:

ibdata1 xtrabackup_checkpoints xtrabackup_logfile

I stopped at this point, what should I do know?
For testing ,I stoped MySQL and removed the existing database folder db1 and the file ibdata1 inside the data directory and then I cpoied the ibdata1 from the backup folder to the data directory and started MySQL but I did not work.

Thanks for help :)

Question information

English Edit question
Percona XtraBackup moved to Edit question
No assignee Edit question
Solved by:
Alexey Kopytov
Last query:
Last reply:
Revision history for this message
Alexey Kopytov (akopytov) said :

Please read the Partial Backups section in the XtraBackup manual:

Revision history for this message
usef (albarak) said :

Hi Alexey,
I read the documentation but still I did not find the way to restore the database. I have full backup and incremental back up. I followed the explanation here:

What is the next step after running the following command:
./xtrabackup --prepare --apply-log-only --target-dir==/home/backup/base --incremental-dir==/home/backup/inc

How can I use the generated files to restore the database?


Revision history for this message
Best Alexey Kopytov (akopytov) said :

As explained in the Partial Backups documentation I referenced, when backing up and restoring individual databases or tables, you have to use the --export option to prepare the backup, and then use ALTER TABLE DISCARD/IMPORT TABLESPACE to restore them.

Revision history for this message
usef (albarak) said :

Hi Alexey,
I followed the documentation, I used --export option to export the database. In the documentation it is mentioned that I should get .exp and idb files but I got idb files only, .exp files do not exist.

here is my command
./xtrabackup --export --prepare --apply-log-only --target-dir==/home/backup/base

Thanks for your time Mr.Alexey

Revision history for this message
usef (albarak) said :

Thanks Alexey. I got it working, I run the incremental and the I got .exp files.

Revision history for this message
usef (albarak) said :

Thanks Alexey Kopytov, that solved my question.