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

Page Contents

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!

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

  1. 195
    drucker
    10-1-09 10:08PM

    This is an invaluable resource. Many thanks.

    Im a php novice and it took me hours (more than I care to count) to get the script working. I see some folks are also struggling. One key is found in comment #74 on/about page 5. Neglecting part B of Sean’s comment is a show-stopper and neglecting part A is almost as troubling (for example at/about lines 255,301,362,379,381). Also comment 97 is good and because Im hosted by godaddy, comment 136 was invaluable. Also I struggled with the mkdir error and found it best to make these directories manually; also comment out rmdir at end of the script — what’s the harm in letting it be? Im on unix, php5, mysql5 and happily report that this script is a gem, backing up my data daily.

    For my own benefit I must wonder why comment 74 is so little discussed when it seems so vital. Maybe its just me. Can someone shed a light?

  2. 194
    Keenan
    9-7-09 10:59PM

    Hmm… I keep running into an error… I’m not sure what’s causing it. Any help?
    Well here is the error (the HTML didn’t parse 0_o).

    
<br />
<b>Warning</b>:  mkdir() [<a href='function.mkdir'>function.mkdir</a>]: Permission denied in <b>/home/disney5/public_html/forums/includes/cron/backup_dbs.php</b> on line <b>156</b><br />
<br />
<b>Notice</b>:  Undefined index:  f_log in <b>/home/disney5/public_html/forums/includes/cron/backup_dbs.php</b> on line <b>86</b><br />
Backup directory could not be created in /db_backups
<br />
<b>Notice</b>:  Undefined index:  f_err in <b>/home/disney5/public_html/forums/includes/cron/backup_dbs.php</b> on line <b>86</b><br />
Backup directory could not be created in /db_backups
Backup directory could not be created in /db_backups

  3. 193
    elp
    8-14-09 4:35AM

    while i try it with appserv ( XP pro sp2 ). then i got these message

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

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

    Notice: Undefined index: f_err in C:\AppServ\www\cron\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

    anyone could help me?

  4. 192
    jsherk
    8-11-09 1:35PM

    Akshay… the date() part of it should NOT be in single quotes… it should look like this:
    $BACKUP_NAME = ‘mysql_backup_’ . date(‘Y-m-d’);
    Notice there are NO quotes on the outside of the date(), only around ‘mysql_backup_’ and around ‘Y-m-d’.

  5. 191
    naveen
    7-27-09 11:13PM

    Executing MySQL Backup Script v1.4
    Processing Databases..
    Dumped DB: weberservices_co_in_ws
    Compressed DB: weberservices_co_in_ws
    Archiving files..
    FAILED
    tar: db_backups/mysql_backup_2009-07-28.tar: Cannot open: No such file or directory
    tar: Error is not recoverable: exiting now
    FAILED
    tar: db_backups/mysql_backup_2009-07-28.tar: Cannot open: No such file or directory
    tar: Error is not recoverable: exiting now

    *** ERRORS DETECTED! ***
    Check your email account naveennathtripathi@gmail.com for more information!

    There were errors!
    Emailing error log to naveennathtripathi@gmail.com ..

  6. 190
    Akshay
    7-25-09 9:09AM

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in D:\wamp\www\backup\backup_dbs_config.php on line 43

    43. $BACKUP_NAME = ‘mysql_backup_’ . ‘date(“Y-m-d”)’;

    could antone help out me in resilving this?
    I am using WapServer.

    Akshay

  7. 189
    Joy
    7-14-09 10:38PM

    Hi,
    My testlink is associated with Mamp since I am using Mac machine. I can’t find the backup files that this script would produce upon running it. I tried to find the backups in the db and temp folders from my Mamp but no backups that had been created.

    I really need help to fix this. If possible a step by step procedure to ensure the script will run correctly.. thanks!

  8. 188
    Krishna
    7-9-09 11:24AM

    There is Cron Job which runs every day. All the logs will come to be through mail. Suddenly I am getting mails without any data in it. When I check for logs on server, they are of 0KB. What could be wrong? And how can I resolve this issue?

  9. 187
    Admin
    7-1-09 9:13PM

    This script was created about four years ago, and the last small change I did was two years ago. It still runs on my servers to this day, but i have not even looked at it in a long time. In other words, it is not really a top priority as far as updates are concerned, but hey, it is free to download! ;)

  10. 186
    Joseph
    5-28-09 8:36AM

    Everytime I try to run the script I get this
    Warning: Unexpected character in input: ‘\’ (ASCII=92) state=1 in /home/gwauctio/public_html/backup program/backup_dbs.php on line 2

    Parse error: syntax error, unexpected T_STRING in /home/gwauctio/public_html/backup program/backup_dbs.php on line 2

  11. 185
    Raktri4u
    4-13-09 3:41PM

    can any one make me sure that this script do locks the tables of the mentioned databases or not???
    thanks in advance
    Rak

  12. 184
    help
    4-10-09 4:00PM

    Thanks for the script works on one server but not on Centos, I get this

    more rackmonkey_mysql_backup_2009-04-10_1655.log
    2009-04-10 16:55:35: Executing MySQL Backup Script v1.4
    2009-04-10 16:55:35: Processing Databases..

    I dont see any db getting back up , i do see /tmp/backup_temp get created but no email or a back up file. Also I notice it does not leave a copy of the db on the server

  13. 183
    Daniel
    3-25-09 5:08AM

    Great script but could not work on WAMP setup on Windows. I changed the parameters pointing to the mysql path etc and whenever i run the script, i get this error:-

    
DUMP FAILED
'..' is not recognized as an internal or external command,
operable program or batch file.
DUMP FAILED

  14. 182
    DEEPAK BHATIA
    3-9-09 5:20AM

    Hi,

    The mail is not working. I have Xampp hosted on a linux machine.

    Thanks

    Deepak Bhatia

  15. 181
    Phil
    3-3-09 10:11AM

    Works great, thanks for the invaluable resource.

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

Leave a Comment

Before you comment: If you are having an issue with a script, please make sure you have read the entire article. Also, please read through the comments because most common issues have already been discussed many times. Thanks.


Be sure to wrap all code in <code></code> tags.