The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Transferring MySQL to a different directory

Discussion in 'Workarounds and Optimization' started by HostSafely, Dec 30, 2009.

  1. HostSafely

    HostSafely Registered

    Joined:
    Dec 7, 2007
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    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
     
  2. rachelm

    rachelm Well-Known Member

    Joined:
    Jan 26, 2005
    Messages:
    94
    Likes Received:
    1
    Trophy Points:
    8
    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).
     
  3. linuxbabu

    linuxbabu Registered

    Joined:
    Sep 13, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    The instructions are correct. There should be no issues migrating the mysql to different directory.

    :)
     
Loading...

Share This Page