How to move MySQL data directory

[Serra]

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]

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.

 

[Indianets]

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]

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]

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]

Re: how to move mysql data folder

The previously provided steps did not include symlinking the old /var/lib/mysql to the new location:

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:

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz

2. Uncheck monitor in WHM > Service Manager for Mysql and save the area

3. Stop MySQL

/etc/init.d/mysql stop

4. Make the directory for MySQL in /home, move it and symlink it:

[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:

cat /root/.my.cnf

Once you have the password, then you can use this command to restore from that file:

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?

^[[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?

 

[sahostking]

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.

 

[vicos]

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]

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]

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.

 

[cPanelLauren]

See SOLVED - mysql appears to be down after moving database for an update to this for MariaDB

 

[Waqass]

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'

This saved so much hassle. It must be added in the top thread. I was banging my head why it was not working when each step was executed carefully and repeatedly the same issue. CageFs was the culprit.

 

[azadhussnain]

When i run mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
i get this error:
mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES

 

[cPRex]

@azadhussnain - it sounds like there could be a problem with that table. Do you see any more details in the MySQL error log related to that database?