最简单的方法来复制MySQL数据库？ [英] Easiest way to copy a MySQL database?

问题描述

有没有人知道将数据库从一台计算机复制到一个文件，然后将其导入另一台计算机的简单方法？

解决方案

以下是几个选项：

mysqldump

，保证工作方式就是使用 mysqldump 。请参阅此实用程序的手册页：

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

基本上转储重建数据库内容所需的SQL脚本，包括创建表，触发器和其他对象以及插入数据（它们都是可配置的，因此如果已经在其他位置设置了模式，则只需转储

如果您在有大量的数据和您正在使用MyISAM存储引擎为您要复制的表，您可以关闭mysqld并复制.frm，.myd和.myi文件一个数据库文件夹到另一个（甚至在另一个系统上）。这将不适用于InnoDB表，可能或不可以用于其他存储引擎（我不太熟悉）。

mysqlhotcopy

如果您需要在数据库服务器运行时转储数据库的内容，您可以使用 mysqlhotcopy （注意，这只适用于MyISAM和Archive表）：

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

复制整个数据文件夹

如果您要复制整个数据库安装，所以，所有的数据库和每个数据库的内容，你可以关闭mysqld，压缩你的整个MySQL数据目录，并将其复制到新服务器的数据目录。

这是唯一的方法（我知道）将InnoDB文件从一个实例复制到另一个实例。这将工作正常，如果你在运行相同的操作系统家族和相同版本的MySQL的服务器之间移动;它可以用于在操作系统和/或MySQL的版本之间移动;离开我的头顶，我不知道。

Does anyone know of an easy way to copy a database from one computer to a file, and then import it on another computer?

解决方案

Here are a few options:

mysqldump

The easiest, guaranteed-to-work way to do it is to use mysqldump. See the manual pages for the utility here:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Basically, it dumps the SQL scripts required to rebuild the contents of the database, including creation of tables, triggers, and other objects and insertion of the data (it's all configurable, so if you already have the schema set up somewhere else, you can just dump the data, for example).

Copying individual MyISAM table files

If you have a large amount of data and you are using the MyISAM storage engine for the tables that you want to copy, you can just shut down mysqld and copy the .frm, .myd, and .myi files from one database folder to another (even on another system). This will not work for InnoDB tables, and may or may not work for other storage engines (with which I am less familiar).

mysqlhotcopy

If you need to dump the contents of a database while the database server is running, you can use mysqlhotcopy (note that this only works for MyISAM and Archive tables):

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Copying the entire data folder

If you are copying the entire database installation, so, all of the databases and the contents of every database, you can just shut down mysqld, zip up your entire MySQL data directory, and copy it to the new server's data directory.

This is the only way (that I know of) to copy InnoDB files from one instance to another. This will work fine if you're moving between servers running the same OS family and the same version of MySQL; it may work for moving between operating systems and/or versions of MySQL; off the top of my head, I don't know.

这篇关于最简单的方法来复制MySQL数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！