MySQL databases on their own separate Logical Volume I'm looking for a way to keep MySQL database sizes within certain limits. That's why I was thinking about creating a logical volume for each database. So for example:

db1 => lv_db1 (5 GB)
db2 => lv_db2 (5 GB)
...

This way the databases will not be able to grow beyond their 5 GB.

I know the databases are stored in /var/lib/mysql/&lt;database&gt;, so my idea is to mount the logical volume to this folder. However, the problem now is that I need to create and mount the logical volume before creating the database, which causes the CREATE DATABASE to fail because it thinks the database already exists.

ERROR 1007 (HY000): Can't create database 'foo'; database exists

Does anyone have suggestions how I can achieve this?

My backup solution is to mount one logical volume to /var/lib/mysql, but that way one database could claim all the space.
This may sound twisted but here it goes....

DON'T USE CREATE DATABASE

If you login as root and you do the following:

cd /var/lib/mysql
mkdir db1
chown -R mysql:mysql db1


you just created the database. The INFORMATION_SCHEMA database will dynamically register db1 in the INFORMATION_SCHEMA.SCHEMATA table as a database.

Since you want to create a volume for it, you can do this

cd /var/lib/mysql
mkdir db1
mount /dev/sdb db1
chown -R mysql:mysql db1


GIVE IT A TRY !!!

Bad News: Backing up /var/lib/mysql will not scoop up mount volumes. You will have to resort to doing mysqldumps. That should not be an issue if you have 5GB mounts.