… rows in set (0.00 sec) Now let’s see how performance is affected when turning expand_fast_index_creation on… is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires… must be indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index on…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… that after restoring table from mysqldump, the query that scans data by primary key was slow. How slow ? Let me show. The… | | 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 may…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to handle it, or be able to learn it. It is also important to adjust processes as required to work with Innodb… will start taking way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… in case you will need to look into it more later. Another alternative is to dump table with MySQLDump and load it back… MySQL crashes to make sure indexes are not corrupted. So we looked at how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB – can I just copy .ibd files from one server to another and answer “use mysqldump” is quite disappointed…
Post: Interested to learn about New Innodb Plugin Performance ?
April 15, 2008 By 2 Comments
… is now publicly released we’ll try to put it to some production slaves to see how well it works. There are still usability… you can blame both MySQL and Innodb for) – for example neither of standard data load tools (mysqldump import, using LOAD DATA INFILE or ALTER TABLE from other storage engine) would use Fast Innodb Index creation. Keep…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
… you would like to MySQLDump only Innodb table in one file per database you can do the following: mysql> select concat(“mysqldump “,table_schema… commands to run. How to make it easily runable ? Well just use INTO OUTFILE to create very simple shell script: mysql> select concat(“mysqldump “,table…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… to mysqldump Consistent backups between myisam and innodb tables. Global read lock only held until myisam tables are dumped. We are researching into how… charset mysqldump … -d -R –skip-triggers, out to the schema file create a schema-post file that has the triggers # mysqldump … -d -t How to…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it… when drop original table and rename such temporary table to original name. How does data visibility works in this case ? DDLs are… at the same time as mysqldump was running we got table empty table with new structure in mysqldump instead of table with…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… to perform load effectively Load Options There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output… directory to see how much data is loaded per hour (assuming your Innodb tablespace did not have free space or you’re using innodb…
