For most database work I use phpMyAdmin. However, on the rare occasion I encounter a strange error — looking at your foreign key constraints — I opt for the Windows command prompt. I do this because a) troubleshooting database issues is far outside of my skillset and b) I need to get stuff done regardless of that. Also, the errors in phpMyAdmin typically don’t exist when performing the same task in the Windows command prompt (for the commands I run).

Dumping and Restoring MySQL databases on the Windows command prompt is a simple process.

  1. Fire up your Windows command prompt. In Windows 7, click the start orb and type cmd in the Search Programs and Files input and hit enter.

    Screenshot of the windows start menu with "cmd" in the search programs and files input
    Start Windows Command Prompt
  2. If MySQL has been added to your Windows path variable, continue to step 3. If this is the first time you have run a mysql command in the Windows command prompt, it will return the error captured below.

    Screenshot of the Windows command prompt with the mysql command not recognized
    MySQL Not Recognized

    Fear not, your computer just needs to know the location of MySQL to run MySQL-related commands. You tell Windows this location by adding the MySQL directory to your path variable. I covered how to add PHP to your Windows path variable using the Windows GUI in a different post. But in the spirit of going commando, let’s add MySQL’s location to the Windows path variable using the command prompt.

    • In your Windows command prompt, execute the command: set path=C:\path\to\mysql\executable, where C:\path\to\mysql\executable corresponds to the location of the mysql.exe file on your machine. I am running MySQL version 5.6.12, which was bundled with WAMP at my time of my download. So, the MySQL executable is located in C:\wamp\bin\mysql\mysql\mysql5.6.12\bin.

    • After you have added MySQL’s location to your Windows path variable, you should test the mysql command. Below is a screenshot of MySQL being added to the Windows path variable and the mysql command tested on my machine.

      Screenshot of the Windows path variable being updated via the Windows command prompt
      Add MySQL to Windows Path

      Now that mysql is recognized in the Windows command prompt, you can run the MySQL commands listed below to dump and restore your databases.

  3. To dump/export a MySQL database, execute the following command in the Windows command prompt: mysqldump -u username -p dbname > filename.sql. After entering that command you will be prompted for your password. Once the password is entered your dump file will be available in the root directory for your Windows user – ie: C:\Users\username. An example is shown in the screenshot below.

    Screenshot of MySQL database dump command in the Windows command prompt
    MySQL Dump Command
  4. To restore/import a MySQL database, execute the following command in the Windows command prompt: mysql -u username -p dbname < filename.sql. After entering that command you will be prompted for your password. Once the password is entered your dump file that exists in the root directory for your Windows user will be restored. An example is shown in the screenshot below.

    Screenshot of MySQL database restore command in the Windows command prompt
    MySQL Restore Command

That's it! We have successfully dumped and restored a MySQL database using the Windows command prompt.

Posted by: John Dugan

Comments

  • good.. but how to import and export the csv data into only one table..

    i have a csv file with 5 Lac lines of records..
    PHPmyadmin is going down..
    any help

  • Tytto

    https://sourceforge.net/projects/backupautomaticomysql

    Sistema grátis de backup automático, muito útil para quem não quer se preocupar com seus backups para banco de dados MySQL, agendamento de backup com intervalos de 4 horários.

  • CarlosTech

    thanks for the post, i found when completing these steps the database dump was actually placed in the “bin” folder and not the root directory for your Windows user.

    which would make sense as earlier in your post you set the path to the bin?