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

innodb_file_per_table

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
or
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
or
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

http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

Options: ReplyQuote


Subject
Views
Written By
Posted
7129
March 19, 2009 05:42AM
Re: How do we resize ibdata file?
8735
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.