Ways on how to backup MySql Database

In this tutorial I will show you the most common ways on how to backup a MySql Database.

 

MySql Dump

It is a built-in tool which comes with MySql. Which means that when you install MySql, you also install this tool on your hard drive. Or when you install WampServer then you also install this tool. Since WAMP, if you don’t already know it, stands for:

  • Windows
  • Apache
  • MySql
  • Php

To use this tool, all you need to do is to include the path to your MySql folder to the environment variables. If you don’t know how to set environment variables then check out this tutorial.

The installation folder of MySql in my case, is in:

C:wampbinmysqlmysql5.1.36bin

In the ‘bin’ folder you will find something like this:

image

If not, then you must have messed up with your installation. You might as well want to uninstall wampserver then install it again.

So if you didn’t customize your wampserver installation and you have installed windows on C: drive, then you might as well copy the path above.

All you need to do now is to open up the command line and type the following commands:

mysqldump –u root –p dbname > x:SaveLocationfilename.sql

  • save location – is any folder where you want to save the .sql file
  • -u –user, the default is root
  • -p – password for the user, the default is none. But if you have password, then you have to put it after the –p with no spaces.

ex. –pMyPassword

  • dbname – the name of the database which you want to backup
  • x – the drive letter, it could be anything. It all depends on you.

 

MySql Workbench

MySql Workbench when you ask me what it is, then my answer would be: its like a MS Sql Server Management Studio. But for MySql Database. It allows you to manage your MySql Database. And do things like backing up and restoring database, make queries. More or less, its like a desktop application equivalent of PhpMyAdmin.

For first time use, you need to create a new server instance before you could get fancy using this application. I’ve already made a separate tutorial for this and you can find it here.

All you have to do now is to double-click on the new server instance that you have just created. Then click on the ‘Data Dump’ tab:

image

What you have to do now is to select the database that you want to backup, in my example I want to backup the ‘onstor’ database.

image

Then you select which backup option you would want. In this example I used self-contained file. Which is similar to saying that it will produce a .sql file that you could later use to restore your database. Just check all of those checkboxes to ensure that you could still perform all the operations needed in your database once you restored it from the file that its going to backup.

image

After that, click on  ‘start export’ on the lower right corner of the window.

To check if it the program succeeded in backing up your database, then you go check it in the folder that you selected in the program a while ago. Mine is in:

C:UsersNrewDocumentsdumps

As you can see from the screenshots above.

 

PhpMyAdmin

This is probably the most common way of doing MySql Database backups. But for those who doesn’t know yet, this is how you do it:

First, you need to launch WampServer.

Second, you need to open up your browser, then type in:

localhost/phpmyadmin

Navigate to the database of your choice, mine is ‘cdvd’ database where I store the different information for my grand dvd collection. Then click on ‘export’

image

Then select all the tables that you want to include in the backup, the select SQL as the file type

image

Scroll down a bit, then check the ‘save as a file’ checkbox. So that it will produce a .sql file.  Give a comprehensive name for the backup, most likely you would want to name it as the name of the database itself. Select  ‘zipped’ or ‘gzipped’ if the database is big. When I say big, it has to have about 100 tables on it and about 100,000 to 1,000,000 records on every table.

image

 

Conclusion

That’s it,  you now know of the most common ways to backup a MySql Database. Please share this one on facebook or on twitter if you liked it, thanks for readingSmile

2 thoughts on “Ways on how to backup MySql Database

  1. Pingback: Assoc of keyboard ninja forum « Data Integrated Entity

  2. Pingback: Automatically backup mysql database using a batch file « Data Integrated Entity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s