If you’re just running innodb, you can run mysqldump with the –singletransaction flag which will get you a consistent logical … issue flush tables with read lock, but to do this right you have to hold the lock until your dump completes. A read lock is just that, a lock…
Comment: An argument for not using mysqldump
November 8, 2010 By 24 Comments
… (using mysqldump) Write a customized backup script 1) Extract the names of all databases (except mysql and information_schema) 2) FLUSH TABLES WITH READ LOCK; 3) Record Log File and Position from SHOW MASTER STATUS; 4) Run all mysqldumps of each database in…
Post: Percona XtraDB Cluster Feature 1: High Availability
January 17, 2012 By 18 Comments
…SST available in Percona XtraDB Cluster: mysqldump, rsync and xtrabackup ( Percona XtraBackup with support of XtraDB Cluster will be …FLUSH TABLES WITH READ LOCK command). Xtrabackup SST does not require READ LOCK for full time, only for syncing .frm files (the same as with regular backup). Even with…
Post: 10 things you need to know about backup solutions for MySQL
March 3, 2009 By 23 Comments
…, etc? What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it… InnoDB by simply copying its files? Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing. What other effects are there…
Comment: An argument for not using mysqldump
November 8, 2010 By 24 Comments
… perfect. You need to make sure that you hold a lock after FLUSH TABLES WITH READ LOCK, so in command line scripting you need to add…. There are other issues with mysqldump – but I didn’t want to cover them in this article. mysqldump needs to churn all the… this synchronizes the backup to the start of running mysqldump. Xtrabackup synchronizes with the end of the backup, which is probably the…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 25, 2010 By 28 Comments
… with a transaction snapshot, in a way which does NOT involve FLUSH TABLES WITH READ LOCK? My understanding is that mysqldump –single-transaction –master-data does a FLUSH TABLES WITH READ LOCK, only so that it can do a BEGIN TRANSACTION; SHOW MASTER STATUS then immediately UNLOCK TABLES. This means…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 28 Comments
…FLUSH TABLES WITH READ LOCK. It immediately acquired locks on all tables — effectively write locks, as you say — except this one log table. It held those locks waiting for the lock… thing with a subquery, if I remember correctly. I’ve since moved to InnoDB for everything, and use mysqldump –single…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 28 Comments
… worthwhile to note that even for InnoDB-only systems, using FLUSH TABLES WITH READ LOCK may be a requirement: in the case you want to… your’e doing “mysqldump –single-transaction –master-data” – you’re good. But a mylvmbackup would have to use the FLUSH TABLES… in order…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 12 Comments
…mix of innodb and myism tables and run on linux with 16gigs or ram. Here is…table_cache = 3072 open_files_limit = 9216 tmp_table_size=1000M sort_buffer_size = 100M read_buffer_size = 100M read…_size = 32M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed…
Comment: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…, I have problem with mysql server. here of my server detail : DELL …-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read…buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_thread_concurrency = 8 [mysqldump] quick max_allowed_…
