在Windows 7中通过命令行导入SQL文件。

I want to import an SQL file (size > 500MB) into a database. I have wamp on my PC. Phpmyadmin does not work well with this size. I changed all parameters in php.ini (max_upload_size etc.). But it does not work for me. So I want to import it by command line.

我想将一个SQL文件(大小为> 500MB)导入数据库。我的电脑上有wamp。Phpmyadmin不能很好地处理这个大小。我在php中修改了所有参数。ini(max_upload_size等等)。但这对我不起作用。我想通过命令行导入它。

I used these commands for import the file:

我使用这些命令来导入文件:

mysql -u root -p sysdat < D:\Nisarg\ISPC\Database\sysdat.sql 
mysql -u root -p -D sysdat < D:\Nisarg\ISPC\Database\sysdat.sql
mysql -u root sysdat < D:\Nisarg\ISPC\Database\sysdat.sql -p 

These all are not working.

这些都不起作用。

15 个解决方案

#1

Try like this:

试试这样:

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

我认为您需要在命令行中使用完整的路径，比如:

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

Refer this link also:

也请参考这个链接:

http://www.ryantetek.com/2011/09/importing-large-sql-files-through-command-line-when-using-phpmyadminxampp/

http://www.ryantetek.com/2011/09/importing-large-sql-files-through-command-line-when-using-phpmyadminxampp/

#2

If you have wamp installed then go to command prompt , go to the path where mysql.exe exists , like for me it was : C:\wamp\bin\mysql\mysql5.0.51b\bin , then paste the sql file in the same location and then run this command in cmd :

如果安装了wamp，请转到命令提示符，转到mysql所在的路径。exe存在，就像我一样，它是:C:\wamp\bin\mysql\mysql5.0.51b\bin \bin，然后将sql文件粘贴到相同的位置，然后在cmd中运行这个命令:

C:\wamp\bin\mysql\mysql5.0.51b\bin>mysql -u root -p YourDatabaseName < YourFileName.sql

#3

To import database from dump file use:

从转储文件导入数据库:

mysql -u UserName -p Password DatabaseName < FileName.sql 

In wamp

在里面

C:\wamp\bin\mysql\mysql5.0.51b\bin>mysql mysql -uroot -p DatabaseName < FileName.sql 

#4

----------------WARM server.

- - - - - - - - - - - - - - - - -温暖的服务器。

step 1: go to cmd go to directory C:\wamp\bin\mysql\mysql5.6.17 hold Shift + right click (choose "open command window here")

步骤1:去cmd到目录C:\wamp\bin\mysql\mysql5.6.17按住Shift +右击(选择“打开命令窗口”)

step 2: C:\wamp\bin\mysql\mysql5.6.17\bin>mysql -u root -p SellProduct < D:\file.sql

第二步:C:\wamp\bin\mysql\mysql5.6.17\bin>mysql -u root -p SellProduct < D:\file.sql

in this case
+ Root is username database  
+ SellProduct is name database.
+ D:\file.sql is file you want to import

---------------It's work with me -------------------

- - - - - - - - - - - - - - - - -与我的工作- - - - - - - - - - - - - - - - - - -

#5

To import database from dump file (in this case called filename.sql)

从转储文件导入数据库(在本例中称为filename.sql)

    use: mysql -u username -p password database_name < filename.sql 

you are on Windows you will need to open CMD and go to directory where mysql.exe is installed. you are using WAMP server then this is usually located in: C:\wamp\bin\mysql\mysql5.5.8\bin (*note the version of mysql might be different)

在Windows上，您需要打开CMD，并进入mysql所在的目录。exe安装。您正在使用WAMP服务器，那么它通常位于:C:\ WAMP \bin\mysql\mysql5.5.8\bin(*注意，mysql的版本可能不同)

So you will: cd C:\wamp\bin\mysql\mysql5.5.8\bin

cd C:\wamp\bin\mysql\mysql5.5.8\bin

and then execute one of the above commands. Final command like this

然后执行上面的一个命令。最终这样的命令

    C:\wamp\bin\mysql\mysql5.5.8\bin>mysql -u rootss -p pwdroot testdatabasename < D:\test\Projects\test_demo_db.sql

#6

TRY THIS

试试这个

  C:\xampp\mysql\bin\mysql -u {username} -p {databasename} < {filepath}

if username=root ,filepath='C:/test.sql', databasename='test' ,password ='' then command will be

如果用户名=根,filepath = ' C:/测试。sql'， databasename='test'，password =' then command will be

  C:\xampp\mysql\bin\mysql -u root  test < C:/test.sql

#7

I use mysql -u root -ppassword databasename < filename.sql in batch process. For an individual file, I like to use source more because it shows the progress and any errors like

我使用mysql -u根-ppassword databasename <文件名。sql在批处理过程。对于单个文件，我更喜欢使用源代码，因为它显示了进度和类似的任何错误< p>

Query OK, 6717 rows affected (0.18 sec)
Records: 6717  Duplicates: 0  Warnings: 0

1. Log in to MySQL using mysql -u root -ppassword
2. 使用MySQL -u根-ppassword登录到MySQL

3. In MySQL, change the database you want to import in: mysql>use databasename;

   在MySQL中，更改要导入的数据库:MySQL >使用databasename;

   • This is very important otherwise it will import to the default database
   • 这是非常重要的，否则它将导入到默认数据库。

