LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   Mysql - Need to move ibdata1 file (https://www.linuxquestions.org/questions/linux-server-73/mysql-need-to-move-ibdata1-file-654409/)

helpmhost 07-08-2008 05:22 PM

Mysql - Need to move ibdata1 file
 
Hi,

Our /var partition is full and it is causing problem with our MySQL database. We've noticed that the ibdata1 file is taking up about 90% of the partition. We need a quick solution. It doesn't have to be the best because this server is being shut down permanently in 4 months.

What I was thinking of doing is moving the ibdata1 file to a larger partition. From what I've read, I think the following might work but would like to know for sure.

1. Stop the mysql server
2. Move the ibdata1 file to the other partition (/ibdata)
3. Edit my.cnf and add the following in the [mysqld] section:
innodb_data_home_dir=/ibdata
4. Restart the mysql server

Can anyone confirm if this will work in moving the file? Is there a chance that we lose some data in doing this?

Thanks.

emi_ramo 07-09-2008 05:30 AM

Another possibility is to let untouch mysql conf and make a symbolic link of ibdata1.

trickykid 07-09-2008 09:56 AM

I'd also suggest cleaning up or archiving things you may not need any longer. If you are using InnoDB for your database, sometimes a simple mysqldump and restore will do the trick. InnoDB can become fragmented and doesn't reuse the space all that well. A dump and restore can sometimes free a lot of space. How big is the ibdata1 file currently?

helpmhost 07-10-2008 04:52 PM

Hi,

emi_ramo: Do you mean move the file to another partition, and create a symlink in the old spot pointing to the new one? I originally thought of doing this but was worried that MySQL might not work if it is pointing to a symlink.

trickykid: The file is currently about 1GB. And our database itself is actually very small. Less than 50MB. So would you suggest doing the following:
mysqldump --all-databases > alldb.sql
mysql < alldb.sql


Thanks to you both.

trickykid 07-10-2008 09:30 PM

Quote:

Originally Posted by helpmhost (Post 3210501)
trickykid: The file is currently about 1GB. And our database itself is actually very small. Less than 50MB. So would you suggest doing the following:
mysqldump --all-databases > alldb.sql
mysql < alldb.sql


Thanks to you both.

I would imagine if you do a dump and the database is 50MB in size, you should be able to dump the database, drop the database, reimport and save you probably enough space to last the remaining 4 months or so.


All times are GMT -5. The time now is 06:38 AM.