running a large .sql file

rgb192
rgb192 used Ask the Experts™
on
I have a 500mb sql file


I am trying to open and put in sql manager for mysql ems, but says out of memory error

I also tried with mysql workbench


I can open the file in notepad

or large text file viewer  but it is too large to run


maybe I could use a sql server manager to use with mysql


what should I do
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Do you just need to run the whole SQL script?  If so why not just run mysql command line:

mysql -u username -p -h hostname databasename < filename.sql

Author

Commented:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> mysql -u root -p -h localhost xcart444
    ->


I am trying to use database xcart444 on localhost for user root with no password

I first want to select * from table
Top Expert 2012

Commented:
no what I gave you was meant to be run in the windows command line, calling the mysql client program.  Since you are already in the program, you can do:

use xcart444;
select * from table;
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 87
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.16    |
+-----------+
1 row in set (0.04 sec)

mysql> use xcart;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'xcart'
mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql>
Top Expert 2012

Commented:
Again you DO NOT run "mysql -u root -p -h localhost xcart444;" on the mysql prompt but the WINDOWS command prompt.

 Access denied for user ''@'localhost' to database 'xcart' just means that - an anonymous user is not allowed to access the xcart database.  Log on as the root user, then grant privileges to another user - easiest would be:

grant all on xcart444 .* to username@localhost identified by 'password';

See http://dev.mysql.com/doc/refman/5.5/en/grant.html
Top Expert 2012

Commented:
Go to your windows command line, then execute:

mysql -u root -p -h localhost xcart444

You may need to include the full location of your mysql client program when you call it.  Once inside, you should be able to select from your tables
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi!

If your 500 mb sql file contains the database DDL and data then you would have to import it first to the mysql engine to be able to query it later.
In your windows command line issue

mysql -u root -p -h localhost < yoursqlfile.sql

After that you will probably have to adjust the user privileges to the database to suit your needs.

Regards,
     Tomas Helgi

Author

Commented:
>>
 Access denied for user ''@'localhost' to database 'xcart' just means that - an anonymous user is not allowed to access the xcart database.  Log on as the root user, then grant privileges to another user - easiest would be:

grant all on xcart444 .* to username@localhost identified by 'password';




how to log on as the root user
Top Expert 2012

Commented:
I already gave it to you above.  here it is again:

mysql -u root -p -h localhost xcart444

Author

Commented:
mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql>

Author

Commented:
mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql> mysql -u root -p -h localhost;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p -h localhost' at line 1
mysql>

Author

Commented:
same error when I do

mysql -u root;
Top Expert 2012

Commented:
How many times do we have to say you do not run that inside MySQL prompt but on windows command line.
Top Expert 2012
Commented:
Windows Command Prompt or Command Line.  On Windows XP (the only Windows I have access to at the moment it's under Accessories).  See screenshotscreenshot

Author

Commented:
using cmd.exe



Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Acer>mysql -u root;
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>mysql -u root -p
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>mysql -u root -p;
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>
Top Expert 2012

Commented:
Again I also said above that you may have to look for the actual path to the mysql client binaries and include it when you call the program.

Please try to understand the error message you are getting and not just copy+paste here for someone else to interpret it for you.  A single "'mysql' is not recognized as an internal or external command, operable program or batch file." should have already told you that Windows cannot find the mysql client program.  That is a standard Windows error message, not a MySQL issue.

Author

Commented:
I tried

C:\Program Files\MySQL\

but there is no folder

>> path

should I look in 'environment variables'
Top Expert 2012

Commented:
See if there's a C:\Program Files\MySQL\bin and if there are .exe files in there.  One of them is the one you need

Author

Commented:
there is no
C:\Program Files\MySQL\
folder
Top Expert 2012

Commented:
Sorry I thought you said there's one but still cannot find the needed files.  Anyway, I assisted your "Where is Mysql" question clarifying to the other experts what you actually need.
Top Expert 2012

Commented:
Based on the output in your other question it looks lilke it's C:\wamp\bin\mysql\mysql5.5.20\bin\mysql.exe

Maybe you can go to that directory then run it.  Again it's "mysql -u root -p -h localhost xcart444" without any semicolon.  Semicolon is only for ending sql statements, and this is not an SQL statement but a windows executable file being called with parameters.
Top Expert 2012

Commented:
Turns out there's also a C:\xampp\mysql\bin\mysql.exe so just try which one will work.
hey dont run the script from the mysql prompt.run it from the dos prompt.check the image
mysql.jpg
Top Expert 2012

Commented:
From my first reply at the top:


Do you just need to run the whole SQL script?  If so why not just run mysql command line:

mysql -u username -p -h hostname databasename < filename.sql

I realize now the author must have been confused when I said "mysql command line" -> I meant mysql command line client which is run on the OS command line.  But I already clarified that many times over in succeeding posts.

Author

Commented:
thanks for answer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial