469,089 Members | 1,210 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,089 developers. It's quick & easy.

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
Jul 23 '05 #1
10 8714
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
Jul 23 '05 #2
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.


Jul 23 '05 #3
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.


Jul 23 '05 #4
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"
Jul 23 '05 #5
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
Jul 23 '05 #6
MLH wrote:
[snip]
The output I get appears to be several screens full.


mysql> show variables like 'datadir';

Regards,
Bill K.
Jul 23 '05 #7
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
Jul 23 '05 #8
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.


Jul 23 '05 #9
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
Jul 23 '05 #10
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.
Jul 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Martin Lucas-Smith | last post: by
9 posts views Thread by Bob Bedford | last post: by
1 post views Thread by Victor Spång Arthursson | last post: by
1 post views Thread by lord.zoltar | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.