Rambling about partitioning and directory tree

[vodoo]

It is fairly standard (and all newcomers are told) to make a separate big partition for /home and reserve about 20GB to /

Then once you configure MySQL all data will live under /var and the root partition quickly fills up. Many other apps keep their data under /var too (like named, mailman ...).

Upgrading the OS will overwrite /var. A lot of data must be reloaded from the backup. This is a mess. Am I the only one who thinks that our file system tree is a poor solution? In a perfect world all user data and system configuration should survive a system upgrade.

is it not possible to define exactly where /var is mounted?
like, maybe where YOU want it mounted?

like, if you need it so: maybe a really fast drive OTHER than that
where either root or home rest? (maybe on different continent?)

or, is it not possible to also specify exactly where any db's or mail
or _any_ other program's data lives?

where stuff goes is one of the choices admins get to make...on the
other hand if you want to accept the default mounting spot, and then
overwrite it in a new install--that is, well, your choice..

--
platinum

[hcvv]

Create your databases somewhere else. I use e.g.

LOAD DATA INFILE '/home/databases/dias/diareeks.inport' INTO TABLE diareeks;

and thus have the bulk of the database files in the /home partition.
MySQL then creates symbolic links:

boven:/var/lib/mysql # l /var/lib/mysql/dias/
total 60
drwx------  2 mysql mysql 4096 Jul 19 21:58 ./
drwxr-xr-x 10 mysql mysql 4096 Sep 28 10:20 ../
-rw-rw----  1 mysql mysql   61 Jul 19 21:58 db.opt
lrwxrwxrwx  1 mysql mysql   28 Jul 19 21:58 dia.MYD -> /home/databases/dias/dia.MYD
lrwxrwxrwx  1 mysql mysql   28 Jul 19 21:58 dia.MYI -> /home/databases/dias/dia.MYI
-rw-rw----  1 mysql mysql 8842 Jul 19 21:58 dia.frm
lrwxrwxrwx  1 mysql mysql   33 Jul 19 21:58 diareeks.MYD -> /home/databases/dias/diareeks.MYD
lrwxrwxrwx  1 mysql mysql   33 Jul 19 21:58 diareeks.MYI -> /home/databases/dias/diareeks.MYI
-rw-rw----  1 mysql mysql 8624 Jul 19 21:58 diareeks.frm
lrwxrwxrwx  1 mysql mysql   30 Jul 19 21:58 serie.MYD -> /home/databases/dias/serie.MYD
lrwxrwxrwx  1 mysql mysql   30 Jul 19 21:58 serie.MYI -> /home/databases/dias/serie.MYI
-rw-rw----  1 mysql mysql 8696 Jul 19 21:58 serie.frm
lrwxrwxrwx  1 mysql mysql   37 Jul 19 21:58 voorstelling.MYD -> /home/databases/dias/voorstelling.MYD
lrwxrwxrwx  1 mysql mysql   37 Jul 19 21:58 voorstelling.MYI -> /home/databases/dias/voorstelling.MYI
-rw-rw----  1 mysql mysql 8630 Jul 19 21:58 voorstelling.frm
boven:/var/lib/mysql #

You could of course also make

/var/lib/mysql

a separate partition.
You could also move everything inside /var/lib/mysql to /home/mysql and then make a symbolic link to there.

In other words, like platinum already demonstrated there are a lot of possibilities for a system/database administrator.

[vodoo]

@platinum: Yes I can. But changing the data location for all apps writing to /var is just a little bit more tedious than restoring data after a fresh install. Moving a lot of things around means fiddling with permissions too.

@hcw: symlinking mysql tables means looking for trouble. And it works for MyIsam tables only. Better to symlink the whole data directory.

@audience: Sure I can make my own distro with openSUSE Studio and put everything where I want it to be. But my ramble was in fact not a cry for help (that's why it is in soapbox ). I just don't understand why things are made the default way like they are now.

[microchip8]

There's nothing restricting you in making /var live on its own partition and many servers do so too

[hcvv]

Originally Posted by vodoo

@hcw: symlinking mysql tables means looking for trouble. And it works for MyIsam tables only. Better to symlink the whole data directory.

The symlinking of the tables is done by MySQL itself on me using an SQL statement where to put things. So that is not some wiggly construction by myself, but supported by MySQL.

And yes, As I said, symlink of /var/lib/mysql is easy, something to be easily repeated after a reinstall. Making the same directory a mountpoint for a partition separates the database even further from /home (becoming 100% full from out of hand going users). All these things are to be contemplated in respect to size, importance, etc. of the database.

The default is just a default for those who do not want to contemplate. Let we hope that they are the people which do not have big, important, highly available databases in their domain to manage. IMHO in no way, the ease at which nowadays home systems can be used for big tasks by novices, will result in stable, well done, well managed systems because of some well chosen defaults.

And I do not know if the /var/lib/mysql default is chosen by MySQL, openSUSE or another one. But I do admit that it is a bit of a strange place. I would consider /var vulnarable to reinstallation and any /lib/ suspect of holdiing libraries of software. Make a better proposal to whome it may concern

[vodoo]

I absolutely agree that /var/lib is a bad place to put data. That was MySQL's decision; we can't blame SuSE here.

As for symlinking tables: I really don't like it because the .frm files cannot be symlinked and this makes backups a pain (2 different locations for the files of one table).

[hcvv]

Originally Posted by vodoo

I absolutely agree that /var/lib is a bad place to put data. That was MySQL's decision; we can't blame SuSE here.

As for symlinking tables: I really don't like it because the .frm files cannot be symlinked and this makes backups a pain (2 different locations for the files of one table).

About the symlinking, I have given you enough alternatives I think.

I myself export my database and backup the exported files. I do not like to backup a running database, except by the software (snapshot/export) provided by the database software itself.

[john_hudson]

Originally Posted by vodoo

I just don't understand why things are made the default way like they are now.

I think it is probably history. If you were a professional, you knew what to do with mysql. Ten years ago, there were lots of differences between the ways in which distros handled the directory tree. So it was easier for mysql to chose a location which would work whatever the distro.

Also, I don't think many distro users stored blobs in mysql. So far, my entire mysql folder has got up to 2.4Mb, including all the tables I began to construct over 20 years ago which took up far more space on my previous machine.

As users begin to use mysql as a backend for far more applications, this issue is going to become more pressing but no-one envisaged that ten years ago.

[vodoo]

Ten years ago, there were lots of differences between the ways in which distros handled the directory tree. So it was easier for mysql to chose a location which would work whatever the distro.

Yep, but 10 years ago mysql stuff went into /usr/local/lib (if my memory serves me well).

My /var directory has grown to 23GB by now, mostly due to mysql and mailman and j-chkmail. I don't store any large blobs, but zillions of weather reports, texts with fulltext indexes and the like.