MySQL windows下如何使用mysql的source命令导入大文件

How do you use MySQL's source command to import large files in windows

提问by Daniel Nill

I have a large (~150mb) sql file that I am trying to import. It is too large to do it through PHPMyAdmin even splitting it into many pieces and it is too large to import through a php script as it times out after 30 seconds of processing the script. So I'm looking for how to directly import the file from MySQL command line.

我有一个大 (~150mb) 的 sql 文件要导入。它太大了，无法通过 PHPMyAdmin 完成，甚至将其拆分为许多部分，而且它太大而无法通过 php 脚本导入，因为它在处理脚本 30 秒后超时。所以我正在寻找如何从 MySQL 命令行直接导入文件。

Searching online shows that I want to either use database_name < file_name.sqlor source file_name.sqlbut I can't get either of these to work.

在线搜索显示我想要使用database_name < file_name.sql或source file_name.sql但我无法让其中任何一个工作。

Using <gives the generic MySQL syntax error while using sourcegive a slightly more promising failed to open file 'file_name.sql', error: 2so I am inclined to think that the source command is on the right track.

Using<给出了通用的 MySQL 语法错误，而 usingsource给出了一个稍微更有希望的，failed to open file 'file_name.sql', error: 2所以我倾向于认为 source 命令是在正确的轨道上。

