Running out of space for MySQL dbs - move them to diff. drive? - Knowledgebase

Portal Home > Knowledgebase > Articles Database > Running out of space for MySQL dbs - move them to diff. drive?

Running out of space for MySQL dbs - move them to diff. drive?

Posted by Dave_77, 10-06-2008, 05:31 PM
Hi all, We have a dedicated Fedora 7 server with Apache and mysql installed on it, and we are running a forum and a couple of blogs on it. We have recently noticed that we are running out of space on drive #3 which holds mysql databases, but have plenty of space on drive #4. It's like this: Drive sda2 has 750 megabytes free, but this is OK, protected directories only Drive sda1 is similar, small protected directories Drive sda3 is the problem. 90% of it is full (drive is about 23.7 Gb large), and it holds mySQL databases /var/lib/mysql /var/qmail/mailnames /var/named/run-root Our forum's database is getting quite large, and we have to do something about this. It is the biggest database on that drive, close to 10 gigabytes. On the other hand, drive sda 6 has almost 180 gigabytes completely free, and uses only home/httpd/vhosts which holds mostly web content files, but will never use anything near 180 gigabytes, in fact, web content will hardly go over 2 gigabytes, which means that most of the 180 free gigabytes would be free. Seeing that we have a big problem with our mySQL databases, could you offer us some advice on what is the best course of action to free up some space for mySQL databases and/or move mysql databases to this new drive? Cheers
Posted by Jonathan Kinney, 10-06-2008, 06:33 PM
I would suggest looking into pruning of old content, or something along those lines to reduce the size. Also make sure that the forum and blogs do not have any spam buildup like in the comments or by bots in the forums, that has been a major reason for giant databases in my experience. As for putting the databases on another drive, I have done that in the past. The trick is to stop the database, create a uniform directory for this purpose such as /home/httpd/vhosts/mysql/ and then move the database to that directory, and then sym-link from the original mysql directory to the new database location. Make sure the permissions and ownership are the same, and then start it back up. That is just an idea of a way to do it, but it should work.
Posted by tulix, 10-06-2008, 07:42 PM
Couple of options - the correct one will be export data base onto 180 GB partition, change my.cnf to put db files on 180 GB partition and then import the data. You can also try to move the data files and change my.cnf - may work.
Posted by jem3, 10-06-2008, 07:53 PM
I have many times moved databases to another partition that has more available space. The method Jonathan describes has always worked well for me, but instead of moving each database, I would more the entire mysql data directory and then symlink it to the new location to avoid future issues with space on the next big database. Assuming that your data directory is called 'data': 1. stop mysql daemon 2. copy data directory to /home/mysql/data (have to create /home/mysql) 3. check that your data directory copied ok to new location 4. remove old data directory from old location 5. cd /var/lib/mysql/; ln -s /home/mysql/data data 6. start mysql daemon
Posted by tanfwc, 10-06-2008, 11:00 PM
another way out is to create a dd image and mount it as /var/lib/mysql
Posted by steveks, 10-07-2008, 12:00 AM
I too would like to go with Jonathan. Creating symbolick link should do the trick. Also, try to empty the mysql log file. May be it can consume more space. Mostly it will be like /var/lib/mysql/hostname.err
Posted by Dave_77, 10-07-2008, 05:37 PM
Thanks a lot guys

Add to Favourites Print this Article