1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Change MySQL data directory

Discussion in 'General Discussion' started by teksupportrena, Jul 30, 2009.

  1. teksupportrena

    teksupportrena New Member

    Joined:
    Feb 8, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    We know the steps to change the data directory of mysql.

    I have created a script to move the datadirectory of mysql............


    just try this.........................
     

    Attached Files:

  2. curriertech

    curriertech Member

    Joined:
    Jun 25, 2004
    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    03819
    This script works great, thanks!
     
  3. alex6412

    alex6412 New Member

    Joined:
    Apr 8, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    thank you
    but i can't download
     
  4. cPanelTristan

    cPanelTristan Active Member
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,621
    Likes Received:
    4
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    Why do you want to change the directory for MySQL? Where are you wanting to change it to? If you provide the details, I can give you the commands to do it.
     
  5. Lor

    Lor Member

    Joined:
    Apr 29, 2005
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
    I want to move the dir to, because I have no space on drive.

    Who can provide final solution?
    Thanks
     
  6. cPanelTristan

    cPanelTristan Active Member
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,621
    Likes Received:
    4
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    You would use the following steps to move the directory to /home partition after ensuring you've taken a full MySQL dump of the databases:

    Code:
    mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz
    /etc/init.d/mysql stop
    Go to WHM > Service Manager and uncheck monitor for MySQL so that MySQL is not restarted while you are doing this portion.

    Code:
    cd /var/lib
    mv mysql /home/mysql
    ln -s /home/mysql mysql
    chown -R mysql:mysql /home/mysql
    Go back to Service Manager and recheck monitor option for MySQL and save the area.

    Code:
    ps aux | grep mysql
    Check after MySQL is started in Service Manager area that it is running.
     
  7. lbeachmike

    lbeachmike Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    Tristan - Two questions for you -

    1. I have run into the same issue of inadequate space on /var (thanks Softlayer for obsolete provisioning) - is there any reason *not* to move the mysql data files to /home? Any downside? Any benefit to having it on /var?

    2. Why are you prescribing the move using a symlink, versus changing the data directory setting in my.cnf?

    Thanks.

    Mike
     
  8. cPanelTristan

    cPanelTristan Active Member
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,621
    Likes Received:
    4
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    Hi Mike,

    1. The only reason not to move /var/lib/mysql directory to /home would be the worry of a read-only file system. The reason MySQL is in a separate partition (/var) is that you are trying to avoid it being in /home which has more reads and writes, and is thus more likely to go read only. When you have separate partitions, you are minimizing the risk of that less used partition becoming corrupt and inaccessible.

    2. Personally, I do not know the repercussions for any scripts we use that expect the directory to be in /var/lib/mysql of changing the setting in /etc/my.cnf instead. We place the MySQL directory into /var/lib/mysql during installation and that's where we expect it to exist. If you change the directory in /etc/my.cnf rather than moving it and symlinking back to /var/lib/mysql, you might have unexpected results. Do that at your own risk.

    Thanks!
     
  9. lbeachmike

    lbeachmike Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    Thanks Tristan.

    Is there perhaps another directory in /var that takes up some space that would be preferred to symlink over to /home with less concerns?

    We see this issue so often - I'm not certain why server providers provision such a scarce amount of space for /var (and /usr) by default. I can think of no reason other than that they want to see people "outgrow" their server sooner and thus decide to upgrade.

    Thanks for your help.

    Mike
     
  10. cPanelTristan

    cPanelTristan Active Member
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,621
    Likes Received:
    4
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    The largest using directory in /var is normally always /var/lib/mysql, so that's the one that will benefit the most from being moved. The only other directory that takes a lot of space sometimes would be /var/log and you could always try symlinking it elsewhere. It only contains log files, so the worst that could happen if it were improperly moved would be to lose log files.
     
  11. laluvirtual

    laluvirtual New Member

    Joined:
    Aug 31, 2006
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Hi,
    i follow this step but it does't works in my case
    let my explain it:
    1. my /var directory is full
    2. i follow this step for solve my problem
    3. after i follow all step i got this error message: Starting MySQL. ERROR! Manager of pid-file quit without updating file
    4. i run /script/mysqlup --force
    5. MySQL server running with succes but all my site can not connect to the database

    any idea???

    thanks
     
  12. cPanelTristan

    cPanelTristan Active Member
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,621
    Likes Received:
    4
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    Were you in /var/lib when you ran the symlink command? Can you check if you do have a symlink in /var/lib/mysql to /home/mysql or not?
     
  13. SoftDux

    SoftDux Member

    Joined:
    May 27, 2006
    Messages:
    986
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Johannesburg, South Africa
    Are there any suggested steps to move the /var/log/ folder to another partition, without rebooting the server?
     

Share This Page