entityMap|blocks|key|dqkt3|text|首先创建重复数据库：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|c5ef5|CREATE+DATABASE+duplicateddb;|code-block|syntax|js|5sqcj|确保用户和权限都已就绪，并且：|8ndte|mysqldump+-u+admin+-p+originaldb+%7C+mysql+-u+backup+-pPassword+duplicateddb;^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

First create the duplicate database:

<pre><code>CREATE DATABASE duplicateddb;
</code></pre>

Make sure the user and permissions are all in place and:

<pre><code> mysqldump -u admin -p originaldb | mysql -u backup -pPassword duplicateddb; 
</code></pre>

entityMap|blocks|key|5hoo1|text|到远程服务器|type|unstyled|depth|inlineStyleRanges|entityRanges|data|536o8|mysqldump+mydbname+%7C+ssh+host2+"mysql+mydbcopy"|code-block|syntax|js|1dv3u|到本地服务器|eiua6|mysqldump+mydbname+%7C+mysql+mydbcopy^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

To remote server

<pre><code>mysqldump mydbname | ssh host2 "mysql mydbcopy"
</code></pre>

To local server

<pre><code>mysqldump mydbname | mysql mydbcopy
</code></pre>

entityMap|blocks|key|6mf7i|text|我有时会执行mysqldump，并将输出通过管道传输到另一个mysql命令中，以将其导入到另一个数据库中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|f5l1h|mysqldump+--add-drop-table+-u+wordpress+-p+wordpress+%7C+mysql+-u+wordpress+-p+wordpress_backup|code-block|syntax|js^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

I sometimes do a mysqldump and pipe the output into another mysql command to import it into a different database.

<pre><code>mysqldump --add-drop-table -u wordpress -p wordpress | mysql -u wordpress -p wordpress_backup
</code></pre>

entityMap|blocks|key|do4ha|text|在包含数据的系统中创建一个mysqldump文件，并使用管道将此mysqldump文件作为新系统的输入。可以使用ssh命令连接新系统。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6gclt|mysqldump+-u+user+-p'password'+db-name+%7C+ssh+user@some_far_place.com+mysql+-u+user+-p'password'+db-name|code-block|syntax|js|dhn9m|-p之间没有空格密码^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|L|9|@]|A|@]|B|$]]]]

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.

<pre><code>mysqldump -u user -p'password' db-name | ssh user@some_far_place.com mysql -u user -p'password' db-name
</code></pre>

no space between -p[password]

entityMap|blocks|key|6qs68|text|制作数据库的副本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4tm2g|#+mysqldump+-u+root+-p+password+db1+>+dump.sql
#+mysqladmin+-u+root+-p+password+create+db2
#+mysql+-u+root+-p+password+db2+<+dump.sql|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Making a Copy of a Database

<pre><code># mysqldump -u root -p password db1 &gt; dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 &lt; dump.sql
</code></pre>

entityMap|blocks|key|fej4|text|这是我写的一个windows+bat文件，其中结合了文森特和保罗的建议。它会提示用户输入源和目标名称。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|58f2v|只需修改顶部的变量，以设置到可执行文件/数据库端口的正确路径。|a31ad|::+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=%25MYSQL_HOME%25\mysqldump
@set+mysql_exec=%25MYSQL_HOME%25\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:+

%25MYSQL_HOME%25\mysql+--user=%25USERNAME%25+--password=%25PASSWORD%25+--port=%25DEST_PORT%25+--execute="show+databases;"
@IF+NOT+"%25ERRORLEVEL%25"+==+"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):++

%25mysql_exec%25+--user=%25USERNAME%25+--password=%25PASSWORD%25+--port=%25DEST_PORT%25+--execute="create+database+%25DEST_DB%25;"
%25mysqldump_exec%25+--add-drop-table+--user=%25USERNAME%25+--password=%25PASSWORD%25+--port=%25SRC_PORT%25+%25SRC_DB%25+%7C+%25mysql_exec%25+--user=%25USERNAME%25+--password=%25PASSWORD%25+--port=%25DEST_PORT%25+%25DEST_DB%25
@echo+SUCCESSFUL!!!
@GOTO+ExitSuccess

:ExitScript
@echo+"Failed+to+copy+database"
:ExitSuccess|code-block|syntax|javascript|4ooc6|示例输出：|coii3|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;"
%2B--------------------%2B
%7C+Database+++++++++++%7C
%2B--------------------%2B
%7C+information_schema+%7C
%7C+mysql++++++++++++++%7C
%7C+performance_schema+%7C
%7C+sugarcrm_550_pro+++%7C
%7C+sugarcrm_550_ce++++%7C
%7C+sugarcrm_640_pro+++%7C
%7C+sugarcrm_640_ce++++%7C
%2B--------------------%2B
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+++%7C+"C:\sugarcrm\mysql\bin"\mysql+--user=root+--password=MyPassword+--port=3308+sugarcrm_640_ce
SUCCESSFUL!!!^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|P|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|Q|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|R|9|@]|A|@]|B|$H|I]]]]

Here's a windows bat file I wrote which combines Vincent and Pauls suggestions. It prompts the user for source and destination names.

Just modify the variables at the top to set the proper paths to your executables / database ports.

<pre><code>:: 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
</code></pre>

<h2>Sample output:</h2>

