Change MySQL data directory

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Why do you want to change the directory for MySQL? Where are you wanting to change it to? If you provide the details, I can give you the commands to do it.
 

Lor

Well-Known Member
Apr 29, 2005
63
0
156
Why do you want to change the directory for MySQL? Where are you wanting to change it to? If you provide the details, I can give you the commands to do it.
I want to move the dir to, because I have no space on drive.

Who can provide final solution?
Thanks
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
You would use the following steps to move the directory to /home partition after ensuring you've taken a full MySQL dump of the databases:

Code:
mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz
/etc/init.d/mysql stop
Go to WHM > Service Manager and uncheck monitor for MySQL so that MySQL is not restarted while you are doing this portion.

Code:
cd /var/lib
mv mysql /home/mysql
ln -s /home/mysql mysql
chown -R mysql:mysql /home/mysql
Go back to Service Manager and recheck monitor option for MySQL and save the area.

Code:
ps aux | grep mysql
Check after MySQL is started in Service Manager area that it is running.
 

lbeachmike

Well-Known Member
Dec 27, 2001
306
1
316
Long Beach, NY
cPanel Access Level
Root Administrator
Tristan - Two questions for you -

1. I have run into the same issue of inadequate space on /var (thanks Softlayer for obsolete provisioning) - is there any reason *not* to move the mysql data files to /home? Any downside? Any benefit to having it on /var?

2. Why are you prescribing the move using a symlink, versus changing the data directory setting in my.cnf?

Thanks.

Mike
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hi Mike,

1. The only reason not to move /var/lib/mysql directory to /home would be the worry of a read-only file system. The reason MySQL is in a separate partition (/var) is that you are trying to avoid it being in /home which has more reads and writes, and is thus more likely to go read only. When you have separate partitions, you are minimizing the risk of that less used partition becoming corrupt and inaccessible.

2. Personally, I do not know the repercussions for any scripts we use that expect the directory to be in /var/lib/mysql of changing the setting in /etc/my.cnf instead. We place the MySQL directory into /var/lib/mysql during installation and that's where we expect it to exist. If you change the directory in /etc/my.cnf rather than moving it and symlinking back to /var/lib/mysql, you might have unexpected results. Do that at your own risk.

Thanks!
 

lbeachmike

Well-Known Member
Dec 27, 2001
306
1
316
Long Beach, NY
cPanel Access Level
Root Administrator
Thanks Tristan.

Is there perhaps another directory in /var that takes up some space that would be preferred to symlink over to /home with less concerns?

We see this issue so often - I'm not certain why server providers provision such a scarce amount of space for /var (and /usr) by default. I can think of no reason other than that they want to see people "outgrow" their server sooner and thus decide to upgrade.

Thanks for your help.

Mike
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
The largest using directory in /var is normally always /var/lib/mysql, so that's the one that will benefit the most from being moved. The only other directory that takes a lot of space sometimes would be /var/log and you could always try symlinking it elsewhere. It only contains log files, so the worst that could happen if it were improperly moved would be to lose log files.
 

laluvirtual

Member
Aug 31, 2006
24
0
151
Hi,
i follow this step but it does't works in my case
let my explain it:
1. my /var directory is full
2. i follow this step for solve my problem
3. after i follow all step i got this error message: Starting MySQL. ERROR! Manager of pid-file quit without updating file
4. i run /script/mysqlup --force
5. MySQL server running with succes but all my site can not connect to the database

any idea???

thanks
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Were you in /var/lib when you ran the symlink command? Can you check if you do have a symlink in /var/lib/mysql to /home/mysql or not?