Does mysql have any limitation on number of databases

Question

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 : 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

The MySQL manual states that there is no limit on the number of databases here: maximum number of databases in MySQL

However, since each databases requires the creation of one directory on the file system, and the file system may have a limit on the number of directories in one folder, there is an effective limit.

Check your file system documentation for the maximum number of directories it allows (NTFS ~ 4 billion, ext3 ~32000, ext4 ~unlimited, etc.)

Answer 2

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