How to move MySQL data directory

[pendexgabo]

hi folks
right now I have the default mysql configuration, thats why my data folder is over the /var/ partition,and since this partition is keeping without free space, I wanted to move my data folder.

when I try to stop my mysql server I got:

[[email protected] ~]# service mysql stop
MySQL manager or server PID file could not be found!       [FAILED]
[[email protected] ~]#

so I figured out that mysql I being started by Cpanel ? but adding few parameters:

[[email protected] lib]# ps aux | grep mysql
root      2701  0.0  0.0  2420 1092 ?        S    Jan21   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/xxxx.pid
mysql     2725  0.1  0.4 124448 26660 ?      Sl   Jan21  89:17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/xxxx.pid --skip-external-locking

I'm concern about if I make the data folder move and I don't change those parameters mysql wont start again.

I've been reading all FAQ's and docs about cpanel and I couldn't found any info regarding steps I should follow to modify those scripts.

or just I need to change the /etc/my.cnf and Cpanel will detect the changes?

any help will be appreciated

 

[Zishan]

The easiest way would be moving /var/lib/mysql to any other partition that has enough disk space and create a symbolic link for /var/lib/mysql pointing to that new path. e.g Assuming you have enough space in /home and you want mysql data directory moved there:

mkdir /home/var_mysql
service mysql stop (run ps ax | grep mysql to verify that no mysql process is still running. If running kill that process)
mv /var/lib/mysql /home/var_mysql ( This will move mysql data directory from /var/lib to /home/var_mysql/)
Once mysql data directory will be moved to /home/var_mysql, start mysql server
service mysql start ( run ps ax | grep mysql to verify that mysql is running)

 

[pendexgabo]

@Zishan, thank you for your reply,
I see your point but for me make a symlink looks like a hack, when for me, I should be able to modify the scripts who starts and stop the mysql process.

warm regards

 

[tyuuu]

The easiest way would be moving /var/lib/mysql to any other partition that has enough disk space and create a symbolic link for /var/lib/mysql pointing to that new path. e.g Assuming you have enough space in /home and you want mysql data directory moved there:

im sorry,can i ask some question ?

a) as the articke http://www.halfzerocan.com/howto-move-your-mysql-database-directory-to-a-second-hard-drive/ shows,

if i use your method,i do not need to run "ln -s" and edit /etc/my.cnf ?


b) use "mv" will move all configuation ?
because i use "cp -a",it seems it do not work?


thanx

 

[postcd]

Re: how to move mysql data folder

The easiest way would be moving /var/lib/mysql to any other partition that has enough disk space and create a symbolic link for /var/lib/mysql pointing to that new path. e.g Assuming you have enough space in /home and you want mysql data directory moved there:

Hello, i have just followed Your instructions and when executed command to run mysql i got:

"error retrieving current directory: getcwd: cannot access parent directories: No such file or directory chdir: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory Starting MySQL.Manager of pid-file quit without updating moved mysql linux"

Please where can be problem?

Thank You

 

[cPanelTristan]

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:

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:

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.

 

[jokesarcade1]

Re: how to move mysql data folder

This really help.
i have successfully moved my data. hurray..:D
Thankyou soo much.

 

[mohit]

Re: how to move mysql data folder

Thanks "Tristan" for excellent and easy walkthrough.

there's one correct in your post,

chown -R mysql:mysql /home/var/mysql/mysql

should be

chown -R mysql:mysql /home/var_mysql/mysql

as created dir is /home/var_mysql

 

[cPanelTristan]

Re: how to move mysql data folder

Corrected, thanks

 

[marcelo.dias]

Re: how to move mysql data folder

You can use this how to: Como mudar pasta de dados do MySQL em um Servidor com WHM/cPanel

Para os brasileiros acessem o site que fiz um roteiro: Como mudar pasta de dados do MySQL em um Servidor com WHM/cPanel

 

[BrianW.]

Re: how to move mysql data folder

Another way to move the MySQL data directory is to mount /var/lib/mysql to a separate partition. That way you can retain the default path to the data directory yet also take advantage of having your databases stored on a separate physical drive.

Mount MySQL data directory on a separate partition

Prerequisite: a free partition that will serve as a dedicated MySQL partition.

Note: These instructions assume that the partition you wish to mount is /dev/sdc1

1. Backup all MySQL databases
   

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

2. Stop tailwatchd and the mysql (tailwatchd monitors services, so disable it to prevent it from prematurely restarting mysql)
   

   /scripts/restartsrv_tailwatchd --stop
   /scripts/restartsrv_mysql --stop

3. Backup the MySQL data directory in case something goes awry
   

   mv /var/lib/mysql /var/lib/mysql.backup

4. Create the new mount point
   

   mkdir /var/lib/mysql

5. Configure /etc/fstab so that the new partition is mounted when the server boots (adjust values as necessary)
   

   echo "/dev/sdc1     /var/lib/mysql     ext3     defaults,usrquota    0 1" >> /etc/fstab

6. Mount the new partition. The following command will mount everything in /etc/fstab:
   

   mount -a

7. Change the ownership of the mount point so that it is accessible to the user "mysql"
   

   chown mysql:mysql /var/lib/mysql

8. Ensure that the permissions of the mount point are correct
   

   chmod 711 /var/lib/mysql

9. Start mysql and tailwatchd
   

   /scripts/restartsrv_mysql --start
   /scripts/restartsrv_tailwatchd --start

10. Ensure that the MySQL data directory is mounted correctly:
    

    mount |grep /var/lib/mysql

11. You should see a line that looks like this:
    /dev/sdc1 on /var/lib/mysql type ext3 (rw,usrquota)
    

 

[postcd]

Re: how to move mysql data folder

Someone can find usefull following Linux commands. They are made by professional linux admin and lead to /var/mysql folder being moved to /home/mysql -
/http://ceskeforum.com/viewtopic.php?f=9&t=583. Have comments? They are most welcome.

 

[ibumu.com]

Re: how to move mysql data folder

Hello. Tristan. I used this tutorial and all work fine, realy a good help.

Now I have a question. I use the cpanel backup module, and I not sure if the databases are backing up now.

Can you guide me on this issue?

Thanks a lot for your help!

 

[cPanelTristan]

Re: how to move mysql data folder

I'm uncertain what is meant by the cPanel backup module. Can you explain further what was done exactly?

 

[vicos]

Re: how to move mysql data folder

One other suggestion. Rather than moving /var/lib/mysql to the new location, I would COPY it. Then rename /var/lib/mysql to /var/lib/mysql.old. Then create your link to the new location.

This way you have the original directory intact in case something goes horribly wrong. Then you could simply undo the link, rename the directory back and be back where you started.

 

[cPanelTristan]

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.

 

[vicos]

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.

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]

Re: how to move mysql data folder

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]

Re: how to move mysql data folder

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]

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