Dump MySQL Database to a file on Windows

Most of the people out there are talking about dumping MySQL Database to a file on Linux servers. Many of you must be using MySQL on Windows, with WAMP. However, most of the PHP and MySQL powered websites and applications are hosted on a Linux server, most of the developers must be using WAMP on Windows, as their local development platform. What if you added a new developer to the development team and you all are using your own computer as your local development environment, how will you provide the current MySQL database to the new developer. There is a great chance that you must be using phpMyAdmin. Using phpMyAdmin, you can export your database and save (download) it as a SQL file with zero compression or compressing as zipped or gzipped. But what if you have a large database (in my case, it’s around 100 MB, that means, lots of data)?

You may able to import your (huge) database using phpMyAdmin. Depending on your settings, you may become successful in the import or you may see some errors. If you see some error, then skip this paragraph and read how you can dump MySQL database to a file on Windows. If you don’t see any error, then you need to worry about the download. As this is the local development environment, the download will be much faster. And you don’t need to save the file as zipped or gzipped from the phpMyAdmin, because it’ll not take much time to download the file as compressed. So don’t put pressure on phpMyAdmin (or WAMP) to zip or gzip it. You can zip it later, if you need to send this to someone on a network or on the internet. But if you want to download this import much faster than this, then read the next paragraph.

To dump MySQL Database, you need mysqldump. This is available as a command on Linux machines, after a normal MySQL installation. Similarly, this is available as an executable file (exe) on Windows, after a normal MySQL installation or WAMP installation. You don’t need to use MySQL Console. Now follow the steps.

Open Command Prompt of Windows.

If your WAMP installation is not on C drive, then change to the installation drive. For example, to change to the installation drive D, use this command:

D:

Now open the MySQL folder inside WAMP installation. There you will find a folder named “bin”. Inside that “bin” folder, you will find an executable named “mysqldump.exe”. If this file is not found there, then download this file from the internet and place it there. Now change to this folder on the command prompt.

Now run the following command. Replace username with the username of the database (normally root) and the database_name with of the name of the database. You can change backup_db.sql to any other name, if you want. This is the file name, on which your MySQL Database will be dumped.

mysqldump -h localhost -u username -p database_name > backup_db.sql

It’ll ask for the password of the database user; provide that (normally blank on default installation). Wait for few seconds or minutes (depending upon the size of the database). It’ll take much less time than the phpMyAdmin export feature. Now you will see the dump (backup) file in the same folder. Now you can move this file to any other machine or keep it for backup purpose. And you can compress this too.

Learn how to import this database from this dump file on a Mac using Terminal.