backup large DB daily?

jasondavis

Is there a good method of backing up a large DB daily, large as in over 100mb

devinemke

are you using mySQL? if so mysqldump

Sxooter

jasondavis wrote:
Is there a good method of backing up a large DB daily, large as in over 100mb

Just FYI, 100M isn't a really big database. 100M is actually pretty small. 10G is medium sized. 1T, that's a big database, just barely. 🙂

But for 100M, yeah, mysqldump is just fine.

jasondavis

Sxooter wrote:
Just FYI, 100M isn't a really big database. 100M is actually pretty small. 10G is medium sized. 1T, that's a big database, just barely. 🙂

But for 100M, yeah, mysqldump is just fine.

Thanks for the info actually 100mb is just an example it will actually grow much larger I have a site similar to myspace so my DB grows rapidly, ones I get in the GB stage will mysqldump work good? ALso can mysql dump be ran without me doing ti manually?

Sxooter

If you're going to have a db that big, you need to use a replication slave or two, and you can back up from one of them whlie the site is still up.

You can cron mysqldump to run in the night. Pretty much anything you can do from the command line in unix, you can do with crontab.

jasondavis

does replication slave involve multiple servers?

Sxooter

jasondavis wrote:
does replication slave involve multiple servers?

Generally, yes. you could theoretically run two servers on one machine and use one for backups, but I'm not sure that would be a good idea performance wise.

MarkR

There are really three possible ways to get a CONSISTENT backup:

If you use any non-transactional tables (e.g. the default MyISAM), and you are prepared for a short scheduled downtime, use mysqldump with its options to lock the database. This will prevent ANY writes to the database during the backup. Any attempted writes will block (be delayed) until the backup completes (Note that PHP may timeout those requests before that happens)
If you exclusively use transactional tables (e.g. InnoD😎, then you can take a backup in a transaction without locking anything.
If you are prepared to set up replication with a slave server, you can do backups on that without blocking out the live server - but beware of things which don't replicate correctly. You may want to set up a system to compare the data in the slave(s) with your master - this is by no means guaranteed.

100 Megabytes (I don't know whether that's the size of your data or data + indexes, you didn't specify) is NOT a large database. It is likely that mysqldump'ing it on modern hardware will take less than five minutes.

If you are prepared to have scheduled downtime once per day, you could have your backup script set a flag to stop people using your web site during the backup period. Equally, you could have a period during which you display a message to people before the scheduled downtime to tell them it's going to happen soon (so that they have time to finish what they're doing).

Our master database is about 1.5 Gb as a compressed dump file and takes about 10 minutes to do a full dump (on modern server hardware: Recent xeon processor, lots of ram, raid discs).

Mark

Sxooter

Note that you can also do file system level backups using LVM on linux with snapshots. There are other ways to snapshot as well, such as with certain storage arrays. This is instantaneous and doesn't interrupt your db at all.

If you're database is going to grow as large as you are saying, you'll likely want to go with external storage anyway.