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.

How to move MySQL data directory

Discussion in 'Database Discussions' started by pendexgabo, Mar 11, 2009.

  1. pendexgabo

    pendexgabo Registered

    Joined:
    Nov 6, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    hi folks
    right now I have the default mysql configuration, thats why my data folder is over the /var/ partition,and since this partition is keeping without free space, I wanted to move my data folder.

    when I try to stop my mysql server I got:

    Code:
    [root@xxxxx ~]# service mysql stop
    MySQL manager or server PID file could not be found!       [FAILED]
    [root@xxxxx ~]# 
    
    so I figured out that mysql I being started by Cpanel ? but adding few parameters:

    Code:
    [root@xxxx lib]# ps aux | grep mysql
    root      2701  0.0  0.0  2420 1092 ?        S    Jan21   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/xxxx.pid
    mysql     2725  0.1  0.4 124448 26660 ?      Sl   Jan21  89:17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/xxxx.pid --skip-external-locking
    I'm concern about if I make the data folder move and I don't change those parameters mysql wont start again.

    I've been reading all FAQ's and docs about cpanel and I couldn't found any info regarding steps I should follow to modify those scripts.

    or just I need to change the /etc/my.cnf and Cpanel will detect the changes?

    any help will be appreciated
     
  2. Zishan

    Zishan Well-Known Member

    Joined:
    Aug 6, 2005
    Messages:
    104
    Likes Received:
    1
    Trophy Points:
    0
    The easiest way would be moving /var/lib/mysql to any other partition that has enough disk space and create a symbolic link for /var/lib/mysql pointing to that new path. e.g Assuming you have enough space in /home and you want mysql data directory moved there:

     
  3. pendexgabo

    pendexgabo Registered

    Joined:
    Nov 6, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    @Zishan, thank you for your reply,
    I see your point but for me make a symlink looks like a hack, when for me, I should be able to modify the scripts who starts and stop the mysql process.

    warm regards
     
  4. tyuuu

    tyuuu Well-Known Member

    Joined:
    Oct 16, 2005
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    im sorry,can i ask some question ?

    a) as the articke http://www.halfzerocan.com/howto-move-your-mysql-database-directory-to-a-second-hard-drive/ shows,

    if i use your method,i do not need to run "ln -s" and edit /etc/my.cnf ?


    b) use "mv" will move all configuation ?
    because i use "cp -a",it seems it do not work?


    thanx
     
  5. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    621
    Likes Received:
    6
    Trophy Points:
    18
    Re: how to move mysql data folder

    Hello, i have just followed Your instructions and when executed command to run mysql i got:

    "error retrieving current directory: getcwd: cannot access parent directories: No such file or directory chdir: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory Starting MySQL.Manager of pid-file quit without updating moved mysql linux"

    Please where can be problem?

    Thank You
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    The previously provided steps did not include symlinking the old /var/lib/mysql to the new location:

    Code:
    ln -s /home/var_mysql/mysql /var/lib/mysql
    Additionally, please always create a full mysqldump before ever doing something like this as well as stopping Service Manager > MySQL monitor option, since that will restart MySQL after you've stopped it and while you are moving it. The better steps are these:

    1. Make a full mysqldump file:

    Code:
    mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
    2. Uncheck monitor in WHM > Service Manager for Mysql and save the area

    3. Stop MySQL

    Code:
    /etc/init.d/mysql stop
    4. Make the directory for MySQL in /home, move it and symlink it:

    Code:
    mkdir /home/var_mysql
    mv /var/lib/mysql /home/var_mysql
    chown -R mysql:mysql /home/var_mysql/mysql
    ln -s /home/var_mysql/mysql /var/lib/mysql
    /etc/init.d/mysql start
    5. Re-check monitor in WHM > Service Manager for MySQL and save the area

    If anything goes wrong, you have the full mysqldump backup, and can use these steps to restore from it:

    First, you'll need the MySQL root password:

    Code:
    cat /root/.my.cnf
    Once you have the password, then you can use this command to restore from that file:

    Code:
    gunzip < /home/alldatabases.sql.gz | mysql -u root -p
    You'll be asked for the password here, so simply enter the one noted in /root/.my.cnf location.
     
  7. jokesarcade1

    jokesarcade1 Registered

    Joined:
    Nov 5, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Re: how to move mysql data folder

    This really help.
    i have successfully moved my data. hurray..:D
    Thankyou soo much.
     
  8. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
    Re: how to move mysql data folder

    Thanks "Tristan" for excellent and easy walkthrough.

    there's one correct in your post,

    chown -R mysql:mysql /home/var/mysql/mysql

    should be

    chown -R mysql:mysql /home/var_mysql/mysql

    as created dir is /home/var_mysql
     
  9. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    Corrected, thanks
     
  10. marcelo.dias

    marcelo.dias Registered

    Joined:
    Mar 17, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
  11. cPBrianW

    cPBrianW Sysadmin
    Staff Member

    Joined:
    Jan 26, 2011
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    Another way to move the MySQL data directory is to mount /var/lib/mysql to a separate partition. That way you can retain the default path to the data directory yet also take advantage of having your databases stored on a separate physical drive.

    Mount MySQL data directory on a separate partition

    Prerequisite: a free partition that will serve as a dedicated MySQL partition.

    Note: These instructions assume that the partition you wish to mount is /dev/sdc1
    1. Backup all MySQL databases
      Code:
      mysqldump --opt --all-databases | gzip > /home/alldatabases.sql.gz
    2. Stop tailwatchd and the mysql (tailwatchd monitors services, so disable it to prevent it from prematurely restarting mysql)
      Code:
      /scripts/restartsrv_tailwatchd --stop
      /scripts/restartsrv_mysql --stop
    3. Backup the MySQL data directory in case something goes awry
      Code:
      mv /var/lib/mysql /var/lib/mysql.backup
    4. Create the new mount point
      Code:
      mkdir /var/lib/mysql
    5. Configure /etc/fstab so that the new partition is mounted when the server boots (adjust values as necessary)
      Code:
      echo "/dev/sdc1     /var/lib/mysql     ext3     defaults,usrquota    0 1" >> /etc/fstab
    6. Mount the new partition. The following command will mount everything in /etc/fstab:
      Code:
      mount -a
    7. Change the ownership of the mount point so that it is accessible to the user "mysql"
      Code:
      chown mysql:mysql /var/lib/mysql
    8. Ensure that the permissions of the mount point are correct
      Code:
      chmod 711 /var/lib/mysql
    9. Start mysql and tailwatchd
      Code:
      /scripts/restartsrv_mysql --start
      /scripts/restartsrv_tailwatchd --start
    10. Ensure that the MySQL data directory is mounted correctly:
      Code:
      mount |grep /var/lib/mysql
    11. You should see a line that looks like this:
      /dev/sdc1 on /var/lib/mysql type ext3 (rw,usrquota)
     
    #11 cPBrianW, May 14, 2011
    Last edited: Apr 3, 2014
  12. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    621
    Likes Received:
    6
    Trophy Points:
    18
    Re: how to move mysql data folder

    Someone can find usefull following Linux commands. They are made by professional linux admin and lead to /var/mysql folder being moved to /home/mysql -
    /http://ceskeforum.com/viewtopic.php?f=9&t=583. Have comments? They are most welcome.
     
  13. ibumu.com

    ibumu.com Member

    Joined:
    Jun 9, 2010
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Re: how to move mysql data folder

    Hello. Tristan. I used this tutorial and all work fine, realy a good help.

    Now I have a question. I use the cpanel backup module, and I not sure if the databases are backing up now.

    Can you guide me on this issue?

    Thanks a lot for your help!
     
  14. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    I'm uncertain what is meant by the cPanel backup module. Can you explain further what was done exactly?
     
  15. vicos

    vicos Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    62
    Likes Received:
    0
    Trophy Points:
    6
    Re: how to move mysql data folder

    One other suggestion. Rather than moving /var/lib/mysql to the new location, I would COPY it. Then rename /var/lib/mysql to /var/lib/mysql.old. Then create your link to the new location.

    This way you have the original directory intact in case something goes horribly wrong. Then you could simply undo the link, rename the directory back and be back where you started.
     
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    You cannot copy the MySQL directory in /var/lib/mysql to another location in /var if you are running out of space and that is why it is being moved. If you copy it to /var/lib/mysql.old rather than move or copy it to /home or another partition with space, you'll definitely not have sufficient space in /var to make the copy and wind up with corrupted data. Thus, why we are recommending moving it to /home location. If you want to create both a copy in /home and then move it to /home, sure that would work, but a copy in /var doesn't work for space issues in that partition.

    Also, simply using the cp command doesn't retain ownership on the copied files and will put the ownership to the root user you are using to perform the command, while mv will retain the file and folder permissions. If you copy to /home or anywhere else, you'll wind up with root owning it rather than mysql:mysql for the folders and files, and you'll have to fix the ownership if you copy it back to /var/lib/mysql again.
     
  17. vicos

    vicos Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    62
    Likes Received:
    0
    Trophy Points:
    6
    Re: how to move mysql data folder

    I don't think you understood my post.

    I said nothing about copying /var/lib/mysql to a new location in /var...that makes no sense.

    Rather than moving /var/lib/mysql to /home/mysql-var (or whatever), copy it. Then rename /var/lib/mysql to /var/lib/mysql.old so it essentially where it was in case of catastrophic failure (what if the destination has insufficient space or your ssh session drops and breaks your connection).

    As far as file permission with cp, surely there is a switch that will retain file permissions (how about --preserve?) ? If not, you can chmod -R pretty easily.
     
    #17 vicos, Jan 23, 2012
    Last edited: Jan 23, 2012
  18. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    621
    Likes Received:
    6
    Trophy Points:
    18
    Re: how to move mysql data folder

    Please how do you create this link after copying mysql folder to /home for example and renaming old mysql folder? Can you explain?
     
  19. vicos

    vicos Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    62
    Likes Received:
    0
    Trophy Points:
    6
    Re: how to move mysql data folder

    In Post #6, Tristan suggested these steps after stopping mysql (make sure checkserver does not restart it on you (uncheck MONITOR next to mysql in WHM service manager -- redo when u r all finished):

    mkdir /home/var_mysql
    mv /var/lib/mysql /home/var_mysql
    chown -R mysql:mysql /home/var_mysql/mysql (but if mv preserves permissions, this should not be needed)
    ln -s /home/var_mysql/mysql /var/lib/mysql
    /etc/init.d/mysql start

    ----

    I suggest something like this:

    mkdir /home/var_mysql
    cp -r -p /var/lib/mysql /home/var_mysql (-r is recursive; -p preserves mode,ownership)
    chown -R mysql:mysql /home/var_mysql/mysql (only needed if mysql does not own all the files)
    mv /var/lib/mysql /var/lib/mysql.old
    ln -s /home/var_mysql/mysql /var/lib/mysql
    /etc/init.d/mysql start

    ----

    Actually, in the past, I just copied /var/lib/mysql to /home/var_mysql, and added the right parameter to /etc/my.cnf. I *think* it is DATADIR=/home/var_mysql/mysql. Then I left /var/lib/mysql right where it was. As long as your /var still had plenty of space there was no reason to get rid of it right away. If anything failed, just get rid of the DATADIR variable in the config file and you're back to square one. You could delete /var/lib/mysql after you were confident all was well.

    But, the folks here seem to prefer using the symlink and leaving my.cnf alone

    If your /var free space is critical, then you really need to get /var/lib/mysql moved and my idea about renaming it won't be a good idea.

    doublecheck or test this code on your before having faith in it. No guarantees. Its never a bad idea to have a linux workstation in the office with an OS similar to your server. Then you can test this stuff and play around to make sure it does what you think. Then you can go do it in the real world.

    You could also download a free copy of VirtualBox and install it on linux or Windows. Then you can create a virtual machine and install a copy of Linux. Then, if you hose something up in the virtual machine, you can just recreate a new one and not do any damage to your real workstation.
     
    #19 vicos, Jan 23, 2012
    Last edited: Jan 23, 2012
  20. Indianets

    Indianets Well-Known Member

    Joined:
    Jun 13, 2008
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: how to move mysql data folder

    Lots of good info in this thread. I would suggest a way to minimize the downtime of mysql server in this process -

    1. Mount a new HDD as /data -- replace it with any other location such as /home/var_mysql where you want the mysql directory to be.

    2. Backup with mysqldump if possible to be safe.

    3. Create and do first sync while server is running, we are avoiding a huge downtime here if you mysql dir is over 10GB or so.

    mkdir /data/mysql
    chown mysql.mysql /data/mysql

    rsync -vrplogDtH /var/lib/mysql/ /data/mysql/

    rsync -vrplogDtH /var/lib/mysql/ /data/mysql/


    -- second rsync is not a mistake, run it as many times as you want, it will reduce the amount of data to be synced while mysql is down in next step. after first one, it will take just a few seconds if the server is not so busy and you have lots of small databases (which is the case with most of the hosts).

    4. Stop mysql service from Service Manager. Make sure with ps, kill if needed etc etc..

    rsync -vrplogDtH /var/lib/mysql/ /data/mysql/

    mv /var/lib/mysql /var/lib/mysql.old
    ln -s /data/mysql /var/lib


    5. Edit /my.cnf and change /var/lib/mysql occurrences to /data/mysql or follow the guidelines by others above if you did not have my.cnf already set properly.

    6. Restart the mysql service.

    7. If everything works fine, rm -rf /var/lib/mysql.old

    Hope that helps :)

    Vijay
     
    #20 Indianets, Jan 23, 2012
    Last edited: Jan 23, 2012
Loading...

Share This Page