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

Question

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

254

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

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

然后将其导入到sitedb2中。是否有更简单的方法，而不需要将第一个数据库转储到sql文件中？

- uclajatt

可能是克隆MySQL数据库的重复问题。 - bummi

您还可以使用phpMyAdmin！只需在浏览器中打开http：// localhost / phpmyadmin /，其中包含您的数据库实例。启动phpMyAdmin后，标记您感兴趣的模式，切换到“操作”选项卡，然后查找“复制数据库到”部分。在那里，您选择一些新名称并单击确定按钮。就是这样！ - peter70

16个回答

93

使用MySQL工具

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

因此，为了回答原贴的问题:

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

- Chriki

1

这对我来说很有效，基于mysqldump的解决方案失败了。 - saji89

1

在我的情况下，我必须像这样指定端口：--source=root:your_password@localhost:3307（否则它会给我一个访问被拒绝的错误）。 - pbz

7

需要执行sudo apt-get install mysql-utilities命令，但是这样会直接输入密码，我能否不输入密码并在提示后再输入？ - ADTC

2

@ADTC 我不知道是否有内置的方法让 mysqldbcopy 询问您的密码；至少我在文档中找不到类似的内容。不过，您可以自己构建这个功能。在 Bash 中，它可能看起来像这样：

mysqldbcopy --source=root:"$(read -sp 'Source password: ' && echo $REPLY)"@localhost --destination=root:"$(read -sp 'Destination password: ' && echo $REPLY)"@localhost sitedb1:sitedb2

- Chriki

2

FYI：看起来 Chriki 的命令运行得非常完美。我只需要在 mysqldbcopy 命令中添加 --force，因为我已经创建了目标数据库。谢谢！ - sylbru

显示剩余10条评论

38

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

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

- Dry_accountant_09

3

问题明确说明了出口/进口方法已经知道。 - lav

4

这是最佳的做法。使用这种方法可以处理大型数据库，而管道版本的 mysqldump -u <user> -p <pwd> db_name | mysql -u <user> -p <pwd> new_db_name 在处理大型数据库时可能会出现问题。 - Alex

1

这是最好的答案。只需根据您的要求授予新创建的数据库所需的用户权限即可。 - Promise Preston

20

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

- Peru

3

它对已接受的答案有什么补充？类似，但你添加了一些区别，并增加了一些注释以便更好地理解。 - Yaroslav

这应该是被接受的答案，因为它将创建数据库，也适用于身份验证。当前被接受的答案会告诉您访问被拒绝，然后表不存在。 - Rami Dabain

17

你可以使用以下伪代码：

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

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

请参见CREATE TABLE。

- Emil H

4

由于依赖表还未复制，所以参照完整性可能会失败。也许可以在一个大事务中实现。 - Ondrej Galbavý

@OndrejGalbavý 如果我执行 SET FOREIGN_KEY_CHECKS = 0，它会起作用吗？ - Eboubaker

在FOREACH循环中不应该使用CREATE TABLE。应该是这样的 - CREATE TABLE; FOREACH: INSERT; - undefined

14

你需要从终端/命令提示符中运行该命令。

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

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

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

- Srikanth Gurram

我喜欢这个回答，很简洁。然而，对我来说，在 mysql 密码前需要加上 -p。 - lwitzel

2

我们如何复制原始数据库中创建的函数、事件等内容？目前看起来只能复制表格。 - Dogan Askan

10

如果您已经安装了phpMyAdmin，可以使用以下简单方法：

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

- ch271828n

8

首先创建一个副本数据库：

CREATE DATABASE duplicateddb;

请确保所有权限等都已就位：

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

- svg

6

如 Greg 的回答中所述，mysqldump db_name | mysql new_db_name 是在数据库之间传输数据的免费、安全和简单方法。但是，它也非常慢。 如果您正在寻找备份数据，不能承受丢失数据（在此或其他数据库中），或者正在使用除 innodb 之外的表，则应使用mysqldump 。 如果你正在寻找用于开发的东西，在其他地方备份了所有数据库，并且当出现问题时可以放心地清除并重新安装mysql（可能需要手动操作），那么我有可能为您提供解决方案。

