Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it… we altered table C at the same time as mysqldump was running we got table empty table with new structure in mysqldump instead of table with…
Comment: Best kept MySQLDump Secret
March 29, 2012 By 11 Comments
…while mysqldump is going , alter table was BLOCKED! Eventually mysqldump got the old table definition and old data in it. DROP TABLE IF…InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `z` — LOCK TABLES `z` WRITE; /*!40000 ALTER TABLE…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…table in case you will need to look into it more later. Another alternative is to dump table with MySQLDump… can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | …
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… from MyISAM to InnoDB – can I just copy .ibd files from one server to another and answer “use mysqldump” is quite disappointed. Jokes aside, I see real needs in this: – when we need to restore only single table from… 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…
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… implemented but majority of high level commands or tools (like mysqldump) do not get advantage of it and will use old…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So… the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index on non-nullable columns when the table has no PRIMARY KEY…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…=InnoDB Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump… | Value | +——————————+——-+ | Innodb_scan_pages_contiguous | 45 | | Innodb_scan_pages_jumpy | 35904 | +——————————+——-+ 2 rows in set (0.00 sec) As for mysqldump you may…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… with Innodb. Backup tools like “mysqlhotcopy” does not work etc. Note Performance also affects Operations aspects a lot – for example using mysqldump… way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still… it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details…
Post: Wishes for mysqldump
May 22, 2007 By 19 Comments
… many tables which have different priority for your application. Safe Dump Dumping corrupted Innodb tables you will have some of the them crashing Innodb which breaks mysqldump process. It would be great…
Post: Replication of MEMORY (HEAP) Tables
October 15, 2010 By 6 Comments
… or DELETE operation. So what you can do ? Use Innodb Table Instead Innodb is quite fast when it fits in memory so for… SLAVE STOP; dump all your memory tables using MySQLDump; Restart the MySQL As planned; Load Dumped tables; run SLAVE START; Remove skip-slave… will need to disable binary logging while loading data from mysqldump as you may not want these changes to be replicated…