4. Import the SQL file using source command: mysql>source path\to\the\file\filename.sql;

   使用源命令导入SQL文件:mysql>源路径\到\文件\ \文件名. SQL;

#8

Related to importing, if you are having issues importing a file with bulk inserts and you're getting MYSQL GONE AWAY, lost connection or similar error, open your my.cnf / my.ini and temporarily set your max_allowed_packet to something large like 400M

与导入相关，如果您在导入一个包含大量插入的文件时遇到问题，并且导致MYSQL丢失、连接丢失或类似错误，请打开您的my.cnf / my。并临时将max_allowed_packet设置为400之类的大型包

Remember to set it back again after your import!

请记住在导入后重新设置它!

#9

Try this it will work. Do not enter password it will ask one you execute the following cmd

试试这个就行了。不输入密码它会问一个你执行以下cmd吗

C:\xampp\mysql\bin\mysql -u xxxxx -p -h localhost your_database_name < c:\yourfile.sql

#10

mysql : < (for import) > (for export)

mysql: <(导入)>(导出)

in windows, you want to take backup or import the sql file, then goto cmd prompt type the address were the mysql is installed eg:C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> after this

在windows中，您想要备份或导入sql文件，然后goto cmd提示符键入mysql已安装的地址如:C:\程序文件(x86)\ mysql \ mysql Server 5.6\bin>

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> mysql -u UserName -p Password DatabaseName < FileName.sql (import)

C:\Program Files (x86)\MySQL\MySQL Server 5.6 \ bin > mysql - p - u用户名密码数据库名 <文件名。sql(进口)< p>

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> mysql -u UserName -p Password DatabaseName > FileName.sql (export)

C:\Program Files (x86)\MySQL\MySQL Server 5.6 \ bin > mysql - p - u用户名密码数据库名>文件名。sql(出口)

#11

If those commands don't seems to work -- I assure you they do --, check the top of your sql dump file for the use of :

如果这些命令看起来不起作用(我向您保证它们起作用)，请检查sql dump文件的顶部，以便使用:

CREATE DATABASE {mydbname}

and

和

USE {mydbname}

The last parameter {mydbname} of the mysql command can be misleading : if CREATE DATABASE an USE are in your dump file, the import will in fact be done in this database, not in the one in the mysql command.

mysql命令的最后一个参数{mydbname}可能具有误导性:如果在转储文件中使用了CREATE DATABASE，那么实际上导入将在这个数据库中完成，而不是在mysql命令中。

The mysqldump command that will prompt CREATE DATABASE and USE is :

将提示创建数据库并使用的mysqldump命令是:

mysqldump.exe -h localhost -u root --databases xxx > xxx.sql

Use mysqldump without --databases to leave out CREATE DATABASE and USE :

使用mysqldump without——DATABASE to leave CREATE DATABASE and Use:

mysqldump.exe -h localhost -u root xxx > xxx.sql

#12

If you are using Windows PowerShell you may get the error:

如果您正在使用Windows PowerShell，您可能会得到以下错误:

The '<' operator is reserved for future use.

In that case just type the command:

在这种情况下，只需输入命令:

cmd

To switch to the cmd shell and then retype the command and it will work.

切换到cmd shell，然后重新输入命令，它就会工作。

c:\xampp\mysql\bin\mysql -u root -p my_database < my_database_dump.sql

To get back to PowerShell type:

回到PowerShell类型:

exit

#13

If you don't have password you can use the command without

如果您没有密码，您可以使用该命令。

-u

Like this

像这样

C:\wamp>bin\mysql\mysql5.7.11\bin\mysql.exe -u {User Name} {Database Name} < C:\File.sql

Or on the SQL console

或者在SQL控制台

mysql -u {User Name} -p {Database Name} < C:/File.sql

#14

First open Your cmd pannel And enter mysql -u root -p (And Hit Enter) After cmd ask's for mysql password (if you have mysql password so enter now and hit enter again) now type source mysqldata.sql(Hit Enter) Your database will import without any error

首先打开cmd面板，在cmd询问mysql密码后输入mysql -u root -p(点击回车)(如果你有mysql密码，现在输入回车)，现在输入源mysqldata。您的数据库将导入sql(单击Enter)，不会出现任何错误

#15

If you are running WampServer on your local machine, import means restoring the dump file that you have (in sql format)

如果您正在本地机器上运行WampServer，导入意味着恢复您拥有的转储文件(sql格式)

Here are the steps

以下的步骤

1. Go to command line by going to Start -> Run and typing in cmd.
2. 转到命令行，在cmd中运行并输入>。

3. Change the directory to Mysql bin directory. It will be like

   将目录更改为Mysql bin目录。它将会像

   c:\wamp\bin\mysql\mysql5.7.14\bin

   c:\ wamp \ bin \ mysql \ mysql5.7.14 \ bin

4. It would be better to keep the dump file in the above directory( we can delete, after restoration)

   最好将转储文件保存在上面的目录中(恢复后可以删除)

5. Hope you have created the database (either through phpMyadmin or using command line)

   希望您已经创建了数据库(通过phpadmin myor使用命令行)

6. Then type the command mysql.exe -u root -p databasename < filename.sql

   然后输入命令mysql。exe -u root -p databasename < filename.sql。

Please note the difference, it is 'mysql.exe' not 'mysql'

请注意区别，它是“mysql”。exe”不是“mysql”