… in the buffer pool, fast index creation provides even better performance as compared to the default method, because it does not… the table I used in my previous examples): $ mysqldump -uroot test > dump_unoptimized.sql $ mysqldump -uroot test –innodb-optimize-keys > dump_optimized… data from a dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because…
Comment: An argument for not using mysqldump
November 8, 2010 By 26 Comments
There are two alternatives, one using mysqldump and one not. Option 1 (using mysqldump) Write a customized backup script 1) Extract the… instance. mysqldump databases one by one : 7 hours mysqldump in parallel, 7 databases at a time : 3.5 hours I have performed 3 full restorations in this client’s lifetime with LogicWorks All 650+ DBs (450+ GB), 10.5 hours, loading 32 mysqldumps…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… 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). Generally LOAD DATA can be optimized better and a bit faster because of easier parsing. Mysqldump however takes…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…. Store your binlogs off your primary server so you can perform point in time recovery. Store your binlogs offsite for disaster… backups Strong Points: Very fast for logical backups – compared to mysqldump Consistent backups between myisam and innodb tables. Global read lock…
Post: How to create/restore a slave using GTID replication in MySQL 5.6
February 8, 2013 By 7 Comments
…need to be enabled in slave server and the performance impact should be taken in account. Anyway, this …1-2 | +—————+——————————————+ Now we take a backup with mysqldump from the master: # mysqldump –all-databases –single-transaction –triggers –routines –host…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… has to be performed as part of the move. Performance Innodb has a lot to offer in terms of performance – Performance benefits and drawbacks…” does not work etc. Note Performance also affects Operations aspects a lot – for example using mysqldump as a backup may well work… time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still work for…
Post: Living with backups
November 6, 2008 By 28 Comments
… very heavy queries against each table in the database when mysqldump is used or reading a lot of data when copying… executed which converts into slower queries execution and significantly degraded performance. Anything else? All modern systems usually use caching of whatever… be done about it? Nothing really when you are using mysqldump. You can play some tricks, but it’s mostly beyond…
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… in the chain. So if one node gets overloaded and performance degrades, the same happens with whole cluster. – Heterogeneous-database replication…
Post: Percona XtraDB Cluster Feature 1: High Availability
January 17, 2012 By 20 Comments
… any point of time you can shutdown any Node to perform maintenance or make configuration changes. Or Node may crash or… is three methods of SST available in Percona XtraDB Cluster: mysqldump, rsync and xtrabackup ( Percona XtraBackup with support of XtraDB Cluster… need to use our source code repository). The downside of mysqldump and rsync is that your cluster becomes READ-ONLY for…
Comment: Using LVM for MySQL Backup and Replication Setup
August 18, 2011 By 53 Comments
… replicated slave servers because of which as you can imagine performance goes for a toss and that causes slave to slow… particular volume group they are residing in , now taking a mysqldump is a long process and can’t afford the slave… this mess that i haven’t thought about apart from mysqldump ??? Tariq
