Still having difficulty running MySQL's 'source' command

[MLH]

I am having failures processing the following command and I wonder
if you can tell me what I must do in order to have success. When I try
to run source mysql_dump.sql. txt ==> it is a problem for me.

1) I put the file in /home/mlh/public_html/credifree/sql_script/.
2) I made that directory my current directory
3) I typed mysql and pressed ENTER
4) I then typed source mysql_dump.sql. txt and pressed ENTER

A bunch of error messages saying
ERROR 1046: No Database Selected
filled up the screen.

Well, of course there was no database selected. I do not yet have a
database. I thought the whole point of running
source mysql_dump.sql. txt
was to CREATE a set of database files on my linux box. Is that
incorrect? What must I do to avoid this error 1046?


And now, the last question...
Can you tell me where the database files will be placed when running
source mysql_dump.sql. txt as I described above from the same current
directory as described above? I do wish to KNOW in advance where the
files will be created.

[Chris Hope]

Re: Still having difficulty running MySQL's 'source' command

MLH wrote:
[color=blue]
> I am having failures processing the following command and I wonder
> if you can tell me what I must do in order to have success. When I try
> to run source mysql_dump.sql. txt ==> it is a problem for me.
>
> 1) I put the file in /home/mlh/public_html/credifree/sql_script/.
> 2) I made that directory my current directory
> 3) I typed mysql and pressed ENTER
> 4) I then typed source mysql_dump.sql. txt and pressed ENTER
>
> A bunch of error messages saying
> ERROR 1046: No Database Selected
> filled up the screen.
>
> Well, of course there was no database selected. I do not yet have a
> database. I thought the whole point of running
> source mysql_dump.sql. txt
> was to CREATE a set of database files on my linux box. Is that
> incorrect? What must I do to avoid this error 1046?[/color]

Presumably your file doesn't have any create syntax in it so you need to
create the database first like so:

create database <database name>;
use <database name>;
source mysql_dump.sql. txt;
[color=blue]
> And now, the last question...
> Can you tell me where the database files will be placed when running
> source mysql_dump.sql. txt as I described above from the same current
> directory as described above? I do wish to KNOW in advance where the
> files will be created.[/color]

Depends on the configuration of MySQL. On my machine they're
in /var/lib/mysql. Why do you want to know where the files are though?
You won't be able to do anything with them because they're binary
files. If you want to back up your database you should use the
mysqldump command.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Bill Karwin]

Re: Still having difficulty running MySQL's 'source' command

MLH wrote:[color=blue]
> I thought the whole point of running
> source mysql_dump.sql. txt
> was to CREATE a set of database files on my linux box. Is that
> incorrect? What must I do to avoid this error 1046?[/color]

Some dump files contain the "create database ..." command in them, but
this is optional. It depends on whether the person who produced the
dump file chose that option.

If the script doesn't do it for you, you can create an empty database
and then run the script to create tables and data within that database.

Read this page from the tutorial, and its subsections:

http://dev.mysql.com/doc/mysql/en/database-use.html

I really recommend that you start reading the documentation, especially
the tutorial, to which I have referred you twice. MySQL docs are better
than many, and they can answer a lot of these beginning issues for you
much more quickly than newsgroups can. Newsgroups are best for sticky
issues that aren't covered well in docs.
[color=blue]
> mysql databasename < directory/containing/script/mysql_dump.sql. txt
> I tried it. I got an error msg. Here is the error...
> ERROR 1044: Access denied for user: '@localhost' to database 'mydatabase'[/color]

This most likely indicates that on your machine, anonymous database
connections have been disabled; you must therefore specify a username
when connecting to a MySQL database, using the "-u username" option.
[color=blue]
> ERROR 1045: Access denied for user: 'mlh@localhost' (Using Password:[/color]
YES)[color=blue]
> The mlh user is my non-root linux userID.[/color]

MySQL usernames have nothing to do with operating system logins. You
can create a MySQL username that is the same as your Linux login, but it
is not present by default.

Read this page for more information on creating usernames in MySQL:

http://dev.mysql.com/doc/mysql/en/adding-users.html

[color=blue]
> And now, the last question...
> Can you tell me where the database files will be placed when running
> source mysql_dump.sql. txt as I described above from the same current
> directory as described above? I do wish to KNOW in advance where the
> files will be created.[/color]

The location of your data directory depends on your system
configuration. It varies from installation to installation where the
physical files are kept.

And in any case, there's no way to infer the data directory from the
path where your script is.

All MySQL databases on a given host are stored under a location known as
the data directory, or datadir. Each database's files are stored in a
subdirectory off the datadir, according to the name you define for the
database. You can ask MySQL where its data directory is currently defined:

mysql> show variables like 'datadir';

Regards,
Bill K.

[MLH]

Re: Still having difficulty running MySQL's 'source' command

