[mysql] MySQL: Cloning a MySQL database on the same MySql instance

I would like to write a script which copies my current database sitedb1 to sitedb2 on the same mysql database instance. I know I can dump the sitedb1 to a sql script:

mysqldump -u root -p sitedb1 >~/db_name.sql

and then import it to sitedb2. Is there an easier way, without dumping the first database to a sql file?

mysqladmin create DB_name -u DB_user --password=DB_pass && \
        mysqldump -u DB_user --password=DB_pass DB_name | \
        mysql     -u DB_user --password=DB_pass -h DB_host DB_name

First create the duplicate database:

CREATE DATABASE duplicateddb;

Make sure the permissions etc are all in place and:

mysqldump -u admin -p originaldb | mysql -u backup -p password duplicateddb;

Using MySQL Utilities

The MySQL Utilities contain the nice tool mysqldbcopy which by default copies a DB including all related objects (“tables, views, triggers, events, procedures, functions, and database-level grants”) and data from one DB server to the same or to another DB server. There are lots of options available to customize what is actually copied.

So, to answer the OP’s question:

mysqldbcopy \
    --source=root:your_password@localhost \
    --destination=root:your_password@localhost \
    sitedb1:sitedb2

You need to run the command from terminal / command prompt.

mysqldump -u <user name> -p <pwd> <original db> | mysql -u <user name> <pwd> <new db>

e.g: mysqldump -u root test_db1 | mysql -u root test_db2

This copies test_db1 to test_db2 and grant the access to 'root'@'localhost'

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names. However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

To perform the task of upgrading database names with the new encoding, use ALTER DATABASE db_name UPGRADE DATA DIRECTORY NAME instead: http://dev.mysql.com/doc/refman/5.1/en/alter-database.html

I don't think there is a method to do this. When PHPMyAdmin does this, it dumps the DB then re-inserts it under the new name.

Best and easy way is to enter these commands in your terminal and set permissions to the root user. Works for me..!

:~$> mysqldump -u root -p db1 > dump.sql
:~$> mysqladmin -u root -p create db2
:~$> mysql -u root -p db2 < dump.sql

In addition to Greg's answer, this is the easiest and fastest way if the new_db_name doesn't yet exist:

echo "create database new_db_name" | mysql -u <user> -p <pwd> 
mysqldump -u <user> -p <pwd> db_name | mysql -u <user> -p <pwd> new_db_name

As mentioned in Greg's answer, mysqldump db_name | mysql new_db_name is the free, safe, and easy way to transfer data between databases. However, it's also really slow.

If you're looking to backup data, can't afford to lose data (in this or other databases), or are using tables other than innodb, then you should use mysqldump.

If you're looking for something for development, have all of your databases backed up elsewhere, and are comfortable purging and reinstalling mysql (possibly manually) when everything goes wrong, then I might just have the solution for you.

I couldn't find a good alternative, so I built a script to do it myself. I spent a lot of time getting this to work the first time and it honestly terrifies me a little to make changes to it now. Innodb databases were not meant to copied and pasted like this. Small changes cause this to fail in magnificent ways. I haven't had a problem since I finalized the code, but that doesn't mean you won't.

Systems tested on (but may still fail on):

• Ubuntu 16.04, default mysql, innodb, separate files per table
• Ubuntu 18.04, default mysql, innodb, separate files per table

What it does

1. Gets sudo privilege and verifies you have enough storage space to clone the database
2. Gets root mysql privileges
3. Creates a new database named after the current git branch
4. Clones structure to new database
5. Switches into recovery mode for innodb
6. Deletes default data in new database
7. Stops mysql
8. Clones data to new database
9. Starts mysql
10. Links imported data in new database
11. Switches out of recovery mode for innodb
12. Restarts mysql
13. Gives mysql user access to database
14. Cleans up temporary files

How it compares with mysqldump

On a 3gb database, using mysqldump and mysql would take 40-50 minutes on my machine. Using this method, the same process would only take ~8 minutes.

How we use it

We have our SQL changes saved alongside our code and the upgrade process is automated on both production and development, with each set of changes making a backup of the database to restore if there's errors. One problem we ran into was when we were working on a long term project with database changes, and had to switch branches in the middle of it to fix a bug or three.

In the past, we used a single database for all branches, and would have to rebuild the database whenever we switched to a branch that wasn't compatible with the new database changes. And when we switched back, we'd have to run the upgrades again.

We tried mysqldump to duplicate the database for different branches, but the wait time was too long (40-50 minutes), and we couldn't do anything else in the meantime.

This solution shortened the database clone time to 1/5 the time (think coffee and bathroom break instead of a long lunch).

Common tasks and their time

Switching between branches with incompatible database changes takes 50+ minutes on a single database, but no time at all after the initial setup time with mysqldump or this code. This code just happens to be ~5 times faster than mysqldump.

Here are some common tasks and roughly how long they would take with each method:

Create feature branch with database changes and merge immediately:

• Single database: ~5 minutes
• Clone with mysqldump: 50-60 minutes
• Clone with this code: ~18 minutes

