Skip to content

Instantly share code, notes, and snippets.

@rcmachado

rcmachado/copy-db.sh

Last active April 3, 2021 01:54
Show Gist options
  • Save rcmachado/ef5d57a1718f1feb0858 to your computer and use it in GitHub Desktop.
Save rcmachado/ef5d57a1718f1feb0858 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rcmachado revised this gist Aug 4, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions copy-db.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -96,8 +96,8 @@ fix_permissions()
    make_backup()
    {
    mkdir -p $TEMP_DIR
    innobackupex $MYSQL_OPTS --backup --tables="^$SOURCE_DB[.].*" --target-dir=$TEMP_DIR
    innobackupex $MYSQL_OPTS --prepare --export --target-dir=$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
  2. rcmachado revised this gist Aug 4, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions copy-db.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -96,8 +96,8 @@ fix_permissions()
    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
    innobackupex $MYSQL_OPTS --backup --tables="^$SOURCE_DB[.].*" --target-dir=$TEMP_DIR
    innobackupex $MYSQL_OPTS --prepare --export --target-dir=$TEMP_DIR
    }

    if [[ "$MYSQL_DATADIR" == "" ]]; then
  3. rcmachado created this gist Jul 26, 2014.
    117 changes: 117 additions & 0 deletions copy-db.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,117 @@
    #!/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