Results 1 to 9 of 9

Thread: MySQL DB Stored... where?

  1. #1
    Join Date
    Aug 2006
    Beans
    241
    Distro
    Kubuntu 9.10 Karmic Koala

    MySQL DB Stored... where?

    Where in the filesystem are mysql databases stored?

  2. #2
    Join Date
    Mar 2007
    Beans
    45

    Re: MySQL DB Stored... where?

    Quote Originally Posted by zzzBrett View Post
    Where in the filesystem are mysql databases stored?
    /var/lib/ something by default... sorry couldn't be closer, not on my box...

  3. #3
    Join Date
    Jun 2009
    Location
    Alabama
    Beans
    2,232

    Re: MySQL DB Stored... where?

    You can use "show variables;" and look for datadir. On my system, that is giving /var/lib/mysql, which if correct, is not a very good place. I am new at this, too.

    Tim

  4. #4
    Join Date
    Aug 2006
    Beans
    241
    Distro
    Kubuntu 9.10 Karmic Koala

    Re: MySQL DB Stored... where?

    Thanks for the replies.

    I have a back up of the /var directory, so I assume I have the files where the databases are stored. How would I get these files into .sql files or into my database (that was deleted)?

  5. #5
    Join Date
    Sep 2006
    Beans
    7,566
    Distro
    Lubuntu Development Release

    Re: MySQL DB Stored... where?

    On ubuntu the mysql database usually keeps everything in /var/lib/mysql/
    There will be material for the mysql server there and there will be a subdirectory for each mysql database.

    So if you have databases 'foo' and 'bar' in addition to the pre-exisiting administrative database (the one holding it all together) called 'mysql' you will see three subdirectories:

    /var/lib/mysql/mysql/
    /var/lib/mysql/foo/
    /var/lib/mysql/bar/

    You will also find configuration data in this directory:

    /etc/mysql/

    If you want to let the system manage the programs and only worry about the database (columns, tables, users, data, variables, etc) then you can use mysqldump. Using the database's own backup facilities is the correct way to go if you are concerned about the data and maintaining database integrity. Otherwise, just copying the files from the disk you can get partially complete transactions, etc.

    For more detail information about backup and recovery:
    http://dev.mysql.com/doc/refman/5.1/...-recovery.html

  6. #6
    Join Date
    Aug 2006
    Beans
    241
    Distro
    Kubuntu 9.10 Karmic Koala

    Re: MySQL DB Stored... where?

    Perfect thanks.

  7. #7
    Join Date
    Jun 2009
    Location
    Alabama
    Beans
    2,232

    Re: MySQL DB Stored... where?

    The reason I don't like subdirectories of /var is because it is part of the root partition, which is usually small. I would prefer it to be in the larger partitions (/home or /usr) or, even better, in a filesystem on its own partition for databases. Assuming that some databases will become quite large.

    Tim

  8. #8
    Join Date
    Aug 2006
    Beans
    241
    Distro
    Kubuntu 9.10 Karmic Koala

    Re: MySQL DB Stored... where?

    Quote Originally Posted by Lars Noodén View Post
    On ubuntu the mysql database usually keeps everything in /var/lib/mysql/
    There will be material for the mysql server there and there will be a subdirectory for each mysql database.

    So if you have databases 'foo' and 'bar' in addition to the pre-exisiting administrative database (the one holding it all together) called 'mysql' you will see three subdirectories:

    /var/lib/mysql/mysql/
    /var/lib/mysql/foo/
    /var/lib/mysql/bar/

    You will also find configuration data in this directory:

    /etc/mysql/

    If you want to let the system manage the programs and only worry about the database (columns, tables, users, data, variables, etc) then you can use mysqldump. Using the database's own backup facilities is the correct way to go if you are concerned about the data and maintaining database integrity. Otherwise, just copying the files from the disk you can get partially complete transactions, etc.

    For more detail information about backup and recovery:
    http://dev.mysql.com/doc/refman/5.1/...-recovery.html

    It seems that the mysqldump command dumps that database from an sql server as a backup, but the sql server must be working. For me, all I have is the /var/lib/mysql/*dbname* files such as *.frm, *.MYD, *.MYI files. How can I get these into sql statements or into a database. (Copying the backup from /var/lib/mysql/ to the new directory did not work).

    Thanks

  9. #9
    Join Date
    Aug 2006
    Beans
    241
    Distro
    Kubuntu 9.10 Karmic Koala

    Re: MySQL DB Stored... where?

    Figured it out.. used myisamchk to fix the databases and corrected permissions so the server could read the files. All works now.

    Just one more question: what permissions should the /var/lib/mysql/dbname files have?

    EDIT: should have permissions 770 with owner:group as mysql:mysql

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •