MySQL database filestorage location (how to change?)
 

Hi.

I have several databases on my MySQL server, some of them are getting pretty large and I want to start storing them in separate locations. I have found that you can edit the config files to change where ALL of the database files are stored and then just move the database files to the new location, however, I want to store the databases in different places, some on different drives/partitions etc, is there a way of specifying the file location on a per database basis?

Thanks in advance,

Tim

Hi

Not sure if it's possible by configuration per database, but symlinking works. You can symlink both directories (databases) or files (tables). This is often used to spead up things - I think people usually move to have index files on data on different disks.

http://dev.mysql.com/doc/refman/5.1/...lic-links.html

Yes a symlink that sounds like the solution. The thought hadn't even crossed my mind, it's nice where there is a simple solution.

Cheers :)

Ok, so if I get some more disks / one nice big disk to store the MySQL database/tables on what disk format would be best for the partition?

The database in question is quite large, it has lots of writes but these are mainly delayed inserts and no one is going to shed any tears if they take a little while to happen. It would be good if reads could be as fast as possible, the cache hit rate isn't terribly high due to the randomness of the requests.

Nothing is wrong with the MySQL setup as such, I was just wondering if there was any way of improving the speed by using an efficient filesystem format. I use ext3 on all the other drives and the MySQL (/var/wherever it is), at the moment. Would ext3 be ok for a dedicated MySQL partition or would something simpler improve performance?