save binlog position and flush logs before backup

1 files changed, 14 insertions, 5 deletions

diff --git a/mysql/mysql_backup.sh b/mysql/mysql_backup.sh
index a255314..689fb70 100755
--- a/mysql/mysql_backup.sh
+++ b/mysql/mysql_backup.sh
@@ -41,10 +41,16 @@ _check_tools mysqlshow mysqldump ncftpput
 echo "Started backup of mysql databases ($(date +%c)):" | _log
 echo | _log
 
-# pause the replication to be able to do consistent backups
-echo "STOP SLAVE SQL_THREAD;" | mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
+# flush logs on master
+# get master
+MYSQL_MASTER=$(sed -n '4p' /var/lib/mysql/master.info)
+
+echo "flush logs;" | mysql -h${MYSQL_MASTER} -u${MYSQL_USER} \
     -p${MYSQL_PASSWORD}
 
+# pause the replication to be able to do consistent backups
+mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} stop-slave
+
 # create temporary dir
 mkdir -p ${TMP_DIRECTORY}
 TMP_DIR=$(mktemp -d --tmpdir=${TMP_DIRECTORY})
@@ -55,16 +61,19 @@ MYSQL_DATABASES=$(mysqlshow -h${MYSQL_SERVER} -u${MYSQL_USER} \
     -p${MYSQL_PASSWORD} | awk '{ print $2 }' | grep -v "^$" | sed \
     '1d')
 
+# get binlog position
+MYSQL_BINLOG=$(sed -n '2p;3p' /var/lib/mysql/master.info | while read i
+    do echo -n "-$i" ; done)
+
 # backup all databases to file
 for db in ${MYSQL_DATABASES}
 do
     mysqldump -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
-	${db} | bzip2 -zc > "${BACKUP_PREFIX}_${db}.sql.bz2"
+	${db} | bzip2 -zc > "${BACKUP_PREFIX}-${db}${MYSQL_BINLOG}.sql.bz2"
 done
 
 # restart the replication
-echo "START SLAVE SQL_THREAD;" | mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
-    -p${MYSQL_PASSWORD}
+mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} start-slave
 
 # echo created log of file sizes
 du -sch * | _log