Learning Journey in SQL
From A to SQL: Avoiding disasters using MySQL workbench data export
The importance of backing up your database
Welcome back to the “From A to SQL” series. The series has been growing steadily, and we’ve covered database design, establishing relationships, joins, subqueries, and using built-in functions. You can read them in any order for the theory, but if you want to follow the examples at the end, I recommend following each one in sequence. You can access the latest chapter 6 here.
We have covered a lot of SQL knowledge so far, and if you have been following along with the examples, you will have developed a good foundational business database. In this article, we will focus on the process of backing up a database, why it’s important, and what to do if a disaster strikes.
What’s a database back up anyway?
We like to think we live in a perfect world, where the projects we work on will be available until we choose to delete them. However, unexpected things happen all the time.
There are many reasons why you may suddenly lose data, ranging from your hardware breaking, a human error to more sinister reasons like cyberattacks and malware. The loss of data could be just one row relating to a customer, all the way to losing an entire database.
If you are working on a personal project, this may just cause frustration, but if you are part of a business, this could even mean being subject to fines for not adhering to appropriate data protections laws and regulations.
The key piece of information to remember is that a backup of a database allows you to create a copy of your database. This copy can be stored in a different location, so that it can be reinstated if the original file is corrupt.
How do I perform a backup on MySQL workbench?
Firstly, it’s worth noting that there are several different ways to complete a backup of a database. Here, I will focus on the data export method using the MySQL workbench.
Here is your step-by-step guide:
1. Inside MySQL Workbench, click on the ‘Administration’ tab, located to the left of the ‘Schemas’ tab.
2. Under the ‘Management’ section, select Data Export.
3. A Data Export window will appear. On the left-hand side table, select the database you wish to export.
4. Once a database is selected, on the right-hand side table, select the tables and views you wish to export.
5. Under ‘Objects to export’, select any additional objects you wish to export. In simple terms, if you have stored functions, stored procedures, events, and triggers, you can select to create a copy of them too.
6. Select an Export option; either ‘to Dump Project Folder’, or ‘to Self-contained file’. The difference between the two options is explained below.
7. Press ‘Start Export’.
Within the Data Export window, there are also a series of advanced options. In these options you can customise your export, including adding a dump date, adding a read lock to the tables, quotes identifiers with backtick characters, and others.
What are my two export choices?
Like mentioned above, you have two options for a data export. When exporting to a ‘Dump Project folder’, there will be separate files for the creation of each individual table. This option is much easier if, for example, you have accidentally dropped one of your tables and just need to restore the single table.
On the other hand, when you export to a ‘Self-contained file’, all code will be copied to a single file, which is much easier to use in a disaster recovery situation.
What happens after the export is complete?
After an export has been completed successfully, it will be available in the location stated. By default, backups are in a ‘dumps’ folder in your user directory on your computer, but you can change the location in the Data Export window if you wish.
The file will contain all the code required to reinstate the database to the last known state.
How do I reinstate a database?
To reinstate a database, instead of selecting ‘Data Export’, you will need to go into ‘Data Import/Restore’ and select relevant options. By selecting the file of the last back up, you can bring it back to life and resume working on the database before the disaster has occurred.
To summarise, we should backup our databases and data regularly, to avoid any unforeseen loss. The schedule of back up will depend on how often you work with your database, and how often it changes, but on average a user would back up their database every 24 hours, maybe even more frequently, but at least once a week.
Using MySQL Workbench, a backup is simple and easy! So, save yourself time and remember to carry out this process frequently.
Go from SELECT * to interview-worthy project. Get our free 5-page guide.
