Transferring MySQL to a different directory

[HostSafely]

Hello to all I am a novice at doing this and want to make sure I do it right as all of the other posts are rather old and we all know cPanel has evolved since then.

We run centos 5 32 bit on all servers, and this is the way I have found to transfer mysql to the home directory to have lots of room in the var directory because the var directory gets very bloated from databases.

So this is what I have figured and it works please let me know if any of you have more info as any would be greatly appreciated:

How to Change the location of MySQL:

1) Stop MySQL using command: service mysql stop
2) cp /var/lib/mysql /home/mysql
3) pico /etc/my.cnf

remove everything in the file and replace with the following:

[mysqld]
set-variable = max_connections=500
pid-file = /home/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock
skip-locking
safe-show-database
skip-bdb
old_passwords=1
long_query_time=2
log-slow-queries=/var/log/mysql-slow-queries.log
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_user_connections=50
max_connections=500
thread_cache_size=256
table_cache=8192
key_buffer=64M
max_connect_errors=20
max_allowed_packet=128M
join_buffer=2M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=1M
thread_concurrency=16
myisam_sort_buffer_size=64M

the hit ctrl+x and save the file

4) chown -R mysql:mysql /home/mysql
5) rm /tmp/mysql.sock
6) ln -s ../../home/mysql/mysql.sock /tmp/mysql.sock
7) Run Command: service mysql restart

Now MySQL should be successfully started and operating from the location /home/mysql

You can remove the old mysql directory by using rm -rf /var/lib/mysql


Thanks,

Derl McMeekin
Administrator
HostSafely Web Hosting

[rachelm]

changing my.cnf should only be needed if you are using a custom one.

Also might want to turn taillogd off or it will restart mysql during the copying of the mysql files which can corrupt the mysql files or copy incorrect files (eg you copy a db then mysql restarts and the database changes after that, would result in missing data).

[linuxbabu]

The instructions are correct. There should be no issues migrating the mysql to different directory.