How to export MySQL database using Command Prompt or batch file

To export MySQL database from command Line a set of commands need to be executed. Follow the steps below for data export using command.

There are two ways to execute a command.

1. Run command directly in command line
2. Store the command in a batch file (windows system)

I am using the batch file method. This method allows you to store your command and re use as many times you want. The command in the batch batch file is:

path_to_bin\mysqldump --user=your_db_username --password=your_db_password --result-file="filename_with_disk_path" your_database_name

Code Explanation:

path_to_bin is path where your bin folder inside mysql folder is present. For example: if your server is installed in c: drive, then
path_to_bin = c:\xampp\mysql\bin\

your_db_username is your database username of MySQL using which you login to your database, which is by default = root

your_db_password is yourdatabase password of MySQL using which you login to your database, which is by default blank in windows

filename_with_disk_path is the name with hard path where your exported database will be stored in the form of queries. If path will not be provided, the file will be stored in the bin folder of mysql directory.

your_database_name is the name of database which is to be exported.

Steps to create the batch file for MySQL database export (considering that server is installed in C Drive and export file will be stored in D drive):

1. Open a text editor like notepad or notepad++ or as per your choice.

2. Type in the following lines

@echo off
c:\xampp\mysql\bin\mysqldump --user=root --password= --result-file="d:\mydbbackups\mybackupfile.sql" my_sample_databse
echo Export is completed!
pause
exit

3. Save the file in d drive mydbbackups folder with name “myexportfile.bat” and select any types/all types (as available) in file types option to save.

*Do remember to mention the extension as .bat

4. Now export file is save in d:\mydbbackups. You need to double click the file to run export command.

Export selected Tables using command

path_to_bin\mysqldump --user=your_db_username --password=your_db_password --result-file="filename_with_disk_path" your_database_name table_one table_two ...

In the above code names of tables to be exported are added after the database name containing those tables.

Export all mysql databases using command

path_to_bin\mysqldump --user=your_db_username --password=your_db_password --result-file="filename_with_disk_path"  --all-databases

Simply remove the database name from the end and add –all-databases.It will let you export all the databses.