PradysTechBlog

Yet another blog

How to backup and restore a huge DB in MySQL (timeout prob in phpmyadmin)

PHPmyadmin is the most widely used tool administer a MySQL database. But the major problem with this web based tool is its inablity to load big Databases. How many times we have had to import a big database and get timed out. On the look out for an alternative to PHPmyadmin,i tried using MySQL GUI tools. Its been nearly 6 months i have been using this to backup and restore my MySQL databases.

You can download the  MySQL GUI from http://dev.mysql.com/downloads/gui-tools/5.0.html 

Install the application and you can find the gui tools under start->Programs ->MySQL

You can use this software to administer the DB, Query the DB and also for migrating the DB.

We will discuss in this article on how to take a Backup and restore and Database of MYSQL using this MySQL gui tools.

Go to start->programs->MySQL->MySQLAdministrator

MySQL Administrator LoginBy Clicking  on the button next to StoredConnection  You can store details of multiple connections so you dont have to remember the username and server details. This is particularly useful if you are handling multiple Databases. You give a name in Connection and it saves the login details so next time you want to access the DB you can choose the the name from the storedConnection.

HostName:  This is your DB Ipaddress or servername, if you are using local machine, you can give it as localhost.

Username : Enter the database username

Password : Enter the password of database.

Schema : Enter the Database name

click on apply and you are ready to login to the DB

Click on OK button.

Backup This is the screen you get after clicking the OK button.

This is where we can take backup and restore a MySQL DB.  For taking a backup just click on Backup  and then click on new project. Enter a name for the project and you can see that the Databases which are available for the username which we logged in is listed under schemata.

Select the database you want to take a backup and click on the right arrow to load the tables. This may take a few seconds or run upto a minute depending upon your internet connection and size o database. If you are using localhost this would load in few seconds.

By default all the tables are selected, you can choose to unselect individual tables or all the tables by clicking on the tick mark on the left of the database name in the backup content window or by unselecting the tick mark before the individual tables.

One can further configure the backup by going to advanced option tab.

Click on execute Backup button to start your backup process. This will get an file dialog box. Choose where you want to save the file. It would save the file with the name of the project and datetime appended to it. This again could take a few minutes depending on the database size and the connection speed.

Now you have the backup ready in your hard drive. We will discuss in the next article on how to restore the database.

Please write in your comments and suggestions.

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: