In this tutorial I will show you the most common ways on how to backup a MySql Database.
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:
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:
In the ‘bin’ folder you will find something like this:
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.
- 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 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:
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.
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.
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:
As you can see from the screenshots above.
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:
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’
Then select all the tables that you want to include in the backup, the select SQL as the file type
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.
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 reading