Where does mysql put database files and how does it name them?

[MLH]

I have a database named credifree. I did it with mysql's create
command. Yet, I can find no files on my linux box except for a\
directory and a gif file that have the name credifree. Why? What's
the name mysql might have made a database file under? Where is
it stored? Just curious.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxx
mlh@appserver mlh $ mysql / -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use credifree
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_credifree |
+------------------------+
| paylog |
| secure_pdf |
| systemlog |
| tbiccreceiptsefx |
| tbiccreceiptsexp |
| tbiccreceiptstu |
| tbladdrchangesefx |
| tbladdrchangesexp |
| tbladdrchangestu |
| tbladdressesequifax |
| tbladdressesexperian |
| tbladdressestransunion |
| tblaftermath |
| tblbureaus |
| tblcfclientchanges |
| tblcfnegchanges |
| tblcr_types |
| tblcredifreeclients |
| tblcredifreenegs |
| tblcreditreports |
| tblhits |
| tblinboundtypes |
| tblinstructions |
| tblmemberlogins |
| tblmerchandise |
| tbloutboundtypes |
| tblprefixes |
| tblreasons |
| tblremoved |
| tblsales |
| tblsleepers |
| tblsuffixes |
+------------------------+
32 rows in set (0.00 sec)

mysql> select * from tblhits;
+------------+----------+
| DateHit | HitValue |
+------------+----------+
| 0000-00-00 | 79417 |
| 2004-09-30 | 76 |
| 2004-10-01 | 16 |
| 2004-10-04 | 11 |
| 2004-10-06 | 24 |
| 2004-10-07 | 20 |
| 2004-10-08 | 37 |
| 2004-10-10 | 9 |
| 2004-10-11 | 4 |
| 2004-10-12 | 19 |
| 2004-10-13 | 13 |
| 2004-10-14 | 7 |
| 2004-10-15 | 8 |
| 2004-10-16 | 30 |
| 2004-10-17 | 31 |
| 2004-10-18 | 26 |
| 2004-10-19 | 3 |
| 2004-10-21 | 9 |
| 2004-10-22 | 10 |
| 2004-10-23 | 5 |
| 2004-10-24 | 7 |
| 2004-10-25 | 21 |
| 2004-10-26 | 12 |
| 2004-10-27 | 4 |
| 2004-10-28 | 4 |
| 2004-10-29 | 7 |
| 2004-10-30 | 4 |
| 2004-10-31 | 20 |
| 2004-11-01 | 11 |
| 2004-11-02 | 4 |
| 2004-11-03 | 4 |
| 2004-11-04 | 7 |
| 2004-11-07 | 1 |
| 2004-11-08 | 18 |
| 2004-11-09 | 11 |
| 2004-11-10 | 38 |
| 2004-11-11 | 41 |
| 2004-11-13 | 1 |
| 2004-11-15 | 18 |
| 2004-11-16 | 11 |
| 2004-11-17 | 9 |
| 2004-11-18 | 7 |
+------------+----------+
42 rows in set (0.00 sec)

mysql> exit
Bye
mlh@appserver mlh $ find / -name '*credifree*' 2>/dev/null
/home/mlh/public_html/credifree
/home/mlh/public_html/credifree/images/credifree.gif
mlh@appserver mlh $ exit

[Chris Hope]

MLH wrote:

I have a database named credifree. I did it with mysql's create
command. Yet, I can find no files on my linux box except for a\
directory and a gif file that have the name credifree. Why? What's
the name mysql might have made a database file under? Where is
it stored? Just curious.

[snip]

From the mysql command prompt enter "show variables" and look for one
named "datadir". That will tell you where the data is stored.

The reason that "find" would not find the files for you is because the
directory where the data is stored is only readable by the user and
group that mysql runs as; because your normal login would not have
these rights you cannot see inside that directory to find files unless
you are that user, or the root user.

In the data directory there is a directory for each database. Depending
on the database format there will be several files for each table named
after the table name. For MyISAM type tables there will be three files
for each table ending with .myi .myd and .frm If you want to know what
each of these files are for then read the mysql manual.

As I have posted in reply to one of your other messages, if you are
wanting to do database backups you should use the mysqldump command and
not try to back these files up.

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

[MLH]

Thank-you, Chris. You helped eliminate some of the mystery for me.
I really do have a bad case of tunnel vision. I know so little about
the ins & outs of linux #AND# mysql. I intend to follow your advice
regarding backup needs and use the dump facility to do so.

Thx again!

BTW, I'm not too good with newsgroups either and I might have
reposted this same Q again. I'll probably get burned for it, but it
was an honest mistake. Tsk. Tsk. Tsk. I'm learning every day.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx

