Top posts
Learn Code Online – Java – Android – PHP – Wordpress Tutorial
Linux Tutorials

Shell Script backup MySQL database daily and remove old backup version

Pinterest LinkedIn Tumblr

This script (bash shell) is used for backup database MySQL daily automatically and remove old backup to save storage by duration. To set script run automatically you will need add it to cron job.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#!/bin/bash
#==============================================================================
#TITLE:            mysql_backup.sh
#DESCRIPTION:      script for automating the daily mysql backups on development computer
#AUTHOR:           tleish
#VERSION:          0.4
#USAGE:            ./mysql_backup.sh
#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=/home/backup_all_db
 
# MYSQL Parameters
MYSQL_UNAME=your_user
MYSQL_PWORD=your_password
 
# Don't backup databases with these names
# 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
 
# Number of days to keep backups
KEEP_BACKUPS_FOR=14 #days
 
#==============================================================================
# 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 {} \;
}
 
function mysql_login() {
  local mysql_login="-u $MYSQL_UNAME"
  if [ -n "$MYSQL_PWORD" ]; then
    local mysql_login+=" -p$MYSQL_PWORD"
  fi
  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}')
}
 
function echo_status(){
  printf '\r';
  printf ' %0.s' {0..100}
  printf '\r';
  printf "$1"'\r'
}
 
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)
 
    rclone sync $backup_file $REMOTE_BACKUP_DATABASE
}
 
 
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(){
  printf '=%.0s' {1..100}
  printf "\n"
}
 
 
#==============================================================================
# RUN SCRIPT
#==============================================================================
delete_old_backups
hr
backup_databases
hr
printf "All backed up!\n\n"

Add it to cron job, run on 2:30 every day.

chmod +x your_script.sh
crontab -e

?
1
30 2 * * * /your_script.sh

I'm a full stack developer. I have experiences with Java, Android, PHP, Python, C#, Web development...I hope website https://learncode24h.com will help everyone can learn code within 24h and apply it in working easily.

Related Posts

Comments are closed.