mysql 定期備份

1.首先不可用script上加密碼，因此要先建造一個密碼路徑


mysql_config_editor set --login-path=aname --host=localhost --user=user --password


h=aname --host=localhost --user=user --password


其中、aname可自行命名!
注意: After entering the password : Use double quotes around the password !!

最後，採用下列方式代替登入

mysql --login-path=aname


採用 crontab 排程

In a command prompt type

sudo crontab -e

And add the following line at the bottom of the file:

30 23 * * * /path/to/mysqldump.sh

執行sh
1.使用 sh test.sh 來執行script檔案

2.新增執行的許可權：

$ chmod  x test.sh

新增完執行許可權之後，便可以使用 ./test.sh 來執行指令碼，

Add the following code to the file( mysqldump.sh) and save it:

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
#USER='root'       # MySQL User
#PASSWORD='webdev' # MySQL Password
DAYS_TO_KEEP=0    # 0 to keep forever
GZIP=1            # 1 = Compress
BACKUP_PATH='/backups/mysql'
#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

# Get list of database names
databases=`mysql --login-path=aname -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`

for db in $databases; do

  if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
    echo "Skipping database: $db"
    continue
  fi
  
  date=$(date -I)
  if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"      
    mysqldump --login-path=aname --databases $db > $BACKUP_PATH/$date-$db.sql
  else
    echo "Backing up database: $db with compression"
    mysqldump --login-path=aname --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
  fi
done

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi