Create a database using MySQL batch file (I think)

I'm a total MySQL beginner, working my way through Joshua Mostafa's (very good) MySQL video tutorial course on VTC.com  I'm running MySQL on Mac OS X 10.5.8 and I can access the MySQL Monitor via Terminal to manually create and manipulate very basic databases.

The VTC course comes with a set of work files which appear to be batch files that create sample databases to use during the course.  Unfortunately, Joshua doesn't cover how to use these files to create databases on a Mac.  He's using a PC and says do the following at the command line:

C:\mysql\bin>mysql filename.sql

This obviously doesn't work on a Mac, so I've got a few questions:

1.  Where should I drag and drop the 'filename.sql' batch file?  (my 'usr/local/mysql/bin' directory is full of stuff and none of them are *.sql files and when I try to drop it in there it says I shouldn't modify that directory)

2.  What is the correct thing to type into Terminal to make the file generate the database?

Any help is greatly appreciated!
LVL 8
TocacarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis AnifantakisProgramming InstructorCommented:
Go and download the mysql workbench

http://www.mysql.com/downloads/workbench/

use the Database Administration (replacing MySQL Administrator)

From there you have to "restore database" and will be asked for a filename.  This is the file you will pass there to restore the backed up database.  After that you will have this database available with all the tables and triggers and views.
0
TocacarAuthor Commented:
ioannisa,

Thank you for your answer.  As a total beginner I'm trying to keep things 'bare bones' so I can learn the basics.  I will eventually use a tool to administer my databases, but it would be great to find a way to do this via Terminal first.  Do you know how I can do this without any additional software (the Mac equivalent of the PC way described above)?  Or is it just not possible on a Mac?

Thanks again for your help.  I do appreciate it.
0
Ioannis AnifantakisProgramming InstructorCommented:
The solution to restore large mysql database from sql dump file is using unix/linux shell command.
To restore mysql database from a dump file, just type the command below:-

mysql -u #username# -p #database# < #dump_file#

Of course you need to replace #username# to your database username and #database# to your target database. and rename #dump_file# to your dump file file name (Ex: dump.sql) Once you enter thecommand, the linux/unix shell will prompt you for your database user password, just key in your database password and you are done.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Ioannis AnifantakisProgramming InstructorCommented:
Also the workbench is available for mac.
If you visit my link, you will see there is a combobox with the available operating systems.

You can pick the MacOS and get it.
I strongly suggest that you make good use of the graphical tools provided by mysql.
0
TocacarAuthor Commented:
I'm not trying to restore anything, I'm just trying to build a sample database from the file for the first time.  I just tried your suggestion (and the file 'sample.sql' was on my desktop) and got the following:

-bash: sample.sql: No such file or directory

I tried it twice because I don't have anything to enter for #database# so I tried using 'sample' here (which is what I imagine the database will be called) and I tried it again with nothing there; both gave the above error.
0
Ioannis AnifantakisProgramming InstructorCommented:
Try that

mysql -u #username# -p < #dump_file#
0
TocacarAuthor Commented:
I did try that, but got the same error.  

I just changed my Terminal prompt to the Desktop (where the 'sample.sql' file was sitting) and was prompted to enter my password.  But then it said:

ERROR 1046 (3D000) at line 15: No database selected

I really appreciate your help with this!
0
Ioannis AnifantakisProgramming InstructorCommented:
I pasted you my exact code, but I had my file inside the bin directory of mysql.
Try to copy it inside the bin directory of mysql (where your mysql.exe exist) and tell me if it fails.

If it does fail, then simply the file is either corrupted or something.
Thats all you have to do.
0
Ioannis AnifantakisProgramming InstructorCommented:
if you keep getting problems, then attach the file here and I will look at it tomorrow (cause now I have to leave office) and will let you know if there's some problem with the file or something
0
TocacarAuthor Commented:
Great!  Thank you.  I've attached the file here for you to see.  I've increased the points to 500 as well as this is taking up more of your time than I had anticipated.

I tried moving the sample.sql file to the /usr/local/mysql/bin directory as you suggested and had to authenticate to allow it to go in.  All the other stuff in there looks like small black rectangles and none of them have any suffixes on the file names.  Anyhow, I got the file in there, then changed my Terminal prompt to that directory and ran:

mysql -u root -p < sample.sql

...entered my password, then got the same error (ERROR 1046 (3D000) at line 15: No database selected).

My sample.sql file is attached.  
sample.sql
0
Ioannis AnifantakisProgramming InstructorCommented:
wait, I'll give it a look, hold on 5 mins
0
Ioannis AnifantakisProgramming InstructorCommented:
OK!!!

I saw the file, its not by the standard mysql backup file, thats why this is the problem.

The standard mysql backup contains the "create database" part, that this backup has left out.

This backup assumes that you already have a database in your system called sample and you simply  restore the tables and their data to the database sample.

But you don't have a "sample" database do you? :)

To make this database follow my steps

1) mysql -u #username# -p
you will log into mysql and you will see something like
mysql>

There you type
2) create database sample;

that will create the database
Now you have to leave the shell so type

3) exit;

Now you are back to normal prompt

From there u simply do what I suggested above, that is
4) mysql -u #username# -p < #dump_file#

I did that on your file and it worked fine for me
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocacarAuthor Commented:
Thank you very much!  

The first quick run through didn't work, but I'm going to keep plugging away for a bit and see if I can get it to work (I was rushing because I know you want to get home).  I'm sure it will work.  I'll send another message to let you know (and award the points).  Thanks again for all your help!
0
Ioannis AnifantakisProgramming InstructorCommented:
I assumed that the "create database" part existed in your file, but it didnt :*(
0
Ioannis AnifantakisProgramming InstructorCommented:
0
Ioannis AnifantakisProgramming InstructorCommented:
0
TocacarAuthor Commented:
Excellent.  I'm setting up my first (non-root) account now following the first of the two links you just sent.  I'm sure this will be working very soon.  Thanks again.
0
TocacarAuthor Commented:
YAAAAAY!!!  It works.  Thanks very much, I've learned a lot.  Awarding points now!
0
TocacarAuthor Commented:
Very grateful for your patience.  Thanks.
0
Ioannis AnifantakisProgramming InstructorCommented:
No problem brother.
Also for others that might find some problem in all this, I made a small illustration and I paste it bellow
steps.jpg
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.