FreeLists / mylvmbackup / [mylvmbackup] [mylvmbackup 7/9] Extra table flushing.

["Robin H. Johnson" <robbat2@xxxxxxxxxx>]

Add the ability to run an extra FLUSH TABLES for busy databases where lvcreate
might take a long time (and overrun the interactivity timeout on the
connection, losing the lock).

---
 man/mylvmbackup.1.txt |    7 ++++++-
 mylvmbackup.conf      |    1 +
 mylvmbackup.pl.in     |   18 ++++++++++++++++--
 3 files changed, 23 insertions(+), 3 deletions(-)

diff --git a/man/mylvmbackup.1.txt b/man/mylvmbackup.1.txt
index 87be852..0f40ce5 100644
--- a/man/mylvmbackup.1.txt
+++ b/man/mylvmbackup.1.txt
@@ -99,11 +99,16 @@ the leading dashes, of course).
   prior to performing the backup.
 
 --skip_flush_tables::
-  Don't issue a FLUSH TABLES WITH READ LOCK command before creating the
+  Don't issue a 'FLUSH TABLES WITH READ LOCK' command before creating the
   snapshot. Only use this option when backing up InnoDB tables (as they
   don't support this function anyway and will require recovery in any case).
   This option skips the (probably time consuming) flushing of buffers.
 
+--extra_flush_tables::
+  If your database performs a lot of writes, it may help to perform an extra
+  initial 'FLUSH TABLES' so that the 'lvcreate' can finish within the
+  interactivity timeout during the read-locked flush.
+
 --pidfile=string::
   Specifies the full path and file name to the PID file of the server instance
   that is spawned to perform the InnoDB recovery (see option
diff --git a/mylvmbackup.conf b/mylvmbackup.conf
index 89391ea..63944dc 100644
--- a/mylvmbackup.conf
+++ b/mylvmbackup.conf
@@ -55,6 +55,7 @@ datefmt=%Y%m%d_%H%M%S
 innodb_recover=0
 pidfile=/var/tmp/mylvmbackup_recoverserver.pid
 skip_flush_tables=0
+extra_flush_tables=0
 
 #
 # Logging options. The Sys::Syslog module is required for syslog option
diff --git a/mylvmbackup.pl.in b/mylvmbackup.pl.in
index 07019b3..9da1f85 100755
--- a/mylvmbackup.pl.in
+++ b/mylvmbackup.pl.in
@@ -41,6 +41,7 @@ my $datefmt;
 my $host;
 my $innodb_recover;
 my $skip_flush_tables;
+my $extra_flush_tables;
 my $lvcreate;
 my $lvname;
 my $lvremove;
@@ -183,8 +184,15 @@ my $dbh= DBI->connect($dsn,$user,$password)
 
 unless ($skip_flush_tables == 1)
 {
-  log_msg ("Flushing tables...", LOG_INFO);
-  $dbh->do("flush tables with read lock") 
+  if($extra_flush_tables == 1)
+  {
+  log_msg ("Flushing tables (initial)...", LOG_INFO);
+  $dbh->do("FLUSH TABLES") 
+    or log_msg ($DBI::errstr, LOG_ERR) && die $DBI::errstr;
+  }
+
+  log_msg ("Flushing tables with read lock...", LOG_INFO);
+  $dbh->do("FLUSH TABLES WITH READ LOCK") 
     or log_msg ($DBI::errstr, LOG_ERR) && die $DBI::errstr;
 }
 
@@ -248,6 +256,7 @@ sub load_config
   $innodb_recover=$cfg->val ('misc', 'innodb_recover', $innodb_recover);
   $pidfile=$cfg->val ('misc', 'pidfile', $pidfile);
   $skip_flush_tables=$cfg->val ('misc', 'skip_flush_tables', 
$skip_flush_tables);
+  $extra_flush_tables=$cfg->val ('misc', 'extra_flush_tables', 
$extra_flush_tables);
   $tararg=$cfg->val ('misc', 'tararg', $tararg);
   $tarsuffixarg=$cfg->val ('misc', 'tarsuffixarg', $tarsuffixarg);
   $tarfilesuffix = $cfg->val ('misc', 'tarfilesuffix', $tarfilesuffix);
@@ -299,6 +308,7 @@ sub load_args
     "innodb_recover" => \&innodb_recover,
     "pidfile=s" => \$pidfile,
     "skip_flush_tables" => \&skip_flush_tables,
+    "extra_flush_tables" => \&extra_flush_tables,
     "tararg=s" => \$tararg,
     "tarsuffixarg=s" => \$tarsuffixarg,
     "tarfilesuffix=s" => \$tarfilesuffix,
@@ -349,6 +359,7 @@ sub load_defaults
   $innodb_recover=0;
   $pidfile = '/var/tmp/mylvmbackup_recoverserver.pid';
   $skip_flush_tables=0;
+  $extra_flush_tables=0;
   $tararg='cvzf';
   $tarsuffixarg='';
   $tarfilesuffix='.tar.gz';
@@ -541,6 +552,9 @@ sub innodb_recover {
 sub skip_flush_tables {
   $skip_flush_tables = 1;
 }
+sub extra_flush_tables {
+  $extra_flush_tables = 1;
+}
 
 sub need_xfsworkaround {
        $need_xfsworkaround = 1;
-- 
1.5.3