Importing .sql file

Hi,

I have set up a local instance of My SQL to do some testing and data export functions with an existing database.

However when I try to restore the .sql file into this instance, I'm having all kinds of issues, but bottom line, I'm not getting any response from the Command Line Client, including no errors. Just sits there.

I have to use Command Line because the database is about 120 MB and Heidi SQL barfs during import with Out of Memeory errors.

So here is my syntax. What am I doing wrong?

mysql> mysql -u spw_web_user -p password spw_online <spw_online_may_11_2011.sql

Any ideas?

After I hit Enter I just get:

->

Thanks

Bill
LVL 1
billium99Asked:
Who is Participating?
 
OveCommented:
then there are NO database and table-definitions inside you "backup" :-(
So you wont be able to restore the DB

Ove
0
 
OveCommented:
did you have a look at the database dir or at cpu if anything is happening on the system?

ove
0
 
billium99Author Commented:
Thanks - yeah Heidi SQL shows no change in anything. There is no version of this database currently present in Heidi SQL. When I hit enter. the subsequent prompt is instantaneous.

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
arnoldCommented:
First, the -p requires the password to be attached to it i.e. -ppassword
it all depends on what you have in the sql file.
and how you determine that there is nothing going on.
cat spw_online_may_11_2011.sql | mysql -u spw_web_user --password=password spw_online

You may have in the sql explicit reference to the database into which this data is to be entered i.e. it has use database1;
at which point the command line reference to spw_online will be ignored.
0
 
billium99Author Commented:
Ok when I do this, again, nothing happens. I get the -> prompt, CPU and Memory do not budge on the machine. This is a 120mb db, so I would think there would be some processing required here that I could see:

cat spw_online_may_11_2011.sql | mysql -u spw_web_user -ppassword spw_online

Did you intend for me to paste that in?

Should I mention I'm a complete My SQL newb? Right now I have that .sql file in bin directory under My SQL in Program Files. Is there a different place I should put it? Wouldn't it tell me it couldn't find it in the CMD window?

Thanks

Bill
0
 
billium99Author Commented:
I pasted

cat spw_online_may_11_2011.sql | mysql -u spw_web_user -ppassword spw_online

with the right password and same thing - instantaneous -> with no other activity
0
 
arnoldCommented:
What is in the file?

Are there insert statements?

what changes are you expecting to see?
depending on the table, you can have a 120mb file but all it containst are two inserts for two rows.

0
 
billium99Author Commented:
This is an export of about 20 tables, some of which have 100,000 records or more.

I created the SQL file as a backup from Heidi SQL, and have restored it once a long time ago.

But in this case, I'm aiming to take this file and recreate the database entirely on this local instance of MySQL. So my expectation, is that HeidiSQL would start showing these tables if something happened during my Command Line adventures. But instead, nothing happens.

If I try to import via HeidiSQL, I get timeouts, because of the size I'm guessing. Their own documentation indicates as much and recommends the command prompt route.

Bill
0
 
arnoldCommented:
Does the database exist on the local database?
Are there create statements in the file?
You are using the sfw_online as the database into which these are supposed to be inserted?

I gather the file is the result of running mysqldump?
0
 
billium99Author Commented:
The file is a product of HeidiSQL's export function. The database does not exist yet, locally.
0
 
arnoldCommented:
Head -10 spw_online_may_11_2011.sql  

This will grab the first 10 lines from the files.
I need to see whether there are use, create database, create table directives?

What is sfw_online that you reference as the database into which the data will be pumped in the absence of use databasename in the file itself.

IS the user that you are using spw_web_user have the necessary rights for database/table creation?
Does the behavior change if you use root as the user
cat spw_online_may_11_2011.sql  | mysql -u root --password=password


0
 
OveCommented:
did you drop the db before running the sql-import or is the db not alive atm ?

Ove
0
 
billium99Author Commented:
OK

Head -10 spw_online_may_11_2011.sql  

This did nothing. Just:

->

Is that because my file is in the wrong location? Why am I not getting errors?

Ove the database is not alive on this My SQL instance.

Thanks again

Bill
0
 
OveCommented:
Head -10 spw_online_may_11_2011.sql  

gives nothing ????????????

oops ...
what about
Head -1000 spw_online_may_11_2011.sql  
???

Ove

0
 
billium99Author Commented:
OK here is a paste of the last few activities I've tried. Any idea what is happening here?

    -> \c
mysql> cat spw_online_may_11_2011.sql | mysql -u spw_web_user -ppassword spw_onl
ine
    ->
    -> \c
mysql>
mysql> Head -10 spw_online_may_11_2011.sql
    ->
    -> Head -10 spw_online_may_11_2011.sql
    -> Head -10 spw_online_may_11_2011.sql
    -> \c
mysql> Head -1000 spw_online_may_11_2011.sql
    ->


???

Thanks

Bill
0
 
billium99Author Commented:
If I haven't created a database named spw_online in the My SQL instance, will this work? I assumed the creation of the database would occur during import of the .sql content...

So why would the Head command return no result? The export from the live database went flawlessly as it usully does, so I'm pretty confident the .sql file itself is fine.

But what if I have the sql file in the wrong place? Would it report that it can't find the file, or would it just sit dumbly? Where is it expecting the file to be? Being explicit about the path didn't work anyway - same result.

Don't forget, I could have (and probably did) missed something really simple.

I installed My SQL, left the root with no password, then installed Heidi SQL which happily connects to the instance and shows a test table.

But I'm not really sure what to expect.

Bill
0
 
OveCommented:
what i'm wondering about is the fact, that the is no data on the first 1000 lines of your sql-file.
depending on your creation of your sql-file the db should be recreated when restoring from the file to the db.

to have a look into your sql-file do a:
cat spw_online_may_11_2011.sql |less

Then scroll down the file with PgDN.

Do you see any data in there ???

Ove
0
 
billium99Author Commented:
I just think it's not even looking at the .sql file because I just pasted your code and it instantaneously returned just:

->

I mean if it's going to the db I would expect maybe a split second of something happening, but it's like I'm entering instructions in notepad and hitting enter.

Just nothing...

Bill
0
 
OveCommented:
you may try to include the full path-information of the sql-file when using "cat", "head".
Any success ?

Ove
0
 
billium99Author Commented:
OK I typed cat C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql

and now I get:

ERROR:
Unknown command '\P'.
ERROR:
Unknown command '\M'
ERROR:
Unknown command '\M'
ERROR:
Unknown command '\b'

I've attached an image of what I'm seeing, in case it provides any clues...

sample CMD screen
0
 
OveCommented:
aaahh...moment :-)

