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…
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.
@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 rotfl!). I just don’t understand why things are made the default way like they are now.
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
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.
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.
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.
We (Linux users and admins) are always talking about the deficiencies sponded by M$ during it’s upgrades where patches and upgrades devastate systems. Any Linux distro should not follow this lead. Requiring anyone to circumvent default behavior of apps to move data out of harms way is an unacceptable mix. If the system kills /var during an upgrade or any other main folder then there must be rules:
1) no app can place it’s info into a system modifiable folder or,
2) no system upgrade can wipe/remove info which is non-specific to the system upgrade
These baseline concepts have been around for over 50 years, are we now conceding that we haven’t learned anything! While I don’t have use of Mysql, or mailman, it still concerns me that basic principals of “an upgrade shall do no harm” may have been breached.
Ideally, it’s going to be hard to convince all the different packagers to modify their base tree. Like-wise, if I understand things right, if the /var is replaced, wouldn’t that also result in the system following links and destroying info, or erasing links?
:\
I think you have a point here (and thus has vodoo, who started this out of the same idea I think). In fact I started making backups of /var (besides /etc and /boot where I expect to be configurations) after I found out that I missed something over a reinstall. I have forgotten what it was, but I agree that this should ot have been the case. Wkipedia states That in the Filesystem Hierarchy Standard /var should contain:
Variable files—files whose content is expected to continually change during normal operation of the system—such as logs, spool files, and temporary e-mail files.
IMHO that means that they are not supposed to survive a reboot, let alone a system upgrade.
Back to daily practice. Even if the links do not survive a reinstall I must add that;
. I may hope that a system/database administrator makes note of this to reinstall the links after system upgrade/install, like he has to redo may individual system caracteristics;
. I offered a solution out of a threatening filsysem full situation, not a complete rethinking of the sytem;
. for a completely new re-doing of the system a seperate /var/lib/mysql (as mentioned earlier) would of course be better.
And for a real nice solution one can may be file a suggestion with the MySQL people.
