Back up and restore MySQL databases
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
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