Hi, I need someone to help me figure out how I can run a mysql backup of all the sites I host every 24 hours.
I've recently been having troubles with MySQL and im in need of something than I can run in a cron that will backup sql like so "site1-092604" I don't feel the need for a secondary server that backs up my sites when I can just keep it on the same PC for now.

Excuse me if there is a script like this already, it would help if I could get some names =D.

You can use the mysql command mysqldump found in the mysql/bin folder.

You would do something like

"mysqldump databasename > path/site1-092604"

path being the folder you want this to output to,
you'll also want to gzip that file to reduce the size.

Keeping backup(s) on the same server is pointless.

You need dislocated server in case of <name your favorite natural disaster> or <name your favorite human error>.

Good luck.

I don't plan for my servers to go down, and just incase something happens to MySQL I need them backed up since alot of good sites are run on it, also one of the main uses on my server. A backup FTP account storing backups may be added at a later time.

Originally posted by stickthemepark
I don't plan for my servers to go down, and just incase

That's a good thought to have... most servers that crashed are normally pre-planned.

I had a server a few weeks ago that both drives blew up, thanks god I already had plans for that server to blow up.

</sarcasm>

Ok, seriously, no one plans on servers blowing up. It just happens. So if you're going to spend the time to DO backups, make them useful and actually store them someplace OFF SITE.

Sirius

A very nice MySQL backup script is here:

http://sourceforge.net/projects/automysqlbackup/

Originally posted by RexAdmin
Keeping backup(s) on the same server is pointless.

You need dislocated server in case of <name your favorite natural disaster> or <name your favorite human error>.

Good luck.

While it's far from as good as off-server backups, it is not true that keeping backups on the same server has no point... there are several problems which could happen that could be fixed by same-server backups.

mysqlhotcopy is a better idea

Originally posted by amusive.com
While it's far from as good as off-server backups, it is not true that keeping backups on the same server has no point... there are several problems which could happen that could be fixed by same-server backups.

You have got to be kidding me. So you condone keeping your backups on the same server?

Hmmmm.

Sirius

Originally posted by sirius
You have got to be kidding me. So you condone keeping your backups on the same server?

Hmmmm.

Sirius

Did you read what you are quoting? He clearly stated that "it's far from as good as off-server backups" but " there are several problems which could happen that could be fixed by same-server backups."

I completely agree with his statement. Having a backup on the same server is a good idea. If you have a database go corrupt or accidently delete some data, you can easily recover it. As amusive.com stated however, it is no substitute for an off-server backup.

The lesson here is that you should do both!

Originally posted by sirius
You have got to be kidding me. So you condone keeping your backups on the same server?

Hmmmm.

Sirius

Did you read my whole post? It's far from ideal but there are MANY situations, perhaps some he expects or could see happening, that on-site backups will work for. In fact a good backup plan should have BOTH off and on-site backups; under many situations on-site backups can be used and will be significantly faster to restore. You just have to realize when you can and can not use on-server backups (eg: if you are hacked, do not use an on-server backup).

Also, depending on how mission critical your stuff is, it may not matter too much anyway.

Personally, I do both -- I backup all mysql databases daily onto the same server and also daily backup everything to another NOC. The on-server backup HAS been useful:

One time a certain user used the wrong tar syntax and destroyed part of his mysql db. Since he knew where we kept the onsite backups (in the user's home dir in a subdir) he was able to fix his problem instantly. If we had only offsite backups this would have been impossible, as end users do not gain access to that machine.

Originally posted by amusive.com
Did you read my whole post? It's far from ideal but there are MANY situations, perhaps some he expects or could see happening, that on-site backups will work for. In fact a good backup plan should have BOTH off and on-site backups; under many situations on-site backups can be used and will be significantly faster to restore. You just have to realize when you can and can not use on-server backups (eg: if you are hacked, do not use an on-server backup).


Ohhhh.... nevermind. :smash:

Yes, I completely misread what you wrote, hence my reply.

No, I agree that on site backups are good, but I would only count that as 5-10% of your overall backup plan.

Offsite backups, IMHO, are an absolute requirement for any business.

Sirius