2

Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly. Complicated by the fact that:

its a 400 gb DB and growing its running on windows I am not permitted any real downtime

I have two replication servers going which I used mysqldump with a read lock to get going and I plan to implement any back-up strategy from a slave.

I have thought about setting up an additional replication server and running percona xtrabackup from it but I am not sure what my recovery strategy might look like taking back-ups from a linux box to restore on windows.

mysqldump is no longer a good strategy as the full process dump/zip/move/restore is 2 days at this point.

it's innoDB with a few myisam log tables mixed in.

looking for any insight or direction from those that have gone down this road already. Any options outside of oracles enterprise back-up?

thanks

Improve this question
4
  • 1
    Questions: 1) Do you mysqldump each database into separate files ? 2) Do you mysqldump each table into separate files ? 3) Are the MyISAM logs the biggest files ? 4) Do you purge the logs file periodically ?
    – RolandoMySQLDBA
    Commented May 20, 2014 at 20:39
  • sorry RolandoMySQLDBA, I failed to see your questions. yes currently due to size I created a script that dumps each table (2k tables) separately and i broke the script up into 4 separate so i can run 4 mysqldump scripts at the same time. This way I have it down to 4 hours-ish but I imagine the restore in the event I needed to would be bad. yes I purge logs. I think running on windows is really hurting my options but unfortunately I don't have a choice right now. thanks for the help.
    – jason
    Commented Jun 24, 2014 at 14:21
  • Questions: Can you convert the MyISAM tables to InnoDB ??? Do you use FULLTEXT indexes ??? If the answer to the first question or both questions is Yes, I can post an answer to backup without a second of downtime.
    – RolandoMySQLDBA
    Commented Jun 24, 2014 at 14:30
  • BTW I have scripts to parallel dump 10 databases or 10 tables at a time : dba.stackexchange.com/questions/20/…
    – RolandoMySQLDBA
    Commented Jun 24, 2014 at 14:31

1 Answer 1

Reset to default
1

xtrabackup from a slave will work fine.

If you apply redo log on a linux machine(innobackupex --apply-log) you can use the restored datadir on windows.

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.