我找不到一个好的替代方案，所以我自己构建了一个脚本来执行。我花了很多时间第一次让它工作，现在对于对它进行更改并使之运行起来的想法令我直觉上感到害怕。Innodb 数据库并不意味着可以像这样复制和粘贴。小的更改会导致失败。自从我完成代码以来，我就没有遇到过问题，但这并不意味着您也不会出现问题。

已测试的系统（但仍可能失败）：

Ubuntu 16.04，默认mysql，innodb，每个表单独文件
Ubuntu 18.04，默认mysql，innodb，每个表单独文件

自那时以来，我们已经切换到 Docker 和一个简单的整个 mysql 数据文件夹的复制，因此不再维护此脚本。留下它以防它能帮助未来的任何人。

它是如何运作的

获取sudo 特权并验证您是否具有足够的存储空间来克隆数据库
获取 root mysql 特权
创建一个以当前 git 分支命名的新数据库
将结构克隆到新数据库
切换到 innodb 的恢复模式
删除新数据库中的默认数据
停止 mysql
将数据克隆到新数据库
启动 mysql
在新数据库中链接导入的数据
退出 innodb 恢复模式
重新启动 mysql
授予 mysql 用户对数据库的访问权限
清理临时文件

它与`mysqldump` 比较如何

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

我们是如何使用它的

我们将SQL更改与代码保存在一起，并在生产和开发环境中自动执行升级过程，每组更改都会备份数据库以便在出现错误时进行恢复。我们遇到的一个问题是，在进行具有数据库更改的长期项目时，我们必须在其中间切换分支以修复一个或多个错误。

过去，我们对所有分支使用单个数据库，每当切换到不兼容新数据库更改的分支时，就必须重新构建数据库。而当我们切换回去时，就必须再次运行升级。

我们尝试使用mysqldump来为不同的分支复制数据库，但等待时间太长（40-50分钟），而且在此期间我们什么也不能做。

这种解决方案将数据库克隆时间缩短了1/5的时间（可以喝口咖啡或上个洗手间而不是吃长午餐）。

常见任务及其所需时间

在单个数据库中切换具有不兼容数据库更改的分支需要50多分钟，但在使用mysqldump或此代码进行初始设置后，根本不需要时间。这段代码恰好比mysqldump快约5倍。

以下是一些常见任务及其大致所需时间：

创建具有数据库更改的功能分支并立即合并： - 单个数据库：约5分钟 - 使用mysqldump克隆：50-60分钟 - 使用此代码克隆：约18分钟

创建具有数据库更改的功能分支，切换到main分支进行bugfix，对功能分支进行编辑，然后合并： - 单个数据库：约60分钟 - 使用mysqldump克隆：50-60分钟 - 使用此代码克隆：约18分钟

创建具有数据库更改的功能分支，5次切换到main分支进行bugfix，并在其间编辑功能分支，然后合并： - 单个数据库：约4小时40分钟 - 使用mysqldump克隆：50-60分钟 - 使用此代码克隆：约18分钟

该代码请在完全阅读和理解上述内容之后再使用。它已不再维护，因此随着时间的推移，它越来越可能会出现故障。

#!/bin/bash
set -e

# This script taken from: https://dev59.com/V3RB5IYBdhLWcg3wQVWV#57528198

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

如果一切顺利，您应该能看到类似于以下内容的东西：

- 0b10011

3

您可以尝试以下操作：

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

- Digambar Patil

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RoBorg · Accepted Answer

如MySQL的文档在复制数据库中所述，您可以将转储文件直接导入到mysql客户端：

mysqldump db_name | mysql new_db_name

如果您使用的是MyISAM，可能可以复制文件，但我不建议这样做。这有点冒险。

整合自其他优秀答案

mysqldump和mysql命令都接受用于设置连接细节（以及更多内容）的选项，例如：

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

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

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

使用MySQL工具

它是如何运作的

它与mysqldump 比较如何

我们是如何使用它的

它与`mysqldump` 比较如何