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 <

Tweet