mrofi · February 20, 2023 13:41 · Nov 28, 2017 · Sep 6, 2014 · Sep 6, 2014 · Sep 6, 2014
diff --git a/mysql_backup.config b/mysql_backup.config
@@ -0,0 +1,4 @@
+[client]
+user = "mysql_user"
+password = "mysql_password"
+host = "localhost"
diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -19,18 +19,18 @@
 #==============================================================================
 
 # directory to put the backup files
-BACKUP_DIR=/Users/[your user name]/backup
+BACKUP_DIR=/var/www/backup
 
-# MYSQL Parameters
-MYSQL_UNAME=root
-MYSQL_PWORD=
+# MYSQL Config
+CONFIG_FILE=/var/www/scripts/mysql_backup.config
 
-# Don't backup databases with these names 
+# Don't backup databases with these names
 # Example: starts with mysql (^mysql) or ends with _schema (_schema$)
-IGNORE_DB="(^mysql|_schema$)"
+IGNORE_DB="(^mysql|_schema$|-old$|-recovery$)"
 
 # include mysql and mysqldump binaries for cron bash user
-PATH=$PATH:/usr/local/mysql/bin
+MYSQL=$(which mysql)
+MYSQLDUMP=$(which mysqldump)
 
 # Number of days to keep backups
 KEEP_BACKUPS_FOR=30 #days
@@ -39,8 +39,8 @@ KEEP_BACKUPS_FOR=30 #days
 # METHODS
 #==============================================================================
 
