将较大的 sql 文件导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15278375/
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
Importing larger sql files into MySQL
提问by Anil
I have a 400MB large sql backup file. I'm trying to import that file into MySQL database using WAMP->import, but the import was unsuccessful due to many reasons such as upload file size is too large and so on. So, i've gone ahead and made a few changes to the config PHP and MySQL settings under WAMP. Changes made are
我有一个 400MB 的大型 sql 备份文件。我正在尝试使用 WAMP->import 将该文件导入 MySQL 数据库，但由于上传文件过大等多种原因导致导入失败。所以，我已经继续对 WAMP 下的配置 PHP 和 MySQL 设置进行了一些更改。所做的更改是
Under WAMP->MySQL->my.ini
max_allowed_packet = 800M
read_buffer_size = 2014K
Under WAMP->PHP->PHP.ini
max_input_time = 20000
memory_limit = 128M
My question is, Is this the right way to import such a large sql files using WAMP->import? If so, did i make the right changes to the config files? What are the maximum values allowed for the above variable?
我的问题是，这是使用 WAMP->import 导入如此大的 sql 文件的正确方法吗？如果是这样，我是否对配置文件进行了正确的更改？上述变量允许的最大值是多少？
回答by Ibu
You can import large files this command line way:
您可以通过以下命令行方式导入大文件：
mysql -h yourhostname -u username -p databasename < yoursqlfile.sql
回答by Megan Squire
Since you state (in a clarification comment to another person's answer) that you are using MySQL Workbench, you could try using the "sql script" option there. This will avoid the need to use the commandline (although I agree with the other answer that it's good to climb up on that horse and learn to ride).
由于您声明（在对其他人答案的澄清评论中）您正在使用 MySQL Workbench，因此您可以尝试在那里使用“sql 脚本”选项。这将避免使用命令行的需要（尽管我同意另一个答案，即爬上那匹马并学习骑马是件好事）。
In MySQL Workbench, go to File menu, then select "open script". This is probably going to take a long time and might even crash the app since it's not made to open things that are as big as what you describe.
The better way is to use the commandline. Make sure you have MySQL client installed on your machine. This means the actual MySQL (not Workbench GUI or PhpMyAdmin or anything like that). Here is a link describing the command-line tool. Once you have that downloaded and installed, open a terminal window on your machine, and you have two choices for slurping data from your file system (such as in a backup file) up into your target database. One is to use 'source' command and the other is to use the < redirection operator.
在 MySQL Workbench 中，转到“文件”菜单，然后选择“打开脚本”。这可能需要很长时间，甚至可能会使应用程序崩溃，因为它不是用来打开与您描述的一样大的东西。
更好的方法是使用命令行。确保您的机器上安装了 MySQL 客户端。这意味着实际的 MySQL（不是 Workbench GUI 或 PhpMyAdmin 或类似的东西）。这是描述命令行工具的链接。下载并安装后，在您的机器上打开一个终端窗口，您有两种选择可以将文件系统（例如备份文件）中的数据传输到目标数据库中。一种是使用 'source' 命令，另一种是使用 < 重定向运算符。
Option 1: from the directory where your backup file is:
选项 1：从备份文件所在的目录：
$mysql -u username -p -h hostname databasename < backupfile.sql
Option 2: from the directory where your backup file is:
选项 2：从备份文件所在的目录：
$mysql -u username -p -h hostname
[enter your password]
> use databasename;
> source backupfile.sql
Obviously, both of these options require that you have a backup file that is SQL.
显然，这两个选项都要求您有一个 SQL 备份文件。
回答by Wesam Na
Use this from mysql command window:
从 mysql 命令窗口使用它：
mysql> use db_name;
mysql> source backup-file.sql;
回答by Magnus Lindgren
You can make the import command from any SQL query browser using the following command:
您可以使用以下命令从任何 SQL 查询浏览器执行导入命令：
source C:\path\to\file\dump.sql
Run the above code in the query window ... and wait a while :)
在查询窗口中运行上面的代码......并等待一段时间:)
This is more or less equal to the previously stated command line version. The main difference is that it is executed from within MySQL instead. For those using non-standard encodings, this is also safer than using the command line version because of less risk of encoding failures.
这或多或少等于前面提到的命令行版本。主要区别在于它是从 MySQL 内部执行的。对于那些使用非标准编码的人来说，这也比使用命令行版本更安全，因为编码失败的风险更小。
回答by tuliomarchetto
I really like the BigDumpto do it. It's a very simple PHP file that you edit and send with your huge file through SSH or FTP. Run and wait! It's very easy to configure character encoding, comes UTF-8 by default.
我真的很喜欢BigDump来做这件事。这是一个非常简单的 PHP 文件，您可以通过 SSH 或 FTP 编辑并与您的大文件一起发送。运行并等待！配置字符编码非常容易，默认为 UTF-8。
回答by Ali Abdulla
We have experienced the same issue when moving the sql server in-house.
我们在内部移动 sql server 时遇到了同样的问题。
A good solution that we ended up using is splitting the sql file into chunks. There are several ways to do that. Use
我们最终使用的一个很好的解决方案是将 sql 文件分成块。有几种方法可以做到这一点。用
http://www.ozerov.de/bigdump/seems good (but never used it)
http://www.ozerov.de/bigdump/看起来不错（但从未使用过）
http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/used it and it was very useful to get structure out of the mess and you can take it from there.
http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/使用了它，它对于摆脱混乱的结构非常有用，您可以从那里获取它。
Hope this helps :)
希望这可以帮助 ：）
回答by Shiv Singh
3 things you have to do, if you are doing it locally:
如果您在本地进行，则必须做 3 件事：
in php.inior php.cfgof your php installation
在php.ini或php.cfg你的PHP安装
post_max_size=500M
upload_max_filesize=500M
memory_limit=900M
or set other values. Restart Apache.
或设置其他值。重新启动阿帕奇。
OR
或者
Use php big dump tool its best ever i have seen. its free and opensource
使用我见过的最好的 php 大转储工具。它的免费和开源
回答by live-love
Had a similar problem, but in Windows. I was trying to figure out how to open a large MySql sql file in Windows, and these are the steps I had to take:
有类似的问题，但在 Windows 中。我试图弄清楚如何在 Windows 中打开一个大型的 MySql sql 文件，这些是我必须采取的步骤：
- Go to the download website (http://dev.mysql.com/downloads/).
- Download the MySQL Community Server and install it (select the developer or full install, so it will install client and server tools).
- Open MySql Command Line Client from the Start menu.
- Enter your password used in install.
In the prompt, mysql>, enter:
CREATE DATABASE database_name;
USE database_name;
SOURCE myfile.sql
- 转到下载网站 ( http://dev.mysql.com/downloads/)。
- 下载 MySQL Community Server 并安装它（选择开发者或完全安装，这样它将安装客户端和服务器工具）。
- 从开始菜单打开 MySql 命令行客户端。
- 输入安装时使用的密码。
在提示符 mysql> 中，输入：
创建数据库数据库名称；
使用数据库名称；
源 myfile.sql
That should import your large file.
那应该导入你的大文件。
回答by Usman Ali Maan
Simplest solution is mySql workBench just copy the .sql file text to query window of mysql-workbenck and just execute it, All renaming things will done by it.
最简单的解决方案是 mySql workBench 只需将 .sql 文件文本复制到 mysql-workbenck 的查询窗口并执行它，所有重命名的事情都会由它完成。
回答by angus
If you are using the source command on Windows remember to use f:/myfolder/mysubfolder/file.sqland not f:\myfolder\mysubfolder\file.sql
如果您在 Windows 上使用 source 命令，请记住使用f:/myfolder/mysubfolder/file.sql而不是f:\myfolder\mysubfolder\file.sql
