Does anyone know how to make mySQL use another directory other than the current one it is using for storing databases?? The directory it is on is on a small partition, and i would like to move it to a bigger one before i run out of space ... any ideas??
(Redhat 6.2)

Admins, can you move this thread to the software section??? I posted it there, and i have no idea why it is here.

You can symlink the directories into a larger partition.

Do something like:

ln -s /var/lib/mysql/database_name /home/yourusername/mysql/database_name

That will put the data in your home partition rather than the /var partition.

Hope that helps.

Best Regards,
Matt Lightner
mlightner@site5.com

Create a file /etc/my.cnf containing the following lines:

[mysqld]
datadir=/path/to/datadir

Wow, that was fast :)
Thanks guys. 1 question though:

I Already have mysql installed with some data ...
what about my existing data tables?? Do i have to move them to the new directory?

THANKs~

Kill mysql and then move the files. Then create the link and start mysql up again.

Regards,
Matt

Thanks matt!
Works great ;)

Does this (creating a symlink) create any significant performance hit ?? (for example , if running a site like webhostingtalk)

Oops, theres a problem :(

Even though the symlink links the file to the /home/username directory, but when i tested by trying to "cp rpm3.0.rpm /var/lib/mysql/dbname" , my disk usage shows that the /var partitions increases in size , not the /home partition ...
am i doing something wrong??
i used

ln -s /var/lib/mysql/dbname /home/username/mysql

Originally posted by allending
i used

ln -s /var/lib/mysql/dbname /home/username/mysql

I think that's backwards, man ln shows

NAME
ln, link - make links

SYNOPSIS
ln [-fisv] source_file [target_file]
ln [-fisv] source_file ... [target_dir]
link source_file target_file

Oh well .... doesnt work :(
That order makes a directory in /var/lib/mysql/ instead .. any ideas anyone?? What am i doing wrong?

Sorry, I was forgetting that you were running Redhat. Regardless of the contents of the man page, the syntax is the same: you want a link from /var/lib/mysql/dbname to /home/username/mysql/dbname, so you want

mv /var/lib/mysql/dbname /home/username/mysql/dbname
ln -s /home/username/mysql/dbname /var/lib/mysql

Thanks for your help cperciva :)

Still doesnt work though ...... :(
Oh well, ill have to try more then .. hope i dont delete /var/lib accidentally.

ps: do i have to create the directory /home/username/dbname before i run "ln" ?

Well, the file/directory has to be there before you can create a link to it. As I understand it, what you're trying to do is move the files from /var to /home and create links in their places which point at the new locations. So you want to mv the files (or, if your OS can't mv across partitions, cp/rm the files) and then ln -s to create links pointing at the new location.

Redhat Procedure to put mysql data dir on /home partition:

Stop MySQL

mkdir /home/mysql
mkdir /home/mysql/dbname
cp -a /var/lib/mysql/dbname /home/mysql/dbname
(verify data moved by checking home directory contents - VERY IMPORTANT)

mv /var/lib/mysql/dbname /var/lib/mysql/dbname.bak
(make backup directory just in case)

ln -s /home/mysql/dbname /var/lib/mysql/dbname

Restart MySQL

Should work fine. I've done it a bunch of times without problem, and var partition should be cleared up.

Hope this helps!

- John C.

Thanks a lot~
That works great !
(had to change owner or /home/mysql/dbname to mysql though)
Thanks for all the help ... webhostingtalk.com really is one great place to find help.