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

307 Comments on “Automatic MySql Backup Script”

Pages: « 21 … 20 19 18 17 16 [15] 14 13 12 11 10 … 1 » Show All

225

M@

7-26-10 10:05AM

how do we configure properly for a windows site? I’ve tracked down my problem – the page is blank, to this statement failing:
require( $current_path.’\\backup_dbs_config.php’ );

I have the backup_dbs_config in the same directory and my current_path is correct. I’ve tried with a bunch of different slashes, etc.

$MYSQL_PATH = ‘C:\\Program Files\\MySQL’;
$BACKUP_DEST = ‘e:\\websites\\helpdesk site\\backup\\’;
$BACKUP_TEMP = ‘e:\\websites\\helpdesk site\\backup\\tmp\\backup_temp\\’;

I have the same config on the config page, but it’s an issue with the require is the issue I think. Any ideas?
224

jits

6-23-10 6:12AM

hello,
your script is great but i want to know that how can we automate this script in windows environment ?? kindly guide me through this…
223

E. Greenhaw

5-26-10 9:42AM

I’ve been using your backup script since Dec 2005 and it’s worked wonderfully, saving me alot of headaches. However with the latest update from MYSQL I’m finding that it kicks out an error that I can’t find documentation on anywhere. It seems to be related to the 2>&1 section. The error is:
DUMP FAILED
mysqldump: unknown option ‘–quick-names’

Any ideas?
Thanks again, for every daily use from Dec 2005 until now
222

Dave Mills

5-15-10 6:37PM

Hi
I get this error when running the script

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

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

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

WOuld you mind advising me how to fix this please?

Kind regards

Dave Mills
221

Matt

5-5-10 4:08PM

Dave,

I used your ftp script, and it’s lookin good, but when I try to open the tar, it tells me it’s corrupt. I can open the email copy fine tho. Any ideas? Something’s happening in the ftp section. I put it under the email, and before the //do we delete the backup file?

Any ideas?
thanks,
M@
220

Damir

5-3-10 6:31AM
Hi,
My Config file for path is this:
```
$MYSQL_PATH = '"C:\Program Files\MySQL51\bin\mysqldump.exe"';
$BACKUP_DEST = 'C:\php\db_backups';
$BACKUP_TEMP = 'C:\php\backup_temp';
```
The scripts create $BACKUP_DEST and $BACKUP_TEMP, but the error occured always:

DUMP FAILED
The directory name is invalid.

I can’t understand where is the problem, and “The directory name is invalid” is not a string in your script.

I hope you can help me.

Compliment for your scripts.
Damir
219

arash soltani

4-10-10 10:45AM

Warning: mkdir() [function.mkdir]: No such file or directory in C:\xampp\htdocs\backup_dbs.php on line 160

Notice: Undefined index: f_log in C:\xampp\htdocs\backup_dbs.php on line 86
Backup temp directory could not be created in /tmp/backup_temp

Notice: Undefined index: f_err in C:\xampp\htdocs\backup_dbs.php on line 86
Backup temp directory could not be created in /tmp/backup_temp
Backup temp directory could not be created in /tmp/backup_temp

plz help me out

218

Dave

4-4-10 7:40AM

Thought I’d share this in case you were interested in adding this functuality.

To upload to a remote FTP server, I added this to the config file:


######################################################################
## FTP Options
######################################################################

// Use FTP Option?
$useftp = true;

// Use passive mode?
$usepassive = true;

// FTP Server Address
$ftp_server = 'host';

// FTP Username & Password
$ftp_user_name = 'username';
$ftp_user_pass = 'password';

and this to the main file below email sending:


// do we ftp the file? 
if ($useftp == true) {
$file = $BACKUP_DEST.'/'.$BACKUP_NAME;
$remote_file = $BACKUP_NAME;

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// turn passive mode on?
ftp_pasv($conn_id, $usepassive);

// upload a file
if (ftp_put($conn_id, $remote_file, $file, FTP_ASCII)) {
 echo "successfully uploaded to ftp: $remotefile\n";
} else {
 echo "There was a problem while uploading $remotefile\n";
}

// close the connection
ftp_close($conn_id);
}

Let me know if there are any major flaws in that, would be interested to hear them!

217

Steve Crowder

3-28-10 3:09PM

Hi

loading /usr/local/Zend/lib/Optimizer-3.3.3/php-5.2.x/ZendOptimizer.so: /usr/local/Zend/lib/Optimizer-3.3.3/php-5.2.x/ZendOptimizer.so: cannot open shared object file: No such file or directory

The path to ZendOptimizer.so has changed on my hosting provider. What do I need to edit in the script to make sure it goes to the new path for the ZendOptimiser.co library?

Thanks
216

Jorbyn Network

3-27-10 11:20AM

nice links & i like it. good share
215

Paul Rowzee

3-26-10 9:09AM

I too am getting the errors about the failed dump etc… please help. If someone figures this out please drop an email to me at paul@pixccelerate.com

=========================
Executing MySQL Backup Script v1.4
Processing Databases..
DUMP FAILED

DUMP FAILED

COMPRESSION FAILED

COMPRESSION FAILED

Emailing information_schema backup to mike@camperreg.com ..
DUMP FAILED

DUMP FAILED

COMPRESSION FAILED

COMPRESSION FAILED
214

Alexandre Ribeiro

3-19-10 12:23PM

Hi, the script works fine.
i have only one small problem, when i use it like this:
“$EXCLUDE_DB = ‘information_schema’;”
Everything is ok.
If i exclude some more db, like this for example:
“$EXCLUDE_DB = ‘information_schema’,'billing’,'phpmyadmin_z3iVJAt1M8zz’;”
it stops working

Can u help me’
Regards
213

Felipe

3-15-10 6:49PM

Hi everybody,

I have been able to successfully run the script from a web browser. But when running as a cron job in Plesk I get the errors below. Any idea on how to fix this?

Many thanks.

==============================
Executing MySQL Backup Script v1.4
Processing Databases..
sh: /mysqldump: No such file or directory
DUMP FAILED

DUMP FAILED

sh: /nice: No such file or directory
COMPRESSION FAILED

COMPRESSION FAILED

Archiving files..
sh: /cd: No such file or directory
FAILED
==============================
212

Jason

3-11-10 3:11AM

Argggghhhhhhhhhh!!!!!!!!!!!!!!!!!!1

I have the script runing ok from cron BUT-
Tar file created on first attempt was 25M. (strange as my DB is only small).
Tar file created on second attempt just kept growing larger until it consumed all space on the server like it was the blob. Yup all 160Gb of it!!!!

It is almost as if the Db backup just continues to repeat in endless loop? Strange tho that it wouldn’t have continued on the first attempt.

I have flicked through all posts below however no one seems to have had this prob in the past. Is it a PHP version issue?

Any help appreciated, I really want this to work out between us.

p.s. I am using PHP 5.3 on Fedora 11
211

nagaraj

3-9-10 6:25AM

Hi I am using xampp which has mysql where to place these both php file (backup_db_config, backup_db) files to take back up ….

Appreciate your help
Nagaraj

Pages: « 21 … 20 19 18 17 16 [15] 14 13 12 11 10 … 1 » Show All