Welcome to Dagon Design. In addition to free scripts, WordPress plugins, and articles, we offer a variety of services including custom theme design, plugin creation, and PHP scripting. Contact me for more information.

Version 2.1 Updated Thursday, May 3rd, 2007 at 8:08am

Automatic MySql Backup Script

This script finds all of the MySQL databases on your server, individually backs them up (for easy recovery of specific data), compresses them, and stores the file in a specified directory and/or emails it to you. It has been updated since the initial public release and includes several new features.

I would like to thank Oliver Mueller for his many contributions to this release, including additional features, fixes, and testing. Thanks Oliver!

Download v2.1

If you have found this page useful, please consider donating. Thanks!

Other versions

backup_dbs_austin.txt – a custom version of this script by Austin, which was modified to email each database separately.

Change log

v2.1 (May 3, 2007)

New option added to allow comma separated list of databases to exclude
v2.0 (September 27, 2005)

Completely rewritten in PHP
Config is now in separate file
New logging and error system
Log files are time-stamped
Log files can be sent with backup email
Error log can be emailed to alternate address
Windows support (more info below)
Can set max execution time for script
Option to flush and optimize databases
Can be executed from browser, cron job, etc..
v1.2 (March 30, 2005 – First public release)

Written in Perl for Unix-based systems
Automatically finds all of your MySQL databases
Databases are backed up individually
The backup files are compressed into a single file
Backups can be stored on the server and/or emailed

Requirements

There are very few requirements for this script to function. You must of course have PHP. You will also need to supply the script with a username and password for MySQL so that it can extract the databases. It is best to use the root MySQL account so it will be able to perform a complete backup.

This script was originally written for Unix-based systems, but this new version will work on Windows platforms, provided that you make the required configuration changes, and have an acceptable zip and tar replacement (that can properly handle the command line parameters).

Installation

To install the script, just download the two files above, renaming them from .txt to .php. You can put them anywhere on your server, as long as they are together. Since the script is now in PHP you can even run it directly from your browser, although if you do, a password-protected directory is recommended.

Configuration

Before running this script, open up the configuration file – backup_dbs_config.php – and make the required changes. Most of the options do not have to be changed, but some are required for the script to function properly (such as the MySQL connection info).

The logging / error system

Two log files are created every time the script runs. The standard log and error log. What happens to them depends on your configuration.

When you are performing an email backup, the standard log will be placed inside the body of the message. The backup file will of course be attached to this message. You can also choose if the backup file and standard log are left on the server after emailing.

Error messages are still shown in the standard log, but the separate error log allows you to receive notification (to a separate email address) in case of error. This can be useful if you want errors sent to your primary account, while backups are sent to an alternate (that you do not check every day). That way you can always be aware of problems.

Manually running the script

Depending on your configuration and user permissions, you may have to run this script as root.

From the command line, you can type: php /path_to_script/backup_dbs.php

You can also run it directly from your web browser: http://www.domain.com/path/backup_dbs.php

Setting up a cron job

Once again, depending on your configuration and user permissions, you may have to be logged in as root to create a cron job.

type crontab -e and add a new line. Here is an example:

30 0 * * * php /root/scripts/backup_dbs.php

That will configure cron to run the script every day at 12:30 am. To learn more about cron jobs, you can visit this cron guide.

After adding the new line, save the file and exit. Cron may have to be restarted before it will recognize the new job. To do this, simply type:

/etc/init.d/crond restart

On certain servers, emails are sent out containing the output of cron jobs. If you would like to prevent this, you can add the following code to the end of your new cron job line:

> /dev/null 2>&1

If you add this code, do not forget to restart cron again before testing. That line only prevents email notifications after cron jobs execute. This will not effect the script’s log file options.

Final notes

This script has been tested extensively, but I take no responsibility for any damages caused by misuse, improper configuration, or differences in server configurations. If you have any questions or comments, feel free to post them below!

Topic: PHP Scripts | RSS Feed

213 Comments on “Automatic MySql Backup Script”

Pages: « 15 14 13 [12] 11 10 9 8 7 6 5 … 1 » Show All

180

Jim Selleck

2-24-09 9:44PM

This is a nice script, although it should have better error handling.

Unfortunately, you cannot run it on a Bravenet hosted web site because their internal security rules for virtual hosting accounts prevent the use of exec().

That is not the fault of the script writer, it is an issue with Bravenet.
179

coolscript

2-23-09 3:27PM

Have any of you seen this before:

Fatal error: Cannot redeclare writelog() (previously declared in C:\xampp\htdocs\backup\backup_dbs.php:80) in C:\xampp\htdocs\backup\backup_dbs_config.php on line 98

thanks!

178

chinthaka

2-16-09 6:43AM

Hi i wanna backup ‘mysql’ data base to remote computer at the same network.So how can i do that?
What shuld be the –>> $BACKUP_TEMP and $BACKUP_DEST path..?



$MYSQL_PATH = 'C:\wamp\bin\mysql\mysql5.0.51b\bin';

// Mysql connection settings (must have root access to get all DBs)
$MYSQL_HOST = '192.168.xx.xx'; // Ip address of Mysql data base
$MYSQL_USER = 'root';
$MYSQL_PASSWD = 'root';

// Backup destination (will be created if not already existing)
$BACKUP_DEST ='what shuld be this path if i want to backup mysql database to remote computer';

// Temporary location (will be created if not already existing)
$BACKUP_TEMP = 'what shuld be this path if i want to backup mysql database to remote computer';

Thank you..!!

177

chinthaka

2-16-09 1:24AM

