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 ?
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.
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):
Then restart MySQL and you'll be using your recovered databases which you can use
If you're using InnoDB tables you may need to read through this.