Make a MySQL Backup by SSH (Export)
With the following command you can make / export a backup of a database using SSH
mysqldump --host=localhost --user=db_user --password=pass db_name > db_name.sql
If the above command didn’t work for you, you can try this one.
Watch out! in this second command the password is pasted to the -p
mysqldump -h localhost -u db_user -ppass db_name > db_name.sql
Restore a MySQL Backup by SSH (Import)
With the following command you can restore / import a backup of a database using SSH.
Watch out! in this second command the password is pasted to the -p
mysql --host=localhost --user=db_user --password=pass db_name < db_name.sql
Si el comando anterior no te ha funcionado, puedes probar con este otro.
Ojo! en este segundo comando la contraseña ve pegada a la -p
mysql -h localhost -u db_user -ppass db_name < db_name.sql
Automatic backup and sending by FTP to a remote server
Enter this script in a cron to run a regular backup of your database and send the copy to a remote FTP server:
You must modify the values marked in bold by your own.
You must modify the values marked in bold by your own.
#! /bin/bash
export PATH="/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"
## CONFIGURA LOS DATOS DE CONEXION A LA BASE DE DATOS ##
SQL_HOST='localhost'
SQL_USER='Usuario-SQL'
SQL_PASS='Pass-SQL'
SQL_DDBB='Nombre-SQL'
## CONFIGURA LOS DATOS DE CONEXION AL FTP DE BACKUP ##
FTP_HOST='Servidor-FTP'
FTP_USER='Usuario-FTP'
FTP_PASS='Pass-FTP'
## CONFIGURA EL DIRECTRIO DE BACKUP LOCAL Y LOS DIAS DE RETENCION DE FICHEROS##
BackupDestination='/backup'
Retention='5' #dias
## NO EDITES NINGUN VALOR A PARTIR DE AQUI ##
FECHA=`date +%Y%m%d_%H-%M`
echo Detectando Fecha: ${FECHA}
cd ${BackupDestination}
echo Accediendo al directorio de backup: ${BackupDestination}
echo Ejecutando mysqldump
mysqldump --host=${SQL_HOST} --user=${SQL_USER} --password=${SQL_PASS} ${SQL_DDBB} > ${SQL_DDBB}-${FECHA}.sql
echo Comprimiendo backup
tar cvzf ${SQL_DDBB}-${FECHA}.tar.gz ${SQL_DDBB}-${FECHA}.sql
echo Backup comprimido: ${SQL_DDBB}-${FECHA}.tar.gz
echo Eliminando backup sin comprimir: ${SQL_DDBB}-${FECHA}.sql
rm -rf ${SQL_DDBB}-${FECHA}.sql
echo Eliminando backups con mas de ${Retention} dias
find ${BackupDestination} -type f -ctime +${Retention} | grep ${SQL_DDBB}- | xargs rm -rf
echo Proceso finalizado
echo Enviando al servidor FTP
ftp -in << EOF
open ${FTP_HOST}
user ${FTP_USER} ${FTP_PASS}
bin
verbose
prompt
put ${SQL_DDBB}-${FECHA}.tar.gz
bye
EOF
echo Transferencia FTP finalizada.
echo FIN del trabajo. 
