jameskane
asked on
How to import sql database into mysql workbench
Several months ago I sucessfully imported an sql database to the workbench. I am attaching an image of it. Unfortunately my experience with workbench is limited and I have forgotton how I did it.
I now have another sql database which I need to import to the workbench. Like the first one, it was generated by a conversion tool (access to mysql) and saved in a dump file as officenew.sql. I then attempted to import it to the workbench and have not succeeded.
The last two images attached show the workbench environment as I tried to import and the error message.
It strikes me that I have tried to import data - wheras I really want to import the complete database (data plus schema). Seems like I am not using the correct facility in workbench ??
Thank you for your help .
workbenchImage.JPG
access_mysql_converted-image.JPG
newdatabase.JPG
DataImport.JPG
ERROR-MESSAGE.JPG
I now have another sql database which I need to import to the workbench. Like the first one, it was generated by a conversion tool (access to mysql) and saved in a dump file as officenew.sql. I then attempted to import it to the workbench and have not succeeded.
The last two images attached show the workbench environment as I tried to import and the error message.
It strikes me that I have tried to import data - wheras I really want to import the complete database (data plus schema). Seems like I am not using the correct facility in workbench ??
Thank you for your help .
workbenchImage.JPG
access_mysql_converted-image.JPG
newdatabase.JPG
DataImport.JPG
ERROR-MESSAGE.JPG
ASKER
Thanks for the reply Dan.
Could you give me a few words/pointer related to the solution?
thanks
Could you give me a few words/pointer related to the solution?
thanks
It's the simplest way to import a MySQL dump. Just open a command prompt and use that command. It will run mysql, ask for the root password and then execute all commands inside that sql file.
You can open the file with Notepad or another text editor and look at the commands.
You can open the file with Notepad or another text editor and look at the commands.
ASKER
ASKER
Hi Dan,
I am attaching two attempts at using the command line. In each case , on hitting return, the dialogue ends with an underscore.
In the second try I tired to enter the name of the database I wanted in additon to the name of the dump file.
Obviously doing something stupid I'm afraid
commandline.JPG
commandlineeeeeeeeeeeeee.JPG
I am attaching two attempts at using the command line. In each case , on hitting return, the dialogue ends with an underscore.
In the second try I tired to enter the name of the database I wanted in additon to the name of the dump file.
Obviously doing something stupid I'm afraid
commandline.JPG
commandlineeeeeeeeeeeeee.JPG
ASKER
Hi Dan,
just sent you a further two images showing the homepage of the workbench and command line checking for connections to databases.
One thing which suprises me is that when I create a new schema, I automatically get ALL the databases I created in each of them. I expected that on creating a new schema, I would have no databases to begin with ????
just sent you a further two images showing the homepage of the workbench and command line checking for connections to databases.
One thing which suprises me is that when I create a new schema, I automatically get ALL the databases I created in each of them. I expected that on creating a new schema, I would have no databases to begin with ????
you forgot the spaces
mysql <space> -uroot <space> -p <space> < <space> officenew.sql
mysql <space> -uroot <space> -p <space> < <space> officenew.sql
ASKER
Re entered into command line (attached) . It is still looking for more information - does it need pointer address to officenew.sql ?
commandline2.JPG
commandline2.JPG
OK, let's take it from the beginning:
Start CMD (Windows->Run->CMD)
Switch to the folder that contains the sql file:
Start CMD (Windows->Run->CMD)
Switch to the folder that contains the sql file:
cd c:\Utilisateurs\Win7\Documents\dumps <Enter>
Run the command:mysql -u root -p < officenew.sql <Enter>
ASKER
I was unable to switch to the folder
c:\Utilisateurs\Win7\Docum ents\dumps
(probably due to fact that I am using french computer and the Utilisateurs used instead of USERS)
I moved the officenew.sql file to a new dumps folder directly under C and the problem went away.
I am attaching an image relating to changing to the folder dumps as you suggested. I then tried the mysql - u root -p<officenew.sql - but this did not work.
I then used the command line client (image attached) - but it is looking for more information ???
commandline2.JPG
commandline1.JPG
c:\Utilisateurs\Win7\Docum
(probably due to fact that I am using french computer and the Utilisateurs used instead of USERS)
I moved the officenew.sql file to a new dumps folder directly under C and the problem went away.
I am attaching an image relating to changing to the folder dumps as you suggested. I then tried the mysql - u root -p<officenew.sql - but this did not work.
I then used the command line client (image attached) - but it is looking for more information ???
commandline2.JPG
commandline1.JPG
ASKER
This is another try using mysql command line client and the two lines you gave me. Still does not complete - waiting for further input ?
Please note, I changed the position of the dumps folder and its officenew.sql file for convenience. See image commandline4
commandline3.JPG
commandline4.JPG
Please note, I changed the position of the dumps folder and its officenew.sql file for convenience. See image commandline4
commandline3.JPG
commandline4.JPG
OK, the right way is in commandline1. Where it says mysql is not recognized as a command.
But it looks like you don't have mysql added to the path?
Try this (just copy and paste it by right clicking on the black window):
But it looks like you don't have mysql added to the path?
Try this (just copy and paste it by right clicking on the black window):
"c:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u root -p < C:\dumps\officenew.sql
ASKER
Attached is latest try. Copied and pasted as you suggested. Seems to have moved forward, but not there yet !!
Thanks very much for taking the time with this tedious support request - really appreciate it.
AAAlatesttry.JPG
Thanks very much for taking the time with this tedious support request - really appreciate it.
AAAlatesttry.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic !!!!!!
Problem solved.
Thanks very much for your solution, your patience and the learning I received.
James
Problem solved.
Thanks very much for your solution, your patience and the learning I received.
James
ASKER
PROBLEM SOLVED !
Many thanks for your solution, your patience and the learning I received.
James
Many thanks for your solution, your patience and the learning I received.
James
You're welcome.
Glad I could help!
Glad I could help!
HTH,
Dan