MySQL 将Mysql数据库转移到另一台计算机

Transfer Mysql database to another computer

提问by fmsf

I have a mysqldatabase filled up and running on a Windowscomputer, is there any tool to transfer the database to another computer (running Ubuntu)?

我mysql在Windows计算机上填充并运行了一个数据库，是否有任何工具可以将数据库传输到另一台计算机（运行Ubuntu）？

Else I'll just write a scriptto take all the data base into SQLand insertit on the other computer. Just trying to save some time :)

否则，我将编写一个script将所有数据库放入SQL并将其插入另一台计算机的文件。只是想节省一些时间:)

Thank you all.

谢谢你们。

回答by benlumley

The tool you speak of already exists: mysqldump

你说的工具已经存在：mysqldump

It dumps out to sql, which you can then copy to another machine and re-load.

它转储到 sql，然后您可以将其复制到另一台机器并重新加载。

eg:

例如：

on source:

来源：

mysqldump -u username -p databasename > dumpfile.sql

Then use ftp/rsync/whatever to move the file to the destination machine, and on there, create an empty database to import into and run:

然后使用 ftp/rsync/whatever 将文件移动到目标机器，并在那里创建一个空数据库以导入并运行：

mysql -u username -p databasename < dumpfile.sql

You'll also need to set up permissions on any users that may have been transferred as well, as they aren't held within the database.

您还需要为可能已转移的任何用户设置权限，因为它们不在数据库中。

Alternatively, you can copy the files from the mysql data dir - but mysqldump is the easiest/most reliable way.

或者，您可以从 mysql 数据目录复制文件 - 但 mysqldump 是最简单/最可靠的方法。

Worth noting that the table names may become case sensitive on one system when they weren't on the original. It depends on the config at both ends - in particular the case sensitivity (or otherwise) of the filesystem.

值得注意的是，当表名不在原始系统上时，它们在一个系统上可能会区分大小写。这取决于两端的配置 - 特别是文件系统的区分大小写（或其他）。

回答by Sanjay Zalke

Rather than exporting the databases one by one, you can export them all with a single command. Then import them. eg.

您可以使用单个命令将它们全部导出，而不是一个一个地导出数据库。然后导入它们。例如。

mysqldump -u username -p --all-databases > c:\alldbs.sql

PS- The biggest advantage is that you do not lose the user privileges.

PS-最大的好处是你不会失去用户权限。

回答by staticsan

The on-disk files are 100% compatible between all editions of MySQL. You just have to watch out for the case of the filenames because it matters on Unix, whilst it only sometimes does on Windows.

磁盘上的文件在 MySQL 的所有版本之间 100% 兼容。你只需要注意文件名的情况，因为它在 Unix 上很重要，而它只是有时在 Windows 上。

And remember to stop MySQL before you take a copy. MyISAM files are okay to take a copy whilst running, but InnoDB files are not really safe to do that and Windows' MySQL defaults to InnoDB files.

并记住在复制之前停止 MySQL。MyISAM 文件在运行时可以复制，但 InnoDB 文件这样做并不安全，Windows 的 MySQL 默认为 InnoDB 文件。

回答by Ariel Arjona

Be careful with character sets when using mysqldump, especially on windows. I prefer to explicitly set the charset my db uses and use the --result-file=file instead of using the > operator lest something becomes mangled.

使用 mysqldump 时要小心字符集，尤其是在 Windows 上。我更喜欢显式设置我的数据库使用的字符集并使用 --result-file=file 而不是使用 > 运算符，以免某些东西被破坏。

回答by Petre Lukarov

From the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/copying-databases.html

来自 MySQL 文档：http: //dev.mysql.com/doc/refman/5.0/en/copying-databases.html

shell> mysqldump --quick db_name | gzip > db_name.gz

Transfer the file containing the database contents to the target machine and run these commands there:

将包含数据库内容的文件传输到目标机器并在那里运行这些命令：

shell> mysqladmin create db_name
shell> gunzip < db_name.gz | mysql db_name

回答by MarkR

mysqldump should dump it out correctly and preserve the character set of anything in the database. It is advisable to move to the same version of mysql if you don't want problems.

mysqldump 应该正确地将其转储出来并保留数据库中任何内容的字符集。如果不想出问题，建议迁移到相同版本的mysql。

The file produced by mysqldump is NOT a text file, despite appearances, don't edit it with notepad.exe etc or you'll end up in trouble.

mysqldump 生成的文件不是文本文件，尽管看起来如此，请不要使用 notepad.exe 等对其进行编辑，否则您最终会遇到麻烦。

Dumps produced by third party gui tools, especially phpmyadmin, are generally inaccurate and won't necessarily restore correctly.

第三方 gui 工具（尤其是 phpmyadmin）生成的转储通常不准确，并且不一定会正确恢复。

回答by AdamK

I often find myself using some variation of this one-liner to copy a database over the network.

我经常发现自己使用这种单行的一些变体来通过网络复制数据库。

mysqldump --opt --compress --user=username database | mysql --user=username2 --password=p2 --host=hostB -D database -C database

Which I originally read about here:

我最初在这里读到的：

http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/

http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/

回答by zhongxiao37

With new mysql workbench (gui tool), you can easily export the database and import it to the database you want to migrate.

使用新的mysql工作台（gui工具），可以轻松导出数据库，导入到要迁移的数据库中。

回答by Gonzalo Quero

You can make a backup using any gui tool, like Mysql Administrator (http://dev.mysql.com/downloads/gui-tools/on Windows, aptitude install mysql-admin on Ubuntu) or phpmyadmin (http://www.phpmyadmin.net/home_page/index.phpon Windows, aptitude install phpmyadmin on Ubuntu), and then recover it in the other computer.

您可以使用任何 gui 工具进行备份，例如 Mysql Administrator（Windows 上的http://dev.mysql.com/downloads/gui-tools/，Ubuntu上的 aptitude install mysql-admin）或 phpmyadmin（http://www.Windows上phpmyadmin.net/home_page/index.php，Ubuntu上aptitude安装phpmyadmin），然后在另一台电脑上恢复。