How do I restore a MySQL dump?

If you have an all-database MySQL dump, follow the instructions below to restore your SQL file with all database data & privileges.

Please note that the following procedures require command line access and is assuming you are the administrator of the computer.

Step 1:
Open up command line.
Start > Run > Type ‘cmd’ > Enter

Step 2:
Change directory to your MySQL bin folder. This can be C:Program FilesMySQLMySQL 4.1 Serverbin — or C:xamppmysqlbin depending on what your setup is like. You can do so by the ‘cd’ command as follows:

cd C:xamppmysqlbin

Step 3:
Ensure that you have moved your backup file (eg: db_backup.sql) into the bin folder and run the following command where test is an empty MySQL database.

mysql -u root -p test < db_backup.sql

It will prompt you for your root password. Enter that and it will start importing your SQL file. It can take several minutes depending on how big your SQL file is.

Good luck!

How to backup MySQL server?

Recently, I bought a new laptop and was challenged to move my development sites over. Copying files and sites is all pretty easy, however, if you do not backup your MySQL server correctly – you will face quite a hassle recreating all the databases and privileges.

I came across the following way that was pretty easy to move around 150 databases with their privileges swiftly.

Please note that the following procedures require command line access and is assuming you are the administrator of the computer.

Step 1:
Open up command line.
Start > Run > Type ‘cmd’ > Enter

Step 2:
Change directory to your MySQL bin folder. This can be C:Program FilesMySQLMySQL 4.1 Serverbin — or C:xamppmysqlbin depending on what your setup is like. You can do so by the ‘cd’ command as follows:

cd C:xamppmysqlbin

Step 3:
Type in the following command to dump all databases into an sql file.

mysqldump –all-databases > db_backup.sql

This can take several minutes depending on the size of your databases. Once done, check your bin folder for db_backup.sql file which contains SQL queries of your entire MySQL server.

You may want to see my other post regarding restoring MySQL server.

Welcome to Mohsin Sumar’s blog

It’s been a while, couple of years actually, since I updated my website. For those who do not know me, please see my biography page for more information about me. I have had my personal blog on my to-do list for quite sometime and finally, here it is.

It’s good to be back. Just to let you know that I will be updating my blog with personal experiences, advises, tips & tricks and more on a variety of topics including but not limited to web development, PHP, MySQL and Apache.

I will also put up my tutorial and download files that I had posted up on Flash-dB where I was a moderator for a long time. My tutorial, in particular, GuestBook Tutorial was released freely and available at http://www.flash-db.com/Tutorials/guestbook/.

You can get in touch with me if you like assistance, however, due to time & busy life we all lead, I may not be able to attend all queries.

Till then,
Mohsin.