Bash script to back up and restore a MySQL database

Author: Victor Laguna Category: Blog

Database backups are essential before migrations, updates and infrastructure changes. This guide shows the key commands behind a Bash workflow for backing up and restoring MySQL databases.

Create a dump

mysqldump -h ${MYSQL_HOST} \
  -P ${MYSQL_PORT} \
  -u ${MYSQL_USER} \
  -p${MYSQL_PASS} \
  ${MYSQL_DB} \
  | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' \
  | grep -v 'Warning' \
  > ${HOME}/${DB_BACKUP_PATH}/${TODAY}/${MYSQL_DB}-${TODAY}.sql

The sed step removes DEFINER clauses that can break imports on a different server.

Create the destination database

mysql -h ${MYSQL_HOST_TEST} \
  -u ${MYSQL_USER_TEST} \
  -p${MYSQL_PASS_TEST} \
  -e "create database ${MYSQL_DB};"

Restore the dump

mysql -h ${MYSQL_HOST_TEST} \
  -u ${MYSQL_USER_TEST} \
  -p${MYSQL_PASS_TEST} \
  ${MYSQL_DB} \
  -e "source ${HOME}/${DB_BACKUP_PATH}/${TODAY}/${MYSQL_DB}-${TODAY}.sql"

Backup checklist

Always verify the dump size, test a restore, store a copy outside the server and protect credentials. A backup that has never been restored is only a hope, not a recovery plan.

Leave a Reply