MySQL Forums
Forum List  »  InnoDB

Re: How do we resize ibdata file?
Posted by: Aftab Khan
Date: March 19, 2009 09:45AM

yes there is a way

Add following line into my.cnf(ini) file


You can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

#lets make a backup of current database. -p is used if there is pw set

/usr/local/mysql/bin/mysqldump -R -q -p --all-databases > /some-path/all.sql

#stop mysql so we can remove all the files in the dir

/etc/init.d/mysql stop
mysqladmin -p shutdown

rm -fr /path-to-datadir/* #remove all the files

path-to-mysql-install/mysql_install_db #install default dbs

#change ownership so mysql user can read/write to/from files

chown -R mysql.mysql path-to-datadir/

#start mysql so we can import our dump
/etc/init.d/mysql start
path-to-mysql-bin/mysqldsafe --user=mysql --datadir=/path.. &

#restore backup

/usr/local/mysql/bin/mysql < /path-to-backup/all.sql

Now if you delete records than *.ibd files will shrink

Options: ReplyQuote

Written By
March 19, 2009 05:42AM
Re: How do we resize ibdata file?
March 19, 2009 09:45AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.