Downloading MySQL dump from command line

Question

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

User · Accepted Answer

You can accomplish this using the mysqldump command-line function.

For example:

If it's an entire DB, then:

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

If it's all DBs, then:

   $ mysqldump -u [uname] -p --all-databases > all_db_backup.sql

If it's specific tables within a DB, then:

   $ mysqldump -u [uname] -p db_name table1 table2 > table_backup.sql

You can even go as far as auto-compressing the output using gzip (if your DB is very big):

   $ mysqldump -u [uname] -p db_name | gzip > db_backup.sql.gz

If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):

   $ mysqldump -P 3306 -h [ip_address] -u [uname] -p db_name > db_backup.sql

It should drop the .sql file in the folder you run the command-line from.

EDIT: Updated to avoid inclusion of passwords in CLI commands, use the -p option without the password. It will prompt you for it and not record it.

User · Answer

If you are running the MySQL other than default port   mysqldump exe -u username -p -P PORT NO database  gt  backup sql

User · Answer

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

User · Answer

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

User · Answer

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 gt 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

User · Answer

If downloading from remote server  here is a simple example   mysqldump -h my address amazonaws com -u my username -p db name  gt   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

User · Answer

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  gt   path backupname sql

User · Answer

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  gt  dbname dump sql

User · Answer

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 gt  C  db-dontdelete db sql

User · Answer

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  gt  db backup sql  and then it will ask for the password

User · Answer

Don t go inside mysql  just open Command prompt and directly type this  mysqldump -u  uname  -p pass  db name  gt  db backup sql

User · Answer

If you have the database named archiedb  use this  mysql -p  lt password for the database gt  --databases archiedb  gt   home database backup sql  Assuming this is Linux  choose where the backup file will be saved

User · Answer

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  gt  C  localhost sql   save this into a text file such as backup cmd

[mysql] Downloading MySQL dump from command line

Examples related to mysql

Examples related to command-line

Examples related to command-line-arguments

Examples related to linode