4

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?

5 Answers 5

6

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.

4

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.

2
  • 1
    +1 - if it's only 40MB, mysqldump is fine and you probably won't notice it at all. -single-transaction and --skip-lock-tables are useful.
    – James
    Feb 22, 2010 at 15:58
  • Thank you for this practical, straightforward answer that takes scale into account! Nov 6, 2018 at 23:22
2

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.

0

You can do it, but I'd suggest trying to break it into multiple different jobs, spaced over that 3 hour period. This should be possible if the 10 relevant databases are independent. That way you don't just get one big, long period of intense I/O activity. Smaller chunks are better.

0

I'd go with Daves advice. Set up a replication slave, and run your backups there. You dont want to impact your production database server. I would give MySQL-ZRM a shot if you want a nice out of the box tool. The community version works fine. When you config ZRM you can select that its a slave, and it will stop the replication for you while you back up. Also as Dave says, its just plain good to have that slave and its logs. MySQL-ZRM

You must log in to answer this question.