mysqlbackup.php

Purpose:

This is a small PHP script, I wrote, which actually does many 'key' things, when it comes to backing up your server. There are many backup tools out there. Many are very complex, in most cases, overly complex.

Features:

• Backs up all files under your /etc directory.
• Backs up all files under your /home directory.
• Dumps your root crontab to /home, to than be included in the backup.
• Performs a full mysqldump of all your MySQL databases.
• Select which databases to skip from being backed up.
• Upload your backups to any number of remote servers.
• Keeps a local backup copy, in a directory of your choosing.
• Rotates the locally backed up tarballs, with controls on how long to keep them.
• Select FTP or SSH(scp) when uploading your backups to remote servers.
• Runs as a cronjob, which gives complete control over when and how many backups to make.
• Configuration file is in a plaintext key/value pair format. (No PHP Skills Required.)

Summary

In essense, mysqlbackup.php, actually does a bit more than simply backing up, and securely uploading the backups, to any number of remote servers, as well as storing the backups locally. It rotates the local backup directory, to prevent hard drive fill up. The script contains some basic, but important pre-execution, error checking of the configuration data and server state.

/etc is compressed and tar'd, because this is where all your servers configurations are stored. On a disk failure, you can use these files to help rebuild your system, to it's original state. Typically, using the old config files, as a reference or a complete reimplementation. (In other words, if your server needs to be reinstalled, due to a drive crash, you will most likely be running a more up to date kernel, and accompaning software packages. The /etc back up, provides a great reference to help you remember how you configured everything, based on the pre-crashed state, and apply them to the newer, up to date /etc files.

/home is another key location, where you really need a back up. Most of the time, many will write and store all their scripts and documents, in their /home directory. I, personally, place my WWW doc root, in my /home directory. /home/user/www/mydomain.com for instance. Backing up /home than becomes paramount, as all my PHP, HTML, JS, CSS and images are located there.

Also, many times, the root crontab, may contain several entires, and mysqlbackup.php dumps a copy of it to /home, which then gets backed up. Again, this can assist you, in reconfiguring everything, after catastropic failures, that require Operating System reinstalls.

Finally, backing up your data is one thing. But, just as important, is where to store the back ups? This script will save the tarball backups on the machine the script was executed on, called local. But, you can also specify, any number of remote servers, to store your backups. This is paramount, as it does no good to backup a server, only to save the backups on the server that failed! Resulting, in the loss of the backups! Remotely uploading/sending the back ups, to at least one remote server, wil; give you piece of mind, that multiple copies are stored.

For instance, if you have 3 servers. You may want to remotely save a backup, to at least 1 of your other 2 remaining servers. Better yet, you can easily configure mysqlbackup.php, to remotely send the backups to both your remote servers. A local copy will be stored on the originating server, as well, resulting in 3 backups on all 3 servers.

You can then place mysqlbackup.php on all 3 of your servers, and do the same thing. All your servers will be backed up, as many times per day or week as you like (via cronjob) and each server will have a backup of each REMOTE server! It is recommended you store your back ups in at least 1 remote server, but the more the better.

It is possible, to run a server for a fair amount of time, WITHOUT any type of RAID configuration. However, RAID 1, along with the use of mysqlbackup.php (or any other backup program) is the best way to go about preserving server UPTIME, as well as, data, specifically Databases.

Although, RAID is beyond the scope of this tool and this write up. I can tell you, that by simply mirroring, by running RAID level 1, will ensure your server continue to operate, even if a disk happens to completely fail. RAID 1 requires 1 additional disk, used to pair up, with disks you want to mirror. So, if you have two (2), 36 gig scsi hard drives, you will need an additional two (2) 36+ gig scsi hard drives, used to mirror your existing 2 drives. RAID 1 is a very common way to ensure, near 100% uptime, even when you have a drive crash on you. I HIGHLY, with almost religious passion, recommend a RAID 1 set up, in conjunction with a back up of 'key' datas. And, thoses datas, saved on multiple servers.

The possible scenerios are as follows. (If using RAID 1 + mysqlbackup.php)

1) One of your drives crashes and burns. Have a tech, or yourself, IMMEDIATELY, remove the failed disk, and replace it with a new one of equal size or greater. Resulting in, No downtime, and no loss of data. Very smooth, Mr. Operator. :)

2) Both mirrored drives, happen to fail, AT THE SAME TIME! This is VERY, VERY rare, but can happen. In this case, you will be glad you were backing up your data, and remotely sending it to at least 1 remote server. Although, the backups that mysqlbackup.php provide, are not drop in replacement backups. Your MySQL Databases will be 100% recoverable AND your server's configuration state is saved, in the /etc tarball back up, along with your /home tarball back up. You then need to get replacement drives, reinstall the latest and greatest Linux OS. Finally, once everything is RAID 1 configured and running, along with 1 or more spare drives within reach, you can begin to rebuild your server. Restore using /home and /etc. It may be as simple as untaring /home into it's new location and making a few tweeks. Paying close attention to file permission resets and '.' hidden folders and files.

It is NOT recommended you untar the /etc backup over top the new one! The reasons is that the new Distro will be using updated software packages, and the old configuration files, in your /etc backup, may not be compatible.

What you want to do, is untar /etc to /tmp/etc or similar, and cherry pick which files that can be used as a reference to help you get your new server configuration, back to how it used to be configured. Services like, Apache Virtual Host data, my.cnf settings, a custom php.ini, tweeks you may have made to any default /etc file. In other words, the backup of /etc is not meant as a drop in replacement, but rather, a snapshot of how your server was previously set up. You can use them, to get the ball rolling again, as painlessly as possible.

Included, is your root's crontab. My root's crontab has over a dozen schedualed items, so trying to recall all that from memory, would be a royale pain in the arse.

Restoring MySQL

Restoring your MySQL data, is just a matter of uncompressing the .sql files. One .sql file per database backup. After you reinstal the same or newer version of a MySQL Server, begin executing (as root user), the following for each .sql file you have.

mysql -u root -p < mysql_your_backed_up_db.sql