Maybe no, as you knew. > 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.
Not only binary logs, each tables in your dump is based the time when mysqldump began to dump *each* tables. It means, for example, table1 in your dump is based "2014-10-07 00:00:00", and next table2 is based "2014-10-07 00:00:01", and next table3 is .. I don't have a motivation for restoring its consistency.. Regards, 2014-10-07 15:44 GMT+09:00 geetanjali mehra <mailtogeetanj...@gmail.com>: > 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 > >> > > >> > > >> > > > > >
