mysqldump

[resonator]

When I'm making a backup of mysql databases, during the dump apache is unable to load pages because mysql is no longer responding to new requests.

The backup is taking 5 minutes or so, which essentially brings down the website until it's finished. Has anyone got any suggestions on how to overcome this problem?

I'm thinking maybe if I could quickly make a copy of the database to another database within mysql, then backup the copy that was just made.

Thanks.

[godisatheist]

Hi,

how are you doing your backups? 5 minutes is very unusual...
I'm doing backups on the command line using mysqldump and it's always very fast.
You can also do your backups in the middle of the night using a cronjob, which will do the backup job for example daily on 04:00. This time the five minutes won't be a problem, as many big websites have their website offline for a longer time.

If you just copy your database, this will not wait for table locks or transactions to be finished, so soon or later you will have failures in the database.

Greetings, Andreas

[Nick]

How large is your database?

[resonator]

I'm using mysqldump via cron every 8 hours. I just timed it properly and it took 3 minutes, to dump a database of 1.5GB uncompressed.

I'm thinking if I were to put up a maintenance message during the dump, then I can probably get away with a little downtime if it must be. If possible though, i'd prefer to be able to create a backup without any downtime.

[longneck]

with myisam tables, it's usually faster to copy the database tables than it is to use mysql dump. first LOCK the tables in question, then FLUSH the tables. you can then copy the .frm, .myd and .myi files directly out of the mysql data directory. finally, UNLOCK your tables.

mysql actually provides a script for doing this called mysqlhotcopy.

if you're using innodb tables, you should use innodb hot backup instead.