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

228 Comments on “Automatic MySql Backup Script”

Pages: « 16 … 13 12 11 10 9 [8] 7 6 5 4 3 … 1 » Show All

120

Jasper

11-25-07 7:20AM

I had that very, very same errors. CHmodding to 777 helped, though.

The solution to create the folders already worked as a temporary solution for the first error thrown – it gave me the second error Henry posted. After CHModding the both folders to 777, the script ran without any errors.
The script deletes the temporary directory however, so creating the directories already is not going to be any step towards a permanent solution.
119

Craig

10-28-07 9:37PM

I would like to use the “Exclude” function, but for some reason, it is not excluding any database. I have the excluded databases listed with a “,” and space in between databases. Is there another way to list then, like with no space between the “,” and next database name? Also, it backs up the “information_schema” database.

Any ideas on what is or what I am doing wrong? Other than this, it works fine. But I do not want all databases emailed.

Thanks.
118

joe

10-23-07 9:21PM
ok i have a strange problem
i have installed the script and if i run it from IE or firefox it works a treat but if i run it as a cron job it seems to just hang all i get in the log is
```
"2007-10-24 03:10:02: Executing MySQL Backup Script v1.4
2007-10-24 03:10:02: Processing Databases.."
```
and nothing else
the error file is blank
so it appears the the cron job is starting the script ok but it just doesnt seem like the script will complete
any idea’s?
117

Abhik

10-11-07 2:28PM

I just wanted to tell you a BIG THANK YOU for creating this script.
It was a real help for me as I have a slow internet connection and its a real pain in the head to manually download those big backups.
116

David

10-9-07 12:44PM

Hi I am try desperatley to get your script working. I have set the permissions of the directory and the 2 files to 777, however I still keep geting the following messsage on the screen when I try running it: A file permissions error has occurred. Please check the permissions on the script and the directory it is in and try again. Have you got any ideas for me thanks
115

mike

8-9-07 6:13PM

and what i mean by that (the breaking up files) is not doing each sql separately, but to where it will take a set size, and break the zip files up into those sizes and email each part as a separate email.

as to the second part, i have a few directories that i need to have backed up with the databases, as they are interlinked (basically the site if you upload a file, will put it in the db (the page reference) and save it to a directory)
114

mike

8-7-07 5:46PM

2 things, is there a way to have it break up zip files (windows) and email the sections separately, as the files can become VERY large, and 2, is there a way to tell it to backup directories and make them part of the backup?
113

Jonny

8-2-07 8:41PM

I had the same problem reported below with the creation of the backup directory failing.

I fixed it by specifying the full path to where I wanted the backup directory. So if you want them in the directory the backup script is stored, change the path in config to the same path as is shown in the error message (less the backup_dbs.php bit).

So for:

/home/chs/calellabarcelona.com/home/html/admin/backup/backup_dbs.php

Set the backup path in config to:

/home/chs/calellabarcelona.com/home/html/admin/backup/
112

Aki Karkkainen

7-30-07 9:02AM

@Geo: do you have ‘exec’ function enabled in PHP? If not, this script won’t work…

I like this script, but I have a problem when restoring the backup: phpMyAdmin returns ‘error in your SQL syntax’. If I do manual backup with phpMyAdmin and restore that, it works. Any ideas?
111

Geo

7-26-07 1:39PM

This seems like it’ll do it but my problem is that it never creates any output at all. It creates the log file and it says it dumped the databases but NADA….
I need to use gzip since I don’t have tar. I’m on a novell server so I am a little bit uncertain about the paths but since it creates the log file it should be ok.
I also get the download page, and I don’t know why…..
Anybody got a clue?
110

CSM

7-17-07 12:31PM

Hi. Thank you very much for a useful code. I have installed it, and have tested it by running the php files from browsers IE (Internet Explorer), Opera and Firefox. The program works fine in Opera and Firefox, and the following code is reported on the screen:

Executing MySQL Backup Script v1.4
Processing Databases..
Dumped DB: XYZ
Compressed DB: XYZ
Archiving files..
Backup complete!

However, when running from IE the job is being done (DB backup is created), but suddenly an error message appears in the IE browser:

Internet Explorer can not download backup_dbs.php from http://www.cosmotorg.net.

Why is the program causing IE to try to download anything at all? It shouldn’t be necessary. The progress should be reported on the screen, as it happens in Opera and Firefox.
109

David

7-14-07 11:57AM

Will this script work on a 200MB DB in phpadmin, the .sql backup file is a little over 300MB? If so would it need any changes different than your instructions?

Of course I do not want the backup e-mailed to me.

Thank You for your time!
David
108

leoliet

7-1-07 10:06PM

if you are using joomla,
you can use Jombackup –> http://extensions.joomla.org/component/option,com_mtree/task,viewlink/link_id,640/Itemid,35/

107

Mathias

7-1-07 2:10PM

I am having the exact same problem as Gabi (#106).

Exact message is:

<br />
<b>Warning</b>:  mkdir(/db_backups): Permission denied in <b>/hsphere/local/home/mjcraig/bluenetwork.org/DB_Backup/backup_dbs.php</b> on line <b>156</b><br />
<br />
<b>Notice</b>:  Undefined variable:  f_log in <b>/hsphere/local/home/mjcraig/bluenetwork.org/DB_Backup/backup_dbs.php</b> on line <b>86</b><br />
Backup directory could not be created in /db_backups
<br />
<b>Notice</b>:  Undefined variable:  f_err in <b>/hsphere/local/home/mjcraig/bluenetwork.org/DB_Backup/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

106

Gabi

6-24-07 7:52PM

Hi all,

I’ve installed and configured but have a permission error in mkdir. Then i have created the directory and do 777 perms but have the same result:

Warning: mkdir() [function.mkdir]: Permission denied in /home/chs/calellabarcelona.com/home/html/admin/backup/backup_dbs.php on line 156

Notice: Undefined index: f_log in /home/chs/calellabarcelona.com/home/html/admin/backup/backup_dbs.php on line 86
Backup directory could not be created in /db_backups
Notice: Undefined index: f_err in /home/chs/calellabarcelona.com/home/html/admin/backup/backup_dbs.php on line 86
Backup directory could not be created in /db_backups Backup directory could not be created in /db_backups

Pages: « 16 … 13 12 11 10 9 [8] 7 6 5 4 3 … 1 » Show All