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?

Improve this question
2
  • with mysqldump it's difficult (although facebook does it). what you need is incremental backups with xtrabackup. twindb.com/how-to-setup-mysql-incremental-backup
    – akuzminsky
    Commented Dec 3, 2015 at 17:32
  • @akuzminsky yup, found that too. But I don't mind using a tool different to original mysqldump if it helps
    – oldhomemovie
    Commented Dec 3, 2015 at 17:33

1 Answer 1

Reset to default
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...
Improve this answer

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.