Limit space in MySQL databases?

[jerrypr]

Hi all,

I _know_ this issue must have been addressed somewhere. One of the boxes I'm managing has no limits on the user's db area. Actually, it started with no quotas at all, but I know how that works and got that to work properly with cPanel. Now, I need to do something similar for the user db's. I've been managing the partition pretty well with some of the db's, but I have one user who is still growing their db quite rapidly, and I'm afraid that they'll swallow the whole partition if I can't limit them somehow.

Any ideas on what's going on? I'm running FreeBSD, with a fairly recent version of cPanel.

Thanks,
J.

[dysk]

Hi-
As I'm sure you know by now, mysql databases run under a seperate user, so it is difficult to make them work under the quota.
In this situation, I would create a crontab that emails the administrator with the size of each mysql database, and when one is excessively large, simply call up the client and work out an arrangement to either charge them for it, or they reduce the size.
Sure this is low tech, but it should work. Let whoever the client is know that they'll be charged substantially if they go over the agreed-upon limit, and they'll probably work to keep it down.


Regards,
Erek Dyskant

[mickalo]

If you run alot of MySQL database, this maybe not the best idea, but we use to do this, but it got to be too of pain.

this will include the MySQL database with each user's disc quota.

when a new database is created, IE username_dbname it is create in the /var/lib/mysql folder, IE./var/lib/mysql/username_dbname, now go into the the account's /home/username folder and create the same identical folder, IE. /home/username/username_dbname= then go back into the /var/lib/mysql folder, remove the folder, username_dbname and create symbolic link from the /home/username/username_dbname to the /var/lib/mysql, like so:

cd /var/lib/mysql
ln -s /home/username/username_dbname username_dbname
chown -R mysql /home/username/username_dbname

Now their database tables./data will be stored in the /home/username folder, but again, like I say, this is a pain if you manage alot of customers with MySQL. But it include their db's with their over-all disc quota.

But remember, if they create a db in their Cpanel, the db folder is created in the /var/lib/mysql folder, and you need to go through the process again, to create a symlink to the db.

Hope this may help
Mickalo

[jerrypr]

Hi,

Originally posted by dysk
Hi-
As I'm sure you know by now, mysql databases run under a seperate user, so it is difficult to make them work under the quota.
In this situation, I would create a crontab that emails the administrator with the size of each mysql database, and when one is excessively large, simply call up the client and work out an arrangement to either charge them for it, or they reduce the size.
Sure this is low tech, but it should work. Let whoever the client is know that they'll be charged substantially if they go over the agreed-upon limit, and they'll probably work to keep it down.

I guess I was misled by the fact that in a user's cPanel there is an area for "db quota". I figured there was some magic there that I didn't understand, but apparently not .

I'm not opposed to hacks and stuff, but there should be a finer control over this. Effectively, this means that a user with quite limited disk allocation can exhaust space if he has access to a database.

Ok, now that I know I can't limit usage, is there any way to know which tables in a DB are chewing up space? I know what _users_ are eating space, but not which tables, and you know that is what the user will want to know.

Any ideas?

Thanks,
J.

[jerrypr]

Hi,

Originally posted by mickalo

this will include the MySQL database with each user's disc quota.

This is an excellent suggestion. I might even hack the create routines to do this automagically.

Does anyone know the consequence to MySQL of having the db's spread out like this? Is there any performance penalty from having them in different dirs?

Second, anyone have a clue where cPanel actually does the creation? I'll dig through the scripts, but if you have a pointer, that might help.

Thanks,
J.

[jerrypr]

Here is what my users are seeing, now:

SQL Disk usage 1.00 Megabytes

Any way to just fix _that_?

Thanks,
J.

[Brio]

If you SSH into your box as root and run

/scripts/updatemysqlquota

it will change the owner of each database to the user (If I remember correctly it parses out the username from the username_databasename format.)

Then, when you log into cPanel it will include the users SQL and FTP space in the disk usage figures.

You would have to run this script as a cron job to make sure any newly created databases also have the right permissions.

It has the potential to be a little dangerous - if a user runs out of disk while writing to a database it can cause corruption if the database.

[jerrypr]

Hi,

Originally posted by Brio
If you SSH into your box as root and run

/scripts/updatemysqlquota

it will change the owner of each database to the user (If I remember correctly it parses out the username from the username_databasename format.)

Then, when you log into cPanel it will include the users SQL and FTP space in the disk usage figures.

You would have to run this script as a cron job to make sure any newly created databases also have the right permissions.

Great tip. Thanks a lot.

It has the potential to be a little dangerous - if a user runs out of disk while writing to a database it can cause corruption if the database.

Yup, I can see that. MySQL doesn't have an issue with the database not being owned by it though? Or is there another tweak I need, like an SUID bit on MySQL? (Maybe cpanel just runs MySQL as root, then again, obviating the need for that)

I'm not too worried about corrupting databases.... I'm more worried about running out of room GENERALLY on my database permissions. I have one guy who had a db twice as big as a very large db, who just hadn't bothered to "clean up" something. I'd rather one user
run into some weirdness then have the potential for one user to screw up EVERYONE's db.

Thanks,
J.

[navmonkey]

If you SSH into your box as root and run

/scripts/updatemysqlquota

it will change the owner of each database to the user (If I remember correctly it parses out the username from the username_databasename format.)

Wouldn't this cause problem if the script parses out the username from the username_databasename file name format? As all existing programs using database are referring to username_databasename instead of just databasename file naming convention.

Any comments for those we had done this. Thank you.l

[jerrypr]

Hi,

Wouldn't this cause problem if the script parses out the username from the username_databasename file name format? As all existing programs using database are referring to username_databasename instead of just databasename file naming convention.

Any comments for those we had done this. Thank you.l

I don't think so, since this is the database name, and it has to be consistent.

J.

[anup123]

Something along these lines if possible run through cron:

SQL Disk Usage + Disk Space Usage > Allocated Space

Update the privelages to disallow insert/create for the database/user which the Admin resets upon renegotiation of the SQL Disk space with the client.

Just a idea ...

Anup

[trout21]

So THIS is the way MySQL is setup?

A user has only 100Mb space on their account - but their db's can end up being larger than this amount and are not counted as being part of their space?

You must use Mikalos hack to add them to the space?

Have I got the gist of this....?? This has shocked me!! A user can therefore be actually using 500Mb in a massive database and only 50Mb of their space?

Please tell me I am wrong!