What are the consequences if I backup LIVE databases every 3-hours using mysqldump?

If I am not mistaken, when executing mysqldump, it will lock the tables, isn't it? Erm, never mind, my question is:

What are the consequences if I backup LIVE databases hourly using mysqldump?

I have lots of databases (around 15), but the ones that I need to backup only 10 databases (2 of them are quite huge in size, around 40 mb). Thus, in my batch file, I execute mysqldump per databases not using "--all-" option.

I wanted to backup those important databases as often as I can, I am planning to do a backup every 3 hours. However, I am concerned with the performances, I afraid it will slow down my website. Any advise?

You will put a huge load on your database servers IO subsystem.

If you need to do this, then my advice would be to setup mysql replication to a second server and run the backup from there.

Technically once you have this replication running, the binary logs on your slave are good enough for point in time recovery, and you shouldn't need to run a full backup so often.

Also, when running the backup consider the --single-transation flag on mysqlbackup to avoid doing a table level lock on each table. This requires you are using InnoDB not MyISAM, which you should be doing anyway.

Unless you meant 40G and not 40M you should have absolutely no problem doing it. 40M is not huge, its quite tiny :) and should definitely fit into memory (even considering the rest of the databases which as you said are even smaller), that is unless you a very low on memory and didn't give your mysql enough of it, but even then even loading the data completely off disk should only take a couple of seconds.

I suggest you time it at least once with the 'time' shell command.

while the backup is going (and assuming you use the right options (I'm using "-ceKq --single-transaction --create-options") all queries to the db from your app will be locked, but if its just a second once every couple of hours I wouldn't be too worried unless you have a reeeeeely hight traffic site, in which case mysqldump is not really a backup option.

LVM snapshots can provide frozen images of you database's file system in a fraction of a second. If using MyISAM tables, the procedure would be to:

1. flush tables with read lock; # lock all tables in the server
2. create the LVM snapshot
3. unlock tables;

The lock will only be held for only a second or so.

The snapshot can then be mounted and a second mysql instance run against it. Or, you can just compress and copy the snapshot to a remote system where it will be kept until needed.