i thought you're using a "normal" shell access....not the MySQL Commandline Client!

SO: the sql-file resides on y windows machine, right ?
The mysql-server also resides on that windows-machine, right ?

So pls to START -> run -> cmd -> ENTER -> type "C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql"

this should show you your sql-file. You may interrupt the running-lines via Ctrl + c

Ove
0
 
billium99Author Commented:
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Bill>cd..

C:\Users>cd..

C:\>Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql
'Program' is not recognized as an internal or external command,
operable program or batch file.

C:\>C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.

C:\>run C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql
'run' is not recognized as an internal or external command,
operable program or batch file.

C:\>

Any ideas?

Thanks

Bill
0
 
arnoldCommented:
The commands for head were to be run in the shell and not within mysql.

OK you are on a windows system.

Get workbench GUI tool from http://dev.mysql.com/downloads/workbench/5.2.html

You have to be in the directory where the file is and make sure that mysql is in the PATH.
more "C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql" | mysql -u root
and see what happens (if you set root password add a --password=password to the above line.)

The problem Is that I have no idea when you exported the database which options you used. Some tools provide for the use/create/drop directives as part of the backup.

alternatively, you can run the file from within mysql:
mysql -u root -ppassword
mysql> source "C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql";
and see what you get.
0
 
OveCommented:
ahh....you should als enter the word "type"....

C:\>type "C:\Program Files\MySQL\MySQL Server 5.5\bin\spw_online_may_11_2011.sql"

like above :-)

Ove

0
 
billium99Author Commented:
ok - when i do that, every record starts with REPLACE INTO "tablenames"
0
 
OveCommented:
and at the beginning of the file anything like this:

DROP TABLE IF EXISTS `columns_priv`;
CREATE TABLE `columns_priv` (
  `Host` char(60) collate utf8_bin NOT NULL default '',
  `Db` char(64) collate utf8_bin NOT NULL default '',
  `User` char(16) collate utf8_bin NOT NULL default '',
  `Table_name` char(64) collate utf8_bin NOT NULL default '',
  `Column_name` char(64) collate utf8_bin NOT NULL default '',
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') character set utf8 NOT NULL default '',
  PRIMARY KEY  (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';


Ove
0
 
billium99Author Commented:
Hmm no - the file starts with:

'0', '0',0,1);

REPLACE INTO "first table name"

0
 
arnoldCommented:
is there an entry that deals with drop database if exists ''
create database?
Did you try the source method I referenced?
you may need to do the use sfw_online; prior to sourcing the file which will execute its contents.
0
 
arnoldCommented:
Mixed up the drop/create from Ove's comments.
You have to create the database/tables and then you can restore the data.
0
 
OveCommented:
sounds to me that he doesn't have the data to create the db & tables anymore...

Ove
0
 
arnoldCommented:
I am unfamiliar with the Heidi MYSQL client interface, and perhaps what you did was export the data only rather than include the DB schema/structure.
Do you still have access to the database where the data is?
running
show create table 'tablename'
will get you the information you need to recreate the structure and then run this file to import the data.
0
 
billium99Author Commented:
Hmm - yes that is odd.

I'm recreating the backup now and confirmed in the UI the IF Exsts, and Create commands are all present in the pending output, so I'll try again later this evening. Thanks for all the help guys. I'll get an update posted asap.

Bill
0
 
billium99Author Commented:
Never figured this out but sorry I left the question open like this...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.