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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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