Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say.
If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra <mailtogeetanj...@gmail.com>: > It seems to me that once the read lock is acquired, only the binary log > coordinates are read. Soon after binary log coordinates are read, lock is > released. Is there anything else that happens here? > > It means that after lock is released, dump is made while the read and write > activity is going on. This dump then, would be inconsistent. So, to make > this dump a consistent one when restoring it, binary log will be applied > starting from the binary log coordinates that has been read earlier. > > This is what I understand. Please correct me if my understanding is wrong. > > Best Regards, > Geetanjali Mehra > Senior Oracle and MySQL DBA Corporate Consultant and Database Security > Specialist > > > On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green <shawn.l.gr...@oracle.com> > wrote: > > > Hello Geetanjali, > > > > On 9/23/2014 7:14 AM, geetanjali mehra wrote: > > > >> Can anybody please mention the internals that works when we use > mysqldump > >> as follows: > >> > >> > >> *mysqldump --single-transaction --all-databases > > backup_sunday_1_PM.sql* > >> > >> MySQL manual says: > >> > >> This backup operation acquires a global read lock on all tables at the > >> beginning of the dump (using *FLUSH TABLES WITH READ LOCK > >> <http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this > >> lock > >> has been acquired, the binary log coordinates are read and the lock is > >> released. If long updating statements are running when the FLUSH > >> <http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is > issued, > >> the backup operation may stall until those statements finish. After > that, > >> the dump becomes lock-free and does not disturb reads and writes on the > >> tables. > >> > >> Can anyone explain it more? Please. > >> > >> > > Which part would you like to address first? > > > > I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works > but > > I want to be certain before answering. > > > > Yours, > > -- > > Shawn Green > > MySQL Senior Principal Technical Support Engineer > > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > > Office: Blountville, TN > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql > > > > >
