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!
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!
ASKER
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.
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.
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.
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.
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.
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.
ASKER
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.
-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.
Try that
mysql -u #username# -p < #dump_file#
mysql -u #username# -p < #dump_file#
ASKER
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!
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!
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.
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.
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
ASKER
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
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
wait, I'll give it a look, hold on 5 mins
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
I assumed that the "create database" part existed in your file, but it didnt :*(
ASKER
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.
ASKER
YAAAAAY!!! It works. Thanks very much, I've learned a lot. Awarding points now!
ASKER
Very grateful for your patience. Thanks.
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
Also for others that might find some problem in all this, I made a small illustration and I paste it bellow
steps.jpg
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.