MySQL backups over SSH

Creating a MySQL backup over SSH (export)

With the following command you can export a database backup over SSH:

mysqldump --host=localhost --user=db_user --password=pass db_name > db_name.sql

If that command does not work for you, try this one instead:

mysqldump -h localhost -u db_user -ppass db_name > db_name.sql

If you have root access, you can also run the backup like this:

mysqldump --single-transaction db_name > db_name.sql

Restoring a MySQL backup over SSH (import)

With the following command you can restore a database backup over SSH:

mysql --host=localhost --user=db_user --password=pass db_name < db_name.sql

If that command does not work for you, try this one (remember: password attached to -p):

mysql -h localhost -u db_user -ppass db_name < db_name.sql

If you have root access, you can also restore like this:

mysql -f db_name < db_name.sql

Automatic backup shipped to a remote FTP server

Add this script to a cron job to run a periodic backup of your database and send the copy to a remote FTP server.

#!/bin/bash
export PATH="/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"

## DATABASE CONNECTION SETTINGS ##
SQL_HOST='localhost'
SQL_USER='SQL-User'
SQL_PASS='SQL-Pass'
SQL_DDBB='SQL-Name'
## BACKUP FTP CONNECTION SETTINGS ##
FTP_HOST='FTP-Server'
FTP_USER='FTP-User'
FTP_PASS='FTP-Pass'
## LOCAL BACKUP DIRECTORY AND FILE RETENTION DAYS ##
BackupDestination='/backup'
Retention='5' # days
## DO NOT EDIT ANYTHING BELOW THIS LINE ##

FECHA=`date +%Y%m%d_%H-%M`
echo Detecting date: ${FECHA}

cd ${BackupDestination}
echo Entering backup directory: ${BackupDestination}

echo Running mysqldump
mysqldump --host=${SQL_HOST} --user=${SQL_USER} --password=${SQL_PASS} ${SQL_DDBB} > ${SQL_DDBB}-${FECHA}.sql

echo Compressing backup
tar cvzf ${SQL_DDBB}-${FECHA}.tar.gz ${SQL_DDBB}-${FECHA}.sql
echo Backup compressed: ${SQL_DDBB}-${FECHA}.tar.gz

echo Removing uncompressed backup: ${SQL_DDBB}-${FECHA}.sql
rm -rf ${SQL_DDBB}-${FECHA}.sql

echo Removing backups older than ${Retention} days
find ${BackupDestination} -type f -ctime +${Retention} | grep ${SQL_DDBB}- | xargs rm -rf

echo Process finished

echo Uploading to the FTP server

ftp -in << EOF
open ${FTP_HOST}
user ${FTP_USER} ${FTP_PASS}
bin
verbose
prompt
put ${SQL_DDBB}-${FECHA}.tar.gz
bye
EOF

echo FTP transfer finished.
echo END of job.