….00MB(free space) after this I executed alter table abc engine=innodb and re-executed SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024… space remains the same. that means that table could not be defragmented. is there any other way to defragment innodb tables? thanks Kuldeep
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases …– — Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET …
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… –innodb-file-per-table –target-dir=/mnt/mysql/export/ –tables=data # xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export ALTER TABLE data DISCARD TABLESPACE; # replace ibd with the export files SET GLOBAL innodb_import_table_from_xtrabackup = 1; ALTER TABLE…
Comment: Best kept MySQLDump Secret
March 29, 2012 By 11 Comments
…=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `z` — LOCK TABLES `z` WRITE; /*!40000 ALTER TABLE `z` DISABLE KEYS */; INSERT INTO `z` VALUES (‘a’); /*!40000 ALTER TABLE `z` ENABLE KEYS */; UNLOCK TABLES; /*!40103…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… repositories i.e. updates on CentOS. If you are using InnoDB, it is also important that your transaction logs (ib_logfile….15 sec) mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age smallint… is when you are planning to execute a long running ALTER or server upgrade, this method can be good quick rollback…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and… for ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy… again! Conclusion: As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are…
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of…_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Next step is to remove created .ibd file, it’s done by command: alter table order_line… command: alter table order_line import tablespace; now some magic happens and you can see progress of import in error.log InnoDB: import…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… as with MyISAM tables when you just copy table.frm, table.MYD, table.MYI files, but so nice here – just can copy InnoDB tables in fully… innodb_expand_import=1 setting) 4) run alter table IMPORTEDTABLE discard tablespace; 5) copy .exp and .ibd files to destination server 6) run alter table…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are… days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build… aspect separately. Improved Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts…
