Create a Database on a network drive

[nsteiner]

Hi all
I am trying to create a database that will not reside in "C:\program file\mysql\db\d ata" but somewhere else on the disk or even on a network drive.
Is it possible ?
if so, how?

P.S - I am using Mysql 5.0
Thanks

[Atli]

Hi.

You can change the location where MySQL will store data by editing the "datadir" variable in the "my.cnf" configuration file.

Just make sure MySQL has proper permission on the target directory.

[nsteiner]

Thanks Atli.
One more thing:
Is it possible to keep different databases on separate locations ?
Is my.cnf per database ? or per server ?

TIA

[Atli]

I'm not sure you can distribute them like that (although, I admit, I have not explored that option very thoroughly).

But, you can split InnoDB data between several files.
(See MySQL :: MySQL 5.0 Reference Manual :: 13.2.6 Adding, Removing, or Resizing InnoDB Data and Log Files)

So if your tables use the InnoDB engine, you can store the data in several files, each at a location you can choose.

[mwasif]

Thanks Atli.
One more thing:
Is it possible to keep different databases on separate locations ?
Is my.cnf per database ? or per server ?

TIA

my.cnf is per server. On windows it is my.ini.

You can keep different databases on different locations by creating Symbolic links. To enhance the performance, we keep them on different disks. Checkout the MySQL Manual for more info.

[Atli]

You can keep different databases on different locations by creating Symbolic links.

This I didn't know. I'm going to have to try that out :)

I knew you could Create symbolic links for files on Linux, but I didn't think it wold work like this. And I would never have through Windoze would support it.

[mwasif]

I never used this method on Windows but knew it works (according to manual:-)). I have been using symbolic links on Linux since a long time.