<pre><code>C:\sugarcrm_backups\SCRIPTS&gt;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&gt;"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&gt;"C:\sugarcrm\mysql\bin"\mysql --user=root --password=MyPassword --port=3308 --execute="create database sugarcrm_640_ce;"

C:\sugarcrm_backups\SCRIPTS&gt;"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!!!
</code></pre>

entityMap|blocks|key|an93|text|这对InnoDB不起作用。仅当您尝试复制MyISAM数据库时才使用此解决方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8ac43|如果在备份期间锁定表，并且在数据库导入期间可能暂停MySQL是可以接受的，|brm5p|mysqlhotcopy|f1t7j|可能工作得更快。|i57k|例如。|cna92|备份：|6j5j3|#+mysqlhotcopy+-u+root+-p+password+db_name+/path/to/backup/directory|code-block|syntax|js|3dqsj|恢复：|a7tln|cp+/path/to/backup/directory/*+/var/lib/mysql/db_name|3hvoi|2sgrn|还可以通过SSH+(scp)传输文件，并且可能直接传输到重复数据库目录中。|bc68o|5kppp|#+mysqlhotcopy+-u+root+-p+password+db_name+/var/lib/mysql/duplicate_db_name^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|11|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|12|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|13|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|14|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|15|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|16|9|@]|A|@]|B|$]]|$2|M|4|N|6|O|8|17|9|@]|A|@]|B|$P|Q]]|$2|R|4|S|6|7|8|18|9|@]|A|@]|B|$]]|$2|T|4|U|6|O|8|19|9|@]|A|@]|B|$P|Q]]|$2|V|4|F|6|7|8|1A|9|@]|A|@]|B|$]]|$2|W|4|X|6|7|8|1B|9|@]|A|@]|B|$]]|$2|Y|4|J|6|7|8|1C|9|@]|A|@]|B|$]]|$2|Z|4|10|6|O|8|1D|9|@]|A|@]|B|$P|Q]]]]

This won't work for InnoDB.
Use this workaround only if you are trying to copy MyISAM databases.

If locking the tables during backup, and, possibly, pausing MySQL during the database import is acceptable, mysqlhotcopy may work faster.

<h2>E.g.</h2>

<h3>Backup:</h3>

<pre><code># mysqlhotcopy -u root -p password db_name /path/to/backup/directory
</code></pre>

<h3>Restore:</h3>

<pre><code>cp /path/to/backup/directory/* /var/lib/mysql/db_name
</code></pre>

mysqlhotcopy can also transfer files over SSH (scp), and, possibly, straight into the duplicate database directory.

<h2>E.g.</h2>

<pre><code># mysqlhotcopy -u root -p password db_name /var/lib/mysql/duplicate_db_name
</code></pre>

entityMap|blocks|key|f17cl|text|在mysql+shell之外的命令提示符下，这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|dopcb|#+mysqldump+-u+root+-p+password+db1+>+dump.sql
#+mysqladmin+-u+root+-p+password+create+db2
#+mysql+-u+root+-p+password+db2+<+dump.sql|code-block|syntax|javascript|8ncc1|这对我来说是最好的方式。如果压缩"dump.sql“，您可以将其作为压缩备份进行symply存储。酷！对于包含Innodb表的1+1GB数据库，创建"dump.sql“大约需要一分钟，将数据转储到新的DB+db2中大约需要三分钟。|5mb4i|直接复制孔db目录(mysql/data/db1)对我不起作用，我猜是因为InnoDB表的原因。^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|O|9|@]|A|@]|B|$]]]]

This worked for me with command prompt, from OUTSIDE mysql shell:

<pre><code># mysqldump -u root -p password db1 &gt; dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 &lt; dump.sql
</code></pre>

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. 

Straight copying the hole db directory (mysql/data/db1) didn't work for me, I guess because of the InnoDB tables.

entityMap|blocks|key|bk18a|text|对我来说，下面这几行代码做到了这一点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1vnej|mysqldump+--quote-names+-q+-u+username1+--password='password1'+originalDB+%7C+mysql+-u+username2+--password='password2'+duplicateDB|code-block|syntax|js^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

For me the following lines of code did the trick
<pre><code>mysqldump --quote-names -q -u username1 --password='password1' originalDB | mysql -u username2 --password='password2' duplicateDB
</code></pre>

entityMap|blocks|key|cktqt|text|曾几何时，在MySQL中，您只需将所有表文件复制到mysql树中的另一个目录中即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8ise2|mysql+cli+-创建数据库db2|5he37|linux+cli+-+cp+db1+db2^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|H|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|I|9|@]|A|@]|B|$]]]]

Once upon a time in MySQL you could just copy all the table files to another directory in the mysql tree

mysql cli - create database db2

linux cli - cp db1 db2

Is it posible to duplicate an entire MySQL database on a linux server?

I know I can use export and import but the original database is >25MB so that's not ideal.

Is it possible using mysqldump or by directly duplicates the database files?

Duplicate Entire MySQL Database

 在linux服务器上复制整个MySQL数据库是可能的吗？ 我知道我可以使用export和import，但是原始数据库大于25MB，所以这并不理想。 是否可以使用mysqldump或直接复制数据库文件？

问复制整个MySQL数据库
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问复制整个MySQL数据库EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问复制整个MySQL数据库
EN