… 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 7 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…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
… to MySQLDump only Innodb table in one file per database you can do the following: mysql> select concat(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql”) from tables where engine=’innodb…
Post: Unexpected problem with triggers and mysqldump
February 11, 2013 By 8 Comments
… and the data, then edit the CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new… TABLE statements in the output, but includes CREATE TRIGGER statements. Is it documented? Well, sort of… If you look at the mysqldump documentation, you will see: –triggers Include triggers for each dumped table in the output. This option…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… data to be removed or overwritten A table (or entire schema) was dropped accidentally Your InnoDB table was corrupt and mysql shuts down… logical backups – compared to mysqldump Consistent backups between myisam and innodb tables. Global read lock only held until myisam tables are dumped. We are…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… is important to load data in primary key order into Innodb tables for optimal performance, but effect does not end here. If… you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized… per hour (assuming your Innodb tablespace did not have free space or you’re using innodb_file_per_table=1). Do not expect…
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-keys… specifications and then adding them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys has been fixed to take…
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-keys… specifications and then adding them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys has been fixed to take…
Post: An argument for not using mysqldump
November 8, 2010 By 26 Comments
… to restore. Right? Wrong. Mysqldump recovery time is not linear. Bigger tables, or tables with more indexes will always take more time to restore. If I restore from a raw backup (LVM snapshot, xtrabackup, innodb…) * 1024)/70/60 = ~17 minutes I can tell progress with mysqldump by monitoring the rate at which show global status like…
