Bash - Mysql Backups

Question

Bash - Mysql Backups

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

6

I set up the script below to perform backups of three Mysql databases. I wonder if it's correct.

#VARIAVEIS
DATE='date +%Y%m%d-%H%M'
HOSTNAME1="xxxxx"
HOSTNAME2="yyyyy"
HOSTNAME3="zzzzz"
USER='xyzedmar'
PASSWORD='xyz2'
DATABASE1='xxxxx'
DATABASE2='yyyyy'
DATABASE3='zzzzz'
DIR='http://site/mysql-bkp/'

#backup do banco de dados

mysqldump --host=$HOSTNAME1 --user=$USER --password=$PASSWORD --databases $DATABASE1 > $DIR/backup_$DATABASE1_$DATE.sql
mysqldump --host=$HOSTNAME2 --user=$USER --password=$PASSWORD --databases $DATABASE2 > $DIR/backup_$DATABASE2_$DATE.sql
mysqldump --host=$HOSTNAME3 --user=$USER --password=$PASSWORD --databases $DATABASE3 > $DIR/backup_$DATABASE3_$DATE.sql

But I need help for

1) How do I delete the files from the previous days, after the updated backups have been made?

2) What is the Cron code for running the script?

mysql linux bash cron backup

asked by anonymous 07.12.2015 / 22:51

3 answers

Save multiline text in Blob field in SQLite [closed] Printing text and image on Zebra printer

score 0 · Answer 1

Here is a suggested implementation, you can adapt and use it in your own script:

#!/bin/bash
#E-mail do adm do servidor
EMAIL='[email protected]'
#Data Atual
DATE='date +%Y%m%d'
#Diretório do backup
DIRETORIO=/var/backup
#Data Retroativa para apagar backup antigo, neste caso com data d-2
DATEP='date +%Y%m%d --date="2 days ago"'
#Opções mysqldump para mais info acesse:
#http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_all-databases
#-x bloqueia todas as tabelas
#-e acelera inserções
#-A todos os bancos de dados
mysqldump -u usuarioBackup -pPassword -x -e -A > $DIRETORIO/bd.$DATE.sql

if [ $? -ne 0 ]; then   #envia email se não efetuou backup
     echo "Backup MySQL falhou em $DATE" | mail -s "Erro no Backup MySQL" $EMAIL
else
    #remove o arquivo mais antigo se efetuou o novo backup
    rm $DIRETORIO/bd.$DATEP.sql
fi

Save the file with sh extension, example backup.sh . To run this script automatically you have to program cron, so run the command crontab -e and program as desired. The line below will cause the backup to occur every day at 00:00:

00 00 * * * sh /caminho/para/arquivo/backup.sh

For more information about Cron

NOTE: If you are not familiar with vi to schedule Cron, run export VISUAL=nano; crontrab -e and use the nano editor.

score 0 · Answer 2

You can use the following line in the crontab to delete the files.

To delete files older than 30 days in the /var/backup/mysql folder:

find /var/backup/mysql/ -mtime +30 -type f -delete

Just include this line in your cron, as in this example (Run every day at 18:40):

40 18 * * * find /var/backup/mysql/ -mtime +30 -type f -delete

To edit the crontab, you can run crontab -e as the root user.

To run your script on Cron every day at 15:30:

30 15 * * * sh /root/backup_mysql_script.sh

To run your script on Cron on Saturdays at 9:00 AM:

0 09 * * SAT sh /root/backup_mysql_script.sh

For more information on Cron, click here .

score 0 · Answer 3

Hello,

Required packages: mysql, mysqldump, and zip.

git clone link

vi ./backupMysql/backup.sh

zip_passwd="YOUR PASSWORD"

ESC + WQ + ENTER

sh ./backupMysql/backup.sh

Download and instructions: link