使用 mysqldump 时要小心字符集,尤其是在 Windows 上。 我更喜欢显式设置我的数据库使用的字符集并使用 --result-file=file 而不是使用 > 操作员以免东西被损坏。
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 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.
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.
Dumps produced by third party gui tools, especially phpmyadmin, are generally inaccurate and won't necessarily restore correctly.
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:
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.
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.
磁盘上的文件在所有版本的 MySQL 之间 100% 兼容。 您只需注意文件名的大小写,因为它在 Unix 上很重要,而有时在 Windows 上却很重要。
并记住在获取副本之前停止 MySQL。 MyISAM 文件可以在运行时进行复制,但 InnoDB 文件这样做并不安全,而且 Windows 的 MySQL 默认为 InnoDB 文件。
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.
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.
发布评论
评论(9)
使用 mysqldump 时要小心字符集,尤其是在 Windows 上。 我更喜欢显式设置我的数据库使用的字符集并使用 --result-file=file 而不是使用 > 操作员以免东西被损坏。
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.
来自 MySQL 文档:
http://dev.mysql.com/doc/refman/5.0 /en/copying-databases.html
将包含数据库内容的文件传输到目标计算机并在那里运行以下命令:
From the MySQL documentation:
http://dev.mysql.com/doc/refman/5.0/en/copying-databases.html
Transfer the file containing the database contents to the target machine and run these commands there:
您可以使用任何 gui 工具进行备份,例如 Mysql Administrator (http://dev.mysql .com/downloads/gui-tools/(Windows 上),aptitude install mysql-admin(Ubuntu 上)或 phpmyadmin (http://www.phpmyadmin.net/home_page/index.php,Ubuntu 上为 aptitude install phpmyadmin),然后在另一台计算机上恢复。
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.php on Windows, aptitude install phpmyadmin on Ubuntu), and then recover it in the other computer.
mysqldump 应该正确转储它并保留数据库中任何内容的字符集。 如果不想出现问题,建议迁移到相同版本的 mysql。
mysqldump 生成的文件不是文本文件,尽管看起来如此,但不要使用 notepad.exe 等编辑它,否则您将遇到麻烦。
第三方 GUI 工具(尤其是 phpmyadmin)生成的转储通常不准确,并且不一定能正确恢复。
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.
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.
Dumps produced by third party gui tools, especially phpmyadmin, are generally inaccurate and won't necessarily restore correctly.
我经常发现自己使用这种单行代码的一些变体通过网络复制数据库。
我最初在这里读到:
http:// www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/
I often find myself using some variation of this one-liner to copy a database over the network.
Which I originally read about here:
http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/
使用新的mysql工作台(gui工具),您可以轻松导出数据库并将其导入到您要迁移的数据库中。
With new mysql workbench (gui tool), you can easily export the database and import it to the database you want to migrate.
你所说的工具已经存在: mysqldump
它转储到sql,然后你可以将其复制到另一台机器并重新加载。
例如:
在源上:
然后使用 ftp/rsync/whatever 将文件移动到目标计算机,然后创建一个空数据库以导入并运行:
您还需要为可能具有以下权限的任何用户设置权限:也被转移,因为它们不保存在数据库中。
或者,您可以从 mysql 数据目录复制文件 - 但 mysqldump 是最简单/最可靠的方法。
值得注意的是,表名在一个系统上可能会变得区分大小写,而它们在原始系统上并不敏感。 它取决于两端的配置 - 特别是文件系统的区分大小写(或其他)。
The tool you speak of already exists: mysqldump
It dumps out to sql, which you can then copy to another machine and re-load.
eg:
on source:
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:
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.
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.
您可以使用单个命令导出所有数据库,而不是逐个导出数据库。 然后导入它们。
例如。
PS-最大的优点是你不会失去用户权限。
Rather than exporting the databases one by one, you can export them all with a single command. Then import them.
eg.
PS- The biggest advantage is that you do not lose the user privileges.
磁盘上的文件在所有版本的 MySQL 之间 100% 兼容。 您只需注意文件名的大小写,因为它在 Unix 上很重要,而有时在 Windows 上却很重要。
并记住在获取副本之前停止 MySQL。 MyISAM 文件可以在运行时进行复制,但 InnoDB 文件这样做并不安全,而且 Windows 的 MySQL 默认为 InnoDB 文件。
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.
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.