Mysql create database with new database location

views:

232

answers:

+1 Q:

Mysql create database with new database location

Few months ago I have asked a question regarding how to change database location at runtime - I didn't get any solution for this problem yet.

I am needing to create more than 32,000 databases in MySQL. The default data location of MySQL data folder, after creating 32,000 database on that location, I want to change the data directory to other location. I am planning to do this through Java code.

But before that can anyone tell me if this is possible?

I am really needing to implement this as an requirement. Please help me out with this.

I am sorry If I am unclear anywhere in this description, but let me know if you all need any more information.

That sounds difficult. I don't understand why 32000 - is mysql refusing to create more databases than that? What error do you get? Seems arbitrary - maybe this is a config variable that can be changed?

A few possibilities. You could run more than one mysql server, each with a different data directory. I don't think it would help, but you might look into the NDB storage engine, it can handle tablespaces which just might let you store data in multiple locations.

Keith Randall 2009-10-22 20:39:17

Hi Keith,Its not a mysql restriction but its a restriction of folder limit which is there on OS level. I cannot keep my those db's in NDB.

MySQL DBA 2009-10-23 04:18:12

Keith Randall 2009-10-23 07:14:15

no I am not using FAT32 file system.

MySQL DBA 2009-10-23 12:38:43

Keith Randall 2009-10-23 18:34:40

Please also post the error mysql gives you when you do the create database that fails.

Keith Randall 2009-10-23 18:36:08

OK then - resolved as "not needed"?

Keith Randall 2009-11-03 08:09:01

As I said earlier that mysql is not giving me an error for the problem of create more databases than 32000. But yes now mysql is giving me an error i.e. :- Can't create database (err:31).Please help me.

MySQL DBA 2009-11-16 06:14:11

OK, looks like you're running the ext3 filesystem. I was unaware it had such a low directory limit. You should use ext4 - check out http://serverfault.com/questions/74885/bypassing-the-32k-subdirectory-limit-dirnlink . You could also use ReiserFS or XFS (check mysql docs to see if there are any issues with these).

Keith Randall 2009-11-16 17:18:42

You can create several partitions and join them using LVM (this is in Linux) and mount the partition to the DATADIR path. Also you can use soft links the the databases moved to other folders/partitions.

Unfortunately MySQL supports only one DATADIR but it seemed to me that InnoDB tables can be places to separate path. Could you check this?

StarWind Software 2009-10-23 09:55:28

ansaurus

tags:

views:

answers:

Mysql create database with new database location

related questions