Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to copy all tables from one database to another.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment