Does mysql have any limitation on number of databases

Question

Hi All,

I am creating mysql databases in mysql default data directory in "/Var/lib/mysql/data" But it is not allowing me to create more than 31999 databases in it.

The similar sort of question I have asked before : http://stackoverflow.com/questions/1607793/mysql-create-database-with-new-database-location

Where I said that mysql can create more than 32000 databases but, when I tried this thing on production server. It is not happening.

Is there any possibility, where I can store databases on different locations.

any input will be an help.

thanks,

Manasi

Answer 1

This might be a limitation of the file system your data directory resides on, because MySQL tries to create a directory for each database. If you cannot create more than a certain number of subdirectories inside a given parent, MySQL will fail on CREATE DATABASE.

Edit: As ysth says, multiple mysqld instances on different ports would pose a workaround. Most Linux distributions come with mysqld_multi prepackaged. This allows quite easily handling multiple database servers on one machine.

Edit2: According to Wikipedia (and my memory) "The max number of subdirectories in one directory is fixed to 32000" (see here: Wikipedia ext3 entry), so as for that you are probably out of luck. Again, I think mysqld_multi is your best bet if you cannot get the number of databases down - or choose another filesystem that does not have this limitation (but might have others)

See the MySQL manual for more information on mysqld_multi

Answer 2

I can't think of any reason why you couldn't have multiple mysqld servers listening on different sockets/ports and using different data directories. But rethinking whatever is causing you to want umpteen thousand databases would be better.