Great JOB man….Really great……Thank you so much….

176

Ellimist

2-7-09 4:41AM

First of all, this is an excellent script. Thank you for providing it to us free of cost.

This script had been working without any errors for a year, but suddenly, it started giving this error.

Executing MySQL Backup Script v1.4
Processing Databases..
Dumped DB: xxxxxx
Compressed DB: xxxxxx
Archiving files.. 
Backup complete!
Emailing backup to XXXXX@XXXX.XXX .. 
<br />
<b>Fatal error</b>:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 23149108 bytes) in <b>/home/animorph/public_html/backup_dbs.php</b> on line <b>312</b><br />

The database has a size of 55 MB, if it’s of any use.

175

Rob

2-6-09 8:17PM

Hi, this script is fantastic! Works like a charm.. Thank you so much..

I’m just wondering if it locks the db while it’s backing up? I wanted to set up a cron for 1:30pm to catch any changes made in the morning but don’t know if it will cause any problems to updates that are being made?

Thanks again!

Rob.
174

Paul K (spoony)

1-27-09 5:57AM

Just a reply to a few of the previous comments.

To BloggerSavvy .. dont run the script with the configured ‘root’ username.. change this to the username/password that you have given rights to your databases to be backed up (a mysql user).

To fashionsjamaica .. those directories need to be world writable (ie. 777) ie. the db_backups directory and the tmp / backups directory (cant remember the other ones name off the top of my head). I precreated my dirs and made them 777.

And to the script author.. thanks HEAPS for this script. I implemented this after the one I was running previously kept failing and running into memory errors (even though I was overriding php’s memory limit).
This one works like a charm and even better than my previous one did. I use this to email all my databases to my gmail account.. using a gmail account to receive your databases is a great form of backups.

Gmail can accept up to 20 meg attachments.. we have a forum with 10,000 posts (35meg database..) that compresses down to 4megs.. so we’ve still got a while before I need to work out a better backup solution. (and even then I’ll probably still use this script and then automate the ftp to elsewhere).

I like to run multiple versions of this script each configured with different mysql database users that only have privileges to specific databases so I get them individually emailed to me and not all databases in one hit (because I’m doing the email thing).

Thanks again.

Paul
173

fashionsjamaica

1-21-09 8:14PM

I am getting this error: http://fashionsjamaica.mc-dollar.net/db_backups/backup_dbs.php

Warning: mkdir() [function.mkdir]: Permission denied in /home2/fashions/public_html/db_backups/backup_dbs.php on line 156

Notice: Undefined index: f_log in /home2/fashions/public_html/db_backups/backup_dbs.php on line 86
Backup directory could not be created in /db_backups

Notice: Undefined index: f_err in /home2/fashions/public_html/db_backups/backup_dbs.php on line 86
Backup directory could not be created in /db_backups
Backup directory could not be created in /db_backups
172

Daniel

1-20-09 5:40PM

is there a solution for SMTP mailing (with user/pw needed) instead of standard phpmail?
my hoster only allows smtp with authentification…

thanks in advance for your help!
171

BloggerSavvy

12-31-08 9:43AM

This script does not work using ‘root’ (on php5 server). Additionally, most web servers do not allow access permissions of ‘root’ to gain access to all databases on the server.

Does anyone have a fix for this? (I don’t think I messed anything up in configurations etc. When manually running the script (via ssh), ‘root’ is simply denied access.

Thank you!
170

Divyang

12-17-08 6:34AM

Very nice job….
Thanks a lot….
169

jonathan

12-12-08 7:02PM

i would like to know if Scooter is right and the script doesn’t work with php5
168

Scooter

11-30-08 8:41PM

Ran this using php 4… works perfectly

My hosting company uses php5… tried the switch and it does not work….

Question :

Is there a way to make this compatible for the latest version of php, if so how can this be accomplished..?

Thank you for your time.
167

Chris

11-27-08 2:26PM
arjen, and others. I’m too using Windows and had the infamous ‘Zip’ error.

I can only speak from my experience of Winzip 12, but it appears winzip no-longer support command-line functions as standard, you have to download the ‘WinZip Command Line Support Add-on’ that only supports the ‘Standard’ & ‘Pro’ versions (so you have to pay). Once you’ve installed WinZip 12 & the Command Line Add-on you will still get the error….you have to copy the file ‘WZZIP.EXE’ to both the directory the script resides in AND the temp file that your backups are created in.
You then have to change a couple of lines of code in the script to tell it to stop using the command ‘ZIP’ and start using ‘WZZIP’ (the code is set to always use ‘ZIP’ for windows systems regardless of what you put in the $COMPRESSOR variable:
```
Line 221 changes to:
exec( "wzzip $BACKUP_TEMP/$db.sql.zip $BACKUP_TEMP/$db.sql 2>&1" , $output, $res );

Line 256 changes to:
exec("wzzip $BACKUP_DEST/$BACKUP_NAME * 2>&1", $output, $res);
```
That will get the code working with winzip the first time you run it, however, at the end of the script it tidies up the temp directory, so you’ll need to tell that bit of the script to NOT delete the ‘WZZIP.EXE’ you just put there (probably a better way of doing this but this worked so meh):
```
Line 377 changes to:
if (!is_dir($file) AND $file != 'WZZIP.EXE') {
```
I hope this helps anyone else with this problem.

P.S. Great script, thank you so much, saved me a lot of time writing my own
166

Steve

11-27-08 1:58AM

Nice work, thanks

Pages: « 15 14 13 [12] 11 10 9 8 7 6 5 … 1 » Show All