Concluded my first MySQL University Session about MySQL backups using file system snapshots - some questions remained unanswered...

Thanks for the great lecture. Let me propose a safe way to shutdown and restart a Slave for the purposes of taking an LVM backup. As noted in the lecture, FLUSH TABLES WITH READ LOCK is not a safe option. So the server must be stopped. I am assuming as system with a mixture of InnoDB and MyISAM, but no Maria or Falcon. Graceful shutdown... 1. Take the Slave out of rotation -- Tell the load balancer to send read traffic elsewhere. This may take some time to take effect. 2. STOP SLAVE IO_THREAD; -- This defines the "point in time" of the snapshot; now we need to get InnoDB and MyISAM to that point. 3. SET GLOBAL innodb_max_dirty_pages_pct = 10; -- start the flushing of the Buffer pool. This will take some time, and probably overwhelm even a RAID's BBWC. "10%" leaves some room for the final read and replication statements to run somewhat efficiently. 4. FLUSH TABLES; (without WITH READ LOCK). -- This would flush MyISAM blocks, but is probably not necessary. 5. Wait for the SQL_THREAD to be idle. -- This says that replication has caught up to the "point in time". However, cached stuff still has not been safely flushed to disk. This is probably the longest step. 6. Set the innodb_fast_shutdown = 0? 1? -- I guess this causes the buffer pool to be flushed <u>before</u> shutdown, not recovered after restart. The goal is to avoid ROLLBACKs that would violate the "point in time". 7. Stop MySQL. -- I hope this will finish the flushing of the buffer pool, key_buffer, etc. Will it? Then the shapshot should capture the replication position and tables of both engines to the "point in time". Graceful Restart... 1. Start MySQL. (Hopefully the flags that were changes will spring back to the normal values.) 2. SLAVE START. -- In case you default it to "stopped". 3. Wait for replication to catch up. 4. Depending on various factors, make the server live now, or wait until after the snapshot: 4a. IF this is a slave you had no other slaves in rotation, put it in rotation immediately. (Being up is more important than being fast.) 4b. IF this is a snapshot of the live Master go live right away. 4a. IF disk activity is normally under 30%, put the slave back in rotation. -- At this point, the caches would not be primed much, but readers will also be slow to return to this slave. So performance should not be too bad. 4b. IF disk activity is normally high, don't put the slave back in rotation until after the snapshot is finished and the LV is dropped. Now take the snapshot (rsync / tar-zip / whatever). -- Rick (the one who pestered you with questions) Duh?? How do I put hard returns in this blog??