… indexes were temporarily dropped and then recreated using fast index creation; dumps produced by mysqldump first create tables with all secondary indexes…_options: Comment: 1 row in set (0.01 sec) mysqldump Quoting the fast index creation chapter in the MySQL manual: “… you can… if expand_fast_index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…. Mydumper for logical backups Strong Points: Very fast for logical backups – compared to mysqldump Consistent backups between myisam and innodb tables. Global… views/triggers/procedures etc. Run with –no-schemas, instead use mysqldump for the schemas and rely on mydumper for data only…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index…’t respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index…’t respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized better and a bit faster because of easier parsing. Mysqldump however takes care of special options to make multi value inserts as fast as possible as well…
Post: How to create/restore a slave using GTID replication in MySQL 5.6
February 8, 2013 By 7 Comments
…:1-2 | +—————+——————————————+ Now we take a backup with mysqldump from the master: # mysqldump –all-databases –single-transaction –triggers –routines –host=… to solve it. First we have the bad and fast way, that is, point to another GTID that the …
Post: State of the art: Galera - synchronous replication for InnoDB
October 27, 2009 By 15 Comments
… Release 0.7pre. In current version propagation is done by mysqldump from one of nodes (“donor”). In next release Codership is… quick maintenance, after start, the node has to load whole mysqldump, like it is new empty node. I hope Codership guys… data. Although it also has serious drawback – our cluster is fast as fast the “weakest” node in the chain. So if one…
Comment: How would you compress your MySQL Backup
June 6, 2008 By 29 Comments
If you want the best possible compression ratio go with mysqldump + bzip. I can squeeze a 130gb database into about 6… use bzip2smp or some other parralelized bzip implementation. bzip2smp is fast enough for me that it is not the bottleneck (on… 8 core server). I get almost 50% better compression of mysqldump files with bzip2smp and it runs twice as fast as gzip.
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
… with mysqldump, final size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast…, secSize, MB Baseline (1), default format, usual load915443032 default format, fast index creation4678 (0.51)*40400 (0.93) compress format, default load13735 (1.50)16284 (0.38) compress format, fast index creation6844 (0.74)14968 (0.35) * – ratio to baseline…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… on disk The problem is that after restoring table from mysqldump, the query that scans data by primary key was slow… | 35904 | +——————————+——-+ 2 rows in set (0.00 sec) As for mysqldump you may use –order-by-primary options to force dump… it (tough it is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with queries scan many records…
