Results 1 to 13 of 13

Thread: Change MySQL data directory

  1. #1
    Registered Member teksupportrena's Avatar
    Join Date
    Feb 2008
    Posts
    8

    Default Change MySQL data directory

    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 Attached Files

  2. #2
    Registered Member
    Join Date
    Jun 2004
    Location
    03819
    Posts
    37

    Default

    This script works great, thanks!

  3. #3
    Registered User
    Join Date
    Apr 2011
    Posts
    1

    Default re: Change MySQL data directory

    thank you
    but i can't download

  4. #4
    cPanel Staff cPanelTristan's Avatar
    Join Date
    Oct 2010
    Location
    somewhere over the rainbow
    Posts
    7,609
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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.
    cPResources: Support Options | More Support Options | Forums Search | cPanel.net Site Search | Mailing Lists(Alt) | Docs
    -- Tristan, Technical Analyst III, Forums Specialist, cPanel Tech Support

    Submit a ticket | Check an existing ticket

  5. #5
    Lor
    Lor is offline
    Registered Member
    Join Date
    Apr 2005
    Posts
    28

    Default re: Change MySQL data directory

    Quote Originally Posted by cPanelTristan View Post
    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.
    I want to move the dir to, because I have no space on drive.

    Who can provide final solution?
    Thanks

  6. #6
    cPanel Staff cPanelTristan's Avatar
    Join Date
    Oct 2010
    Location
    somewhere over the rainbow
    Posts
    7,609
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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.
    cPResources: Support Options | More Support Options | Forums Search | cPanel.net Site Search | Mailing Lists(Alt) | Docs
    -- Tristan, Technical Analyst III, Forums Specialist, cPanel Tech Support

    Submit a ticket | Check an existing ticket

  7. #7
    Registered Member
    Join Date
    Dec 2001
    Location
    Long Beach, NY
    Posts
    313
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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. #8
    cPanel Staff cPanelTristan's Avatar
    Join Date
    Oct 2010
    Location
    somewhere over the rainbow
    Posts
    7,609
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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!
    cPResources: Support Options | More Support Options | Forums Search | cPanel.net Site Search | Mailing Lists(Alt) | Docs
    -- Tristan, Technical Analyst III, Forums Specialist, cPanel Tech Support

    Submit a ticket | Check an existing ticket

  9. #9
    Registered Member
    Join Date
    Dec 2001
    Location
    Long Beach, NY
    Posts
    313
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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. #10
    cPanel Staff cPanelTristan's Avatar
    Join Date
    Oct 2010
    Location
    somewhere over the rainbow
    Posts
    7,609
    cPanel/WHM Access Level

    Root Administrator

    Default re: Change MySQL data directory

    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.
    cPResources: Support Options | More Support Options | Forums Search | cPanel.net Site Search | Mailing Lists(Alt) | Docs
    -- Tristan, Technical Analyst III, Forums Specialist, cPanel Tech Support

    Submit a ticket | Check an existing ticket

  11. #11
    Registered Member
    Join Date
    Aug 2006
    Posts
    21

    Default Re: Change MySQL data directory

    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. #12
    cPanel Staff cPanelTristan's Avatar
    Join Date
    Oct 2010
    Location
    somewhere over the rainbow
    Posts
    7,609
    cPanel/WHM Access Level

    Root Administrator

    Default Re: Change MySQL data directory

    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?
    cPResources: Support Options | More Support Options | Forums Search | cPanel.net Site Search | Mailing Lists(Alt) | Docs
    -- Tristan, Technical Analyst III, Forums Specialist, cPanel Tech Support

    Submit a ticket | Check an existing ticket

  13. #13
    Registered Member
    Join Date
    May 2006
    Location
    Johannesburg, South Africa
    Posts
    988
    cPanel/WHM Access Level

    Root Administrator

    Default Re: Change MySQL data directory

    Are there any suggested steps to move the /var/log/ folder to another partition, without rebooting the server?
    • cPanel :: Fantastico :: RVSkin :: WHM :: ModernBill
    • Reseller Hosting :: SSL Certificates :: Domain Registrations :: Affiliate Program
    • Blog Hosting :: CMS Hosting :: Forum Hosting :: E-Commerce Hosting

    SoftDux- The Leaders in Software
    Use the coupon: cpanel-06 to get 20% off our packages

Similar Threads

  1. delete data under .sqmaildata directory
    By crazyaboutlinux in forum Database Discussions
    Replies: 24
    Last Post: 10-13-2010, 10:46 PM
  2. Change mysql database directory
    By Kailash1 in forum cPanel & WHM Discussions
    Replies: 7
    Last Post: 04-03-2008, 09:02 PM
  3. How to change Mysql list data bases
    By equens in forum User Experience
    Replies: 3
    Last Post: 05-17-2005, 01:21 PM
  4. Changing MySQL Data Directory
    By xaze in forum cPanel & WHM Discussions
    Replies: 2
    Last Post: 11-25-2004, 01:34 PM
  5. Get skin data from different directory
    By creative7 in forum User Experience
    Replies: 0
    Last Post: 03-09-2004, 10:08 AM
bargain