Back up and restore MySQL databases

This article will briefly explain how to use the command line to backup your databases, making it easy to automate your tasks using bash scripts.

I use MySQL on a daily basis. Most web developers connect to databases using GUI’s such as phpmyadmin or MySQL query browser. This article will briefly explain how to use the command line to backup your databases, making it easy to automate your tasks using bash scripts.

Introduction

Backing up a mysql database

mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]

Restoring your MySQL database

mysql - u [uname] -p[pass] [dbname] < [backfile.sql]

The above commands require a bit more explanation the square brackets represent a variable that you would enter yourself (don’t put in the brackets).

Note there is no space between the -p option and the password. Usually when working on the command line we enter the password separately for security purposes.

MySQL Bash Backup script

The Script

View on GitHub

Documentation

File System setup

The script assumes you have your file system setup as so.

• example.com
  • scripts
  • sql

The database.bash should be placed and run from the scripts folder, all our sql dumps will be stored to the sql folder.

File names

The name of the scripts directory and the database.bash file do not matter, just the sql directory is hard codded. The name of the dump will always be the databasename.sql

Use the parameters

Instead of following the wizard each time you need to dump or update the database, you can create a bash script in the scripts directory to dump the database like so.

./database.bash d databaseName databaseUser databasePassword databaseHost

A script to update all you would have to do is change the u to a d:

./database.bash u databaseName databaseUser databasePassword databaseHost

If you name these files dump.bash and update.bash, all you would have to do is run

bash dump.bash

To dump the database.
This is helpful when you add the script to a git hook, as then you have a snapshot of your database that matches the file system snapshot.

Bonus, backup mysql in a docker container:

docker exec ${DOCKERNAME}-db /usr/bin/mysqldump -u ${DBUSER} --password=${DBPASSWORD} ${DB} > sql/${DOCKERNAME}-${DB}-${tstamp}.sql