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?

回答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.

回答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.

回答3:

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.

回答4:

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

回答5:

Open Command prompt, and directly type this command. Don't go inside mysql and then type this command.

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

回答6:

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

回答7:

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

回答8:

If you are running the MySQL other than default port:

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

回答9:

Use this If you have the database with the name archiedb, use this mysql -p --databases archiedb > /home/database_backup.sql

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

回答10:

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