How to move MySQL data directory

Serra

Well-Known Member
Oct 27, 2005
258
17
168
Florida
Re: how to move mysql data folder

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.
 

vicos

Well-Known Member
Apr 18, 2003
73
1
158
Re: how to move mysql data folder

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:

Indianets

Well-Known Member
PartnerNOC
Jun 13, 2008
65
0
56
cPanel Access Level
Root Administrator
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.

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.
 

Kurieuo

Well-Known Member
Dec 13, 2002
106
0
166
Australia
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.
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
 

Kurieuo

Well-Known Member
Dec 13, 2002
106
0
166
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
Nevermind, I just happened to do it during a peak period. Didn't really get that high when off-peak.
 

sahostking

Well-Known Member
May 15, 2012
355
7
68
Cape Town, South Africa
cPanel Access Level
Root Administrator
Twitter
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:
[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
[/CODE]

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 [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?
 
Last edited:

vicos

Well-Known Member
Apr 18, 2003
73
1
158
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.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,196
363
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.
The error log you posted indicates InnoDB corruption. You should address that before attempting to move MySQL to another partition.

Thank you.
 

sahostking

Well-Known Member
May 15, 2012
355
7
68
Cape Town, South Africa
cPanel Access Level
Root Administrator
Twitter
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: