How to create a SQL dump from the MySQL database files
772
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 ?
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.
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/mysqlto, 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
mysqldumpagainst.If you're using InnoDB tables you may need to read through this.