Create MySQL Database with .SQL File

Question

I don't know much about MySQL at all. But I am trying to reverse engineer a MySQL database using Visio. I know what steps I should take to do this, but I can't get my database to show in the 'Databases' section (as below):

How do I create the MySQL database using the .SQL file and get it to show up in this list? I have tried this code: mysql -u username -p password database_name < filename.sql using my own credentials of course. But that doesn't seem to work. In what folder should the .SQL file be placed if this statement is to work?

Answer 1

1) Create a file "filename.sql"

2) Create a database in your DB in which you want to import this file.

3) From command-prompt/terminal, move to the directory where you have created a "filename.sql".

4) Run the command: mysql -u username -p password database_name < filename.sql. (You can also give the proper path of your file and run this command from anywhere). It might be the case that you don't have a password set for MySQL. If so, mysql -u username database_name < filename.sql will also work.

In your case if you have created a database with name ojs and also created a file with name ojs.sql in C: drive then run the following command:

Edit: Put the path inside quotes.

mysql -u username -p password ojs < "C:\ojs.sql"

There is another way of importing tables in mysql. You can do it this way as well:

1) Connect your database

2) Type command "use ojs;"

3) Type command "source C:/ojs.sql"

Answer 2

Most MySQL SQL files that create databases create the database 'on-the-fly', so you typically needn't do anything except:

1. log-in

   mysql -u [username] -p[password]

(Note: make sure you do NOT include a space (' ') character between the -p and the [password]. MySQL will think that [password] is the name of the database you want to connect to. The 'general' log-in (above) does not assume you want to connect to any particular schema.)

1. source the file (do not use quotes around filename)

   mysql> source [database_creation_file].sql