SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: mysqldump
-
Jun 19, 2008, 20:29 #1
- Join Date
- Jun 2008
- Posts
- 6
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
mysqldump
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.
-
Jun 20, 2008, 03:59 #2
- Join Date
- May 2007
- Posts
- 109
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jun 20, 2008, 04:03 #3
- Join Date
- Jun 2003
- Location
- East Coast
- Posts
- 2,215
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How large is your database?
█ Nick . all that we see or seem, is but a dream within a dream
█ Show someone you care, send them a virtual flower.
█ Good deals on men's watches
-
Jun 20, 2008, 05:53 #4
- Join Date
- Jun 2008
- Posts
- 6
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 20, 2008, 06:41 #5
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
Bookmarks