Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30

Thread: How to move MySQL data directory

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

    Root Administrator

    Default 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.
    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

  2. #17
    Registered Member
    Join Date
    Apr 2003
    Posts
    52

    Default Re: how to move mysql data folder

    Quote Originally Posted by cPanelTristan View Post
    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.
    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.
    Last edited by vicos; 01-23-2012 at 02:14 AM.

  3. #18
    Registered Member postcd's Avatar
    Join Date
    Oct 2010
    Posts
    360

    Default Re: how to move mysql data folder

    Quote Originally Posted by vicos View Post
    Then create your link to the new location.
    Please how do you create this link after copying mysql folder to /home for example and renaming old mysql folder? Can you explain?

  4. #19
    Registered Member
    Join Date
    Apr 2003
    Posts
    52

    Default Re: how to move mysql data folder

    Quote Originally Posted by postcd View Post
    Please how do you create this link after copying mysql folder to /home for example and renaming old mysql folder? Can you explain?
    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.
    Last edited by vicos; 01-23-2012 at 01:11 PM.

  5. #20
    Registered Member Indianets's Avatar
    Join Date
    Jun 2008
    Posts
    65
    cPanel/WHM Access Level

    Root Administrator

    Default 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
    Last edited by Indianets; 01-23-2012 at 05:55 PM.

  6. #21
    Registered Member
    Join Date
    Oct 2005
    Posts
    176

    Default Re: how to move mysql data folder

    Quote Originally Posted by Indianets View Post
    7. If everything works fine, rm -rf /var/lib/mysql.old
    Just for reference, I used the old method with a 5gb database and the total time mysqld was offline was about 15 minutes. Since I did it very early in the morning, it wasn't that much of an issue and I warned all of the sensitive data people the move was coming.

    Your method would have limited that time down to about 5 minutes, which would be very important with a 10gb database.

    Thanks for your input.

  7. #22
    Registered Member
    Join Date
    Apr 2003
    Posts
    52

    Default Re: how to move mysql data folder

    Quote Originally Posted by Indianets View Post
    4. Stop mysql service from Service Manager. Make sure with ps, kill if needed etc etc..
    chksrv is going to try and restart mysql every 5 minutes if it finds it not running. You should go into WHM service configuration and uncheck the monitor box next to mysql. Then recheck it once you are finished. You don't want it starting up during your move.

    I would also use:

    service mysql stop

    to stop it. Then you can see if you get the OK or if there are issues. But, be sure to check it with ps -ef|grep mysql to be sure.
    Last edited by vicos; 02-26-2012 at 06:58 AM.

  8. #23
    Registered Member Indianets's Avatar
    Join Date
    Jun 2008
    Posts
    65
    cPanel/WHM Access Level

    Root Administrator

    Default Re: how to move mysql data folder

    Thank you for detailing this, I actually concentrated on the move part which minimizes the downtime and skipped the details of other steps.

    Quote Originally Posted by vicos View Post
    chksrv is going to try and restart mysql every 5 minutes if it finds it not running. You should go into WHM service configuration and uncheck the monitor box next to mysql. Then recheck it once you are finished. You don't want it starting up during your move.

    I would also use:

    service mysql stop

    to stop it. Then you can see if you get the OK or if there are issues. But, be sure to check it with ps -ef|grep mysql to be sure.

  9. #24
    Registered Member
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    97

    Default Re: how to move mysql data folder

    Quote Originally Posted by Indianets View Post
    Thank you for detailing this, I actually concentrated on the move part which minimizes the downtime and skipped the details of other steps.
    Hi Indianets -interested to do your technique.

    CPU resources get eaten up though quite a bit though which drains the server.

    Do you know of a way to throttle to use of CPU resources or memory with rsync?

    Cheers, Kurieuo

  10. #25
    Registered Member
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    97

    Default Re: how to move mysql data folder

    Quote Originally Posted by Kurieuo View Post
    Hi Indianets -interested to do your technique.

    CPU resources get eaten up though quite a bit though which drains the server.

    Do you know of a way to throttle to use of CPU resources or memory with rsync?

    Cheers, Kurieuo
    Nevermind, I just happened to do it during a peak period. Didn't really get that high when off-peak.

  11. #26
    Registered Member
    Join Date
    May 2012
    Location
    Cape Town, South Africa
    Posts
    209
    cPanel/WHM Access Level

    Root Administrator

    Default Re: how to move mysql data folder

    Quote Originally Posted by cPanelTristan View Post
    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:
    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.
    Just tried this and received the following error?

    Code:
    ^[[A^[[AFailed to locate MySQL socket. Please check the mysql configuration.
    -- Startup Output --
    Starting MySQL... ERROR! The server quit without updating PID file (/var/lib/mysql/servername.pid).
    131128 01:24:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    131128  1:24:29 [Note] libgovernor.so found
    131128  1:24:29 [Note] All governors functions found too
    131128  1:24:29 [ERROR] Governor not connected
    131128  1:24:29 [Note] All governors lve functions found too
    131128  1:24:29 [Note] Plugin 'FEDERATED' is disabled.
    131128  1:24:29 InnoDB: The InnoDB memory heap is disabled
    131128  1:24:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    131128  1:24:29 InnoDB: Compressed tables use zlib 1.2.3
    131128  1:24:29 InnoDB: Using Linux native AIO
    131128  1:24:29 InnoDB: Initializing buffer pool, size = 128.0M
    131128  1:24:29 InnoDB: Completed initialization of buffer pool
    131128  1:24:29 InnoDB: highest supported file format is Barracuda.
    131128  1:24:30  InnoDB: Waiting for the background threads to start
    InnoDB: Error: trying to access page number 1551 in space 0,
    InnoDB: space name ./ibdata1,
    InnoDB: which is outside the tablespace bounds.
    InnoDB: Byte offset 0, len 16384, i/o type 10.
    InnoDB: If you get this error at mysqld startup, please check that
    InnoDB: your my.cnf matches the ibdata files that you have in the
    InnoDB: MySQL server.
    131128  1:24:31  InnoDB: Assertion failure in thread 140042440066816 in file fil0fil.c line 4579
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to MySQL Bugs.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: MySQL :: MySQL 5.5 Reference Manual :: 14.3.20.2 Starting InnoDB on a Corrupted Database
    InnoDB: about forcing recovery.
    23:24:31 UTC - mysqld got signal 6 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    We will try our best to scrape up some info that will hopefully help
    diagnose the problem, but since we have already crashed, 
    something is definitely wrong and this may fail.
    
    key_buffer_size=8388608
    read_buffer_size=131072
    max_used_connections=0
    max_threads=151
    thread_count=0
    connection_count=0
    It is possible that mysqld could use up to 
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338543 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    stack_bottom = 0 thread_stack 0x40000
    /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7b2ed5]
    /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x68a764]
    /lib64/libpthread.so.0(+0xf500)[0x7f5e40f5d500]
    /lib64/libc.so.6(gsignal+0x35)[0x7f5e401258e5]
    /lib64/libc.so.6(abort+0x175)[0x7f5e401270c5]
    /usr/sbin/mysqld[0x877f5e]
    /usr/sbin/mysqld[0x8518c8]
    /usr/sbin/mysqld[0x852177]
    /usr/sbin/mysqld[0x8462b4]
    /usr/sbin/mysqld[0x82eef7]
    /usr/sbin/mysqld[0x8385cb]
    /usr/sbin/mysqld[0x8679a4]
    /usr/sbin/mysqld[0x868a05]
    /usr/sbin/mysqld[0x8d5ed5]
    /usr/sbin/mysqld[0x8ca437]
    /usr/sbin/mysqld[0x80652a]
    /usr/sbin/mysqld[0x7fb251]
    /lib64/libpthread.so.0(+0x7851)[0x7f5e40f55851]
    /lib64/libc.so.6(clone+0x6d)[0x7f5e401db92d]
    The manual page at MySQL :: MySQL 5.6 Reference Manual :: C.5.4.2 What to Do If MySQL Keeps Crashing contains
    information that should help you find out what is causing the crash.
    131128 01:24:31 mysqld_safe mysqld from pid file /var/lib/mysql/servername.pid ended
    -- End Startup Output --

    I did create a new mount point and type mount -a.
    Then copied the files and fixed permissions as they were on root:root after copying across from mysql.backup to new mysql folder.
    Then tested and it kept failing. Tried renaming my.cnf to old and still the same aswell.

    Any ideas?
    Last edited by sahostking; 11-28-2013 at 12:36 AM.

  12. #27
    Registered Member
    Join Date
    May 2012
    Location
    Cape Town, South Africa
    Posts
    209
    cPanel/WHM Access Level

    Root Administrator

    Default Re: how to move mysql data folder

    Any response as to what the issue could be ? I want to attempt this again on the weekend and perform maintenance.

  13. #28
    Registered Member
    Join Date
    Apr 2003
    Posts
    52

    Default Re: how to move mysql data folder

    Did you stop the mysql server before doing all of this? Also remember that chksrvd is going to try and restart mysql every 5 minutes, so disable that until the move is complete.

    Note: I don't use the rsync option someone else vountered because I was not concerned about how long mysql was down for the move.

  14. #29
    cPanel Staff cPanelMichael's Avatar
    Join Date
    Apr 2011
    Posts
    13,948
    cPanel/WHM Access Level

    Root Administrator

    Default Re: how to move mysql data folder

    Quote Originally Posted by sahostking View Post
    Any response as to what the issue could be ? I want to attempt this again on the weekend and perform maintenance.
    The error log you posted indicates InnoDB corruption. You should address that before attempting to move MySQL to another partition.

    Thank you.
    cPResources: Support Options - Submit a ticket here - Additional Support Options - Forums Search - Mailing Lists - Documentation - Migration Services - Change Logs
    -- cPanelMichael - Migration Specialist, Technical Analyst, cPanel Technical Support

  15. #30
    Registered Member
    Join Date
    May 2012
    Location
    Cape Town, South Africa
    Posts
    209
    cPanel/WHM Access Level

    Root Administrator

    Default Re: how to move mysql data folder

    I found the problem - just for anyone else. It was related to rsync method. When I use mv it seems to have worked ok.

    More information as per this link I found: Problem transferring a mysql database with rsync | PatchLog

    I also had to do the following as we use Cloudlinux with CageFS:

    Added a new mysql directory to mountpoints of cagefs, this is done with /etc/cagefs/cagefs.mp , and after that execute '/usr/sbin/cagefsctl --remount-all'

    Hope this helps anyone struggling, seems rsync corrupts some data possibly and cagefs gives me db connection errors.
    Last edited by sahostking; 04-18-2014 at 06:52 PM.

Page 2 of 2 FirstFirst 12

Similar Threads

  1. Change MySQL data directory
    By teksupportrena in forum General Discussion
    Replies: 12
    Last Post: 01-18-2013, 05:52 AM
  2. local installation before move to data center
    By Paonza in forum Database Discussions
    Replies: 5
    Last Post: 11-27-2006, 05:55 PM
  3. Move server data
    By itrends in forum General Discussion
    Replies: 2
    Last Post: 03-10-2005, 10:46 AM
  4. Changing MySQL Data Directory
    By xaze in forum General Discussion
    Replies: 2
    Last Post: 11-25-2004, 01:34 PM
bargain