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

jason
  • 79
  • 1
  • 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 May 20 '14 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 Jun 24 '14 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 Jun 24 '14 at 14:30
  • BTW I have scripts to parallel dump 10 databases or 10 tables at a time : http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database/2227#2227 – RolandoMySQLDBA Jun 24 '14 at 14:31

1 Answers1

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.

akuzminsky
  • 4,967
  • 14
  • 16