Scheduled MySQL server backups with cron and mysqldump

December 20, 2012 under Main

This is a task I perform fairly regularly, but not regularly enough to remember exactly which permissions are needed, so I invariably end up having to look up the MySQL reference manual.

The task is to set up a scheduled backup of a MySQL server using mysqldump and cron. The backup should contain not only a copy of all databases on the server but also the state of the binary logs at the time, ie. using the –master-data option. I find binary logging + regular full snapshots of the MySQL server to be a great and simple backup strategy that allows not only for restoring the entire server to a known good state (from the snapshot backup) but also restoring to any point in time using the binary logs.

For example, let’s say you run a daily backup at midnight. At 13:26 a friendly Web developer (definitely not you) made a typo in an SQL query and wiped out a whole column full of very important data.

With snapshots + binary log, you can:

1. Restore the snapshot from midnight
2. Use the mysqlbinlog command to extract all the data-affecting queries that were run on the server between the time the backup ran and the time when the erroneous query was made. You can do this by starting your search from the log position recorded at the top of your backup file, and ending with the position just before the bad query. Use mysqlbinlog and grep to find the latter position. Pipe all this extracted data into another sql file.
3. Import this sql file into your MySQL server.
4. You now have your server restored to the exact state it was in immediately prior to the nefarious query.

Sounds good? Here’s how to set up this backup scheme:

1. Set up a MySQL user with permission to run the backups, nothing more:

mysql> GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD, SUPER, REPLICATION CLIENT ON *.* TO ‘backup’@’localhost’;

mysql> FLUSH PRIVILEGES;

2. Set up a cron job: [root@myserver ~]# crontab -e
# MySQL backup
0 0 * * * mysqldump -u backup -A –master-data -v | gzip > /backups/current/MySQL/backup.sql.gz

Change the path to taste. Or if you prefer to store 7 days worth of backups, you could do something like:

0 0 * * * mysqldump -u backup -A –master-data -v | gzip > /backups/current/MySQL/backup-`date +\%a`.sql.gz

This will name your backups with the day of the week in the filename, eg. backup-Mon.sql.gz. See ‘man date’ for formatting options.

Esc, :w and :q to save your crontab and exit.

An explanation of the options to mysqldump:

-u: The MySQL user name to use when connecting to the server.
–master-data: Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.
-A: Dump all tables in all databases.
-v: Verbose mode. Print more information about what the program does.

MySQL dump files are plain text which compress really well, so I feed the data into gzip before writing to disk.

It is worth noting that in order for mysqldump to take a consistent snapshot using –master-data, –lock-all-tables is automatically turned on: “Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump.”

If you have a large amount of data, the backup may take a considerable amount of time, during which any INSERT/UPDATE/DELETE operations on your databases will be blocked. The queries will be shown in the process list as “Waiting for release of readlock” and will execute when your backup has completed, if the connection has not yet timed out. To avoid this problem, I typically recommend to my customers to run a separate MySQL server specifically as a disaster recovery and/or backup system. This can be on a separate server, a small VM, or even on the same server as the primary but bound to a different port. Set the backup server to replicate from the master using MySQL’s built-in replication system then schedule your backups to run on the backup server instead of the master. This way you will avoid any disruption to your live system and still get clean snapshots. While your backup is running the replication slave process will have to wait for the table locks, but as soon as the backup is finished replication will automatically resume so your slave catches up with the master again.

comments: Comments Off on Scheduled MySQL server backups with cron and mysqldump

Script to convert ECSL CSV files exported from MYOB on Mac to HMRC compatible CSV

December 4, 2012 under Main

Every 3 months we have to send Her Majesty’s Revenue and Customs (HMRC) in the UK a list of customers in the EU to which we have sold our services, this list is called the ECSL or European Community Sales List. Unfortunately due to choices made long ago which are non-trivial to change, we use a program called MYOB on a Mac for our book keeping. Unlike some of its competitors, MYOB can’t upload the ECSL data directly to HMRC, so we have to enter the data into forms manually.

This is a very tedious job, and since I don’t like tedious things, I set out to find a better solution. HMRC offer a facility whereby you can automate the data entry by importing a CSV file formatted a specific way. MYOB can export the ECSL to CSV, but the format is not compatible with the HMRC system.

The solution I came up with was to write a small Perl script to convert from the MYOB format to the HMRC format. Posted here, in case anyone else finds it useful!

#!/usr/bin/perl -w

# Input file (MYOB export) should be passed as an argument to this script
my $infile = $ARGV[0];
chomp($infile);

my $outfile = $infile . '-hmrc.csv';

# Ask for year and month for this submission
print "Year: ";
my $year = <STDIN>;
chomp($year);
print "Month: ";
my $month = <STDIN>;
chomp($month);

# open output file
open(HMRCFILE, '>', $outfile) or die "Could not open output file: $!";

# Write header
my $vatregno = "your_number_here";
my $subsidiary = "000";
my $name = "Christopher Wik";
print HMRCFILE "HMRC_VAT_ESL_BULK_SUBMISSION_FILE\n";
print HMRCFILE "$vatregno,$subsidiary,$year,$month,GBP,$name,0\n";

# Set record separator to \r (Mac) - MYOB saves in this format
$/ = "\r";

# Convert data from MYOB to HMRC format
open(MYOBFILE, $infile) or die "Could not open input file: $!";
while(<STDIN>) {
  next if !/,\w\w\d+/;
  my ($cust,$vat,$amount) = split(/,/,$_,3);
  my $country = substr($vat, 0, 2);
  my $vatno = substr($vat, 2);
  $amount =~ /(\d+)/;
  $amount_num = $1;
  $amount =~ /(-)/;
  if( $1 eq '-' ) { $amount_num *= -1; }
  print HMRCFILE "$country,$vatno,$amount_num,3\n";
}
close(MYOBFILE);

# close HMRC output file
close(HMRCFILE);
comments: Comments Off on Script to convert ECSL CSV files exported from MYOB on Mac to HMRC compatible CSV
Subscribe