MySQL: Cloning a MySQL database on the same MySql instance

Question

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?

Loyalty

Possible duplicate of Clone MySQL database – Cernuous

You can also use phpMyAdmin! Just open http: // localhost/phpmyadmin/ in your browser, where your database instance is located. After phpMyAdmin has started, mark the schema you are interested in, switch to the "Operations" tab and look for the "Copy database to" section. There you choose some new name und click the Ok button. And that was it! – Yuu

Answer 1

As the manual says in Copying Databases you can pipe the dump directly into the mysql client:

mysqldump db_name | mysql new_db_name

If you're using MyISAM you could copy the files, but I wouldn't recommend it. It's a bit dodgy.

Integrated from various good other answers

Both mysqldump and mysql commands accept options for setting connection details (and much more), like:

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

Also, if the new database is not existing yet, you have to create it beforehand (e.g. with echo "create database new_db_name" | mysql -u <dbuser> -p).

Yuzik

That is basically the same thing as dumping it to an external file. – Isochromatic

Kinda... it skips a lot of disk IO though as you don't have to read/write the data twice – Yuzik

If your database is gigabytes in size this probably won't gain you much. I think whta the OP is getting at is they don't want to externalize the copy: can it be done purely within mysql? – Isochromatic

I'd say the bigger the DB the more it gains you... There's no way to do this within MySQL afaik (except by hand, one table / view at a time) – Yuzik

I first had to create new_db by using standard mysql command: "CREATE DATABASE new_db;" and then used these commands: mysqldump -u root -p old_db | mysql -u root -p new_db – Unboned

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;" – Agiotage

If you don't create the database first by sql command, you get an error like: ERROR 1049 (42000): Unknown database 'new_db_name'. You need to check the comment by @Unboned – Sideling

the --routines=true on the original mysqldump could also be useful to copy stored_procedures and functions – Bromate

When executed as root doesnt require any password! – Buyse

Don't use it if in tables you are using codepage different then local codepage -> all "names" will become gibberish. – Spradling

This was so helpful! For me, using Laravel Homestead, this worked: echo "create database homestead_testing" | mysql, then mysqldump homestead | mysql homestead_testing – Tarboosh

This doesn't work for me, if I have to put in the password for dumping and importing like this: mysqldump -uroot -p database1 | mysql -uroot -p database2. I get prompted for both pws but can only put in one. The prompt looks like this: Enter password: Enter password: . After giving the first pw, the process waits forever. – Postpone

if i run, i got this error. unknown variable 'innodb_autoinc_lock_mode=2'. please help. – Highoctane

