Automatic Local, FTP & Email Backups of MySQL Databases with Cron

I’ll start by saying this is not all my own code, it is based on dagon design’s original release in 2007 (Automatic MySql Backup Script) but this version builds on their version 2.1 to add FTP support.

What this script can do:

  • Backup all of your MySQL databases on a server individually, then package them into a single tar.
  • Save that tar locally, on a FTP server or even email it to you

What you need:

  • PHP
  • MySQL
  • Preferably the root mysql login & password (allows you to backup all databases in one go)

I do not guarantee:

  • That this will definately work on your server, or Windows servers out of the box.

However it is here for people to use. I have tested it on several linux machines and it runs great.

Download:

Zip containing dbbackup.php & dbbackupconfig.php – dbbackup.zip

What I’ve added:

To upload to a remote FTP server, I added this to the config file:

######################################################################
## FTP Options
######################################################################

// Use FTP Option?
$useftp = true;

// Use passive mode?
$usepassive = true;

// FTP Server Address
$ftp_server = 'host';

// FTP Username & Password
$ftp_user_name = 'username';
$ftp_user_pass = 'password';

and this to the main file below email sending:

// do we ftp the file?
if ($useftp == true) {
$file = $BACKUP_DEST.'/'.$BACKUP_NAME;
$remote_file = $BACKUP_NAME;

// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// turn passive mode on?
ftp_pasv($conn_id, $usepassive);

// upload a file
if (ftp_put($conn_id, $remote_file, $file, FTP_BINARY)) {
 echo "successfully uploaded to ftp: $remotefile\n";
} else {
 echo "There was a problem while uploading $remotefile\n";
}

// close the connection
ftp_close($conn_id);
}

That’s all!

Godaddy FTP Backup Servers – Dedicated Plesk Server

One of our clients recently had an issue where his Godaddy FTP Backup Server was full – causing his seperate dedicated Plesk Server to fill up it’s own Drive with Temp files, while at the same time use a lot of resources trying to put files to the full FTP backup server.

You can sign up for a remote backup server when you order a godaddy hosting service, and it does sound a good idea considering it should keep your data safe. You can only access the data on the FTP server from within the Godaddy network – stopping any outside access.

When Plesk attempts to connect to a remote FTP server, it first tries to place a file on the server – which is a no-go if the server is full. This fails of course, so you can’t even get a directory listing of the remote server through Plesk.

Easy solution though – connect to your server through SSH and then use commands to connect to the remote FTP through your server.

  1. Open your SSH application of choice (we use PuTTY, available free from greenend.org.uk) and connect to your server. Use your main IP address or your hostname (domain name), depending on what your host supports. For dedicated servers from Godaddy, either can be used.
  2. Once you’ve logged in with your root username and password, simply enter

    FTP XXX.XXX.XXX.XXX (where xxx…. is the IP address of the FTP server) and hit enter.
    then your login username, hit enter and then your login password (also followed by enter). For Godaddy all of this is available in your Server Management Area.

  3. Now you should be logged on to the server. Type DIR and hit enter to produce a list of the directory contents. This also shows file sizes and other details.
    1. You can DELETE individual files on the server using the DELETE command. For example, to delete info.php use DELETE info.php and hit enter.
    2. To change directories type CD directoryname/ and hit enter.
    3. To copy a file to your local server type WGET filename.file and hit enter. If you don’t specify a directory then the file will be copied to your server in the /root/ directory. You can access this directory by connecting to your server using PuTTY and typing DIR and hitting enter when you’ve logged in. (It will be in the default directory).

That’s a basic guide, there are many better out there but this follows the basics. There are a couple of useful SSH commands on this site, including how to set specific options.