On Sun, 27 Mar 2005 06:39:58 +1200, Chris Hope
<blackhole@elec trictoolbox.com > wrote:
[color=blue]
>MLH wrote:
>[color=green]
>> I am having failures processing the following command and I wonder
>> if you can tell me what I must do in order to have success. When I try
>> to run source mysql_dump.sql. txt ==> it is a problem for me.
>>
>> 1) I put the file in /home/mlh/public_html/credifree/sql_script/.
>> 2) I made that directory my current directory
>> 3) I typed mysql and pressed ENTER
>> 4) I then typed source mysql_dump.sql. txt and pressed ENTER
>>
>> A bunch of error messages saying
>> ERROR 1046: No Database Selected
>> filled up the screen.
>>
>> Well, of course there was no database selected. I do not yet have a
>> database. I thought the whole point of running
>> source mysql_dump.sql. txt
>> was to CREATE a set of database files on my linux box. Is that
>> incorrect? What must I do to avoid this error 1046?[/color]
>
>Presumably your file doesn't have any create syntax in it so you need to
>create the database first like so:
>
>create database <database name>;
>use <database name>;
>source mysql_dump.sql. txt;
>[/color]
You are correct! There were lots of instances of CREATE TABLE but none
of CREATE DATABASE in the dump file. I tried to do what you said. I
typed mysql under my user login and pressed ENTER. The mysql> command
prompt appeared. I typed create database mydb and pressed ENTER. Here
is the error I got...
ERROR 1044: Access denied for user: '@localhost' to database 'mydb'

So, I exit'd mysql back to linux command prompt and logged in as the
super user and tried again. Here's the error I got typing mysql and
pressing ENTER...
ERROR 1045: Access denied for user: 'root@localhost ' (Using password:
NO)
It seems I can't win for losing!

[color=blue][color=green]
>> And now, the last question...
>> Can you tell me where the database files will be placed when running
>> source mysql_dump.sql. txt as I described above from the same current
>> directory as described above? I do wish to KNOW in advance where the
>> files will be created.[/color]
>
>Depends on the configuration of MySQL. On my machine they're
>in /var/lib/mysql. Why do you want to know where the files are though?
>You won't be able to do anything with them because they're binary
>files. If you want to back up your database you should use the
>mysqldump command.[/color]

Its a force of habit, really - from the old DOS days. If it is not
important, then I won't lose any sleep over it. But I would be curious
to know what files are created and where.

[MLH]

Re: Still having difficulty running MySQL's 'source' command

<snip>[color=blue]
>I really recommend that you start reading the documentation, especially
>the tutorial, to which I have referred you twice. MySQL docs are better
>than many, and they can answer a lot of these beginning issues for you
>much more quickly than newsgroups can. Newsgroups are best for sticky
>issues that aren't covered well in docs.[/color]
Could you refer it one more time, just for good measure? <grin> I'm
only kidding. I appreciate you suggestions and I did read it the first
time. I believe the problem I should have asked for help on is likely
a security issue more basic than not being able to run the source
command. Please pardon my ignorance in both the linux and MySQL
environments as well as this darned newsreader of mine.
[color=blue]
>[color=green]
> > mysql databasename < directory/containing/script/mysql_dump.sql. txt
> > I tried it. I got an error msg. Here is the error...
> > ERROR 1044: Access denied for user: '@localhost' to database 'mydatabase'[/color]
>
>This most likely indicates that on your machine, anonymous database
>connections have been disabled; you must therefore specify a username
>when connecting to a MySQL database, using the "-u username" option.
>[/color]
Thx Bill.
[color=blue][color=green]
> > ERROR 1045: Access denied for user: 'mlh@localhost' (Using Password:[/color]
>YES)[color=green]
> > The mlh user is my non-root linux userID.[/color]
>
>MySQL usernames have nothing to do with operating system logins. You
>can create a MySQL username that is the same as your Linux login, but it
>is not present by default.
>
>Read this page for more information on creating usernames in MySQL:
> http://dev.mysql.com/doc/mysql/en/adding-users.html
>[/color]
Yes, I will read that section. The guy I bought the server from loaded
with linux, Apache, PHP and MySQL. I am uncertain what outstanding
'do-items' might still remain pertinent to the MySQL installation. Its
likely that some 'holes' exist that I'm not aware of. Tasks I'm
attempting now are probably reliant on certain prerequisites having
been completed. I'm working backwards to find out what wasn't
done that still needs to be done.
[color=blue][color=green]
>> And now, the last question...
>> Can you tell me where the database files will be placed when running
>> source mysql_dump.sql. txt as I described above from the same current
>> directory as described above? I do wish to KNOW in advance where the
>> files will be created.[/color]
>
>The location of your data directory depends on your system
>configuratio n. It varies from installation to installation where the
>physical files are kept.
>
>And in any case, there's no way to infer the data directory from the
>path where your script is.
>
>All MySQL databases on a given host are stored under a location known as
>the data directory, or datadir. Each database's files are stored in a
>subdirectory off the datadir, according to the name you define for the
>database. You can ask MySQL where its data directory is currently defined:
>
> mysql> show variables like 'datadir';
>
>Regards,
>Bill K.[/color]
10-4. I appreciate your comments, Bill.