Create feature branch with database changes, switch to master for a bugfix, make an edit on the feature branch, and merge:

• Single database: ~60 minutes
• Clone with mysqldump: 50-60 minutes
• Clone with this code: ~18 minutes

Create feature branch with database changes, switch to master for a bugfix 5 times while making edits on the feature branch inbetween, and merge:

• Single database: ~4 hours, 40 minutes
• Clone with mysqldump: 50-60 minutes
• Clone with this code: ~18 minutes

The code

Do not use this unless you've read and understood everything above.

#!/bin/bash
set -e

# This script taken from: https://stackoverflow.com/a/57528198/526741

function now {
    date "+%H:%M:%S";
}

# Leading space sets messages off from step progress.
echosuccess () {
    printf "\e[0;32m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echowarn () {
    printf "\e[0;33m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echoerror () {
    printf "\e[0;31m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echonotice () {
    printf "\e[0;94m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echoinstructions () {
    printf "\e[0;104m %s: %s\e[0m\n" "$(now)" "$1"
    sleep .1
}
echostep () {
    printf "\e[0;90mStep %s of 13:\e[0m\n" "$1"
    sleep .1
}

MYSQL_CNF_PATH='/etc/mysql/mysql.conf.d/recovery.cnf'
OLD_DB='YOUR_DATABASE_NAME'
USER='YOUR_MYSQL_USER'

# You can change NEW_DB to whatever you like
# Right now, it will append the current git branch name to the existing database name
BRANCH=`git rev-parse --abbrev-ref HEAD`
NEW_DB="${OLD_DB}__$BRANCH"

THIS_DIR=./site/upgrades
DB_CREATED=false

tmp_file () {
    printf "$THIS_DIR/$NEW_DB.%s" "$1"
}
sql_on_new_db () {
    mysql $NEW_DB --unbuffered --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')
}

general_cleanup () {
    echoinstructions 'Leave this running while things are cleaned up...'

    if [ -f $(tmp_file 'errors.log') ]; then
        echowarn 'Additional warnings and errors:'
        cat $(tmp_file 'errors.log')
    fi

    for f in $THIS_DIR/$NEW_DB.*; do
        echonotice 'Deleting temporary files created for transfer...'
        rm -f $THIS_DIR/$NEW_DB.*
        break
    done

    echonotice 'Done!'
    echoinstructions "You can close this now :)"
}

error_cleanup () {
    exitcode=$?

    # Just in case script was exited while in a prompt
    echo

    if [ "$exitcode" == "0" ]; then
        echoerror "Script exited prematurely, but exit code was '0'."
    fi

    echoerror "The following command on line ${BASH_LINENO[0]} exited with code $exitcode:"
    echo "             $BASH_COMMAND"

    if [ "$DB_CREATED" = true ]; then
        echo
        echonotice "Dropping database \`$NEW_DB\` if created..."
        echo "DROP DATABASE \`$NEW_DB\`;" | sql_on_new_db || echoerror "Could not drop database \`$NEW_DB\` (see warnings)"
    fi

    general_cleanup

    exit $exitcode
}

trap error_cleanup EXIT

mysql_path () {
    printf "/var/lib/mysql/"
}
old_db_path () {
    printf "%s%s/" "$(mysql_path)" "$OLD_DB"
}
new_db_path () {
    printf "%s%s/" "$(mysql_path)" "$NEW_DB"
}
get_tables () {
    (sudo find /var/lib/mysql/$OLD_DB -name "*.frm" -printf "%f\n") | cut -d'.' -f1 | sort
}

STEP=0


authenticate () {
    printf "\e[0;104m"
    sudo ls &> /dev/null
    printf "\e[0m"
    echonotice 'Authenticated.'
}
echostep $((++STEP))
authenticate

TABLE_COUNT=`get_tables | wc -l`
SPACE_AVAIL=`df -k --output=avail $(mysql_path) | tail -n1`
SPACE_NEEDED=(`sudo du -s $(old_db_path)`)
SPACE_ERR=`echo "$SPACE_AVAIL-$SPACE_NEEDED" | bc`
SPACE_WARN=`echo "$SPACE_AVAIL-$SPACE_NEEDED*3" | bc`
if [ $SPACE_ERR -lt 0 ]; then
    echoerror 'There is not enough space to branch the database.'
    echoerror 'Please free up some space and run this command again.'
    SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
    SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
    echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
    echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
    exit 1
elif [ $SPACE_WARN -lt 0 ]; then
    echowarn 'This action will use more than 1/3 of your available space.'
    SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
    SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
    echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
    echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
    printf "\e[0;104m"
    read -p " $(now): Do you still want to branch the database? [y/n] " -n 1 -r CONFIRM
    printf "\e[0m"
    echo
    if [[ ! $CONFIRM =~ ^[Yy]$ ]]; then
        echonotice 'Database was NOT branched'
        exit 1
    fi
fi

