You can often achieve better performance if your MySQL databases are stored on a drive other than your "main" drive. Thanks to M|22 for some guidance.

I am assuming that your second drive is mounted as /home2

The first step is to stop MySQL so that all your data gets copied correctly.
/etc/rc.d/init.d/mysql stop

We are going to make your new data directory /home2/mysqldata
cp -R /var/lib/mysql/ /home2/mysqldata

Set the correct owner and group, permissions are kept in the copying.
chown -R mysql.mysql /home2/mysqldata/

Remove your old data directory, we will just rename it but you can later delete it if you wish.
mv /var/lib/mysql/ /var/lib/mysql_old

Create a symlink from the old data location to the new one for any programs that rely on the common location.
ln -s /home2/mysqldata/ /var/lib/mysql

Set the correct owner and group on the symlink.
chown mysql.mysql /var/lib/mysql

Now that your data is moved we will edit the configuration file to update our changes. You do not have to do this since MySQL can follow the sym link we created but why make it follow the link if it doesn't have to.
pico /etc/my.cnf

Comment out the old settings and add a line for the new one as I have done here.

#datadir=/var/lib/mysql
datadir=/home2/mysqldata
#socket=/var/lib/mysql/mysql.sock
socket=/home2/mysqldata/mysql.sock

#basedir=/var/lib
basedir=/home2

Exit and save

Start MySQL
/etc/rc.d/init.d/mysql start

If MySQL refuses to start look in /var/log/mysqld.log for the reason.

For the record, this doesn't work on ensim PRO.

It should work except for high security since PHP is running in CGI mode.

No it doesn't work, cause you will only move symlinks.
All the databases are in site the chroots in ensim PRO.
The cgi mode has nothing to do with it.

this may/may not work on anything other than RH7.3 which i have installed on my dell dual xeon, it probably won't work with ensim.

M|22

what kind of performance increase are we talking about? I'd love to try this but wouldn't want to risk stuffing it up unless there was a relatively significant gain in speed.

Ok, I wasn't aware it stored them in each home directory.

Symlink is wrong!

It must be:

ln -s /home2/mysqldata/mysql/ /var/lib/mysql

Then it is working perfect for me (Redhat 7.3rpm + plesk 5.0.5rpm)

wow that looks like quit a difference! thanks daveman i'm going to try it!

When you copy it you copy the mysql directory and basically rename it to mysqldata on /home2.

Anyone want to figure out how to do this with Ensim Pro, and post it? I could use this...

I believe you would just have to move your mysql data directory in the users home directory to the new drive. Then symlink the old location to the new one.

Just for the record... if you use this for cpanel, you just need to copy, rename your old mysql dir, then symlink the new... no need to change the actual mysql variables.

You don't have to but by doing so then MySQL never even looks on your old disk, by just symlinking then it still has to follow that symlink.

This How-To works well.

Mind you, symlink was so transparent that when I deleted the original mysql directory, I deleted the mysqldata directory - LOL! I lost 2 months worth of data just like that (my daily backups were corrupted).

In any case, this is a great idea. Thanks for the How-To.

can ne1 verify this?

D

Could you supply us a step by step for Cpanel box's. I love your previous howto's

I didn't have the datadir or basedir lines in my.cnf. I tried adding them to the top but got an error when I restarted MySQL. Where should they go?

They should be under the [mysqld] section.

This is my my.cnf file for a Dual Xeon with 1GB ram and that does a lot of MySQL.

[mysqld]
#datadir=/var/lib/mysql
datadir=/home2/mysqldata
#socket=/var/lib/mysql/mysql.sock
socket=/home2/mysqldata/mysql.sock
skip-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=7
thread_cache_size=128
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=100
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
log-bin
server-id=1
long_query_time=4

[mysql.server]
user=mysql
#basedir=/var/lib
basedir=/home2

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

daveman's instructions work for CPanel. Just backup everything TWICE just in case.

daveman692 your cnf is missing pid-file=/var/run/mysqld/mysqld.pid

It is my understanding that if you do not specify that variable, mysqld looks for a file with the hostname of the machince in the data directory.

daveman692 you are running mysql4.0.* right? I'm asking because I tried your conf on a mysql3 and it didn't work..

And yes, you are right about the .pid

You are correct that this conf file will not work on MySQL 3. It has variables that were introduced in version 4.

Got a cPanel server here. The plan is to move mysql onto another drive to improve performance for customers.



Will i need to change any cPanel settings to reflect the change to another drive? I don't want to move 5GB of SQL to find something breaks under cPanel. Can anyone confirm?
Running MySQL 5.0

Edit: sorry for digging this up. Didn't realise it was posted so far back until after i posted.

Regards
Scott

I followed the steps as described in how to. However it didn't work for me.
I have Cpanel installed and it is absolutely driving me creazy.

When I start mysql database I get result:
mysql 18105 2.6 7.9 621788 81340 ? S 13:58 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/home2/mysql/ --user=mysql --pid-file=/home2/mysql//host.yobug.com.pid --skip-external-locking --open-files-limit=8192 --socket=/home2/mysql/mysql.sock

And the error.

/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! mysql has failed, please contact the sysadmin (result was "Warning, no valid mysql.sock file found.mysql has failed").


All my scripts are looking for mysql.sock in /var/lib/mysql and I don't know what to do with it. The support is no help at all at this point. My database went completely down and they fixed it. But it is still not working.

Can anybody Help!!!

Great tutorial. Works great, and is exactly what I needed seeing as though /var/ was partitioned too small.

Make sure you RESTART MySQL and not just start and stop. Starting and stopping won't load your new my.cnf - restarting will.

Using RHEL/PHP5/MYSQL5/Cpanel

I just have to co-sign on what's already being stated. The guide is very well put together and helpful, thanks.

Ok we're about to attempt this on one of our boxes.. As this guide is fairly old, is there any other issues we should worry about that hasn't been mentioned?
RHEL5/MySQL5/cPanel 11

we'll leave the old data location there in case of failure