[vox-tech] Tar, MySQL, and cron-ed backups

[David Hummel]

On Fri, Dec 05, 2003 at 04:43:25PM -0800, Mitch Patenaude wrote:
> Basically, you can use a tool like mysqldump to dump a version of the
> tables, which can be used to reconstruct the database later.   If you
> don't have enough disk space, then get another (or bigger) disk.  Even
> then, transactional rules that aren't strictly enforced by the
> database may lead to inconsistent data if you're dumping a "live"
> database.
>
> For example... Say the mysqldump script is working on an order 
> processing database.  If it dumps the customer table first, followed by 
> the product table and then the order table you can end up with a 
> customer record which was added to the database after the dump for the 
> customer table completed, (and therefore isn't in the backup) but a 
> corresponding order record was added before the backup of the order 
> table, which means that the restored database would have an order 
> corresponding to a non-existent customer record, violating the foreign 
> key constraint.
> 
> If you "fix" this by backing up the order table first, then you end up 
> with the same problem with customer record deletions.  The solution 
> here is to have a way to "suspend" certain types of operations during 
> the backup.  (e.g. don't delete customers/orders, just set a flag in 
> the table that says they're invalid, and delete them later.. this is 
> still no guaranty though... but it may lessen the amount of work needed 
> to manually "fix" the inconsistencies when you have to restore.

If you're worried about inconsistencies, use the -l switch to mysqldump.
This is a per database lock, so if you're dumping multiple databases
with related tables, you might run into problems.

Marc didn't say what version of MySQL server he is running, or what
table types he is using.  You need InnoDB tables for transactions, and
server 4.0.x for foreign key constraints.

But none of this matters if you use mysqldump -l.

So the revised command might be:

  mysqldump -l --opt mydb | gzip > mydb_`date +%y%m%d%H%M%S`.sql.gz

-David