Change MySQL data directory

[teksupportrena]

Hi,

We know the steps to change the data directory of mysql.

I have created a script to move the datadirectory of mysql............


just try this.........................

[curriertech]

This script works great, thanks!

[alex6412]

thank you
but i can't download

[cPanelTristan]

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]

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]

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:

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.

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.

ps aux | grep mysql

Check after MySQL is started in Service Manager area that it is running.

[lbeachmike]

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]

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]

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]

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]

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]

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?

[SoftDux]

Are there any suggested steps to move the /var/log/ folder to another partition, without rebooting the server?