MySQL backups.
Installling backup for MySQL
Once you’ve got a bunch of data installed in your database, you need to find someway of backing it up. Westhost provide a couple of ways:
- Use the Site Manager backup. This will backup the whole web site, which includes the MySQL databases.
- Use PhpMyAdmin to export the database to a local computer. or possibly:
- Run a shell script to backup the /var/lib/mysql directories.
The main disadvantage of the above is that they have to be done manually and if backup procedures are to work, then they need to be automated. I looked around for a way of automating the backups of the MySQL data, and I came up with mysql_backup.pl, which is available from worldcommunity.com. Although the script hasn’t been updated for a couple of years, it is a straightforward Perl script and works well on Westhost. ‘mysql_backup.pl’ is designed to be run by a cron script or manually. It will use mysqldump to dump out the database tables and then tar/gzip the data up into an archive and store that locally. In addition it can be setup to either email the archive or ftp it to another site.
Here are the instructions for installing the script.
In Brief:
- Make sure that you have the PerlMySQL application installed in the Site Manager. This is needed. You will also need the Build System if you want to compile and install DBI.
- Log in to your account through SSH and run cpan (see Modifying Perl and install MIME::Lite (for email backups).
- Edit and install the ‘mysql_backup.pl’ perl script (see below for details).
- Create the ftp directory if using FTP
- Run the script from the shell to check that it works.
- Install it as a cron job to run regularly.
In a Little More Detail:
1. mysql_backup.pl needs the Perl MySQL libraries to run, the easiest way to get them is to use the Site Manager to install them. Look for PerlMySQL in the Database section. And, of course, you will need Perl but that is normally installed anyway.
2. MIME::Lite seems to install quite cleanly in the regular Perl library location, so there is no need to install it in /perl5lib/. This is only needed if you are going to have the backups emailed.
3. The backup script is one file and all the configuration variables are at the start of the file, well commented, so there should not be any problem with configuring the script. First you need to decide whether, or if, you want the backups emailed to you or ftp’d to another site. From the top, these are the bits you are looking for:
- $ftp_backup — set to yes to enable ftp backup.
- $email_backup — enables email backup.
- $send_method = ’sendmail’ The mail settings for smtp are only used on Windows boxes, we will use sendmail on Westhost.
- $admin_email_to — your email address
- $admin_email_from — something that appears in the from line.
In the “database options” section:
- $web_test_database — only used for web access to backup script
- @selected_databases — as needed, overridden by next
- $process_all_databases — as needed.
- @skip_databases — I usually set this to skip ’sphera_db’
- $password_location = ‘cnf’
In the dbhost section:
- $db_host = ‘localhost’;
- $db_port = ‘3306′;
- $cnf_file = ‘/etc/my.cnf’;
- $site_name — set it to your site name (whatever you want really…)
$subject — set it to a subject that appears in the email header.
$mysql_backup_dir — this needs to point to a valid directory. I create a directory in my home directory call ‘mysql_backups’, so this path would be ‘/home/mydomain/mysql_backups’
$find_commands = ‘no’ (we already know where the commands are).
- $compress_method = ‘pipe’;
Other optional variables:
- $show_file_list_in_email = ‘yes’; if you want to see a list of files that have been backed up.
If you are going to use ftp for the backup, you need to set the ftp section variables.
- $ftp_host = ‘ftp.mydomain.com’;
- $ftp_passive = ‘1′;
- $ftp_user = ‘admin’;
- $ftp_password = ‘topsecret’
- $ftp_dir = ‘backups’; this directory needs to be created on the receiving server. See note later about creating an ftp repository.
- $file_prefix — give the file a prefix, e.g. ‘mydomain.mysql’
I needed to change line 1149 from: @lines = $ftp->dir(”$ftp_dir”) or to: @lines = $ftp->dir(”.”) or
4. Creating an FTP repository. If you want to do offsite backups to another machine, then FTP may be an easy way to go. I have many sites on Westhost, one of them I set up to receive backups from the other sites. It would be even better of course to use a site hosted somewhere completely different. On the site that will be a repository, create a user which has FTP capability. This will create a directory for that user in ‘/ftp/pub/’. For example… Create a user ‘backup’ with password ’secret’. This will create a directory ‘/ftp/pub/backup/’. Create an empty file in there with ‘touch /ftp/pub/backup/marker’. This is to get around a bug in the script where it tries to get a listing of the directory and if it is empty (which it will be on first usage) then the script fails.
5. By now you should be able to run the script from the shell, watch lots of message whizz by and then see the line “Done! Exiting from MySQL Backup Script.” which indicates success.
6. The final touch is to make an entry in your crontab to have the script run regularly. To run it once a week, put the following line in your crontab:
0 3 * * sat /home/prabhumusic/bin/mysql_backup.pl
which runs it at 3am every Saturday.
Further notes:
If you follow the setup above, the script will run once a week, ftp the files to an ftp site and remove any old backups older than 5 days… which with a weekly backup is not what we need. The section of the script around 595-600 is what controls how many old backups are kept. To do a weekly backup and keep 5 old backups, then change ‘$increment_type’ to ‘Weeks’ and ‘$increment_multiplier’ to 604800. The comments explain in a little more detail what options there are. As the default script comes, it assumes a daily backup and keeps 5 old backups.
Hope that helps someone out, as usual, any comments, corrections etc, send them in.