Solved!
Go to SolutionCompact MySQL DB to clear empty tables.
Hello,
I am looking for a best practice to manage the DB size. We need to compact the DB size as the volume is running out of disk space.
This is obviously temporary solution until we move DB to dedicated SQL box.
Regards,
Paresh
I am looking for a best practice to manage the DB size. We need to compact the DB size as the volume is running out of disk space.
This is obviously temporary solution until we move DB to dedicated SQL box.
Regards,
Paresh
lmurphy1
57 Posts
188
0
Posted September 29th, 2010 16:00
Reclaiming Table Space
MySQL with innodb engine does not shrink the tablespace file, even after you truncate the tables. To decrease the size of your tablespace, use the following procedure:
1. Stop the Foglight Management Server:
$FOGLIGHT_HOME\bin\fms -q
2. Start the MySQL server:
$FOGLIGHT_HOME\bin\runDb.bat
3. Use mysqldump to dump all your InnoDB tables:
cd $FOGLIGHT_HOME\mysql\bin
mysqldump -P[dbport] -u[dbuser] -p[dbpwd] -e -q -B [dbname] -r mysqldata.sql
Note: Substitute the correct username, password, port, and dbname.
4. Stop the MySQL server:
$FOGLIGHT_HOME\bin\shutdownDb.bat
5. Remove all the existing tablespace files:
cd $FOGLIGHT_HOME\mysql\data
del ib_log*
del ibdata*
6. Restart the MySQL server:
$FOGLIGHT_HOME\bin\runDb.bat
7. Import the dump files:
cd $FOGLIGHT_HOME\mysql\bin
mysql -P[dbport] -u[dbuser] -p[dbpwd] Note: Substitute the correct username, password, and port.
8. Stop the MySQL server.
$FOGLIGHT_HOME\bin\shutdownDb.bat
9. Restart the Foglight Management Server.
$FOGLIGHT_HOME\bin\fms
-Larry