Move MySQL Databases

[patchwork]

I want to move the MySQL databases to a different location, is this the correct procedure? Do I need to do anything else or modify any other files?


# stop services
/etc/rc.d/init.d/chkservd stop
/etc/rc.d/init.d/mysql stop


# double check mysql has stopped
ps aux|grep mysql

# copy databases to a new location
cp -r /var/lib/mysql /newpath/mysql


alter /etc/my.cnf

change
datadir=/var/lib/mysql
to
datadir=/newpath/mysql


# restart services
/etc/rc.d/init.d/mysql start
/etc/rc.d/init.d/chkservd start


Thanks
Pete

 

[Brownie]

What i did, was:

stopped mysql,

moved all the databases to /home/mysql

then went back to /var/lib - and created a symlink - much easier :p

 

[wwsphil]

solution to move mysql db away from /var

Hi all
I had the same problem. The/var directory got full and I had to move the mysql databases.

I requested support from cpanel. Answer:

http://www.eth0.us/mysql-dir


Regards,
Phil

copy and pasted text from http://www.eth0.us/mysql-dir:

Change the mysql directory
By eth00
Created 2005-12-16 10:21

While not all datacenters do there are some that by default partition the system in a less then ideal manner. I have had to deal with quiet a few clients who have filled up their /var partition due to mysql being stored by default on the partition. This is a short little guide to show you how to move mysql to another parition on the system. I have another guide that shows you how to parition a drive for mysql but many times it is not possible to repartition a drive and it is a lot easier to simply move it to another live parition. I would make sure and leave mysql in the old place, or at least a backup copy, for a few days before deleting it to make sure all of the data was transfered fine. If you want to move to a partition other then /backup/ simply change backup to home or the partition name anyplace you see it. In addition to the space considerations moving to a different drive can also have a profound positive influence on the system performance.

First edit the my.cnf:

pico -w /etc/my.cnf

Now in the mysqld section add the following:

pid-file = /backup/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock

datadir = /backup/mysql
basedir = /backup/mysql



Now we are going to copy all of the data to the new partition. Notice that we do the copy TWICE, that is because moving 5gb of data can take some time and the tables may have changed. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down mysql before running this command. If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.


rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/
rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/


Now we need to setup the mysql.sock so that it operates correctly:
ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

Restart mysql so it is on the new parition:
killall -9 mysqld
service mysql start



*Note* I do not show you deleting the /var/lib/mysql directory, go ahead and do that a few days after the move if you do not have good backups incase something went wrong. Make sure when you delete the /var/lib/mysql directory you recreate it so that the mysql.sock file can be created in the directory. Do the following to remove the old data and get the mysql.sock correctly set back up.

rm -rf /var/lib/mysql
mkdir /var/lib/mysql
chown mysql /var/lib/mysql
service mysql restart
ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

Thats it, you are all done with moving mysql!

 

[Crooner]

What i did, was:

stopped mysql,

moved all the databases to /home/mysql

then went back to /var/lib - and created a symlink - much easier :p

Is there any reason this should not work? It seems so much simpler than the other solutions in this thread.

Thanks,
Dean

 

[chirpy]

It works just fine.

 

[Crooner]

It works just fine.

Thanks - I've been running tests all day and haven't seen any problems but assurance from an expert seals the deal!

BTW - take a look at ASSP. I've had you install Mailscanner/Spamassasin on all my other servers but this looks like a great spam solution.

 

[forlinuxsupport]

i used the symlinking and cpanel did NOT include mysql in the backup.

Please check and make sure your mysql is still being backed up.

 

[chirpy]

If you edit /etc/my.cnf and under [mysqld] specify the new path to the mysql directory:

datadir = /home/mysql/

