Bash - Mysql backups

Asked

Viewed 339 times

7

I rode the script below, to perform backups of three Mysql bases. I would like to know if it is 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 to

1) how to delete files from x previous days, after made the updated backups?

2) What is the code Cron to rotate the script?

mysql linux bash backup cron

  • Managed to solve your problem?

  • No. I couldn’t adapt the code you suggested.

  • What couldn’t you do? Tried to run bash? Error? I use this code to back up my banks and it works perfectly

  • My backup routines are all automated, whether by scripts, software, etc. Take a look at my comment on this thread that can help you with your implementation. http://answall.com/a/101888/21263 Hug

  • > You can use this Python script that I created, it backs up all databases. Download it, run a chmod +x backup_mysql.py and put in the folder /usr/local/bin/

3 answers

0

0

Follow an implementation suggestion, you can adapt and use 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 extension sh, example backup.sh. To run this script automatically you must 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:00hs:

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.

0

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

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

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 root user.

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

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

To run your Cron script on Saturdays at 09:00:

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

For more information about Cron, click here.

Browser other questions tagged mysql linux bash backup cron

You are not signed in. Login or sign up in order to post.