Not the answer you need?
Register and ask your own question!

Can't restore partial backup

apirasakapirasak EntrantCurrent User Role Beginner
I'm a newbie and practice in Percona xtrabackup tools
I try backup database by
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --database="mydatabase" /home/mysql/Backup/
status completed.

innobackupex --apply-log --export /home/mysql/Backup/2014-09-24_12-23-19
status completed

I try to restore but got error message.
innobackupex --copy-back /home/mysql/Backup/2014-09-24_12-23-19
innobackupex: Error: Original data directory '/database/data' is not empty! at /usr/local/xtrabackup/bin/innobackupex line 2162.

Note: in /database/data have more than one databases are running.
How can I restore database??? I want to restore only one database.

Thank you.

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Please follow the steps below for partial backup.

    Take partial backup where "mydb" is the intended database which you need to backup.
    1) $ mysql --skip-column-names -e "SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema = 'mydb';" > /path/to/tables.txt
    $ innobackupex --tables-file=/path/to/tables.txt /path/to/backup/

    You can read further here about partial backup http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

    Apply log with --export option. This will create prepare each table with .exp and .ibd files.
    2) innobackupex --apply-log --export /path/to/backup/

    On importing server create tables manually which need to restore
    3) CREATE TABLE test ...

    Discard the tablespace
    4) ALTER TABLE test DISCARD TABLESPACE;

    Copy the .exp & .ibd files to the datadir where need to import
    5) ALTER TABLE test IMPORT TABLESPACE;

    It's mandatory that your source server from where you taking the backup have innodb_file_per_table option enabled and the importing server should have innodb_file_per_table and innodb_expand_import enabled (only supported for Percona Server). You can read more about it here http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/importing_exporting_tables_ibk.html#importing-tables

    Further, If you are using MySQL/Percona Server version 5.6 then you need to use Transportable Tablespace as mentioned here http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

    Hope that helps.
  • willamowiuswillamowius Entrant Inactive User Role Beginner
    Does the 2nd invocation of innobackupex with --apply-log --export have to be done at the time of the backup ? Or can it be done when the backup needs to be restored ?

    Using the full path (including the date) is not very handy to aoutomate when doing backups.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.