I am in windows and am using xampp as a localhost server (note I'm only trying to import this file on the localhost so that I can execute the sql). I've tried placing the file in xampp\mysql\binand xampp\mysql\data\database_name.

我在 Windows 中并且使用 xampp 作为本地主机服务器（注意我只是试图在本地主机上导入这个文件，以便我可以执行 sql）。我试过将文件放在 xampp\mysql\bin和 中xampp\mysql\data\database_name。

Any suggestions of how to import this .sql file into MySQL either from the MySQL command line or by any other means would be greatly appreciated.

关于如何从 MySQL 命令行或通过任何其他方式将此 .sql 文件导入 MySQL 的任何建议将不胜感激。

回答by Femi

With xampp I think you need to use the full path at the command line, something like this, perhaps:

对于 xampp，我认为您需要在命令行中使用完整路径，例如：

C:\xampp\mysql\bin\mysql -u {username} -p {databasename} < file_name.sql

回答by Valentin Despa

On Windows this should work (note the forwardslash and that the whole path is not quoted and that spaces are allowed)

在 Windows 上，这应该可以工作（注意正斜杠，整个路径没有被引用并且允许空格）

USE yourdb;

USE yourdb;

SOURCE D:/My Folder with spaces/Folder/filetoimport.sql;

SOURCE D:/My Folder with spaces/Folder/filetoimport.sql;

回答by rightstuff

Don't use "source", it's designed to run a small number of sql queriesand display the output, not to import large databases.

不要使用"source"，它旨在运行少量sql queries并显示输出，而不是导入大型数据库。

I use Wamp Developer(not XAMPP) but it should be the same.

我使用Wamp Developer(not XAMPP) 但它应该是一样的。

What you want to do is use the MySQL Clientto do the work for you.

你想要做的是使用MySQL Client为你做工作。

1. Make sure MySQLis running.
2. Create your database via phpMyAdminor the MySQL shell.
3. Then, run cmd.exe, and change to the directory your sqlfile is located in.
4. Execute: mysql -u root -p database_name_here < dump_file_name_here.sql
5. Substitute in your database nameand dump file name.
6. Enter your MySQL root account passwordwhen prompted (if no password set, remove the "-p" switch).

1. 确保MySQL正在运行。
2. 通过phpMyAdmin或MySQL shell.
3. 然后，运行cmd.exe，并切换到您的sql文件所在的目录。
4. 执行： mysql -u root -p database_name_here < dump_file_name_here.sql
5. 替换为您的database name和dump file name。
6. MySQL root account password出现提示时输入您的密码（如果未设置密码，请删除“-p”开关）。

This assumes that mysql.execan be located via the environmental path, and that sqlfile is located in the directory you are running this from. Otherwise, use full paths.

这假设mysql.exe可以通过环境路径定位，并且该sql文件位于您运行它的目录中。否则，请使用完整路径。

回答by Shafiqul Islam

Option 1.you can do this using single cmd where D is my xampp or wampp install folder so i use this where mysql.exe install and second option database name and last is sql file so replace it as your then run this

选项 1.您可以使用单个 cmd 执行此操作，其中 D 是我的 xampp 或 wampp 安装文件夹，因此我使用它，其中 mysql.exe 安装和第二个选项数据库名称，最后一个是 sql 文件，因此将其替换为您的然后运行此

D:\xampp\mysql\bin\mysql.exe -u root -p databse_name < D:\yoursqlfile.sql

Option 1 for wampp

wampp 的选项 1

D:\wamp64\bin\mysql\mysql5.7.14\bin\mysql.exe -u root -p databse_name< D:\yoursqlfile.sql

change your folder and mysql version

更改您的文件夹和 mysql 版本

Option 2Suppose your current path is which is showing command prompt

选项 2假设您当前的路径是显示命令提示符

C:\Users\shafiq;

then change directory using cd..then goto your mysql directory where your xampp installed. Then cd..for change directory. then go to bin folder.

然后使用cd..然后转到安装 xampp 的 mysql 目录更改目录。然后cd..换目录。然后转到bin文件夹。

C:\xampp\mysql\bin;

C:\xampp\mysql\bin\mysql -u {username} -p {database password}.then please enter when you see enter password in command prompt.

C:\xampp\mysql\bin\mysql -u {username} -p {database password}.然后在命令提示符中看到输入密码时请输入。

choose database using

选择数据库使用

mysql->use test (where database name test) 

then put in source sql in bin folder.

然后将源 sql 放入 bin 文件夹中。

then last command will be

那么最后一个命令将是

mysql-> source test.sql (where test.sql is file name which need to import)

then press enter

然后按回车

This is full command

这是完整的命令

C:\Users\shafiq;
C:\xampp\mysql\bin
C:\xampp\mysql\bin\mysql -u {username} -p {database password}
 mysql-> use test
 mysql->source test.sql

回答by Guillermo Hernández

C:\xampp\mysql\bin\mysql -u root -p testdatabase < C:\Users\Juan\Desktop\databasebackup.sql

That worked for me to import 400MB file into my database.

这对我将 400MB 文件导入我的数据库很有用。

回答by Arjun s

Username as root without password

用户名作为 root 没有密码

mysql -h localhost -u root databasename < dump.sql

I have faced the problem on my local host as i don't have any password for root user. You can use it without -p password as above. If it ask for password, just hit enter.

我在本地主机上遇到了这个问题，因为我没有 root 用户的任何密码。您可以在没有 -p 密码的情况下使用它，如上所述。如果它要求输入密码，只需按 Enter。

回答by cdarlint

use mysql sourcecommand to avoid redirection failures, especially on windows.

使用 mysqlsource命令避免重定向失败，尤其是在 Windows 上。

mysql [-u <username>] [-p<password>] <databasename> -e "source /path/to/dump.sql"

where e for "Execute command"

其中 e 表示“执行命令”

On Windows, please remember to use double quote for sql command.

在 Windows 上，请记住对 sql 命令使用双引号。

However, either backslash \ or slash / will work on Windows.

但是，反斜杠 \ 或斜杠 / 都适用于 Windows。

回答by IamJuanValle

On my Xampp set-up I was able to use the following to import a database into MySQL:

在我的 Xampp 设置中，我能够使用以下内容将数据库导入 MySQL：

C:\xampp\mysql\bin\mysql -u {username goes here} -p {leave password blank} {database name} < /path/to/file.sql [enter]

My personal experience on my local machine was as follows:
Username:Root
Database Name:testdatabase
SQL File Location:databasebackup.sql is located on my desktop

我在本地机器上的个人经验如下：
用户名：根
数据库名称：testdatabase
SQL 文件位置：databasebackup.sql 位于我的桌面上

C:\xampp\mysql\bin\mysql -u root -p testdatabase < C:\Users\Juan\Desktop\databasebackup.sql 

That worked for me to import my 1GB+ file into my database.

这对我将我的 1GB+ 文件导入我的数据库很有用。

回答by ravindrapatel042

For importing a large SQL file using the command line in MySQL.

用于在 MySQL 中使用命令行导入大型 SQL 文件。

First go to file path at the command line. Then,

首先在命令行中转到文件路径。然后，

Option 1:

选项1：

mysql -u {user_name} -p{password} {database_name}  < your_file.sql

It's give a warning mesaage : Using a password on the command line interface can be insecure.

它给出了一个警告消息：在命令行界面上使用密码可能不安全。

Done.Your file will be imported.

完成。您的文件将被导入。

Option 2:

选项 2：

mysql -u {user_name} -p {database_name}  < your_file.sql

in this you are not provide sql password then they asked for password just enter password and your file will be imported.

在这种情况下，您没有提供 sql 密码，然后他们要求输入密码，只需输入密码，您的文件就会被导入。

回答by Arash

Hello I had the same problem but I tried many different states and I came to it: SOURCE doesn't work with ; at the end in my case:

您好，我遇到了同样的问题，但我尝试了许多不同的状态，然后我终于解决了：SOURCE 不适用于 ; 最后在我的情况下：

SOURCE D:\Barname-Narmafzar\computer programming's languages\SQL\MySQL\dataAug-12-2019\dataAug-12-2019.sql;

and the error was:

错误是：

ERROR: Unknown command '\B'. '> it also didn't work with a quotation for the address. But it works without ; at the end:

ERROR: Unknown command '\B'. '> 它也不适用于地址的报价。但它没有工作；在末尾：

SOURCE D:\Barname-Narmafzar\computer programming's languages\SQL\MySQL\dataAug-12-2019\dataAug-12-2019.sql

But remember to use USE database_name; before that. I think it's so because the SOURCE or USE or HELP are for the Mysql itself and they are not such query codes although when you write HELP it says:

但记得使用 USE database_name; 在那之前。我认为是这样，因为 SOURCE 或 USE 或 HELP 是针对 Mysql 本身的，它们不是这样的查询代码，尽管在编写 HELP 时它说：

"Note that all text commands must be first on line and end with ; ".

“请注意，所有文本命令都必须在第一行并以 ; 结尾。”。

but here doesn't work.

但这里不起作用。

I should say that I have done it in CMD and I didn't try it in Mysql Workbench. That was it

我应该说我在CMD中做过，我没有在Mysql Workbench中尝试过。就是这样

This is the result

这是结果