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

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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

(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

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


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?


linuxlover992000@yahoo.com wrote:
Quoted text here. Click to load it

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
If so, is it possible to move existing databases to the newly pointed


linuxlover992...@yahoo.com wrote:
Quoted text here. Click to load it

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)


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.


linuxlover992000@yahoo.com wrote:
Quoted text here. Click to load it

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

linuxlover992000@yahoo.com wrote:
Quoted text here. Click to load it

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:

Bill K.

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

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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

Quoted text here. Click to load it

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
- 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.

Quoted text here. Click to load it

                    Gordon L. Burditt

Site Timeline