-# YYYY-MM-DD
-TIMESTAMP=$(date +%F)
+# YYYY-MM-DD-HHMMSS
+TIMESTAMP=$(date '+%F-%H%M%S')
 
 function delete_old_backups()
 {
@@ -49,30 +49,31 @@ function delete_old_backups()
 }
 
 function mysql_login() {
-  local mysql_login="-u $MYSQL_UNAME" 
-  if [ -n "$MYSQL_PWORD" ]; then
-    local mysql_login+=" -p$MYSQL_PWORD" 
-  fi
+  local mysql_login=" --defaults-extra-file=$CONFIG_FILE"
   echo $mysql_login
 }
 
 function database_list() {
   local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
-  echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
+  echo $($MYSQL $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
+}
+
+function test() {
+  echo $(mysql_login)
 }
 
 function echo_status(){
-  printf '\r'; 
-  printf ' %0.s' {0..100} 
-  printf '\r'; 
+  printf '\r';
+  printf ' %0.s' {0..100}
+  printf '\r';
   printf "$1"'\r'
 }
 
 function backup_database(){
-    backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz" 
+    backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz"
     output+="$database => $backup_file\n"
     echo_status "...backing up $count of $total databases: $database"
-    $(mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
+    $($MYSQLDUMP $(mysql_login) $database | gzip -9 > $backup_file)
 }
 
 function backup_databases(){

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -88,8 +88,8 @@ function backup_databases(){
 }
 
 function hr(){
-  for i in {1..100};do echo -n =;done
-  echo ""
+  printf '=%.0s' {1..100}
+  printf "\n"
 }
 
 #==============================================================================

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -6,7 +6,7 @@
 #DATE:             2013-12-20
 #VERSION:          0.4
 #USAGE:            ./mysql_backup.sh
-#DAILY CRON:
+#CRON:
   # example cron for daily db backup @ 9:15 am
   # min  hr mday month wday command
   # 15   9  *    *     *    /Users/[your user name]/scripts/mysql_backup.sh

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -36,40 +36,67 @@ PATH=$PATH:/usr/local/mysql/bin
 KEEP_BACKUPS_FOR=30 #days
 
 #==============================================================================
-# MAIN SCRIPT
+# METHODS
 #==============================================================================
 
+# YYYY-MM-DD
+TIMESTAMP=$(date +%F)
+
 function delete_old_backups()
 {
   echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days"
   find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
 }
 
-delete_old_backups
-
-function mysql_login()
-{
+function mysql_login() {
   local mysql_login="-u $MYSQL_UNAME" 
   if [ -n "$MYSQL_PWORD" ]; then
     local mysql_login+=" -p$MYSQL_PWORD" 
   fi
-  echo "$mysql_login"
+  echo $mysql_login
 }
 
-# build database list
-show_databases="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
-database_list=$(mysql $(mysql_login) -e "$show_databases"|awk -F " " '{if (NR!=1) print $1}')
+function database_list() {
+  local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
+  echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
+}
 
-# YYYY-MM-DD
-timestamp=$(date +%F)
-echo "Filename Timestamp: $timestamp" 
+function echo_status(){
+  printf '\r'; 
+  printf ' %0.s' {0..100} 
+  printf '\r'; 
+  printf "$1"'\r'
+}
 
-# backup all MySQL databases
-for database in $database_list; do
-  backup_file="$BACKUP_DIR/$timestamp.$database.sql.gz" 
-  echo "Backup $database to $backup_file" 
-  mysqldump $mysql_login $database | gzip -9 > $backup_file
-done
+function backup_database(){
+    backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz" 
+    output+="$database => $backup_file\n"
+    echo_status "...backing up $count of $total databases: $database"
+    $(mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
+}
 
+function backup_databases(){
+  local databases=$(database_list)
+  local total=$(echo $databases | wc -w | xargs)
+  local output=""
+  local count=1
+  for database in $databases; do
+    backup_database
+    local count=$((count+1))
+  done
+  echo -ne $output | column -t
+}
 
+function hr(){
+  for i in {1..100};do echo -n =;done
+  echo ""
+}
 
+#==============================================================================
+# RUN SCRIPT
+#==============================================================================
+delete_old_backups
+hr
+backup_databases
+hr
+printf "All backed up!\n\n"
diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -47,15 +47,18 @@ function delete_old_backups()
 
 delete_old_backups
 
-# Build Login String
-mysql_login="-u $MYSQL_UNAME" 
-if [ -n "$MYSQL_PWORD" ]; then
-  mysql_login+=" -p$MYSQL_PWORD" 
-fi
+function mysql_login()
+{
+  local mysql_login="-u $MYSQL_UNAME" 
+  if [ -n "$MYSQL_PWORD" ]; then
+    local mysql_login+=" -p$MYSQL_PWORD" 
+  fi
+  echo "$mysql_login"
+}
 
 # build database list
-show_databases="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'" 
-database_list=$(mysql $mysql_login -e "$show_databases"|awk -F " " '{if (NR!=1) print $1}')
+show_databases="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
+database_list=$(mysql $(mysql_login) -e "$show_databases"|awk -F " " '{if (NR!=1) print $1}')
 
 # YYYY-MM-DD
 timestamp=$(date +%F)
@@ -68,3 +71,5 @@ for database in $database_list; do
   mysqldump $mysql_login $database | gzip -9 > $backup_file
 done
 
+
+
diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -32,17 +32,20 @@ IGNORE_DB="(^mysql|_schema$)"
 # include mysql and mysqldump binaries for cron bash user
 PATH=$PATH:/usr/local/mysql/bin
 
+# Number of days to keep backups
+KEEP_BACKUPS_FOR=30 #days
+
 #==============================================================================
 # MAIN SCRIPT
 #==============================================================================
 
-# delete backups older than 30 days
-echo "Deleting *.sql.gz files in $BACKUP_DIR older than 30 days" 
-find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} \;
+function delete_old_backups()
+{
+  echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days"
+  find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
+}
 
-# YYYY-MM-DD
-timestamp=$(date +%F)
-echo "Filename Timestamp: $timestamp" 
+delete_old_backups
 
 # Build Login String
 mysql_login="-u $MYSQL_UNAME" 
@@ -54,6 +57,10 @@ fi
 show_databases="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'" 
 database_list=$(mysql $mysql_login -e "$show_databases"|awk -F " " '{if (NR!=1) print $1}')
 
+# YYYY-MM-DD
+timestamp=$(date +%F)
+echo "Filename Timestamp: $timestamp" 
+
 # backup all MySQL databases
 for database in $database_list; do
   backup_file="$BACKUP_DIR/$timestamp.$database.sql.gz" 

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -40,7 +40,7 @@ PATH=$PATH:/usr/local/mysql/bin
 echo "Deleting *.sql.gz files in $BACKUP_DIR older than 30 days" 
 find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} \;
 
-# YYYY-MM-dd
+# YYYY-MM-DD
 timestamp=$(date +%F)
 echo "Filename Timestamp: $timestamp" 
 

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -40,7 +40,7 @@ PATH=$PATH:/usr/local/mysql/bin
 echo "Deleting *.sql.gz files in $BACKUP_DIR older than 30 days" 
 find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} \;
 
-# YYYYMM
+# YYYY-MM-dd
 timestamp=$(date +%F)
 echo "Filename Timestamp: $timestamp" 
 

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -26,7 +26,8 @@ MYSQL_UNAME=root
 MYSQL_PWORD=
 
 # Don't backup databases with these names 
-IGNORE_DB="(^mysql|_schema$|_test[0-9]?$)"
+# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
+IGNORE_DB="(^mysql|_schema$)"
 
 # include mysql and mysqldump binaries for cron bash user
 PATH=$PATH:/usr/local/mysql/bin

diff --git a/mysql_backup.sh b/mysql_backup.sh
@@ -0,0 +1,62 @@
+#!/bin/bash
+#==============================================================================
+#TITLE:            mysql_backup.sh
+#DESCRIPTION:      script for automating the daily mysql backups on development computer
+#AUTHOR:           tleish
+#DATE:             2013-12-20
+#VERSION:          0.4
+#USAGE:            ./mysql_backup.sh
+#DAILY CRON:
+  # example cron for daily db backup @ 9:15 am
+  # min  hr mday month wday command
+  # 15   9  *    *     *    /Users/[your user name]/scripts/mysql_backup.sh
+
+#RESTORE FROM BACKUP
+  #$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
+
+#==============================================================================
+# CUSTOM SETTINGS
+#==============================================================================
+
+# directory to put the backup files
+BACKUP_DIR=/Users/[your user name]/backup
+
+# MYSQL Parameters
+MYSQL_UNAME=root
+MYSQL_PWORD=
+
+# Don't backup databases with these names 
+IGNORE_DB="(^mysql|_schema$|_test[0-9]?$)"
+
+# include mysql and mysqldump binaries for cron bash user
+PATH=$PATH:/usr/local/mysql/bin
+
+#==============================================================================
+# MAIN SCRIPT
+#==============================================================================
+
+# delete backups older than 30 days
+echo "Deleting *.sql.gz files in $BACKUP_DIR older than 30 days" 
+find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -exec rm {} \;
+
+# YYYYMM
+timestamp=$(date +%F)
+echo "Filename Timestamp: $timestamp" 
+
+# Build Login String
+mysql_login="-u $MYSQL_UNAME" 
+if [ -n "$MYSQL_PWORD" ]; then
+  mysql_login+=" -p$MYSQL_PWORD" 
+fi
+
+# build database list
+show_databases="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'" 
+database_list=$(mysql $mysql_login -e "$show_databases"|awk -F " " '{if (NR!=1) print $1}')
+
+# backup all MySQL databases
+for database in $database_list; do
+  backup_file="$BACKUP_DIR/$timestamp.$database.sql.gz" 
+  echo "Backup $database to $backup_file" 
+  mysqldump $mysql_login $database | gzip -9 > $backup_file
+done
+
No results found