1

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/<database>, 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.

1

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.

|improve this answer|||||
  • Some things in life can be easy! Thanks ;) – EsTeGe Apr 2 '15 at 13:55
0

If you are to do it over, consider RAID "striping". This is more efficient and gives you a 10GB "logical" disk. Also, it avoids running out of disk space on one drive while having lots of room on the other.

Another drawback to manually segregating parts of MySQL on drives is the tmpdir. When doing ALTER and other maintenance actions, a copy of a table is (often) put in tmpdir, then renamed to where it belongs. But, if tmpdir is on a different physical drive, "rename" is not possible, so a slow "copy" must be performed.

|improve this answer|||||
  • I'll create the databases on separate logical volumes, but they will all be on the same physical disk dedicated to the databases. I'm just using the LVs for their size limiting capabilities. – EsTeGe Apr 3 '15 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.