yes and after that you can do "optimize table" like for MYISAM "ibdata1", "ib_logfile0", "ib_logfile1" will still exists and MUST NOT be removed but "ibdata1" will not grow endless
i do not know "other benefits" but they are enough on the other hand - what are the benefits of having a dumb large and endless growing file with no way to shrink? [root@srv-rhsoft:~]$ ls /data/db/mysql-srv/dbmail/ | grep ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_config.ibd -rw-rw---- 1 mysql mysql 64K 2011-10-02 04:00 cms1_global_cache.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_haupt.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_locks.ibd -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_meta.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_snippets.ibd -rw-rw---- 1 mysql mysql 96K 2011-09-23 17:49 cms1_sub2.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_sub.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_user_group_permissions.ibd -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:43 cms1_user_login.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-24 17:51 cms1_user_modules.ibd -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_users.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases_global.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_allowed_hosts.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_client_admins.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_clients.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 dbmail_acl.ibd -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_aliases.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_auto_notifications.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_auto_replies.ibd -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_ccfield.ibd -rw-rw---- 1 mysql mysql 216K 2011-10-02 18:31 dbmail_datefield.ibd -rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:31 dbmail_envelope.ibd -rw-rw---- 1 mysql mysql 448K 2011-10-02 18:31 dbmail_fromfield.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-30 11:56 dbmail_headername.ibd -rw-rw---- 1 mysql mysql 11M 2011-10-02 18:31 dbmail_headervalue.ibd -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_mailboxes.ibd -rw-rw---- 1 mysql mysql 26M 2011-10-02 18:31 dbmail_messageblks.ibd -rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:50 dbmail_messages.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_pbsp.ibd -rw-rw---- 1 mysql mysql 160K 2011-10-02 18:31 dbmail_physmessage.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-30 11:59 dbmail_referencesfield.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_replycache.ibd -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_replytofield.ibd -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_sievescripts.ibd -rw-rw---- 1 mysql mysql 360K 2011-10-02 18:31 dbmail_subjectfield.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_subscription.ibd -rw-rw---- 1 mysql mysql 376K 2011-10-02 18:31 dbmail_tofield.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_usermap.ibd -rw-rw---- 1 mysql mysql 64K 2011-10-02 18:50 dbmail_users.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_mta.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_recipient_relay.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_reply_groups.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_domains.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_senders.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_sender_relay.ibd -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_spamfilter.ibd -rw-rw---- 1 mysql mysql 128K 2011-09-23 17:50 dbma_transports_error.ibd Am 02.10.2011 22:25, schrieb Dhaval Jaiswal: > Currently in my database only 5 tables are of innodb engine. > > "innodb_file_per_table" will create each file per table is it ? what are the > other benefits of it. > > If so than again i have to follow the dump & restore procedure. > > > > On Mon, Oct 3, 2011 at 1:50 AM, Reindl Harald <h.rei...@thelounge.net>wrote: > >> >> >> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal: >>> Hi All, >>> >>> How to shrink the Mysql ibdata1 files. >>> >>> The actual size of database is only hardly 10G, but ibdata1 is showing >> 73G >>> full. I did some googling and found the following procedure. >>> >>> Do a mysqldump of all databases, procedures, triggers etc >>> Drop all databases >>> Stop mysql >>> Delete ibdata1 and ib_log files >>> Start mysql >>> Restore from dump >>> >>> When you start MySQL in step 5 the ibdata1 and ib-log files will be >>> recreated. >>> >>> >>> Is this the only option with mysql? Can't we update the statistics of >>> database & reduce the file size. >>> >>> I am currently using 5.0.X. >> >> "innodb_file_per_table" is your friend, but way too late >> that should have been considered BEFORE the setup >> >> now you have only the option dump your databases >> reset the server >> configure innodb_file_per_table >> restore the backup >> >> > > -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
signature.asc
Description: OpenPGP digital signature