@Postpone If you can, enter --password='myPassword'. (Obviously not if you're putting this into a script file or something, though.) – Accuracy

@CameronHudson I wouldn't want to do this on the shell itself as well, since I don't want the password to be saved in history. – Postpone

due to permission issues, I have to delete some lines in the dumped sql file and them reimport – Embosom

@Postpone that's because you don't use mysql or mysqldump 'p' flag like that. So it's asking you for the password for both. Read the docs, for -p you don't put a space between the flag and the password. For example -pfoobar – Pancreas

Worked for me when I used it as author described --password=password – Neill

Typing the password in the terminal is not secure. You should either run it from a script, or clear the history afterwards (.bash_history or .zsh_history) – Wanting

@Postpone It work for me, just had to enter the password two times consecutively – Westphal

The create db line that is suppose to be run before wasn't working for me so I had to change the command to this: mysql -u root -p -e 'CREATE DATABASE new_db;' – Misspell

For bonus points you can pipe it through pv in the middle and get some progress information. Ex `mysqldump -u root -p[password] <database> | pv | mysql -u -p – Fallen

@Wanting It's not secure for multiple logons either, or any monitor that exposes the processes - but 99.999% of the time there is a single admin. Nowadays it is rare for users to be created over and above the cloud ubuntu and EC2-user. So that's a classroom argument. history -d can deal with the trail. Also see login-path or supplementary confing (MariaDB). – Illuminant

What about copying the users? I don't think that would work in this case – Afford

Answer 2

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

Phippen

This worked fine for me, mysqldump based solution was failing. – Booklet

In my case I had to specify the port like this: --source=root:your_password@localhost:3307 (otherwise it would give me an access denied error) – Skidway

Need to sudo apt-get install mysql-utilities, but this is very neat. Can I leave out the password and be prompted to enter it? – Jalapa

@Jalapa I don’t know if there is a built-in way to let mysqldbcopy ask you for the password; at least I couldn’t find anything like that in the documentation. You could build this functionality yourself, though. In Bash that could look somewhat like this: mysqldbcopy --source=root:"$(read -sp 'Source password: ' && echo $REPLY)"@localhost --destination=root:"$(read -sp 'Destination password: ' && echo $REPLY)"@localhost sitedb1:sitedb2 – Phippen

Wow, that is super neat! I didn't know passwords could be read like that. But I will try leaving out the password and see what happens. – Jalapa

can't do it: mysqldbcopy: error: Source connection values invalid: .mylogin.cnf was not found at is default location: /home/afigueiredo.Please configure your login-path data before using it (use the mysql_config_editor tool).. – Pear

FYI: It seems like Chriki's command works flawlessly. I just had to add --force to the mysqldbcopy command because I had already created the destination database. Thanks! – Lakesha

Sounds like a nice utility, particularly for master/slave initiation. However, as of writing (apr 2017), there is a weird bug making the db copy failing as soon as you have a single quote in your data bugs.mysql.com/bug.php?id=72951 – Cloth

On Windows this is way slower than mysqldump to mysql pipe – Joyajoyan

It is also slower on linux. mysqldbcopy: 40secs myqsldump | mysql: 5secs – Hyalite

@PeterV.Mørch if your database is small enough that it can be copied in 5 seconds through piping dump to mysql then it isn't a good benchmark. Try it with a database of significant size, 10GB or so at least. – Pancreas

@Shadj: Whether this is a good benchmark or could depends on the use-case. In our case copying a database was as a starting point for integration tests and hence done very many times. – Hyalite

There is one drawback to this solution: Cloning databases that contain foreign key constraints does not change the constraint inthe cloned table. For example, if table db1.t1 has a foreign key constraint on table db1.t2, when db1 is cloned to db2, table db2.t1 will have a foreign key constraint on db1.t2. – Biparietal

Back in the day copying the db folders used to do the trick – Osullivan

why didn't i try this? going to try this next time... – Hartmunn

Answer 3

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

Gyrostatics

The question explicitly stated that export/import method is already known. – Conserve

This is the best way of doing it. Also works with large databases, whereas the piped version mysqldump -u <user> -p <pwd> db_name | mysql -u <user> -p <pwd> new_db_name can be problematic with large databases. – Crossness

This is the best answer. Just be sure to grant required user privileges to the newly created database based on your requirements. – Mandibular

Answer 4

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

Kermie

What does it adds to the accepted answer? Is similar, but you add some differences, add some comments for better understanding – Ferrule

This should be the accepted answer, as it will create the database, also good for auth. the current accepted answer will tell you access denied, then table does not exist. – Devastation

Answer 5

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.

Connecticut

This could fail on reference integrity as dependent tables could not be copied yet. Maybe it could work in one big transaction. – Panegyric

@OndrejGalbavý would it work if i do SET FOREIGN_KEY_CHECKS = 0 ? – Dennett

CREATE TABLE should not be in FOREACH loop. This should be - CREATE TABLE; FOREACH: INSERT; – Coulson

Answer 6

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'

Agiotage

I like this answer, it's crisp. However, for me mysql required -p before the password. – Regretful

How can we also copy functions, events etc. created in the original database? This looks only copies tables. – Hatti

Answer 7

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.

Trash

Answer 8

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;

Mauretta

Answer 9

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

We've since switched to docker and a simple copy of the entire mysql data folder, so this script is no longer maintained. Leaving it in case it's able to help anyone in the future.

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 used it

We had 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 main 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 main 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. It is no longer maintained, so it is more and more likely to be broken as time goes on.

#!/bin/bash
set -e

# This script taken from: https://mcmap.net/q/109173/-mysql-cloning-a-mysql-database-on-the-same-mysql-instance

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:

Unkempt

Answer 10

You can do something like the following:

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

Megargee

Answer 11

You can do:

CREATE DATABASE copy_of_db;
create table copy_of_db.table LIKE source_db.table;

If you want to copy data too:
INSERT INTO copy_of_db.table SELECT * FROM source_db.table;

Repeat for all tables, functions, procedures, etc

(mysqldump is the proper way, but this is a quick and dirty solution useful in many cases)

Leckie

Thats a lot of work! – Bayer

Answer 12

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

Conjuration

Answer 13

Using Mydumper

sudo apt install mydumper

Generate dump

mydumper --user=YOUR_USER --password=YOUR_PASSWORD -B YOUR_CURRENT_DB \ 
         --triggers --routines --events --outputdir YOUR_OUTPUT_DIR

Load dump

myloader --user=YOUR_USER --password=YOUR_PASSWORD --database=YOUR_NEW_DB \ 
         --directory=YOUR_OUTPUT_DIR

Ghat

Answer 14

Using MySQL Workbench you can use Database > Migration Wizard to copy database to the same or to the other server instance. I believe it works server-side so it should be a good solution for duplicating large databases.

Buskirk

all the migrations are created with the same schema name so you can't easily copy them to a new DB – Lynettalynette

Answer 15

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

Justin

Answer 16

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

Seabrooke