mg_hot_replace_table.pl

May 1, 2010

Do you have MyISAM tables you reload with new data?

Do your queries, using that table, get blocked because the table is locked?

Do the waiting queries create idle connections slowing down the table load?

Do you wish you could just replace the table?

Years ago I was told you can replace CSV tables by simply replacing the CSV file. I figured this would also be true of a MyISAM file and it is. I use this perl script to replace MyISAM tables forcast and current observation weather data. The processing and tables are created on another computer. Weather forecasting is CPU and database expensive. I then copy (rsync) the files to the production system and run this script.

#!/usr/bin/perl
################################################################################
################################################################################
# mg_hot_replace_table.pl - Hot Replace a MySQL table.
#
# 2010-05-01 Mark Grennan - Weather Decision Technonigy Inc.
#
################################################################################
################################################################################
 use DBI;
 use File::Copy;
 use Getopt::Long;
 use Pod::Usage;
################################################################################
# These items need to be modified for your needs.
################################################################################
 $mydir="/data/mysql";           # MySQL data directory
 $stage="$mydir/stage";          # Directory containing the NEW data
 # database information
 $db="point_forecast";           # Database that containes the table to be replaced.
 $tb="uv_hourly";                # Table being replaced.
 $host="127.0.0.1";              # IP or FQDN of the MySQL server (localhost)
 $port="3306";                   # Port used by localhost (3306)
 $userid="dba";                  # User with access to database.
 $passwd="dbap@sswd";            # Password for the user.
################################################################################
# take command line options
################################################################################
 $help=0;
 &GetOptions("mydir=s" => \$mydir, "stage=s" => \$stage, "u=s" => \$userid, "p=s" => \$passwd, "port=i" => \$port, "db=s" => \$db, "tb=s" => \$tb, 'help|?' => \$help);
 if ($help) {
 &usage;
 exit 1;
 }

################################################################################
# main
################################################################################

#&check_table;
print "Connecting to database $db.$tb on $host:$port\n";
# make connection to database
$connectionInfo="DBI:mysql:$db:$host:$port";
$dbh = DBI->connect($connectionInfo,$userid,$passwd) || die ;
&lock_table;
&flush_table;
&move_files;
&flush_table;
$sth->finish()
 or die "Couldn't execute statement: " . $sth->errstr;
print "Disconnecting from MySQL.\n";
$dbh->disconnect        # disconnect from database
 or die "Couldn't execute statement: " . $sth->errstr;

print "DONE\n";
exit 0;

################################################################################
# Subrutines
################################################################################
sub lock_table {                # Lock table to prevent READs
 print "Locking Table\n";
 $query = "LOCK TABLES $tb WRITE;";
 $sth = $dbh->prepare($query);
 $sth->execute()
 or die "Couldn't execute statement: " . $sth->errstr;
}
sub unlock_table {              # Un-Lock table to allow use.
 print "UnLocking Table\n";
 $query = "UNLOCK TABLES;";
 $sth = $dbh->prepare($query);
 $sth->execute()
 or die "Couldn't execute statement: " . $sth->errstr;
}
sub flush_table {               # Flush table cache data.
 print "Flushing Table\n";
 $query = "FLUSH TABLE $tb";
 $sth = $dbh->prepare($query);
 $sth->execute()
 or die "Couldn't execute statement: " . $sth->errstr;
}
sub move_files {                        # Move new data to table files being used.
 print "Moving Table Files\n";
#    `rm -f /data/mysql/$db/$tb.frm /data/mysql/$db/$tb.MYD /data/mysql/$db/$tb.MYI`;
#    `mv $stage/$tb.frm  $stage/$tb.MYD $stage/$tb.MYI /data/mysql/$db`;
 if (-e "$stage/$tb.frm" and -e "$stage/$tb.MYD" and -e "$stage/$tb.MYI" ) {
 move("$stage/$tb.frm", "$mydir/$db");
 move("$stage/$tb.MYD", "$mydir/$db");
 move("$stage/$tb.MYI", "$mydir/$db");
 } else {
 print "There is a file missing! Files NOT moved.\n";
 }
}
sub check_table {
 $status = `mysqlcheck $stage $tb`;
 print "Status is  $status";
 if ($status) {
 print "New data table is BAD!\n";
 exit 2;
 }
}
sub usage ($) {
 my ($message)= @_;
 if ( $message )
 {
 print STDERR "$message\n";
 }

print <<HERE;

$0 [ OPTIONS ]
Options are:
 -mydir=      Directory containing mysql data ($mydir)
 -stage=      Staging database ($stage) where new data table is held
 -u=          User name ($userid)
 -p=          Password ($passwd) -port= Port ($port)
 -db=         Destination database holding destination table ($db)
 -tb=         Table ($tb)
 -help        Prints this help
HERE
}

tags: , , , , , ,
posted in Code by admin

Follow comments via the RSS Feed | Leave a comment | Trackback URL

3 Comments to "mg_hot_replace_table.pl"

  1. Nic Zero wrote:

    You can make this more generally useful if you implement it as a class and your routines become methods. Modify them so they take the db handle as a parameter. Then any piece of code you have that has a mysql db handle can call one or more methods as it wants.

  2. mark wrote:

    You are right. I can think of lots of ways to make this a more useful utility.
    – Check that the new data files is on the same file system
    – move the file to the same file system for the user
    – check the file is not corrupted.
    – option to archive the old file
    – add the old file to an archive by renaming it and moving it.

    Most people don’t completely replace a table. What I have is weather forecast data. It’s good for a short time and then is replaced. Old forecast data is replace with the observations for the same location.

    I also found this useful when I had large amounts of cell phone records that arrives in large CVS files and needed to be indexed before “loading” into another database. I would prepare the data on an offline server using “Load File”, index it and then move it to the production server. This method would load millions of records in milliseconds.

  3. Log Buffer #188, a Carnival of the Vanities for DBAs | The Pythian Blog wrote:

    […] the community know about his blog MySQL Fan Boy, where he wrote an interesting post on including a script to replace MySQL table files on a live system, making it faster and limiting locking on large table loads. Also a post this week […]

Leave Your Comment

You must be logged in to post a comment.

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.