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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments