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.
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!
It’s the –skip-opt that does it.
I keep getting the following…
Jump to Comments