How to setup mysql to use another hard drive?

Ok first Im completely new to linux. I know lol sorry..

My questions is, I am running a large database website under linux. I have 5 hard drives, but hard disk 1 is where linux is installed and apache is installed + all other server files. What I am trying to do is, is there anyway that I could make mysql use hard disk 2? here is an example:

Say I have my site on hdd1 and I want to tell my site to use hdd2 to store and retrieve all database files from hdd2 so that way hdd1 wont get filled up and run out of space.

Is there a way to do this? this is will be so helpful if anyone can help. Thank you.

All the databases or just one in particular?

Either way it's the same:
1. Stop mysqld
2a. ls the datadir (eg, /var/lib/mysql or /var/lib/mysql/database) and remember that
2b. Backup whatever you're dealing with, just for kicks
3. Move the datadir files to the new hard drive
4a. (Re)mount the drive at that directory
4b. ls the datadir and see that it looks like it did before
5. Start mysqld and make sure stuff doesn't catch on fire

one last question.

Is there any guide to help me mount different hard drives for different purposes under linux?

For example. Lets say I want to mount hdd2 to something like /system/uploads/ so from hdd1 (where my site is)I can edit the config file and point it to upload all the system files to /system/uploads which is in the 2nd drive hdd2. Say for example I have 3 hdds so the 1st hdd is where my linux is installed the other 2 sits there empty. So i want hdd1 to access hdd2 and hdd3 to store/retrieve files, like hdd1 wants to store a file into hdd2. hope its not confusing.

Depending on your Linux there are different ways to mount drives to folders. Editing /etc/fstab directly is probably an option.

Where you put stuff depends on how the system will be used. If it's a database machine then you probably want the database information on its own drive (RAIDed, even!). If it's a source control repository then you probably want a drive dedicated to holding that. Running a (small) web hosting business? Mount /home on its own drives too.

From what I can gather you have three hard drives and three important locations:
- The root, of course
- An uploads folder. I guess you'll have a lot of large uploads
- The MySQL data directory
Three and three. Use one drive for /, one for /system/uploads, and one for /var/lib/mysql.

Your right thats exactly what I want to do. Is there any guide that would help me towards this setup? if so that will be so helpful.

In your opinion, do you think (performance wise) setting up 3 hdds and linking it to perform different tasks would be better, or buy a large data hard drive something like 3TB? my site will be storing large amounts of data. thanks!

With multiple drives, losing one means only losing part of your data. Stick everything on one drive and you better have backups of everything. Which you should do anyways, but that's not the point.

You should be really learning about this, not just picking up a guide and getting it over with. Learn about hard drives, RAID and redundancy and backups, mounting stuff in Linux... There's a lot to cover that a simple guide might not get. A lot that you should know if you're going to fill this kind of role.

All that aside, I don't know of any guides. Besides Google. And there isn't a whole lot to do: add the drives in /etc/fstab with the right options and you're practically done.

Thanks alot! that makes more sense.

To be honest, I love linux and I really love to learn everything about linux after being a windows user for 11yrs, windows kinda sucks in terms of security and reliability. But everything forums like this makes learning much more easier and I understand more, rathar than flipping notes in books lol. Anyway thanks again for helping!

You don't have to use /var/lib/mysql. You should be able to simply stop mysql, move any existing data files to the new location, edit /etc/my.cnf with the new datafiles location, restart mysql.

Fedora linux (which I use) installs lvm partitions which allows you to expand a partition spanning multiple physical drives to get a larger storage partition. I'd try to use a bigger drive though, I just don't like the idea of not being able to recover data off a drive that's been pulled from a computer.

Thanks. I didnt know that. Btw I also use fedora 10.