PDA

View Full Version : Lots of databases, how does it work?



sfatula
March 10th, 2009, 01:43 AM
Say I have 100 databases to backup on one machine, of various sizes. If I use one job to backup all, with binary logging, I presume all 100 databases are read locked while the backup is going on? So, until the last one completes, the first one is still read locked.

If not, then, I don't see how the binary logs would work. You can't read lock database 1 only, backup, then 2 (which now has likely stored more stuff in binary log), etc.

If it does read lock for all 100 database, this could be a long time. So, wondering how best to do this.

snapshot is not an option. Databases may contact isam, innodb, or both.

I am concerned about downtime.

I realize I can create separate jobs, but, back to the binary logging.... Not sure how that can work. Each database does not have it's own binary log. I want to retain useful binary logs for roll forward recovery for any database.

kkg
March 10th, 2009, 02:51 AM
If snapshot is not an option, you probably want to use separate backupsets for each database. In this case your incremental backups will contain data for all databases. But when you restore, ZRM will ensure that only data related to the specified database gets restored.

Hope this helps
--kkg

sfatula
March 10th, 2009, 10:10 AM
So, is ZRM not opening a new log file when it does a backup?

If current binlog is #5, and, I backup ONE db, it changes to 6, or no? So, if I backup 100 DBS all separate, is it now on log 105? Obviously none of those logs can be purged. How do you know the starting position in the binlog for a given db? After all, if you want to restore to a point during the day, you have to know where to start the roll forward from. And how can it store the binlog with the backup, since the binlog is used for all 100 databases?

kkg
March 10th, 2009, 09:35 PM
So, is ZRM not opening a new log file when it does a backup?

If current binlog is #5, and, I backup ONE db, it changes to 6, or no? So, if I backup 100 DBS all separate, is it now on log 105? Obviously none of those logs can be purged. How do you know the starting position in the binlog for a given db? After all, if you want to restore to a point during the day, you have to know where to start the roll forward from. And how can it store the binlog with the backup, since the binlog is used for all 100 databases?

Everytime a backup is taken, the logs are flushed so if 100 backups are taken there will be 100 log files. We also keep track of which was the last logfile that was present before the flush and during the next incremental backup we pick up the log files that have been created after this. As I said before, the incremental backup will contain data from all of the 100 backups but during restore ZRM will restore only data related to the specified database(s).

--kkg