Then restart mysql (and check it's working as expected).

The cPanel backup procedure will then pick up the new path and back it up correctly

 

[Bravo]

I'm trying to move /var/lib/mysql to /backup/mysql/ doing the following:

stop mysql:
/etc/rc.d/init.d/chkservd stop
/etc/rc.d/init.d/mysql stop

copy to /backup:
rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/

rename /var/lib/mysql/ to /var/lib/mysql2/

Up to this point works fine. Then I try to create a symlink and it fails:

ln -s /backup/mysql/ /var/lib/mysql/
ln: creating symbolic link `/var/lib/mysql/' to `/backup/mysql/': No such file or directory

I double checked and the directory /backup/mysql/ exists. Why am I getting this error?

 

[mohit]

I'm trying to move /var/lib/mysql to /backup/mysql/ doing the following:


rename /var/lib/mysql/ to /var/lib/mysql2/

Up to this point works fine. Then I try to create a symlink and it fails:

ln -s /backup/mysql/ /var/lib/mysql/
ln: creating symbolic link `/var/lib/mysql/' to `/backup/mysql/': No such file or directory

I double checked and the directory /backup/mysql/ exists. Why am I getting this error?

you've already renamed the /var/lib/mysql/ how would it link ????


mohit

 

[Bravo]

you've already renamed the /var/lib/mysql/ how would it link ????

does /var/lib /mysql/ have to exist in order to make it a symlink to /backup/mysql/?
I thought if it existed ln -s would not be able to create it as a symlink

 

[cPanelKenneth]

Drop the trailing /

[email protected] [~]# ln -s /root/tmp /tmp/tmp
[email protected] [~]# ln -s /root/tmp /tmp/tmp2/
ln: creating symbolic link `/tmp/tmp2/' to `/root/tmp': No such file or directory

The first command succeeds, the second fails due to the trailing /

[email protected] [~]# ls -l /tmp
total 296
drwxrwxrwt   8 root     root       4096 Aug 28 09:58 ./
drwxr-xr-x  28 root     root       4096 Aug 21 07:54 ../
drwxr-xr-x   2 root     root       4096 Aug 28 09:49 cpbandwidth/
-rw-r--r--   1 tramel   abcdee       11 Aug 28 00:00 cptest
drwxrwxrwt   2 root     root       4096 Aug 21 07:55 .font-unix/
drwxr-xr-x   2 tomcat   nobody     4096 Aug 21 07:55 hsperfdata_tomcat/
drwxrwxrwt   2 root     root       4096 Aug 21 07:54 .ICE-unix/
drwx------   2 root     root      16384 Sep 13  2006 lost+found/
srwx------   1 nobody   nobody        0 Aug 27 09:20 mod_mono_server_global=
-rw-------   1 nobody   nobody        0 Aug 27 09:20 mod_mono_server_global_1651471482
lrwxrwxrwx   1 root     root         25 Jun 18 10:55 mysql.sock -> /var/lib/mysql/mysql.sock=
-rw-r--r--   1 root     root     196597 Aug 23 13:00 packages.log
-rw-------   1 nobody   nobody    18216 Aug 23 14:18 sess_8d721bfacfef2ac4f67e8e1803298fd1
-rw-------   1 nobody   nobody    16665 Aug 23 14:17 sess_ff7bb60943210e0ff091b6cc4d343619
srwxrwxrwx   1 postgres postgres      0 Aug 28 09:55 .s.PGSQL.5432=
-rw-------   1 postgres postgres     25 Aug 28 09:55 .s.PGSQL.5432.lock
lrwxrwxrwx   1 root     root          9 Aug 28 09:58 tmp -> /root/tmp/
drwx------   2 nobody   nobody     4096 Aug 27 09:20 .wapi/

 

[Bravo]

Drop the trailing /

[email protected] [~]# ln -s /root/tmp /tmp/tmp
[email protected] [~]# ln -s /root/tmp /tmp/tmp2/
ln: creating symbolic link `/tmp/tmp2/' to `/root/tmp': No such file or directory

The first command succeeds, the second fails due to the trailing /

[/quote]

Thanks, it worked just fine without the trailing /

 

[activa]

i have made all the step^ , but in the end i get error when try to starting mysql i have found this error in the log file

071218 01:46:59  mysqld started
071218  1:46:59 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
071218 01:46:59  mysqld ended

note that i have made the move to directory /home/mysql

cp -r /var/lib/mysql /home/mysql

thanks

 

[pcsousa]

There are many solutions here.

I made a script to move databases to a db directory inside user space (located at /home or /home2, script checks) and then create symlink at /var/lib/mysql/

Time to time I check major databases and move them using "./move.db.to.user.area cpanel_user db". I made this because I do not know how cPanel consider mysql space used to global user quota, so I think to put it on user space. This way user also have access to it's mysql files if he wants to move or make file backup.

The script:

if [ -z "$1" ]
then
echo "use: move.db.to.user.area cpanel_user db";
exit;
fi

if [ -z "$2" ]
then
echo "use: move.db.to.user.area cpanel_user db";
exit;
fi

echo Making db directorie on $1...
HomeDirectories[0]="/home/$1"
HomeDirectories[1]="/home2/$1"
# add more if needbe
# HomeDirectories[2]="/home3/$1"

# Setup find correctly...
export IFS=$'\n'

# Loop through our array...
for x in ${HomeDirectories[@]}
do
if [ -h "$x" ]; then
# Prevent repeat because of links on different home directories ...
echo "Leaving $x because is symlink..."
else
z="$x/db"
y="/var/lib/mysql/$2"
mkdir $z
chown mysql.mysql $z
echo "Moving $y to $z..."
mv $y $z
ln -s $z/$2 $2
fi
done
echo "Done."

This works fine but I made my var really small at the beginning. For a 2*750Gb space available I made /var only 5Gb spaces and this has been a major problem.

Probably I'll move all mysql db directory too.

Do you know how mysql space are considered to user quota? Db files are mysql.mysql and not user.user owned. This way system is not considerinf them to user system quota, or am I worng?

Thank you.

 

[sharmaine001]

I followed this and this does not work

I am getting error :

Starting MySQLCouldn't find MySQL manager (/home/mysqlvarli[FAILED]/bin/mysqlmanager) or server (/home/mysqlvarlib/mysql//bin/mysqld_safe)

how do i resolve this??

Hi all
I had the same problem. The/var directory got full and I had to move the mysql databases.

I requested support from cpanel. Answer:

Change the mysql directory | eth0.us - Server admin info for cPanel, plesk, ensim and linux!


Regards,
Phil

copy and pasted text from http://www.eth0.us/mysql-dir:

Change the mysql directory
By eth00
Created 2005-12-16 10:21

While not all datacenters do there are some that by default partition the system in a less then ideal manner. I have had to deal with quiet a few clients who have filled up their /var partition due to mysql being stored by default on the partition. This is a short little guide to show you how to move mysql to another parition on the system. I have another guide that shows you how to parition a drive for mysql but many times it is not possible to repartition a drive and it is a lot easier to simply move it to another live parition. I would make sure and leave mysql in the old place, or at least a backup copy, for a few days before deleting it to make sure all of the data was transfered fine. If you want to move to a partition other then /backup/ simply change backup to home or the partition name anyplace you see it. In addition to the space considerations moving to a different drive can also have a profound positive influence on the system performance.

First edit the my.cnf:

pico -w /etc/my.cnf

Now in the mysqld section add the following:

pid-file = /backup/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock

datadir = /backup/mysql
basedir = /backup/mysql



Now we are going to copy all of the data to the new partition. Notice that we do the copy TWICE, that is because moving 5gb of data can take some time and the tables may have changed. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down mysql before running this command. If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.


rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/
rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/


Now we need to setup the mysql.sock so that it operates correctly:
ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

Restart mysql so it is on the new parition:
killall -9 mysqld
service mysql start



*Note* I do not show you deleting the /var/lib/mysql directory, go ahead and do that a few days after the move if you do not have good backups incase something went wrong. Make sure when you delete the /var/lib/mysql directory you recreate it so that the mysql.sock file can be created in the directory. Do the following to remove the old data and get the mysql.sock correctly set back up.

rm -rf /var/lib/mysql
mkdir /var/lib/mysql
chown mysql /var/lib/mysql
service mysql restart
ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
rm -rf /tmp/mysql.sock
ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

Thats it, you are all done with moving mysql!

 

[cPanelDon]

I followed this and this does not work

I am getting error :

Starting MySQLCouldn't find MySQL manager (/home/mysqlvarli[FAILED]/bin/mysqlmanager) or server (/home/mysqlvarlib/mysql//bin/mysqld_safe)

how do i resolve this??

What are the following two directives set to in your MySQL configuration?
basedir
datadir

For reference, the MySQL configuration file may be found at the following path:

/etc/my.cnf

Here is an example of what they could be set to (before moving anything):

[mysqld]
basedir                         = /usr
datadir                         = /var/lib/mysql

 

[sharmaine001]

I followed the instructions above:

datadir = /home/mysqlvarlib/mysql
basedir = /home/mysqlvarlib/mysql

 

[cPanelDon]

I followed the instructions above:

datadir = /home/mysqlvarlib/mysql
basedir = /home/mysqlvarlib/mysql

The specific instructions referenced are from the third-party and may or may not be safe if followed verbatim; please be cautious when following any guides including those from third-party sources. I recommend referring to official vendor documentation when possible, such as that by the official MySQL vendor or official MySQL documentation resources.

Assuming the MySQL binary installation is intact and located in the standard path I would change the MySQL configuration directive "basedir" to the following:

basedir = /usr

 

[sharmaine001]

The specific instructions referenced are from the third-party and may or may not be safe if followed verbatim; please be cautious when following any guides including those from third-party sources. I recommend referring to official vendor documentation when possible, such as that by the official MySQL vendor or official MySQL documentation resources.

Assuming the MySQL binary installation is intact and located in the standard path I would change the MySQL configuration directive "basedir" to the following:

basedir = /usr

I will try that but the link that your staff gave me is the same with that post. I logged a ticket and i was directed to Change the mysql directory | eth0.us - Server admin info for cPanel, plesk, ensim and linux! by your staff