PASS='badpass'
connect_to_db () {
    printf "\e[0;104m %s: MySQL root password: \e[0m" "$(now)"
    read -s PASS
    PASS=${PASS:-badpass}
    echo
    echonotice "Connecting to MySQL..."
}
create_db () {
    echonotice 'Creating empty database...'
    echo "CREATE DATABASE \`$NEW_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" | mysql -u root -p$PASS 2>> $(tmp_file 'errors.log')
    DB_CREATED=true
}
build_tables () {
    echonotice 'Retrieving and building database structure...'
    mysqldump $OLD_DB --skip-comments -d -u root -p$PASS 2>> $(tmp_file 'errors.log') | pv --width 80  --name " $(now)" > $(tmp_file 'dump.sql')
    pv --width 80  --name " $(now)" $(tmp_file 'dump.sql') | sql_on_new_db
}
set_debug_1 () {
    echonotice 'Switching into recovery mode for innodb...'
    printf '[mysqld]\ninnodb_file_per_table = 1\ninnodb_force_recovery = 1\n' | sudo tee $MYSQL_CNF_PATH > /dev/null
}
set_debug_0 () {
    echonotice 'Switching out of recovery mode for innodb...'
    sudo rm -f $MYSQL_CNF_PATH
}
discard_tablespace () {
    echonotice 'Unlinking default data...'
    (
        echo "USE \`$NEW_DB\`;"
        echo "SET foreign_key_checks = 0;"
        get_tables | while read -r line;
            do echo "ALTER TABLE \`$line\` DISCARD TABLESPACE; SELECT 'Table \`$line\` imported.';";
        done
        echo "SET foreign_key_checks = 1;"
    ) > $(tmp_file 'discard_tablespace.sql')
    cat $(tmp_file 'discard_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
import_tablespace () {
    echonotice 'Linking imported data...'
    (
        echo "USE \`$NEW_DB\`;"
        echo "SET foreign_key_checks = 0;"
        get_tables | while read -r line;
            do echo "ALTER TABLE \`$line\` IMPORT TABLESPACE; SELECT 'Table \`$line\` imported.';";
        done
        echo "SET foreign_key_checks = 1;"
    ) > $(tmp_file 'import_tablespace.sql')
    cat $(tmp_file 'import_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
stop_mysql () {
    echonotice 'Stopping MySQL...'
    sudo /etc/init.d/mysql stop >> $(tmp_file 'log')
}
start_mysql () {
    echonotice 'Starting MySQL...'
    sudo /etc/init.d/mysql start >> $(tmp_file 'log')
}
restart_mysql () {
    echonotice 'Restarting MySQL...'
    sudo /etc/init.d/mysql restart >> $(tmp_file 'log')
}
copy_data () {
    echonotice 'Copying data...'
    sudo rm -f $(new_db_path)*.ibd
    sudo rsync -ah --info=progress2 $(old_db_path) --include '*.ibd' --exclude '*' $(new_db_path)
}
give_access () {
    echonotice "Giving MySQL user \`$USER\` access to database \`$NEW_DB\`"
    echo "GRANT ALL PRIVILEGES ON \`$NEW_DB\`.* to $USER@localhost" | sql_on_new_db
}

echostep $((++STEP))
connect_to_db

EXISTING_TABLE=`echo "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$NEW_DB'" | mysql --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')`
if [ "$EXISTING_TABLE" == "$NEW_DB" ]
    then
        echoerror "Database \`$NEW_DB\` already exists"
        exit 1
fi

echoinstructions "The hamsters are working. Check back in 5-10 minutes."
sleep 5

echostep $((++STEP))
create_db
echostep $((++STEP))
build_tables
echostep $((++STEP))
set_debug_1
echostep $((++STEP))
discard_tablespace
echostep $((++STEP))
stop_mysql
echostep $((++STEP))
copy_data
echostep $((++STEP))
start_mysql
echostep $((++STEP))
import_tablespace
echostep $((++STEP))
set_debug_0
echostep $((++STEP))
restart_mysql
echostep $((++STEP))
give_access

echo
echosuccess "Database \`$NEW_DB\` is ready to use."
echo

trap general_cleanup EXIT

If everything goes smoothly, you should see something like:

You can do something like the following:

mysqldump -u[username] -p[password] database_name_for_clone 
 | mysql -u[username] -p[password] new_database_name

A simple way to do so if you installed phpmyadmin:

Go to your database, select "operation" tab, and you can see the "copy database to" block. Use it and you can copy the database.

If you have triggers in your original database, you can avoid the "Trigger already exists" error by piping a replacement before the import:

mysqldump -u olddbuser -p -d olddbname | sed "s/`olddbname`./`newdbname`./" | mysql -u newdbuser -p -D newdbname

You could use (in pseudocode):

FOREACH tbl IN db_a:
    CREATE TABLE db_b.tbl LIKE db_a.tbl;
    INSERT INTO db_b.tbl SELECT * FROM db_a.tbl;

The reason I'm not using the CREATE TABLE ... SELECT ... syntax is to preserve indices. Of course this only copies tables. Views and procedures are not copied, although it can be done in the same manner.

See CREATE TABLE.