how to move mysql data folder

[cPanelTristan]

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.

[vicos]

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.

[postcd]

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?

[vicos]

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.

[Indianets]

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

[Serra]

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]

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]

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]

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]

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.