Using multiple drives for data

[PROFESSORSPARKIE]

I want to place some of my data tables on another drive, then the one defaulted to during my Win/XP installation. there are multiple options:

1. give up and use the default folder set up in mysql\data
2. do 1. above and place special data on drive x:\myfolder
3. do 2. above but have data on multiple drives for
different queries or even joins of tables in
different drives & folders.

All the texts I look at say it is beyond the scope of that text & to look in MYSQL document. The standard MYSQL document is over 700 pages. I do not want to print it all out and have been unsuccessful in key word searches to find the specific text.

Any quick help out there?

Thanks in advance

Computer thought: I teach a lot of programming so I can learn. You can never learn it all.

 

[hvass]

1. Location of data files controlled by command line option or config file my.cnf (my.ini windows)

http://dev.mysql.com/doc/refman/5.0/en/option-files.html

2. If you want to spread databases across drives then you can use symbolic links not sure it is recommended but have done it on linux boxes without problems. 3. Official way is probably run more than one server either master slave or even mysql-proxy.

 

[ingresman]

To me this has always be a limitation of MYSQL, but I've never put in place a very large db. If I need to do that I much prefer Oracle of SQLServer.
You are pretty much stuck to using an array (perhaps RAID 5) to put big db's on.
The syntax does support data directory but I've tried to use it. The page is

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

but it looks like you can only do it with myisam and there a few other limitations

 

[TonyGroves]

If you were running Linux, you could (as hvass said) simply use softlinks to link to files and directories on other volumes. I've never done that myself, but I see no reason why it wouldn't work. But of course you're running Windows, so that's no help to you.

 

[PROFESSORSPARKIE]

I was expecting a simple multi-folder(different drives) statement to one of the admin commands like

mysqlxxxxx spaces c:\mysql\date d:\acct_rec d:\payroll

but it seems like that is not supported. I would have thought there would be a standard support.

I just thought of this: do ant of you know if using the windows "subst" command to set up a folder as a drive would work? It might work for the data but mess up the direct retrieval of the mysql system, requiring me to install the whole system on that drive to work. Example I could install my databases on a 1 ter/byte drive.

Thanks again in advance for your responses.

Computer thought: I teach a lot of programming so I can learn. You can never learn it all.

 

[PROFESSORSPARKIE]

To: hvass

I am exploring options you directed me to. Thanks

Computer thought: I teach a lot of programming so I can learn. You can never learn it all.

 

[hvass]

Ah windows

Guess you could use RAID to combine physical drives or does windows not have a capability to span drives?

Could also look at federated tables (have some restrictions) - accessing tables on a different server, could run two instances of the server with different data dirs on the same physical box and then use federated tables.

 

[ingresman]

RAID 5 - see my post above !!
RAID5 was invented to cope with Windows issues !

 

[PROFESSORSPARKIE]

To: ingresman

Thanks, I will look in to it.

Sparks

Computer thought: I teach a lot of programming so I can learn. You can never learn it all.

 

[ingresman]

I got my mysql newsletter this morning and it has a ref to a white paper about partitioining (5.1 and above)
it's at

http://www.mysql.com/why-mysql/white-papers/mysql_wp_partitioning.php

You have to register but it's worthwhile anyhow to get the newsletters