How to create a SQL dump from the MySQL database files

Question

I am using Percona's xtrabackup to take backup of my online databases. Is there a way to convert the backup database files into a SQL dump similar to a backup created using mysqldump ?

Answer 1

Unfortunately, the way Percona performs XtraBackup procedure includes running something like rsync against a running production server and then applies InnoDB crash recovery against a tmp folder (the backup directory) with the option to generate ib_logfile0 and ib_logfile1 that can be drop-in replacements for the ones in production. That data is just a backup.

If all your data is InnoDB, you are better off just doing a mysqldump against production using the --single-transaction parameter. Even better, you can do parallel mysqldumps of databases or inidivdual tables again using --single-transaction parameter on each mysqldump process.

Answer 2

Do you mean it's taking a file level copy of /var/lib/mysql (or the Windows equivalent)?

If that's the case, restore the files somewhere and change the MySQL data directory to where you have restored /var/lib/mysql to, e.g. in /etc/my.cnf (or the Windows equivalent):

datadir=/tmp/restore/var/lib/mysql

Then restart MySQL and you'll be using your recovered databases which you can use mysqldump against.

If you're using InnoDB tables you may need to read through this.