Скрипт на bash для резервного копирования MySQL
Порой лучшее решение — не тратить время на создание чего-то нового, а воспользоваться уже готовым решением. С радостью поделюсь с вами скриптом от tleish, который превосходно справляется с задачей резервного копирования баз данных.
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 |
#!/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 #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=/backup/MySQL # MYSQL Parameters MYSQL_UNAME=UserMySQL MYSQL_PWORD="BasePassword" # 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/bin/mysql # Number of days to keep backups KEEP_BACKUPS_FOR=30 #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) } 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" |
Ссылка на оригинал: https://gist.github.com/tleish/1c6e788c84f59200446b