Easy mysqldump backups by example.

This blog post, will present three sample uses of the mysqldump backup utility. It will entail backing up: a database, a table, along with a table without data. This utility can easily be incorporated into shell scripts for those repetitive tasks you may need to execute on multiple occasions.

Photo by Samuel Zeller on Unsplash

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and Database used:

• Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
• MySQL 5.7.21

* Note:
All bash shell output shown in the code snippets will depict a shell prompt indicated by: os-shell:~$.

1: Backing up a database

Suppose we want to backup just one database on the system.
Here’s an example command that will take care of that:

Let’s verify file db_projects.sql exists and then look at this commands’ options.

os-shell:~$ ls -a MySQL_dumps/
. .. db_projects.sql

To see the available mysqldump command-line options, visit the mysqldump official documentation link.
Let’s look at the options used in this command:

• --add-drop-database : Includes DROP DATABASE command prior to any CREATE DATABASE command.
• --add-drop-table : Includes DROP TABLE command prior to any CREATE TABLE command.
• --user=root : The user to connect with during backup dump execution. In this instance, I specified the root user (for the MySQL server, not the Linux client).
• --password : If you do not specify the password value here (Recommended do not specify on the command-line!!!!), you will be prompted for the correct password indicative to the specified user switch flag upon execution of the command. This is a safer option than providing clear-text passwords on the command-line.
• projects : The name of the database to backup.
• MySQL_dumps/db_projects.sql : The file to save the backup to.

To restore, simply direct the backup file to the database on the command-line as in this example:

os-shell:~$ mysql projects < db_projects.sql

2: Backing up a Table

You can backup a single table with mysqldump.
We will backup this friends table,

using the following command:

And we will verify that file now exists within the specified directory:

os-shell:~$ ls -a MySQL_dumps/
 . .. db_projects_friends_tbl.sql db_projects.sql

The option in this command worth noting is:

• --tables friends : The friends table specified to backup.

3: Table only, no data.

In this scenario, we are only interested in backing up the table structure, void of any data.
mysqldump can easily handle this task with a similar command as shown below:

Including the --no-data option following the table name, mysqldump will not dump any table content as part of the backup.

To view the contents of the mock file db_projects_friends_empty_tbl.sql, visit this gist.

Bonus: A sample shell script.

Below I have included a sample shell script -usable on Linux and Unix systems- that I typically use to backup all currently-installed databases on my local test machine.

Feel free to edit as your needs dictate for your specific fit.

This is merely to give you an idea of incorporating mysqldump into a simple, useful shell script.

I pipe (|)the mysqldump output portion, into the gzip utility, creating a nice zip file for storage, once directed to the desired backup directory.
$(date +"%Y_%m_%d") assists in naming the zip file with the current system date of the backup.

Now whenever you need to run a complete backup of all databases, simply call this script and pass in the correct password when prompted.

Easy peasy.

Note — Restrictions:

Be sure and visit the Restrictions section of the documentation for any questions you may have regarding mysqldump limitations or precautions.

Get to dumping those backup files…

I hope through this blog post, I have provided you with information to get started with mysqldump for your client-side utility needs. While I haven’t detailed all available options, the documentation should provide adequate information for your specific needs.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage!
Be sure and visit the “Best Of” page for a collection of my best blog posts while you are there!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Originally published at joshuaotwell.com on April 25, 2018.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.