在同一个 MySql 实例上克隆一个 MySQL 数据库

Cloning a MySQL database on the same MySql instance

提问by uclajatt

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

我想编写一个脚本，将我当前的数据库复制sitedb1到sitedb2同一个 mysql 数据库实例上。我知道我可以将 sitedb1 转储到 sql 脚本：

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?

然后将其导入到sitedb2. 有没有更简单的方法，无需将第一个数据库转储到 sql 文件？

回答by Greg

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

正如手册在复制数据库中所说，您可以将转储直接通过管道传输到 mysql 客户端：

mysqldump db_name | mysql new_db_name

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

如果您使用的是 MyISAM，您可以复制这些文件，但我不建议这样做。它有点狡猾。

Integrated from various good other answers

综合了各种其他好的答案

Both mysqldumpand mysqlcommands accept options for setting connection details (and much more), like:

双方mysqldump并mysql命令接受设置连接细节（等等），类似的选项：

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).

此外，如果新数据库尚不存在，您必须事先创建它（例如使用echo "create database new_db_name" | mysql -u <dbuser> -p）。

回答by Chriki

Using MySQL Utilities

使用 MySQL 实用程序

The MySQL Utilities contain the nice tool mysqldbcopywhich 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.

MySQL Utilities 包含一个很好的工具mysqldbcopy，它默认将一个数据库复制到一个数据库服务器上，其中包括所有相关对象（“表、视图、触发器、事件、过程、函数和数据库级授权”）和数据。数据库服务器。有很多选项可用于自定义实际复制的内容。

So, to answer the OP's question:

因此，要回答 OP 的问题：

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

回答by Peru

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

回答by Srikanth Gurram

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

例如： mysqldump -u root test_db1 | mysql -u root test_db2

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

这会将 test_db1 复制到 test_db2 并授予对 'root'@'localhost' 的访问权限

回答by Naseer-shaik

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

最好且简单的方法是在终端中输入这些命令并为 root 用户设置权限。对我有用..！

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

回答by Emil H

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.

我不使用 CREATE TABLE ... SELECT ... 语法的原因是为了保留索引。当然，这只复制表。视图和过程不会被复制，尽管可以以相同的方式完成。

See CREATE TABLE.

请参阅创建表。

回答by svg

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;

回答by Digambar Patil

You can do something like the following:

您可以执行以下操作：

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

回答by 0b10011

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

正如Greg 的回答中提到的，mysqldump db_name | mysql new_db_name是在数据库之间传输数据的免费、安全且简单的方法。但是，它也很慢。

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.

如果您要备份数据，无法承受丢失数据（在此数据库或其他数据库中），或者正在使用除 之外的表innodb，那么您应该使用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.

如果您正在寻找用于开发的东西，将所有数据库备份到其他地方，并且mysql在出现问题时可以轻松清除和重新安装（可能手动），那么我可能只是为您提供解决方案。

I couldn't find a good alternative, so I built a script to do it myself. I spent a lotof 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.

我找不到一个好的替代方案，所以我构建了一个脚本来自己做。我花了很多时间让它第一次工作，老实说，现在对它进行更改让我有点害怕。Innodb 数据库不应该像这样复制和粘贴。小的变化会导致这以​​惊人的方式失败。自从我完成代码以来，我没有遇到任何问题，但这并不意味着你不会。

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

• Ubuntu 16.04，默认 mysql、innodb，每表单独的文件
• Ubuntu 18.04，默认 mysql、innodb，每表单独的文件

What it does

它能做什么

1. Gets sudoprivilege 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

1. 获取sudo权限并验证您有足够的存储空间来克隆数据库
2. 获取root mysql权限
3. 创建一个以当前 git 分支命名的新数据库
4. 将结构克隆到新数据库
5. 切换到 innodb 的恢复模式
6. 删除新数据库中的默认数据
7. 停止 mysql
8. 将数据克隆到新数据库
9. 启动mysql
10. 在新数据库中链接导入的数据
11. 为 innodb 退出恢复模式
12. 重启mysql
13. 授予mysql用户访问数据库的权限
14. 清理临时文件

How it compares with mysqldump

与它相比如何 mysqldump

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

在 3GB 的数据库上，在我的机器上使用mysqldump和mysql需要 40-50 分钟。使用这种方法，同样的过程只需要大约 8 分钟。

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.

我们将 SQL 更改与代码一起保存，升级过程在生产和开发中都是自动化的，每组更改都会备份数据库以在出现错误时进行恢复。我们遇到的一个问题是，当我们在进行数据库更改的长期项目时，不得不在其中切换分支以修复一三个错误。

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 mysqldumpto 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.

我们尝试mysqldump为不同的分支复制数据库，但等待时间太长（40-50 分钟），在此期间我们无法做任何其他事情。

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

该解决方案将数据库克隆时间缩短到 1/5 的时间（想想喝咖啡和洗手间而不是长时间的午餐）。

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 mysqldumpor this code. This code just happens to be ~5 times faster than mysqldump.

在具有不兼容数据库更改的分支之间切换在单个数据库上需要 50 分钟以上，但在使用mysqldump或此代码的初始设置时间之后根本不需要时间。这段代码恰好比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

• 单个数据库：~5 分钟
• 克隆时间mysqldump：50-60 分钟
• 使用此代码克隆：~18 分钟

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

使用数据库更改创建功能分支，切换到错误master修复，在功能分支上进行编辑，然后合并：

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

• 单个数据库：~60 分钟
• 克隆时间mysqldump：50-60 分钟
• 使用此代码克隆：~18 分钟

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

使用数据库更改创建功能分支，切换到master修复错误 5 次，同时对中间的功能分支进行编辑，然后合并：

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

• 单个数据库：~4 小时 40 分钟
• 克隆时间mysqldump：50-60 分钟
• 使用此代码克隆：~18 分钟

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)" ""
    sleep .1
}
echowarn () {
    printf "\e[0;33m %s: %s\e[0m\n" "$(now)" ""
    sleep .1
}
echoerror () {
    printf "\e[0;31m %s: %s\e[0m\n" "$(now)" ""
    sleep .1
}
echonotice () {
    printf "\e[0;94m %s: %s\e[0m\n" "$(now)" ""
    sleep .1
}
echoinstructions () {
    printf "\e[0;104m %s: %s\e[0m\n" "$(now)" ""
    sleep .1
}
echostep () {
    printf "\e[0;90mStep %s of 13:\e[0m\n" ""
    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" ""
}
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:

如果一切顺利，您应该会看到如下内容：

回答by ch271828n

A simple way to do so if you installed phpmyadmin:

如果您安装了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.

转到您的数据库，选择“操作”选项卡，您可以看到“将数据库复制到”块。使用它，您可以复制数据库。