Sunday, December 30, 2012

Backup and Restore using MySQL Workbench

Not so recently, I installed MySQL Workbench on my local system for a personal project. I had installed it a long time ago and it seems that after Oracle's acquisition of MySQL, they have made quite a few changes to the setup. Especially the bundle that you can install now contains examples and MySQL server and the MySQL workbench.

One nice feature about this new bundle is that you don't need to install additional software if you already have MySQL workbench installed. Earlier, you had to install MySQL admin to do a backup and restore of your system. Now its a bit more simplified since everything is accessible from within MySQL workbench itself.

In this post, I am just going to show the steps involved in using the new MySQL workbench (the one that I have is MySQL Workbench 4.2.44 CE) to take a backup of your database in a simple text file and then later use the same text file to restore your database to a prior state.

My MySQL database is on my local system, uses the standard port 3306 and is at its default install state at the moment. The screenshot of the homepage looks like this.

In order to take a backup of your database, you need to focus on the Server Administration section of Workbench and select your MySQL server from the list of servers displayed.

Once you do that, you are taken to a page where it shows you details about the health of the server and the different operations that you can perform on the this instance of your MySQL server.

Since you want to take a backup, you need to select the option that is circled in red that lets you import and export data.

Lets first export our data. Its pretty easy, select the database and then the tables from that database of which you want to take a backup.

For small projects, its convenient to take a backup in a single file. This file will contain all the sql statements that can be used to backup all the data and structure of you schema. Most of the times, you might want to take a backup of only the data, but not the schema. If you are taking a backup of the schema, it just means that when you use the file to restore, it will try to recreate the tables if the they don't already exist. However, if you don't want this extra detail to be present in your backup file, and only want backup of the data, an option in the advanced tab lets you do that.

Once  you are done mixing and matching all the options, click on the Export progress tab and then click on Start Export.

That's as easy as it gets to take a backup of your MySQL database.

Restoration is an equally easy process. Just select the file that you want to use as the restoration file and click on start import. And you are good to go.

Pretty neat, although I do believe a few more changes to the interface could make some things more obvious.

Hope this helps!

Happy Programming :)
Signing Off 


No comments: