So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be of any useful?
Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 11:55 AM, yoku ts. <yoku0...@gmail.com> wrote: > 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 >> > >> > >> > >
