Mysql Backup Script

[Dr.Morais 130]

Boas Cyber ! Vim publicar 1 script a meu ver , muito necessário..

Requisitos:

mysqldump
gzip
ncftp
FTP configurado corretamente

Função do script:

Faz dump e compacta as tabelas mysql desejadas;
Transfere os backups para o host;
Remove backups desnecessários.

Como usar?

Para usar o script basta correrem na vb:

1)cd /usr/.../game
2)sh backupmysql.sh ->(ou outro nome que derem ao ficheiro)

Para fazer backup automaticamente:

1) crontab -e
2) 0 0 * * * sh /usr/home/someuser/backupmysql.sh all

Como criar o script?

-Abrir a localização do host na pasta game
-Criar um novo ficheiro: backupmysql.sh
-Colar o texto:

#!/bin/sh

# Get mode from user input
# Allowed modes:
# all: backups account, common, log, player, webserver
# game: backups account, common, player
# gameLog: backups log
# web: backups webserver
# exceptLog: backups account, common, player, webserver
MODE=$1

if [ "$MODE" == "" ]; then
MODE="exceptLog"
fi

# Bins
MYSQLDUMP=`which mysqldump`
GZIP=`which gzip`
NCFTP=`which ncftp`

# Date for folders and filenames
DAY=$(date +"%Y-%m-%d")
FILETIME=$(date +"%Y-%m-%d.%T")

# Local backup folder (no trailing slash)
LOCAL_FOLDER="/tmp/backup"

# FTP Configuration
REMOTE_HOST="x.x.x.x"
REMOTE_USER="ftp_user"
REMOTE_PASS="ftp_pass"
REMOTE_FOLDER="/" # With trailing slash

# MySQL Configuration
MYSQL_USER="mysql_user"
MYSQL_PASS="mysql_pass"

# Which databases shall we backup?
# Databases should be separated with a space
DATABASES=""
if [ "$MODE" == "all" ]; then
DATABASES="account common log player webserver"
elif [ "$MODE" == "game" ]; then
DATABASES="account common player"
elif [ "$MODE" == "gameLog" ]; then
DATABASES="log"
elif [ "$MODE" == "web" ]; then
DATABASES="webserver"
elif [ "$MODE" == "exceptLog" ]; then
DATABASES="account common player webserver"
fi

# Check if DATABASES var is set...
if [ "$DATABASES" == "" ]; then
echo -e "033[31mThe specified mode doesn't exist...033[0m"
exit 1
fi

# Dump and compress
for db in $DATABASES
do
FILE=$db.$FILETIME.gz
echo -e "033[32mDumping $db!033[0m"
$MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $LOCAL_FOLDER/$FILE
done

# Transfer all backup files to remote host
echo -e "033[32mnTransfering files!033[0m"
$NCFTP -u$REMOTE_USER -p$REMOTE_PASS $REMOTE_HOST<<EOF
mkdir $REMOTE_FOLDER$DAY
cd $REMOTE_FOLDER$DAY
lcd $LOCAL_FOLDER
mput *
quit
EOF

# Delete local dump files
rm -f $LOCAL_FOLDER/*

-Configurar o script com os dados do servidor:
# Local backup folder (no trailing slash)
LOCAL_FOLDER="/tmp/backup"

# FTP Configuration
REMOTE_HOST="x.x.x.x"
REMOTE_USER="ftp_user"
REMOTE_PASS="ftp_pass"
REMOTE_FOLDER="/" # With trailing slash

# MySQL Configuration
MYSQL_USER="mysql_user"
MYSQL_PASS="mysql_pass"

-Guardar e fazer os passos acima referidos

Créditos:
MadTiago