one mysql installation, multiple dbase directories

[aasterud]

Hi,

Default Mysql stores its databases in /usr/local/mysql/var. I can change the parameter in mysql.server, but this affects all databases. What I need is this:

user 1 has a virtual domain, and stores his databases in /home/user1/

http://www/db/

user 2 has a virtual domain, and stores his databases in /home/user2/

http://www/db/

Can this be done, and how? I tried using ln -s but it looks like Mysql doesn't see the links.

Regards,

Harry M. Aasterud

 

[rycamor]

MySQL shouldn't have any trouble with symbolic links, since AFAIK the filesystem is transparent to MySQL (try shutting down mysql, changing a directoryname, and restarting. You have just renamed a database).

Even on the MySQL website, they mention the performance tweak of splitting your databases onto multiple disks, and just linking them all to the MySQL data directory.

I think the problem is more likely with user permissions. What user is the mysql process running under?

 

[aasterud]

Hi,

Indeed, this works fine. But following problem showed:

Default upload (or entry) for mySQL is port 3306, at /usr/local/mysql/var. I made a ln -s to /home/<user>/db/. Running diskquota for this user results in the fact that it doesn't takes the link in consideration. So therefore I wonder if a mySQL client is able to connect to the user directory, instead of the .../var/ directory. If so, how do I do this. I can't change this in the mySQL config file, since then this will affect all databases.

Regards,

Harry

 

[rycamor]

What does diskquota have to do with it? Maybe you are thinking in reverse from what I meant. Are you saying that the symbolic link is in the user's directory, while the actual data files are in /var/etc..., or is it vice-versa? Are you saying you want the user's diskquota to take the MySQL data into account also, or not?

Anyway, you have just run across one of the very fundamental differences in philosophy between PostgreSQL and MySQL. MySQL was designed to have a user system that is largely unrelated to the actual system user accounts, while PostgreSQL derives its users directly from the Unix user accounts and permissions. This means each user in Postgres can have data files in a different location. This also means that Postgres has to start a new process for each user. Each one has its pros and cons.

 

[aasterud]

Hi,

The way things are running now, a user connects to the mySQL server and does the upload in the .../var directory, which is the default location for all databases. The link I made projects the database into the user directory. This way, a du -skL gives me an idea on how much space this user is eating away. But indeed, afaik quota doesn't deal with links. Btw, do you know a good script to manage this, incl. automated e-mails to the user? (I know this is off-topic, but now we mention it...)

What I would like to see is that a user can connect to the mySQL server, and upload his file into the user directory. So you're telling me this won't work with mySQL. OOOH GOD! I just went through all this to learn mySQL !!!

Mean reason for all this the quota problem. If I find a way around this, I don't care where the files are.

Looking forward to hear from you again. Thanks for the advice so far.

Regards,

Harry

 

[rycamor]

Hold on... easy now, there is always another way to solve your problem, so don't go trashing your MySQL install just yet ;-).

Now, let me see if I understand this correctly; are you saying you want the user to be able to upload raw data files, right into the MySQL data directory? Or are you saying you want the user to be able to upload mysqldump scripts or CSV files to import into MySQL? If it is the former, then that sounds pretty crazy to me. It's rarely a good policy to let users mess directly with the raw data files. If the latter, there is no reason that file even has to be in the MySQL data directory at all. It just has to be readable by whatever user permissions the MySQL process is running under.

Or am I missing something else completely, about what you are trying to do?

As far as determining how much disk space a user is eating up, that is pretty easy to solve no matter where your database files are, as long as you have an organized method of naming databases (corresponding to users). I bet we can make it a Perl one-liner ;-).

 

[aasterud]

I'm using myODBC to connect and export access db to mySQL. Standard procedure is for me to create a new user, and assign his rights to the new &quot;empty&quot; db. Now the user can open access, and export his db to the mysql server. Doing so, he connects to the server at port 3306, and the files come automaticly in the /usr/local/mysql/var directory, in de db I created. So far all works fine, both in PHP and ASP on our linux server.

Now, from the admin point of vieuw (that's me folks)... I run disk quota on the /home partition. All files that are located physicaly in the /home/<user>/ directory are taken in account when calculating the used blocks for this user. What I tried was ln -s /usr/local/mysql/var/user1db/ /home/user1/db. To see how much disk space user1 has taken, I can do a du -skLh /home/user1. With option -L the link is included in the du command. But if I check my quota for this user, the db is not included. Conclusion : quota doesn't handle the links.

Allowing the user to ftp the db into /home/user1/db, and using this with a ln -s /home/user1/db/ /usr/local/mysql/var/user1db/ works fine. The only problem is : How do I get a mySQL client to connect to port 3306 and upload the db straith into the /home/user1/db directory, without using a link? This way the mySQL server has access to the db, AND quota can calculate the correct amount of taken blocks.

Therefor I'm wondering if I'm not taking the wrong approach. Looking forward to your reply.

Harry

 

[rycamor]

Well, I'm not too familiar with MyODBC, but I still don't see the reason the user needs to ftp into the data directory. Once the user connects with MyODBC, MySQL takes it from there, and handles the data. Now if you move the actual data directory to the user's directory, and do ln -s /home/user1/db/ /usr/local/mysql/var/user1db/ , then MySQL transparently handles it, because it doesn't &quot;know&quot; that the directory is no longer in its own data directory. So if the user connects with MyODBC straight to MySQL, MySQL will automatically place that data in the /home/user1/db directory, and work with it from there.

Still, though, the user has no reason to interact *directly* with this data dir, so you can chown it to whatever process is running mysql, and keep the user from messing with it.

However, the real question is &quot;why go to all this trouble?&quot; Is the diskquota program a shell script or a Perl script that you can modify? If so, then you should be able to just add a line that checks the size of /usr/local/mysql/var/user1db/, and just add that result to the result from checking the user's main directory.