Backup/Copy a MySQL table to another table on same server.

So I need to update our table copies at work due to moving to InnoDB table from MyISAM, finally!

I have created a simple script thanks to Todd's MySQL Blog for some insights into transportable tablespaces and MySQL dev site on table copying InnoDB tables.  Trick here is to ensure that you can still copy MyISAM tables during the transition.

More Error handling/checking to come...

create file: copy_table.php

#!/usr/local/bin/php
<?php
define('_REPLICATION_USER', 'xxx');
define('_REPLICATION_PASS', 'xxx');

$db = new PDO('mysql:host='.$argv[1].';dbname=mysql', 'xxx', 'xxx');

$originalschema=$argv[2];
$originaltablename=$argv[3];
$copyedschema=$argv[4];
$copyedtablename=$argv[5];

//copy_table.php localhost originalschema originaltablename copyedschema copyedtablename


// check schema info
$engine = $db->query("select engine from information_schema.tables 
where table_schema = '$originalschema' AND  table_name = '$originaltablename'")->fetch(PDO::FETCH_COLUMN);
$engine2 = $db->query("select engine from information_schema.tables where table_schema = '$copyedschema' AND  table_name = '$copyedtablename'")->fetch(PDO::FETCH_COLUMN);


if($engine && $engine2!=$engine){
    echo "creating $copyedschema.$copyedtablename\n";
    // different table type for copy table, drop it like its HOT!
    $db->query( "DROP TABLE $copyedschema.$copyedtablename" );
    // create copy
    $db->query( "CREATE TABLE IF NOT EXISTS $copyedschema.$copyedtablename LIKE $originalschema.$originaltablename" );
    // make sure there is something in there.
    $db->query( "INSERT INTO $copyedschema.$copyedtablename SELECT * FROM $originalschema.$originaltablename LIMIT 1" );
}

switch ($engine){
//####################################################################################
case "InnoDB":
    echo "InnoDB:\n\n";
    if(file_exists("/var/lib/mysql/$originalschema/$originaltablename.ibd")){
        $db->query( "ALTER TABLE $copyedschema.$copyedtablename DISCARD TABLESPACE" );
        $db->query( "FLUSH TABLES $originalschema.$originaltablename FOR EXPORT" );

        # save data file.
        exec("cp -pf /var/lib/mysql/$originalschema/$originaltablename.ibd /var/lib/mysql/$copyedschema/$copyedtablename.ibd");

        $db->query( "UNLOCK TABLES" );
        $db->query( "ALTER TABLE $copyedschema.$copyedtablename IMPORT TABLESPACE" );
    } else {
        echo "InnoDB file does not exist.\n\n";;
    }

    break;
//####################################################################################
case "MyISAM":
    echo  "MyISAM:\n\n";
    if(file_exists("/var/lib/mysql/$originalschema/$originaltablename.MYI")){
        $db->query( "FLUSH TABLES $originalschema.$originaltablename WITH READ LOCK" );

        # save data files.
        exec("cp -pf /var/lib/mysql/$originalschema/$originaltablename.frm /var/lib/mysql/$copyedschema/$copyedtablename.frm");
        exec("cp -pf /var/lib/mysql/$originalschema/$originaltablename.MYI /var/lib/mysql/$copyedschema/$copyedtablename.MYI");
        exec("cp -pf /var/lib/mysql/$originalschema/$originaltablename.MYD /var/lib/mysql/$copyedschema/$copyedtablename.MYD");

        $db->query( "UNLOCK TABLES" );
        $db->query( "FLUSH TABLES $copyedschema.$copyedtablename" );
    } else {
        echo "MyISAM file does not exist.\n\n";;
    }

    break;
//####################################################################################
}

Make the file executable.
 

Usage:
copy_table.php localhost originalschema originaltablename copyedschema copyedtablename


 

Popular Posts