Why are databases created in /var/lib/mysql ???

I am a newbie in the world of MySQL. In fact I enabled it in my Linux
box only because it is required to run WordPress (the blogging
software). I was trying to plan ahead and figure out a way to backup
(and restore) a database, should my Linux machine ever goes belly up. I
disovered that the databases are stored in subdirectories under
/var/lib/mysql.

My questions are basically three:

(1) Why /var/lib? Why not some /home/mysql or similar? - where one
expects to find data, not executables or libraries?

(2) Is it possible to configure my installation of MySQL to use some
other path for the databases? (e.g. /home/mysql or
/home/linuxlover/mysql)

(3) Is there an article or doc that outlines a good practice for
backing up - and restoring - a database without relying on a specific
MySQL configuration? Ideally, I would like to be able to copy a
database from one server to another... but if not possible, just
backup/restore to same server is good enough. A URL would be
appreciated.

BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.

Thanks!
Lynn

Re: Why are databases created in /var/lib/mysql ???

OK - regarding question #2, using phpMyAdmin I was able to click "Show
MySQL system variables" (left pane) and see that "/var/lib/mysql/" is
stored in a "server variable" named "bdb home".

Is there a way to change it?

If so, how?

Thanks,
Lynn

linuxlover992000@yahoo.com wrote:
> I am a newbie in the world of MySQL. In fact I enabled it in my Linux
> box only because it is required to run WordPress (the blogging
> software). I was trying to plan ahead and figure out a way to backup
> (and restore) a database, should my Linux machine ever goes belly up. I
> disovered that the databases are stored in subdirectories under
> /var/lib/mysql.
>
> My questions are basically three:
>
> (1) Why /var/lib? Why not some /home/mysql or similar? - where one
> expects to find data, not executables or libraries?
>
> (2) Is it possible to configure my installation of MySQL to use some
> other path for the databases? (e.g. /home/mysql or
> /home/linuxlover/mysql)
>
> (3) Is there an article or doc that outlines a good practice for
> backing up - and restoring - a database without relying on a specific
> MySQL configuration? Ideally, I would like to be able to copy a
> database from one server to another... but if not possible, just
> backup/restore to same server is good enough. A URL would be
> appreciated.
>
> BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.
>
> Thanks!
> Lynn

Re: Why are databases created in /var/lib/mysql ???

Oops... I was too quick on the trigger... Actually, there is another
variable that contains the value "/var/lib/mysql/" called "datadir".

I think that "datadir" is the variable I am looking to change.

Is it possible to modify it so that MySQL looks from now on at a new
path?
If so, is it possible to move existing databases to the newly pointed
path?

Thanks,
Lynn

linuxlover992...@yahoo.com wrote:
> OK - regarding question #2, using phpMyAdmin I was able to click "Show
> MySQL system variables" (left pane) and see that "/var/lib/mysql/" is
> stored in a "server variable" named "bdb home".
>
> Is there a way to change it?
>
> If so, how?
>

Re: Why are databases created in /var/lib/mysql ???

And the answer is... Yes it is possible.

All needed is:
1. Shudown mysqld daemon (/etc/init.d/mysqld stop)
2. Move /var/lib/mysql/ to /home/mysql/
3. Modify /etc/my.conf so that datadir=/home/mysql
4. Start mysqld daemon (/etc/init.d/mysqld start)

Simple.

However, I decided not to do so - for the sake of simplifying future
updates of Fedora or MySQL. I realized that it is easier to deal with
the arbitrariness of the databases location than with some uknown
future incompatibilities when upgrading.

Lynn

linuxlover992000@yahoo.com wrote:
> Oops... I was too quick on the trigger... Actually, there is another
> variable that contains the value "/var/lib/mysql/" called "datadir".
>
> I think that "datadir" is the variable I am looking to change.
>
> Is it possible to modify it so that MySQL looks from now on at a new
> path?
> If so, is it possible to move existing databases to the newly pointed
> path?
>

Re: Why are databases created in /var/lib/mysql ???

linuxlover992000@yahoo.com wrote:
> I realized that it is easier to deal with
> the arbitrariness of the databases location than with some uknown
> future incompatibilities when upgrading.

Good plan. Though it is arbitrary, it is a convention on Linux to use
/var for variable files, like logs, temp files, and databases.

For more information on this convention, see:
http://en.wikipedia.org/wiki/Filesystem_Hierarchy_Standard

Regards,
Bill K.

Re: Why are databases created in /var/lib/mysql ???

>I am a newbie in the world of MySQL. In fact I enabled it in my Linux
>box only because it is required to run WordPress (the blogging
>software). I was trying to plan ahead and figure out a way to backup
>(and restore) a database, should my Linux machine ever goes belly up. I
>disovered that the databases are stored in subdirectories under
>/var/lib/mysql.
>
>My questions are basically three:
>
>(1) Why /var/lib? Why not some /home/mysql or similar? - where one
>expects to find data, not executables or libraries?

FreeBSD puts it in /var/db/mysql. I think /var may be the most
appropriate place for data, depending on how tied to the system
MySQL is. For example, some people use MySQL for login authentication,
so you can't even log in without it, so it better be mounted soon.

/home may, in some installations, be a NFS filesystem and shared
between machines, something which might not be wanted for MySQL.

>(2) Is it possible to configure my installation of MySQL to use some
>other path for the databases? (e.g. /home/mysql or
>/home/linuxlover/mysql)

I believe there are three approaches:

(1) set datadir in /etc/my.cnf, (preferred) or
(2) as a command line option to mysqld (sometimes useful for running
TWO instances of mysqld on the same system with different data
directories and ports), or
(3) Create a symlink /var/lib/mysql that points to whatever directory
you want used.

In all cases you want to move the previous contents of /var/lib/mysql
to the new location first, with the server shut down when you do it.
Be sure to preserve permissions and ownerships. Then restart the server.

>(3) Is there an article or doc that outlines a good practice for
>backing up - and restoring - a database without relying on a specific
>MySQL configuration?

mysqldump is pretty good. It doesn't care about the binary format
of tables in different MySQL versions, or directory locations.

>Ideally, I would like to be able to copy a
>database from one server to another... but if not possible, just
>backup/restore to same server is good enough. A URL would be
>appreciated.

mysqldump -h src.hostname.example.com ... | mysql -h dest.hostname.example.com ...

run on another host command.hostname.example.com (which might be the
same as one of the servers). Options to mysqldump specify what to dump
and whether it's to dump schema, data, or both, and there are some
options for making the output palatable to lower-version servers.

For more information, type "mysqldump --help | more".

The source and destination servers can be the same if you are
careful (e.g. copy database xyz to xyzbackup1 on the same server).

For this to work, you need:
- MySQL servers set up and running on src.hostname.example.com and
dest.hostname.example.com.
- MySQL client software set up on command.hostname.example.com.
- Login accounts for some user on command.hostname.example.com on the
servers src.hostname.example.com and dest.hostname.example.com,
with enough privileges to do what you are trying to do.
- Network connectivity between the hosts, including no blocking of
MySQL connections by firewalls.

>BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.

Gordon L. Burditt