Help with cron to backup a MySQL bdd

Question

Help with cron to backup a MySQL bdd

Navigation

#1 by (0 votes)

0

I want to create a backup of my database (MySql with phpMyAdmin) and I found the following script to be able to automate it:

echo off
mysqldump -hlocalhost -udb_admin -pContrasenaDb bdd_n1 > 
copia_seguridad_%Date:~6,4%%Date:~3,2%%Date:~0,2%_.sql
exit

This I keep in .bat and when I run it I create a .sql file that theoretically stores all the information in the database. I say in theory because when I run it, it generates the document .sql but its content is empty. The name of the host ( -h ), the user ( -u ), the password ( -p ), and the name of the database data are correct since they are the same ones that I use to connect the database with my website (which I develop locally with XAMPP).

Could someone tell me what's wrong with the script or would I know some other way to do an automated backup?

mysql windows cron

asked by gmarsi 16.07.2017 в 13:47

source

1 answer

Find out type of a class typed to compare it with object I have a problem with login in codeigniter

score 0 · Answer 1

I leave the program that I use to backup my database.

I use it in a bd in shared hosting. As you say you are in local, you can give more security to the script, saving the connection credentials in /root/.my.cnf and executing the copy command in this way: copycmd=/usr/bin/mysqlhotcopy (inside the script, I indicate it in the part that would go, in commentary). If you can not configure it that way, you will have a security warning, because it is not recommended to write bd passwords on the command line ... it is not serious, if a hacker is not listening to what you type on your machine:)

You can store this script in a file called for example: backup_db.sh you can try it from the console and once it works you can program a cron that executes it as often as you indicate.

The script gives the possibility of saving several versions of copy (you can modify it in this variable numversions=4 ), and adds the date to the file name of each copy.

#archivo: backup_db.sh


#nombre base de datos (pueden ser  varias, separadas por espacio)
dblist="nombre-base-de-datos"
user="nombre-usuario"
pass="constraseña"

# Directorio para for backups
backupdir=/home/...

# Cantidad de versiones a mantener
numversions=4

# Path para el comando de copiado MySQL 
# Si se ponen las credenciales en  /root/.my.cnf usar el comando comentado en vez del otro
#copycmd=/usr/bin/mysqlhotcopy
copycmd="/usr/bin/mysqldump -u$user -p$pass --lock-tables --databases"

# Crear directorio si es necesario
mkdir -p "$backupdir"
if [ ! -d "$backupdir" ]; then
   echo "Directorio inválido: $backupdir"
   exit 1
fi

# Aquí comienza la copia
echo "Copiando bases de datos MySQL ..."
RC=0
for database in $dblist; do
   echo
   echo "Copiando $database ..."
   mv "$backupdir/$database.gz" "$backupdir/$database.0.gz" 2> /dev/null
   $copycmd $database | gzip > "$backupdir/$database.gz"

   RC=$?
   if [ $RC -gt 0 ]; then
     continue;
   fi

   # Rollover the backup directories
   rm -fr "$backupdir/$database.$numversions.gz" 2> /dev/null
   i=$numversions
   while [ $i -gt 0 ]; do
     mv "$backupdir/$database.'expr $i - 1'.gz" "$backupdir/$database.$i.gz" 2> /dev/null
     i='expr $i - 1'
   done
done

if [ $RC -gt 0 ]; then
   echo "MySQL Copia fallida!"
   exit $RC
else
   # Copia completada. Lista de versiones de backup!
   ls -l "$backupdir"
   echo "Copia MySQL completa!"
fi
exit 0