Backing Up MySql Database Using PHP

Question

I have a pretty big db in MySql and I need to backup it every day or so.

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

I wonder how this is done right?

What commands should be used, and is it possible to change the backup settings (for example, Add AUTO_INCREMENT value = true )?

I would be grateful for examples ...

Also, if this is a bad method (unsafe or possibly giving bad backups with bad sql files), which other method would be preferable? I have shell access and I have VPS (ubuntu server).

My Mysql version 5.1

thanks

Answer 1

There is no need to include PHP in the database backup. You just need a script that uses mysqldump to back up the database and set the CRON job to run the script periodically:

 mysqldump db_name > backup-file.sql 

... will back up your database to a file by redirecting the output from mysqldump to the specified file name.

Peter took the good point that the team will give you only one day of archiving - any archive will be overwritten within two days. This will allow you to have a sliding log returning seven days:

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

Also keep in mind that file permissions will be applied - it is impossible to write a file if the user executing the script does not have permissions to access the folder.

Answer 2

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

The only other option I use is setting up a slave server. This provides an almost instant backup of a hardware failure and may be in a different building on your primary server. If you do not have a large number of records in the database, you do not necessarily need a very powerful server, because it does not process requests, but only database updates.