How to restore a database?

Asked by usef on 2012-08-06

Hello,
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

Language:
English Edit question
Status:
Solved
For:
Percona XtraBackup moved to https://jira.percona.com/projects/PXB Edit question
Assignee:
No assignee Edit question
Solved by:
Alexey Kopytov
Solved:
2012-08-09
Last query:
2012-08-09
Last reply:
2012-08-07
Alexey Kopytov (akopytov) said : #1

Please read the Partial Backups section in the XtraBackup manual: http://www.percona.com/doc/percona-xtrabackup/innobackupex/partial_backups_innobackupex.html

usef (albarak) said : #2

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:
http://www.percona.com/doc/percona-xtrabackup/xtrabackup_bin/incremental_backups.html

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?

Thanks

Best Alexey Kopytov (akopytov) said : #3

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.

usef (albarak) said : #4

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

usef (albarak) said : #5

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

usef (albarak) said : #6

Thanks Alexey Kopytov, that solved my question.