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?

Solution 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.

Solution 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.

Solution 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.

Solution 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

Solution 5

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

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

View more solutions

Comments

[Zak]

Your answer in conjunction with stackoverflow.com/questions/2989724/… -- Should do what he is asking, since he did include he needs it downloaded. It's either that or a wget or scp will be needed to retrieve said file once built.

[Pitt]

Small note that it is safer to not enter the password right in the command. Only using the -p option without password will prompt for the password when run, that way the password is not stored in your command history (and potentially retrieved). So using the following command: mysqldump -P 3306 -h [ip_address] -u [uname] -p db_name > db_backup.sql

[behz4d]

or usually having mysql in your PATH variable so you can run mysql commands from everywhere without being in it's directory.

[Phillip Copley]

Copying the first line of code from the accepted answer on a 5-year old question is a bold strategy for karma farming.

[Nithin Raja]

I tried this MySQLdump command inside Mysql prompt and didn't work. so just answered :-)

[Timo]

How do you specify the ssh port if access is remote? Port is not default 22 in my use case ..

[Pathros]

Instead of using > to save, I rather use -r in order to prevent trouble with foreign characters, or that nightmare concerning encoding problems, as stated in this article.

[y2k-shubham]

removing space between -p option and actual password does the trick

[Nabaasa Archie]

shell>mysqldump -p mypassword --databases mydb>/home/backup.sql.

[accord_guy]

For large/actively updated dataases, use --single-transaction parameter. This creates a checkpoint and helps ensure consistency. Also, use switches --routines --triggers, if you have stored procedures/functions/triggers

[gmauch]

The question suggested that MySQL is running on Linux.

[Martin Braun]

You should use --result-file=db_backup.sql instead of > db_backup.sql. Quote from the MySQL documentation: "UTF-16 is not permitted as a connection character set (see Impermissible Client Character Sets), so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format".

[dincer.unal]

When I run this command " >mysqldump -u [uname] -p db_name > db_backup.sql" return the Can not connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' error returning. Mysql is runnig. I can log in "./mysql -u root" command. thank you already

[Nilupul Heshan]

NOTE: I'm using MySQL 8 - it should provide an absolute path for the output file mysqldump -u [user_name] -p [db_name] > D:\dump\mof_dump.sql

[Infa]

@y2k-shubham Thank you mate, you are awesome!