Linux 复制整个 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1887964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Duplicate Entire MySQL Database
提问by Adam Dempsey
Is it posible to duplicate an entire MySQL database on a linux server?
是否可以在 Linux 服务器上复制整个 MySQL 数据库？
I know I can use export and import but the original database is >25MB so that's not ideal.
我知道我可以使用导出和导入，但原始数据库大于 25MB，所以这并不理想。
Is it possible using mysqldump or by directly duplicates the database files?
是否可以使用 mysqldump 或直接复制数据库文件？
采纳答案by Vincent Ramdhanie
First create the duplicate database:
首先创建复制数据库：
CREATE DATABASE duplicateddb;
Make sure the user and permissions are all in place and:
确保用户和权限都已到位，并且：
mysqldump -u admin -p originaldb | mysql -u backup -pPassword duplicateddb;
回答by Paul Tomblin
I sometimes do a mysqldump and pipe the output into another mysql command to import it into a different database.
我有时会执行 mysqldump 并将输出通过管道传输到另一个 mysql 命令以将其导入不同的数据库。
mysqldump --add-drop-table -u wordpress -p wordpress | mysql -u wordpress -p wordpress_backup
回答by Peter Lindqvist
To remote server
到远程服务器
mysqldump mydbname | ssh host2 "mysql mydbcopy"
To local server
到本地服务器
mysqldump mydbname | mysql mydbcopy
回答by blak3r
Here's a windows bat file I wrote which combines Vincent and Pauls suggestions. It prompts the user for source and destination names.
这是我写的一个 windows bat 文件，它结合了 Vincent 和 Pauls 的建议。它会提示用户输入源名称和目标名称。
Just modify the variables at the top to set the proper paths to your executables / database ports.
只需修改顶部的变量即可为您的可执行文件/数据库端口设置正确的路径。
:: Creates a copy of a database with a different name.
:: User is prompted for Src and destination name.
:: Fair Warning: passwords are passed in on the cmd line, modify the script with -p instead if security is an issue.
:: Uncomment the rem'd out lines if you want script to prompt for database username, password, etc.
:: See also: http://stackoverflow.com/questions/1887964/duplicate-entire-mysql-database
@set MYSQL_HOME="C:\sugarcrm\mysql\bin"
@set mysqldump_exec=%MYSQL_HOME%\mysqldump
@set mysql_exec=%MYSQL_HOME%\mysql
@set SRC_PORT=3306
@set DEST_PORT=3306
@set USERNAME=TODO_USERNAME
@set PASSWORD=TODO_PASSWORD
:: COMMENT any of the 4 lines below if you don't want to be prompted for these each time and use defaults above.
@SET /p USERNAME=Enter database username:
@SET /p PASSWORD=Enter database password:
@SET /p SRC_PORT=Enter SRC database port (usually 3306):
@SET /p DEST_PORT=Enter DEST database port:
%MYSQL_HOME%\mysql --user=%USERNAME% --password=%PASSWORD% --port=%DEST_PORT% --execute="show databases;"
@IF NOT "%ERRORLEVEL%" == "0" GOTO ExitScript
@SET /p SRC_DB=What is the name of the SRC Database:
@SET /p DEST_DB=What is the name for the destination database (that will be created):
%mysql_exec% --user=%USERNAME% --password=%PASSWORD% --port=%DEST_PORT% --execute="create database %DEST_DB%;"
%mysqldump_exec% --add-drop-table --user=%USERNAME% --password=%PASSWORD% --port=%SRC_PORT% %SRC_DB% | %mysql_exec% --user=%USERNAME% --password=%PASSWORD% --port=%DEST_PORT% %DEST_DB%
@echo SUCCESSFUL!!!
@GOTO ExitSuccess
:ExitScript
@echo "Failed to copy database"
:ExitSuccess
Sample output:
示例输出：
C:\sugarcrm_backups\SCRIPTS>copy_db.bat
Enter database username: root
Enter database password: MyPassword
Enter SRC database port (usually 3306): 3308
Enter DEST database port: 3308
C:\sugarcrm_backups\SCRIPTS>"C:\sugarcrm\mysql\bin"\mysql --user=root --password=MyPassword --port=3308 --execute="show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sugarcrm_550_pro |
| sugarcrm_550_ce |
| sugarcrm_640_pro |
| sugarcrm_640_ce |
+--------------------+
What is the name of the SRC Database: sugarcrm
What is the name for the destination database (that will be created): sugarcrm_640_ce
C:\sugarcrm_backups\SCRIPTS>"C:\sugarcrm\mysql\bin"\mysql --user=root --password=MyPassword --port=3308 --execute="create database sugarcrm_640_ce;"
C:\sugarcrm_backups\SCRIPTS>"C:\sugarcrm\mysql\bin"\mysqldump --add-drop-table --user=root --password=MyPassword --port=3308 sugarcrm | "C:\sugarcrm\mysql\bin"\mysql --user=root --password=MyPassword --port=3308 sugarcrm_640_ce
SUCCESSFUL!!!
回答by Pavel
This won't work for InnoDB. Use this workaround only if you are trying to copy MyISAM databases.
这不适用于 InnoDB。仅当您尝试复制 MyISAM 数据库时才使用此解决方法。
If locking the tables during backup, and, possibly, pausing MySQL during the database import is acceptable, mysqlhotcopymay work faster.
如果在备份期间锁定表，并且可能在数据库导入期间暂停 MySQL 是可以接受的，那么mysqlhotcopy可能会更快地工作。
E.g.
例如
Backup:
备份：
# mysqlhotcopy -u root -p password db_name /path/to/backup/directory
Restore:
恢复：
cp /path/to/backup/directory/* /var/lib/mysql/db_name
mysqlhotcopycan also transfer files over SSH (scp), and, possibly, straight into the duplicate database directory.
mysqlhotcopy还可以通过 SSH (scp) 传输文件，并且可能直接传输到重复的数据库目录中。
E.g.
例如
# mysqlhotcopy -u root -p password db_name /var/lib/mysql/duplicate_db_name
回答by TheNormalGuy
Create a mysqldump file in the system which has the datas and use pipe to give this mysqldump file as an input to the new system. The new system can be connected using ssh command.
在包含数据的系统中创建一个 mysqldump 文件，并使用管道将此 mysqldump 文件作为新系统的输入。可以使用 ssh 命令连接新系统。
mysqldump -u user -p'password' db-name | ssh user@some_far_place.com mysql -u user -p'password' db-name
no space between -p[password]
-p[密码] 之间没有空格
回答by Maulik patel
Making a Copy of a Database
制作数据库的副本
# mysqldump -u root -p password db1 > dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 < dump.sql
回答by DrOne
This worked for me with command prompt, from OUTSIDE mysql shell:
这对我的命令提示符有用，来自 OUTSIDE mysql shell：
# mysqldump -u root -p password db1 > dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 < dump.sql
This looks for me the best way. If zipping "dump.sql" you can symply store it as a compressed backup. Cool! For a 1GB database with Innodb tables, about a minute to create "dump.sql", and about three minutes to dump data into the new DB db2.
这对我来说是最好的方式。如果压缩“dump.sql”，您可以将其存储为压缩备份。凉爽的！对于包含 Innodb 表的 1GB 数据库，创建“dump.sql”大约需要一分钟，将数据转储到新的 DB db2 大约需要三分钟。
Straight copying the hole db directory (mysql/data/db1) didn't work for me, I guess because of the InnoDB tables.
直接复制孔 db 目录（mysql/data/db1）对我不起作用，我猜是因为 InnoDB 表。
回答by D.M.
Once upon a time in MySQL you could just copy all the table files to another directory in the mysql tree
从前在 MySQL 中，您可以将所有表文件复制到 mysql 树中的另一个目录
mysql cli - create database db2
mysql cli - 创建数据库 db2
linux cli - cp db1 db2
linux cli - cp db1 db2
