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

    Serra Member

    Joined:
    Oct 27, 2005
    Messages:
    190
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Florida
    Re: how to move mysql data folder

    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.
     
  2. vicos

    vicos Member

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

    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.
     
    #22 vicos, Feb 26, 2012
    Last edited: Feb 26, 2012
  3. Indianets

    Indianets Member

    Joined:
    Jun 13, 2008
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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.

     
  4. Kurieuo

    Kurieuo Member

    Joined:
    Dec 13, 2002
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    Re: how to move mysql data folder

    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
     
  5. Kurieuo

    Kurieuo Member

    Joined:
    Dec 13, 2002
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    Re: how to move mysql data folder

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

    sahostking Member

    Joined:
    May 15, 2012
    Messages:
    238
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cape Town, South Africa
    Re: how to move mysql data folder

    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 [url=http://bugs.mysql.com]MySQL Bugs[/url].
    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: [url=http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 14.3.20.2 Starting InnoDB on a Corrupted Database[/url]
    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 [url=http://dev.mysql.com/doc/mysql/en/crashing.html]MySQL :: MySQL 5.6 Reference Manual :: C.5.4.2 What to Do If MySQL Keeps Crashing[/url] 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?
     
    #26 sahostking, Nov 27, 2013
    Last edited: Nov 28, 2013
  7. sahostking

    sahostking Member

    Joined:
    May 15, 2012
    Messages:
    238
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cape Town, South Africa
    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.
     
  8. vicos

    vicos Member

    Joined:
    Apr 18, 2003
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
  9. cPanelMichael

    cPanelMichael Well-Known Member
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    17,523
    Likes Received:
    26
    Trophy Points:
    48
    Re: how to move mysql data folder

    The error log you posted indicates InnoDB corruption. You should address that before attempting to move MySQL to another partition.

    Thank you.
     
  10. sahostking

    sahostking Member

    Joined:
    May 15, 2012
    Messages:
    238
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cape Town, South Africa
    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.
     
    #30 sahostking, Apr 18, 2014
    Last edited: Apr 18, 2014

Share This Page