Provisioning an Oracle slave using Tungsten and MySQL Sandbox

A few years ago, I used MySQL Sandbox to filter binary logs. While that one was a theoretical case, recently I came across a very practical case where we needed to provision an Oracle database, which is the designated slave of a MySQL master.

In this particular case, we needed to provision the Oracle slave with some auditing features in place. Therefore, mass load functions were not considered. What we needed was the contents of the MySQL database as binary rows i.e. the same format used for row-based replication.

To achieve the purpose as quickly and as painlessly as we could, I thought to employ the services of a MySQL Sandbox. The flow of information would be like this:

1. Install a sandbox, using the same version and character set of the master;
2. Set the sandbox default binary log format as ROW;
3. take a backup with mysqldump (with no data)
4. filter the CREATE TABLE statements, replacing INNODB with BlackHole. This pass is important to speed up insert operations and to minimise storage needs. Using this trick, we only needed storage for the binary logs, not the data itself.
5. Load the altered schema into the sandbox
6. Take a full data dump without creation info
7. Load the data into the sandbox
8. Start replicating from the sandbox to the Oracle slave (using Tungsten Replicator)
9. When the provisioning is over, point the replicator to the original master, to the point where we finished dumping data;
10. Start replicating live from MySQL to Oracle.

Despite the many steps, the plan worked quite smoothly. We managed to extract data from three databases (and about 600 tables) for a total of 0.5 TB, in about 7 hours. The task was completed using a tool developed by my colleague Linas, which does an automated DDL translation from MySQL to Oracle. Probably this tool deserves an article of its own.

Filtering mysqldump output

Several people have suggested a more flexible approach at mysqldump output in matter of user privileges. When you dump the data structure for views, triggers, and stored routines, you also dump the permissions related to such objects, with the DEFINER clause. It would be nice to have such DEFINER clauses removed, or even replaced with the appropriate users in the new database.

The mysqldump filter was created with this need in mind. It allows you to remove all DEFINER clauses and eventually replacing them with a better one.
For example:

mysqldump --no-data sakila | dump_filter --delete > sakila_simple.sql
mysqldump --no-data sakila | dump_filter --replace='newuser@`10.%`' > sakila_secure.sql

The first example removes all references to DEFINER, while the second one replaces every definer with a new user name.

Update Since MySQL Forge is not available anymore, here's the code:

#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );

my $replace = undef;
my $delete  = undef;
my $help    = 0;

GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');

help() if $help;

if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}

$delete = 1 unless $replace;

while (my $line = ) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    } 
    print $line;
}

sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
    print "dump_filter - mysqldump filter \n",
          "(C) Giuseppe Maxia, 2009\n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}


__END__
notes: MySQLdump filter.
Removes or replaces the DEFINER clauses from a dump.
It is necessary when you want to migrate a database to a new server with different users.

Editing a dump file from the airport

Keith Murphy asks if you have ever edited your dump files before feeding them to the database. And he recommends using sed. This reminds me of a war story, when I was consulting in Milan. The customer needed, among other things, to import a huge text file (created by a third party application) into a MySQL database. I did everything, including a cron job to load the data every day. By the time I left for the airport, the third party application hadn't yet provided its first sample, but since we knew the fields list, it was a simple case of creating a LOAD DATA INFILE command.
I was boarding my plane, when the customer called, saying that the application was giving dates in the wrong format. Instead of 'YYYY-MM-DD', it was using the Italian human readable format 'DD/MM/YYYY'. No Internet was available, and with just a few minutes before being confined to the airplane, I told the customer "I will send you a text message. Just pipe this command before feeding the file to the database."
And I sent this message:

perl -pe 's{(\d\d)/{\d\d}/{\d\d\d\d}}{$3-$2-$1}g'

I took a picture of my phone before sending the message.

When I landed, I got a text message from the customer. Everything worked fine.
So, that was when I edited 2 GB data file from the airport, using a mobile phone.