On Wed, 30 Mar 2005 10:33:23 +1200, Chris Hope
<bl*******@electrictoolbox.com> wrote:

MLH wrote:

I have a database named credifree. I did it with mysql's create
command. Yet, I can find no files on my linux box except for a\
directory and a gif file that have the name credifree. Why? What's
the name mysql might have made a database file under? Where is
it stored? Just curious.

[snip]

From the mysql command prompt enter "show variables" and look for one
named "datadir". That will tell you where the data is stored.

The reason that "find" would not find the files for you is because the
directory where the data is stored is only readable by the user and
group that mysql runs as; because your normal login would not have
these rights you cannot see inside that directory to find files unless
you are that user, or the root user.

In the data directory there is a directory for each database. Depending
on the database format there will be several files for each table named
after the table name. For MyISAM type tables there will be three files
for each table ending with .myi .myd and .frm If you want to know what
each of these files are for then read the mysql manual.

As I have posted in reply to one of your other messages, if you are
wanting to do database backups you should use the mysqldump command and
not try to back these files up.

[MLH]

[snip]
The output I get appears to be several screens full. I tried piping it
to more and then to less. I found out real quick that those utilities
are useless in mysql. But there must be a way to capture and
scroll through the full text of the output from 'show variables'
(passes off the screen so quickly I have no idea just how much
there really is)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx

From the mysql command prompt enter "show variables" and look for one
named "datadir". That will tell you where the data is stored.

The reason that "find" would not find the files for you is because the
directory where the data is stored is only readable by the user and
group that mysql runs as; because your normal login would not have
these rights you cannot see inside that directory to find files unless
you are that user, or the root user.

In the data directory there is a directory for each database. Depending
on the database format there will be several files for each table named
after the table name. For MyISAM type tables there will be three files
for each table ending with .myi .myd and .frm If you want to know what
each of these files are for then read the mysql manual.

As I have posted in reply to one of your other messages, if you are
wanting to do database backups you should use the mysqldump command and
not try to back these files up.

[jerry gitomer]

MLH wrote:

[snip]
The output I get appears to be several screens full. I tried piping it
to more and then to less. I found out real quick that those utilities
are useless in mysql. But there must be a way to capture and
scroll through the full text of the output from 'show variables'
(passes off the screen so quickly I have no idea just how much
there really is)

[snip]

run MySQL in a terminal window with a scrollbar. You will then
be able to scroll though the 179 lines of output generated by
"show variables"

[Chris Hope]

jerry gitomer wrote:

MLH wrote:

[snip]
The output I get appears to be several screens full. I tried piping
it to more and then to less. I found out real quick that those
utilities are useless in mysql. But there must be a way to capture
and scroll through the full text of the output from 'show variables'
(passes off the screen so quickly I have no idea just how much
there really is)

[snip]

run MySQL in a terminal window with a scrollbar. You will then
be able to scroll though the 179 lines of output generated by
"show variables"

The other thing you can usually do if you're at a physical console is to
use shift+page up and page down to go through the last few screens of
data.

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

[Bill Karwin]

MLH wrote:

[snip]
The output I get appears to be several screens full.

mysql> show variables like 'datadir';

Regards,
Bill K.

[Chris Hope]

Bill Karwin wrote:

MLH wrote:

[snip]
The output I get appears to be several screens full.

mysql> show variables like 'datadir';

That's weird - I could have sworn I tried that earlier on today when I
wrote my reply to the OP and it didn't work. When I tried now it did.
Guess I must have typed it in wrong... :)

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

[Archie Ball]

Wildcards do not seem to be supported...
mysql> show variables like 'd*'
does not work. How could one
query the vars beginning with 'd'?

On Wed, 30 Mar 2005 22:00:22 -0800, Bill Karwin <bi**@karwin.com>
wrote:

MLH wrote:

[snip]
The output I get appears to be several screens full.

mysql> show variables like 'datadir';

Regards,
Bill K.

[Felix Geerinckx]

On 05/04/2005, Archie Ball wrote:

Wildcards do not seem to be supported...
mysql> show variables like 'd*'
does not work. How could one
query the vars beginning with 'd'?

You have to use an SQL wildcard:

mysql> SHOW VARIABLES LIKE 'd%';
--
felix

[Bill Karwin]

Archie Ball wrote:

Wildcards do not seem to be supported...
mysql> show variables like 'd*'
does not work. How could one
query the vars beginning with 'd'?

Like you would with any other LIKE predicate in SQL:

mysql> show variables like 'd%';

Regards,
Bill K.