We will tend to use binary backups (Xtrabackup) for full consistent dataset restore (think slave provisioning and disaster recovery) and logical backups to perform single table restores in the event that a rollback may need to occur if someone drops a table or carries out an insane update. We will also use mydumper instead of mysqldump due to the features of compression and encryption. Mysqldump stops being useful on full|large datasets due to it's single-threaded-ness.
On Tue, Oct 7, 2014 at 8:35 AM, yoku ts. <yoku0...@gmail.com> wrote: > 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 > > >> > > > >> > > > >> > > > > > > > > >
