3

Percona XtraBackup gets a lot of praise, from what I can see, but I find it incredibly frustrating. I'm using:

root@GR-00258:~# xtrabackup --version
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

I can create backups of one or more single databases without problems, but there doesn't appear to be any way to restore them. The only way I have found is to restore them as a full backup into an empty /var/lib/mysql, which means mysql won't start up, of course. It seems like a remarkably poor tool for restore - what is the purpose of being able to make a backup of individual databases, if they cannot be restored?

Enough ranting - is there a way to get this to work, or am I just wasting my time? I know, I can use mysqldump into a csv file, but that is not an attractive option, when the databases are ~500GB - ~1TB.

Nico Haase
  • 11,420
  • 35
  • 43
  • 69
j4nd3r53n
  • 680
  • 2
  • 11
  • 26
  • 1
    [Per the Percona Docs](https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_a_backup_ibk.html) -- According to this, you may have to do some manual directory stripping and permission changes etc .. But seems pretty straight forward. – Zak Sep 04 '19 at 15:37

1 Answers1

2

Percona XtraBackup allows you to make a physical backup of a MySQL datadir without blocking access for clients. It's significantly faster than mysqldump to make a backup.

Restoring is also very fast. All you have to do is copy the backup files to a new datadir (after doing the prepare step, which you could do at the time you create the backup).

When restoring a full backup, you can't do that when the destination MySQL Server is running. You must shut down mysqld then copy the files into place, make sure the files have the right ownership and permissions, then start mysqld.

It's not easy to import selected tables or schemas without overwriting. But it is possible:

You must use the --export option when you prepare the backup. Then you can import individual tablespaces to an existing MySQL datadir. But you must do this one tablespace at a time, unfortunately. There's no way to do it in one step for all tables in a schema. You should be able to write a script to do that.

See a complete example of importing tablespaces from a backup here: https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That seems to be for version 8.0 only - I can't seem to find the same documentation for earlier versions(?) – j4nd3r53n Sep 04 '19 at 15:53
  • https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html – Bill Karwin Sep 04 '19 at 16:12
  • Literally just replace "/8.0/" in the URL with another version number. – Bill Karwin Sep 04 '19 at 16:13
  • I admit the XtraBackup documentation is badly organized. – Bill Karwin Sep 04 '19 at 16:13
  • Thanks for helping me - I've accepted your answer. I think it would be a really good idea if xtrabackup could simply restore individual databases - the very existence of the "--databases" option suggests that this functionality is supported. – j4nd3r53n Sep 05 '19 at 07:54
  • Not if you actually read the documentation for that option and understand what it does! :-) It restricts the database(s) that are backed up. It does not change the way XtraBackup restores a backup. See also https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/partial_backups_innobackupex.html – Bill Karwin Sep 05 '19 at 15:43
  • What?! Read documentation? ;-) – j4nd3r53n Sep 06 '19 at 07:34