Thursday, July 2, 2009

Move MySQL database location

MySQL DB might using some location as data repository where hard-disk space is limited. This will warrant moving the data from that location to some other location which can provide more room to accomodate the increasing data. Following steps can move this data to the new location:


du -h : will show the memory parition of the current folder

--------------------


-->> Set the password for mysqlmanager (required if password is not set already).
mysqlmanager --passwd >> /etc/mysqlmanager.passwd

--> log into mysql manager
mysql -uroot -psh*pp@r --socket=/tmp/mysqlmanager.sock

-->> kill all instances of mysql
ps afx | grep mysql //get process ids
kill -9 4465 4473 4474 // kill those processes

-->> create the directory where you want to transfer the files
mkdir /mnt/mysql/marketingportal

-->> Give mysql the ownership of the directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

--> Open the folder where the current data is
cd mysql-marketingportal/

--> Copy this data to the new folder
cp -R * /mnt/mysql/marketingportal/

-->> Give mysql the ownership of the new directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

-->> Change the name of the old directory (optional - this is done to make sure that it isn't beign used any longer)
mv mysql-marketingportal/ mysql-marketingportal-old/

-->> edit the my.cnf file
change the value of datadir

-->> Start the mysqlmanager
nohup mysqlmanager --user=mysql > mysqlmanager.out &

4 comments:

skroderider said...

Couple of things I noticed -
"MySQL DB might using some location which has limited memory" - I think you mean limited hard disk space?

Also, this assumes that mysqlmanager is being used to start the db server(s).

aneez backer said...

Well, I edited the starting lines. Hope its more clear now. Thanks.

I think I am missing a few links here...I forgot the few intermediary steps we did to migrate the db. Am wondering what is the role of mysql manager in the whole process as such if its not being used to start the db servers. Also, kindly put in the command to start the db servers.

skroderider said...

Well, mysqlmanager is used to start all the database servers mentioned in my.cnf. The mysqlmanager shell even lets you start/stop individual instances.

Ecommerce website developer said...

Off course, we needed this information and finally got it.. Wonderful steps.. You guys are really doing great job. Keep it us.. Always appreciated.