0

I'm backing up production data locally to perform experiments and investigate production bugs.

I need only a handful of tables from product DB. I'm using generic mysqldump and now facing a problem where backing up & restoring takes around 20 minutes.

My question is: is there a way to incrementally amend the data I already have with what appeared on production since the last time I dumped the selected tables?

2

1 Answer 1

1

You have few options to consider but first I hope you're not troubling your production server but may be a slave of it to dump out things? (things may get bad there.)

  1. If you are only needing data that's changed since last backup, and your tables does have modified date as one of the columns (consider to add if you can to ease the process?) you can:

mysqdump --where=' modified_date_column>="Last_bkup_dttime"' XYZ tabll1 table2 table3 > tables.sql

  1. How about setting a slave replicating only tables of choice?

  2. Did you know about mydumper/loader? That can take faster dumps than mysqldump.

  3. Are you backing up your data daily? If yes then why don't you setup an automatic nigtly restore process that restores your dev with latest backup?

  4. You may also choose to play the binary logs from production and discard other tables:

Generate sqls from each of the binlogs: mysqlbinlog --db=dbofyourchoice binlog.000xxx > binlog.000xxx.sql

Play that all those sqls with --force to ignore errors. mysql dbofyourchoice --force < binlog.000xxx.sql

  • and ofcourse partial backups from xtrabackup is there...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.