Backup & recovery of multiple MySQL databases (InnoDB & MyISAM)
- by Cymon
I am working on nightly and hourly backups of MySQL Databases. There are multiple MySQL databases which are either InnoDB or MyISAM (Note: Each database is either InnoDB or MyISAM for a reason). With the 2 different types I want to make sure I am grabbing everything that is needed for backup and recovery. Here is my current plan
Nightly
-mysqldump of each DB which is stored locally and remotely.
Hourly
-flush binary logs and store them locally and remotely.
Weekly
-expire binary logs older than a week.
I feel like I am grabbing everything that is needed for the MyISAM databases but I am concerned about the InnoDB databases and the log files (ib_logfile0, ib_logfile1, ibdata1) they create. Should I backup these files? Nightly? Hourly? Both? Do I really need them if I am already doing the above nightly and hourly backups?
