我想写一个脚本，复制我的当前数据库sitedb1到sitedb2在相同的mysql数据库实例。我知道我可以转储sitedb1到一个sql脚本:
mysqldump -u root -p sitedb1 >~/db_name.sql
然后将它导入sitedb2。 有没有更简单的方法，不用将第一个数据库转储到sql文件?
当前回答
除了Greg的回答，如果new_db_name还不存在，这是最简单和最快的方法:
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
其他回答
正如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特权并验证是否有足够的存储空间来克隆数据库 获得mysql root权限 创建一个以当前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进行错误修复，在特征分支上进行编辑，并合并:
单个数据库:~60分钟 用mysqldump克隆:50-60分钟 用这个代码克隆:~18分钟
创建带有数据库更改的特征分支，切换到main进行5次错误修复，同时在特征分支上进行编辑，并合并:
单个数据库:约4小时40分钟 用mysqldump克隆:50-60分钟 用这个代码克隆:~18分钟
的代码
除非你已经阅读并理解了上面的所有内容，否则不要使用它。它不再被维护，所以随着时间的推移，它越来越有可能被打破。
#!/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
如果一切顺利，你应该会看到如下内容:
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
使用MySQL Workbench，您可以使用数据库>迁移向导将数据库复制到相同或其他服务器实例。我相信它可以在服务器端工作，因此它应该是复制大型数据库的一个很好的解决方案。
除了Greg的回答，如果new_db_name还不存在，这是最简单和最快的方法:
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
使用MySQL实用工具
MySQL实用程序中包含了mysqldbcopy工具，默认情况下，它可以复制一个数据库，包括所有相关对象(“表、视图、触发器、事件、过程、函数和数据库级授权”)和数据，从一个DB服务器到同一个或另一个DB服务器。有许多选项可用于自定义实际复制的内容。
所以，为了回答OP的问题:
mysqldbcopy \
--source=root:your_password@localhost \
--destination=root:your_password@localhost \
sitedb1:sitedb2
推荐文章
- 如何关闭mysql密码验证?
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql，而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 从现有模式生成表关系图(SQL Server)
- HyperLogLog算法是如何工作的?
- 如何复制在bash所有目录和文件递归?
- 数据库和模式的区别
- 如何从命令行通过mysql运行一个查询?
- 如何从命令行在windows中找到mysql数据目录
- 如何找到MySQL的根密码
- 如何改变字符集(和排序)在整个数据库?
- mySQL:: insert到表，数据从另一个表?
