CODEWITHSUNDEEP
phpsqlmysqldatabasebackup
user188962
user188962

Reputation:

Backup MySql database with PHP

I have a pretty large db in MySql, and I need to take backups of it every day or so.

I need to be able to take backups from any computer, so therefore I thought about making a php script to do this and put this php script online (offcourse with password protection and authorization etc so that only I can access it).

I wonder however, how is this done properly?

What commands should I use, and is it possible to change settings of the backup (for instance Add AUTO_INCREMENT value = true)?

I would appreciate examples...

Also, if this is a bad method (unsafe, or maybe gives bad backups with bad sql files), what other method would be preferred? I have shell-access and I have a VPS (ubuntu server).

My Mysql version is 5.1

Thanks

Upvotes: 4

Views: 1575

Answers (2)

Jaydee
Jaydee

Reputation: 4158

I agree with OMG Ponies mysqldump + script is the way to go.

The only other option that I use is to set up a slave server. This provides an almost instant backup against hardware failure and can be located in a different building to your main server. Unless you have a large number of writes to the database, you don't necessarily need a very powerful server as it is not processing queries, only database updates.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332531

There's no need to involve PHP in the database backup. You just need a script that uses mysqldump to backup the database, and setup a CRON job to periodically execute the script:

mysqldump db_name > backup-file.sql

...will backup your database to a file, by redirecting the output from the mysqldump to the specified file name.

Peter brought up a good point, that the command would only give you one day of archiving--any archive over two days old would be overwritten. This would allow you have a rolling log going back seven days:

CURRENT_DAY_OF_WEEK=`date '+%u'`
FILENAME="mysqlbackup_"$CURRENT_DAY_OF_WEEK".sql"

mysqldump db_name > $FILENAME

Also be aware that file permissions will apply - can't write a file if the user executing the script doesn't have permissions to the folder.

Upvotes: 8

Related Questions