I am new to MySQL administration, and must devise a backup plan, I have read
the manual but still want to know if I am understanding and applying the
principals correctly, please let me know if you have a better way, or if I
am wrong somewhere, my main question is regardng the locking of the database
while dumping, is it strictly neccessary (see [Confusion] block)?
I am trying to devise a backup plan for MySQL ISAM Databases, I have chosen
to use binary logs to enable incremental an incremental backup plan.
I need to eliminate possibility of data being lost, and assure integrity of
data, I will mainly be dealing with the former in this message.
The Backup Plan:
Preparation:
1. Enable binary logging on server
2. Prepare backup location (I was thinking SCP over the internet to a backup
host, any faster/better soloutions?)
3. Write full and incremental script(s)
4. Set up cron jobs to run the incremetal and full backup scripts daily, and
weekly respectivley
5. Run the Full backup script once
[Full backup script actions]
1. Lock the database so it cannot change,
2. Backup the entire database via a dump, compress, transfer
3. Reset the logs (RESET MASTER, causing binlog index to be reset,
ALL logs deleted and new log file created, thats ok, we have a full backup)
4. Unlock the database
[/Full backup script]
[Incremental backup script actions]
1. Flush logs
2. backup all logs except the last one via compress and transfer
3. purge all logs except the last one
[/Incremental]
[Schedule]
Day 1: execute incremtal
..
Day 7: execute incremtal
execute full, binary log index is reset
[/Schedule]
Please tell me if this is the best & correct way to do it, and if any of my
following statements are incorrect/I have missed something:
[Confusion]
You need to lock the database when doing a full backup to maintain integrity
of the logs, after the backup time becuase without locking:
1. If you flush logs, then dump, and a change occurs which is reflected in
the dump, restoring the log to this backup will duplicate the change.
2. If you reset logs then dump, you have lost the information between your
last backup and the dump if the dump fails
3. If you dump then reset, and data has changed in between the dump of a
record and the new log, that change will be lost forever.
4. If you dump then flush (excuse the pun), restoring the log from before
the dump to the dump could duplicate data
[/Confusion]
Whereas:
If you lock the database, then dump, then reset logs and unlock, you have an
exact mirror of the data, and a log file which contains changes after the
dump was made
Thanks.
