Script to copy all tables from one database to another.

copy-db.sh

#!/bin/bash
#
# Script to copy all tables from one database to another.
#
# Requires:
#  * MySQL 5.6
#  * Percona xtrabackup tools (http://www.percona.com/software/percona-xtrabackup)
#
# Known caveats:
#  * Will copy structure of all tables from one database to another, but only InnoDB
#    tables will have the data copied
#  * Only support copying tables from databases in the same server (although it's not
#    very difficult to adapt to copy to another server).
#
# Basic Usage:
#  * copy-db.sh source_db destination_db
#
# If you want to specify options for MySQL, use the MYSQL_OPTS variable:
#  * MYSQL_OPTS="-u root -proot" copy-db.sh source_db destination_db
#
# To use a different temp directory (to avoid filling up your hard disk) 
#  * TEMP_DIR="/mnt/big-disk/tmp" copy-db.sh source_db destination_db
#
# License: MIT <http://www.opensource.org/licenses/MIT>
#

set -e

# MySQL user/group
MYSQL_USER="mysql"
MYSQL_GROUP="mysql"

SOURCE_DB="$1"
DESTINATION_DB="$2"

list_innodb_tables()
{
    local SQL="SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '$SOURCE_DB' AND ENGINE = 'InnoDB'"
    mysql $MYSQL_OPTS information_schema --skip-column-names --execute="$SQL"
}

get_datadir()
{
    mysql $MYSQL_OPTS --skip-column-names --execute="SELECT @@datadir";
}

__alter_table_tablespaces()
{
    local OPERATION="$1"
    local TABLES=$(list_innodb_tables)
    local SQL="SET FOREIGN_KEY_CHECKS=0"
    for TABLE in $TABLES
    do
        SQL="$SQL; ALTER TABLE $DESTINATION_DB.$TABLE $OPERATION TABLESPACE"
    done
    SQL="$SQL; SET FOREIGN_KEY_CHECKS=1"
    echo $SQL

}

discard_tablespaces()
{
    local SQL=$(__alter_table_tablespaces DISCARD)
    mysql $MYSQL_OPTS --execute="$SQL"
}

import_tablespaces()
{
    local SQL=$(__alter_table_tablespaces IMPORT)
    mysql $MYSQL_OPTS --execute="$SQL"
}

copy_schema()
{
    mysqldump $MYSQL_OPTS -d $SOURCE_DB | mysql $MYSQL_OPTS $DESTINATION_DB
}

copy_files()
{
    local CMD='rsync -a'
    $CMD $TEMP_DIR/$SOURCE_DB/*.ibd $MYSQL_DATADIR/$DESTINATION_DB/
    $CMD $TEMP_DIR/$SOURCE_DB/*.exp $MYSQL_DATADIR/$DESTINATION_DB/
    $CMD $TEMP_DIR/$SOURCE_DB/*.cfg $MYSQL_DATADIR/$DESTINATION_DB/
}

cleanup()
{
    rm -rf $TEMP_DIR
}

fix_permissions()
{
    chown $MYSQL_USER.$MYSQL_GROUP $MYSQL_DATADIR/$DESTINATION_DB/*.ibd $MYSQL_DATADIR/$DESTINATION_DB/*.exp $MYSQL_DATADIR/$DESTINATION_DB/*.cfg
}

make_backup()
{
    mkdir -p $TEMP_DIR
    xtrabackup $MYSQL_OPTS --backup --tables="^$SOURCE_DB[.].*" --target-dir=$TEMP_DIR
    xtrabackup $MYSQL_OPTS --prepare --export --target-dir=$TEMP_DIR
}

if [[ "$MYSQL_DATADIR" == "" ]]; then
    MYSQL_DATADIR=$(get_datadir)
fi

if [[ "$TEMP_DIR" == "" ]]; then
    TEMP_DIR=$(mktemp -d -t mycpdb.XXXX)
fi

copy_schema
make_backup
discard_tablespaces
copy_files
fix_permissions
import_tablespaces
cleanup