… Baron recommend you to be on the safe / perfect side. innodb_log_file_size is always a fixed parameter and it…). According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT seem to work if you… end result was: I could only use mysqldump to import all the data into a clean/fresh innodb system! Good luck! Thomas
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases… row in set (0.00 sec) SESSION2: root@ubuntu:~/dump# mysqldump –single-transaction dumptest > dump.sql SESSION1: (before dump has completed… 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
… on windows2003 mysql 5.5.15 while mysqldump is going , alter table was BLOCKED! Eventually mysqldump got the old table definition and…_client = utf8 */; CREATE TABLE `z` ( `n` char(1) DEFAULT ” ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign… data from a dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… | Value | +——————————+——-+ | Innodb_scan_pages_contiguous | 45 | | Innodb_scan_pages_jumpy | 35904 | +——————————+——-+ 2 rows in set (0.00 sec) As for mysqldump you may use –order-by-primary options to force dump in primary key order. So notes to highlight: InnoDB fragmentation…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… Fixed InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize… respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables where the… them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys has been fixed to take this into account…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… Fixed InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize… respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables where the… them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys has been fixed to take this into account…
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 as a backup may well work for MyISAM but will start taking way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still…
Post: Wishes for mysqldump
May 22, 2007 By 19 Comments
… corrupted database I’ve got couple of feature ideas for mysqldump or similar tool and appropriate import tool Dump in parallel… application. Safe Dump Dumping corrupted Innodb tables you will have some of the them crashing Innodb which breaks mysqldump process. It would be…
Post: An argument for not using mysqldump
November 8, 2010 By 24 Comments
… take 30×10=5 hours to restore. Right? Wrong. Mysqldump recovery time is not linear. Bigger tables, or tables…. If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much…) * 1024)/70/60 = ~17 minutes I can tell progress with mysqldump by monitoring the rate at which show global status like…
