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: [23] 22 21 20 19 18 17 16 15 14 131 » Show All

  1. If some one wishes expert view on the topic of running a blog afterward i suggest
    him/her to visit this webpage, Keep up the pleasant work.

  2. This design is incredible! You obviously know how to keep a reader entertained.
    Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job.
    I really enjoyed what you had to say, and more than
    that, how you presented it. Too cool!

  3. Yes this is very much needed artilce. I hope it will work with my server. I am very much thankful for this great help because I have lost many of my entries due to delay in taking manual backup of my mysql database. Thanks again

  4. Thank you, I have recently been looking for information
    approximately this topic for a while and yours is the best I’ve discovered so far.
    However, what about the conclusion? Are you certain
    about the supply?

  5. Thanks for the great bu script; worked like a charm. Well done!

  6. Hey I know this is off topic but I was wondering if you knew of
    any widgets I could add to my blog that automatically tweet my
    newest twitter updates. I’ve been looking for a plug-in
    like this for quite some time and was hoping maybe you would
    have some experience with something like this. Please let me know if you run
    into anything. I truly enjoy reading your blog and I look
    forward to your new updates.

  7. 339
    Sandy

    I believe I ran into the same problem as John in comment 303 – quoting him:

    
    Executing MySQL Backup Script v1.4
    Processing Databases..
    Access denied for user 'cronDataBaseBackup'@'localhost' (using password: YES)
    Access denied for user 'cronDataBaseBackup'@'localhost' (using password: YES)
    Access denied for user 'cronDataBaseBackup'@'localhost' (using password: YES)
    

    The problem we both have is that the password contains characters that get interpreted by the shell when running mysqldump. I have fixed this locally by changing line 188 (escaped quotation marks to apostrophes) from

    $db_auth = " --host=\"$MYSQL_HOST\" --user=\"$MYSQL_USER\" --password=\"$MYSQL_PASSWD\"";

    to

    $db_auth = " --host='$MYSQL_HOST' --user='$MYSQL_USER' --password='$MYSQL_PASSWD'";

  8. I need to to thank you for this great read!! I absolutely enjoyed
    every bit of it. I have you bookmarked to check out new stuff you post?

  9. Fantastic goods from you, man. I have understand your stuff previous to and you are just too fantastic.
    I really like what you’ve acquired here, certainly like what you’re saying and the way in which you say it.
    You make it entertaining and you still care for to keep it wise.
    I can not wait to read much more from you. This is really a wonderful website.

  10. you are truly a excellent webmaster. The site loading pace is incredible.

    It kind of feels that you’re doing any distinctive trick. Furthermore, The contents are masterwork. you’ve done a fantastic process on this topic!

  11. Why do you need to use any scripts if you can use any GUI tool like dbForge Studio for MySQL? I prefer clicking instead of typing ;)

  12. 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).

  13. 333
    Dude

    Just wanted to give a possible solution to some of you suffering from this error:

    PHP Warning: set_time_limit(): Cannot set time limit in safe mode in /pathtofile/backup_dbs.php on line 136
    PHP Warning: mkdir(): SAFE MODE Restriction in effect. The script whose uid is 500 is not allowed to access /tmp owned by uid 0 in /pathtofile/backup_dbs.php on line 160
    PHP Notice: Undefined index: f_log in /pathtofile/backup_dbs.php on line 86
    PHP Notice: Undefined index: f_err in /pathtofile/backup_dbs.php on line 86

    My PHP safe mode was on, even though when I looked at the configuration and it was set to off. My solution was to turn ON the safe mode explicitly, save, and restart Apache. Then I set safe mode to off, save and restarted Apache, and this solved the issue. Try this to see if it works for you!

  14. 332
    raghu

    great job thanks will try

  15. klasik gömlek by verdtone

Pages: [23] 22 21 20 19 18 17 16 15 14 131 » 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.