[mysql] Downloading MySQL dump from command line

I am moving away from Linode because I don't have the Linux sysadmin skills necessary; before I complete the transition to a more noob-friendly service, I need to download the contents of a MySQL database. Is there a way I can do this from the command line?

This question is related to mysql command-line command-line-arguments linode

The answer is


If downloading from remote server, here is a simple example:

mysqldump -h my.address.amazonaws.com -u my_username -p db_name > /home/username/db_backup_name.sql

The -p indicates you will enter a password, it does not relate to the db_name. After entering the command you will be prompted for the password. Type it in and press enter.


Just type mysqldump or mysqldump --help in your cmd will show how to use

Here is my cmd result

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

Note: This step only comes after dumping your MySQL file(which most of the answers above have addressed).

It assumes that you have the said dump file in your remote server and now you want to bring it down to your local computer.

To download the dumped .sql file from your remote server to your local computer, do

scp -i YOUR_SSH_KEY your_username@IP:name_of_file.sql ./my_local_project_dir

Don't go inside mysql, just open Command prompt and directly type this:

mysqldump -u [uname] -p[pass] db_name > db_backup.sql

@echo off
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "datestamp=%YYYY%.%MM%.%DD%.%HH%.%Min%.%Sec%"
set drive=your backup folder
set databaseName=your databasename
set user="your database user"
set password="your database password"
subst Z: "C:\Program Files\7-Zip" 
subst M: "D:\AppServ\MySQL\bin"
set zipFile="%drive%\%databaseName%-%datestamp%.zip"
set sqlFile="%drive%\%databaseName%-%datestamp%.sql"
M:\mysqldump.exe --user=%user% --password=%password% --result-file="%sqlFile%" --databases %databaseName%
@echo Mysql Backup Created
Z:\7z.exe a -tzip "%zipFile%" "%sqlFile%"
@echo File Compress End
del %sqlFile%
@echo Delete mysql file
pause;

If you have the database named archiedb, use this:

mysql -p <password for the database> --databases archiedb > /home/database_backup.sql

Assuming this is Linux, choose where the backup file will be saved.


Go to MySQL installation directory and open cmd from there. Then execute the below command to get a backup of your database.

mysqldump -u root -p --add-drop-database --databases db> C:\db-dontdelete\db.sql

For Windows users you can go to your mysql folder to run the command

e.g.

cd c:\wamp64\bin\mysql\mysql5.7.26\bin
mysqldump -u root -p databasename > dbname_dump.sql

For those who wants to type password within the command line. It is possible but recommend to pass it inside quotes so that the special character won't cause any issue.

mysqldump -h'my.address.amazonaws.com' -u'my_username' -p'password' db_name > /path/backupname.sql

In latest versions of mysql, at least in mine, you cannot put your pass in the command directly.

You have to run:

mysqldump -u [uname] -p db_name > db_backup.sql

and then it will ask for the password.


On windows you need to specify the mysql bin where the mysqldump.exe resides.

cd C:\xampp\mysql\bin

mysqldump -u[username] -p[password] --all-databases > C:\localhost.sql

save this into a text file such as backup.cmd


If you are running the MySQL other than default port:

mysqldump.exe -u username -p -P PORT_NO database > backup.sql

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to command-line

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Flutter command not found Angular - ng: command not found how to run python files in windows command prompt? How to run .NET Core console app from the command line Copy Paste in Bash on Ubuntu on Windows How to find which version of TensorFlow is installed in my system? How to install JQ on Mac by command-line? Python not working in the command line of git bash Run function in script from command line (Node JS)

Examples related to command-line-arguments

How to pass arguments to Shell Script through docker run How can I pass variable to ansible playbook in the command line? Use Robocopy to copy only changed files? mkdir's "-p" option What is Robocopy's "restartable" option? How do you run a .exe with parameters using vba's shell()? Bash command line and input limit Check number of arguments passed to a Bash script Parsing boolean values with argparse Import SQL file by command line in Windows 7

Examples related to linode

Downloading MySQL dump from command line ssh server connect to host xxx port 22: Connection timed out on linux-ubuntu