Automatic MySql Backup Script

							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

      backup_dbs.txt
      backup_dbs_config.txt

Other versions

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

Page Contents

      Change log
      Requirements
      Installation
      Configuration
      The logging / error system
      Manually running the script
      Setting up a cron job
      Final notes

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




 Search


    Subscribe
Jump to Comments
While the scripts on this site are free, donations are greatly appreciated.


159 Comments on “Automatic MySql Backup Script”
Pages: [11] 10 9 8 7 6 5 4 3 2 1 » Show All

   1.

          159

          Tiger

          9-24-08 6:08AM

          Only one thing to say, thanks a lot for all your scripts and WP plugins, you
          only made great, clean and usefull scripts
          Thanks again for all your free work !

   2.
     158

     Jack

     9-13-08 5:40PM

     Man, I love you. I’m not gay but I really love you !



3.

     157

     arjen

     9-9-08 8:12AM

     Hi all,

     I am installing this script on a W2k3 box.
     Everything seems fine exept the infamous “ZIP is not recognized as an
     internal or external command…” error.
     I have included ZIP in the PATH: (C:\Program
     Files\WinZip\WINZIP32.EXE)

     The config is like this:

     // Path to the mysql commands (mysqldump, mysqladmin, etc..)
     $MYSQL_PATH = ‘C:\MySQL\bin’;

     // Mysql connection settings (must have root access to get all DBs)
     $MYSQL_HOST = ‘localhost’;
     $MYSQL_USER = ‘root’;
     $MYSQL_PASSWD = ”;

     // Backup destination (will be created if not already existing)
     $BACKUP_DEST = ‘E:\80\backupdbs\db_backups’;
     // Temporary location (will be created if not already existing)
     $BACKUP_TEMP = ‘E:\80\backupdbs\tmp\backup_temp’;

     // Show script status on screen while processing
     // (Does not effect log file creation)
     $VERBOSE = true;

     // Name of the created backup file (you can use PHP’s date function)
     // Omit file suffixes like .tar or .zip (will be set automatically)
     $BACKUP_NAME = ‘mysql_backup_SCE’ . date(’Y-m-d’);

     // Name of the standard log file
     $LOG_FILE = $BACKUP_NAME . ‘.log’;

     // Name of the error log file
     $ERR_FILE = $BACKUP_NAME . ‘.err’;

     // Which compression program to use
     // Only relevant on unix based systems. Windows system will use zip
     command.
     $COMPRESSOR = ‘bzip2′;

     Does anyone have any idea what to correct?

     Thanks in advance



4.

     156

     steve

     9-4-08 10:05AM
     If you run manually from your browser you may find that you don’t have
     permission to write the directories in root /db_backups etc.

     If this is the case simply create db_backups amd tmp/backups_temp in the
     directory that the script is sitting. Be sure to chmod 777 them too.



5.

     155

     Maski

     8-18-08 12:08PM

     I have excluded from de databases a couple that i dont need, but i get the
     backup from those and from information_schem..



6.

     154

     Patrick

     8-12-08 2:23PM

     Hi,

     I believe that I have the script set up correctly but I am having no success
     with it. Nice was not installed on the server so I have installed that.
     However, when the script is executed it is generating the following errors
     (one for each database):

     > Processing Databases..
     > sh: /mysqldump: No such file or directory
     > DUMP FAILED
     >
> DUMP FAILED
>
> sh: /nice: No such file or directory
> COMPRESSION FAILED
>
> COMPRESSION FAILED

(Repeated 10 more times - presumably one for each database)

and finishing up with:

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

FAILED

*** ERRORS DETECTED! ***
Check your email account ************** for more information!

Emailing backup to ******************* ..
PHP Warning: file_get_contents(): Unable to access
/root/db_backups/mysql_backup_2008-08
-12.tar in /root/scripts/backup_dbs.php on line 303
PHP Warning: file_get_contents(/root/db_backups/mysql_backup_2008-
08-12.tar): failed to
open stream: No such file or directory in /root/scripts/backup_dbs.php on
line 303

There were errors!
Emailing error log to *************** ..
qmail-inject: fatal: unable to parse this line:
From: ************>MIME-Version: 1.0
     FAILED to email error log.
     FAILED to email error log.

     (Despite the last few lines I am receiving an e-mail with the errors and an
     attached .tar file of 0 bytes).

     I am running under Plesk which seems to be problematical for some.

     Any help would be appreciated.



7.

     153

     Nguyen Buu

     8-7-08 10:57PM

     Dear all,

     Could you please take a look
     http://saigon.whodigital.com/demo/bk/backup_dbs.php

     I give below error - do you have any idea for helping?

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

     DUMP FAILED

     COMPRESSION FAILED
     zip warning: name not matched: C:\tmp/blog.sql
        zip error: Nothing to do! (C:/tmp/blog.sql.zip)
        COMPRESSION FAILED
        zip warning: name not matched: C:\tmp/blog.sql

        zip error: Nothing to do! (C:/tmp/blog.sql.zip)
        DUMP FAILED



   8.

        152

        Thomas

        7-24-08 4:59PM

        I too am having a issue with the zip portion on Windows. Winzip is
        installed on the server and included in the path. No matter what I try in the
        config or backup php I receive the messager ‘zip’ is not configured and an
        internal or external command.



   9.

        151

        pl

        7-24-08 4:46AM

        I manage to get it work, but now is the zip problem..I installed winzip but
        still have problem, is there a solution for this?

        appreciate for any help.




http://www.dagondesign.com/articles/automatic-mysql-backup-script/#installation