Export 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 export the contents of a MySQL database. Is there a way I can do this from the command line?

Paddie

Try: mediacollege.com/computer/database/mysql/backup.html – Tavares

mysqldump is what you are looking for. – Allegro

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.

Wuhsien

Your answer in conjunction with #2990224 -- 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. – Ransack

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 – Colonialism

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

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. – Mcwhirter

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 – Arsenious

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". – Assassinate

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 – Libation

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 – Freetown

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.

Pontias

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

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

Blasphemy

Answer 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

Oblation

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

Answer 5

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

Reg

Answer 6

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

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

Sweetener

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

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

Answer 7

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

Swank

Answer 8

If you are running the MySQL other than default port:

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

Ceasefire

Answer 9

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

Upstretched

Answer 10

mysqldump is another program in the MySQL directory

Program Files\MySQL\MySQL Server 8.0\bin

step 1: First you have to go to the path and open CMD from the folder.

step 2: Then type mysqldump in the CMD

---

it should display as follows

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

step 3: Then type this command

mysqldump -u [user_name] -p [database_name] > D:\db_dump.sql

Note : you should provide an absolute path for the output file. Here I provide D:\

Freetown

Answer 11

For example, you can export the schema and data of the tables of apple database to backup.sql as shown below. *backup.sql is created if it doesn't exist and my answer explains how to import the schema and data of the tables of a database and my answer, my answer and my answer explain how to export the schema and data of a database, multiple databases and all databases respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:

mysqldump -u john -p apple > backup.sql

Or:

mysqldump --user=john --password apple > backup.sql

Or:

mysqldump -u john -p apple -r backup.sql

Or:

mysqldump --user=john --password apple --result-file=backup.sql

Or:

mysqldump -u john -p -r backup.sql apple

Or:

mysqldump --user=john --password --result-file=backup.sql apple

Then, you need to input a password after running the command above:

Enter password:

In addition, you can export the schema and data of the tables of apple database to backup.sql without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p(--password=) because there is error and my answer explains how to export a database without a password prompt in detail:

mysqldump -u john -pbanana apple > backup.sql

And, using -d(--no-data), you can export only the schema of the tables of apple database to backup.sql as shown below. *My answer explains how to export only schema more:

mysqldump -u john -p -d apple > backup.sql

And, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -t -c apple person animal > backup.sql

And, using -t(--no-create-info), -c(--complete-insert) and --ignore-table, you can export only the data of the tables except person and animal tables of apple database to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export databases except some tables more:

mysqldump -u john -p -t -c apple --ignore-table=apple.person --ignore-table=apple.animal > backup.sql

Burne

Answer 12

For example, using -B(--databases), you can export the schema and data of apple database to backup.sql as shown below. *backup.sql is created if it doesn't exist and -B(--databases) can export one or more databases and generates the schema CREATE DATABASE apple; and USE apple; and my answer explains how to import the schema and data of a database and my answer, my answer and my answer explain how to export the schema and data of multiple databases, all databases and the tables of a database respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:

mysqldump -u john -p -B apple > backup.sql

Or:

mysqldump -u john -p --databases apple > backup.sql

In addition, using -d(--no-data), you can export only the schema of apple database to backup.sql as shown below. *My answer explains how to export only schema more:

mysqldump -u john -p -B -d apple > backup.sql

Or, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -B -t -c apple > backup.sql

Be careful, using -t(--no-create-info) and -c(--complete-insert), you cannot properly export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below:

So, this below gets error:

mysqldump -u john -p -B -t -c apple person animal > backup.sql

mysqldump: Got error: 1049: Unknown database 'person' when selecting the database

And, this below lacks the schema CREATE DATABASE apple; and USE apple; because -B(--databases) is overridden by --tables which excludes the schema CREATE DATABASE apple; and USE apple;:

mysqldump -u john -p -B -t -c apple --tables person animal > backup.sql

But, using -t(--no-create-info), -c(--complete-insert) and --ignore-table, you can properly export only the data of apple database except apple database's person and animal tables to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export databases except some tables more:

mysqldump -u john -p -B -t -c apple --ignore-table=apple.person --ignore-table=apple.animal > backup.sql

Burne

Answer 13

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.

Columbite

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

Answer 14

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

Dortheydorthy

The question suggested that MySQL is running on Linux. – Countermove

Answer 15

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

Generalship

Answer 16

For some versions of MySQL try.

sudo mysqldump [database name] > db_backup.sql

Trina

Answer 17

10 years have passed since the topic was created, and during this time new utilities have appeared that resolve the main problem of mysqldump - performance.

mysqlsh

mysqlsh is a new universal shell for database administration. Unlike the classic mysql client, which allows you to simply execute SQL queries, mysqlsh can work in SQL \ Python \ JS mode and, most importantly, it has access to specialized database objects: dba, utils, shell. Through these objects you can access specialized administration tools.

The util object allows you to dump like this:

mysqlsh --mysql -u adm -h localhost -P 3306 -e "util.dumpInstance('/tmp/sample-backup-instane')"

To restore the dump

mysqlsh --mysql -u adm -h localhost -P 3306 -e "util.loadInstance('/tmp/sample-backup-instane')"

Unlike export via mysqldump, export via mysqlsh is performed in several threads (4 by default), which can significantly speed up dump creation.

Note, by default, dumps are created in TSV format (not .SQL)

mydumper

mydumper is a command line utility created by the community (link to GitHub). Unlike mysqlsh it is easier to use, because it is a highly specialized tool.

The dump created by the mydumper utility can be restored either manually by mysql or using the myloader utility, which works in conjunction with mydumper.

mydumper -u adm -p pass

This command creates a subdirectory with a set of .sql files, where each file corresponds to a table. To download this dump into an empty database use:

myloader --directory=export-20230916-151241

---

mysqlsh documentation

mydumper docs

More examples about backup via mysqlsh or mydumper

Carolinacaroline

Answer 18

For example, using -B(--databases), you can export the schema and data of apple and orange databases to backup.sql with as shown below. *backup.sql is created if it doesn't exist and -B(--databases) can export one or more databases and generates the schema CREATE DATABASE <db>; and USE <db>; and my answer explains how to import the schema and data of multiple databases and my answer, my answer and my answer explain how to export the schema and data of all databases, a database and the tables of a database respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:

mysqldump -u john -p -B apple orange > backup.sql

Or:

mysqldump -u john -p --databases apple orange > backup.sql

In addition, using -d(--no-data), you can export only the schema of apple and orange databases to backup.sql as shown below. *My answer explains how to export only schema more:

mysqldump -u john -p -B -d apple orange > backup.sql

Or, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of apple and orange databases to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -B -t -c apple orange > backup.sql

Be careful, using -t(--no-create-info) and -c(--complete-insert), you cannot export only the data of the specific tables person and animal of apple and orange databases to backup.sql with INSERT statement which has column names as shown below:

So, this below gets error:

mysqldump -u john -p -B -t -c apple orange person animal > backup.sql

mysqldump: Got error: 1049: Unknown database 'person' when selecting the database

And, this below with --tables gets error:

mysqldump -u john -p -B -t -c apple orange --tables person animal > backup.sql

mysqldump: Couldn't find table: "orange"

But, using -t(--no-create-info), -c(--complete-insert) and --ignore-table, you can export only the data of apple and orange databases except apple database's person table and orange database's animal table to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export databases except some tables more:

mysqldump -u john -p -B -t -c apple orange --ignore-table=apple.person --ignore-table=orange.animal > backup.sql

Burne

Answer 19

If you are exporting from one cloud provider and importing into another cloud provider, such as Amazon RDS, and you are using MySQL (not MariaDB), then you have to take heed of GTIDs as well, which could be set for failover and scaleout behavior in an instance that is using MySQL replication with Global Transaction Identifiers (GTIDs). Remember MySQL instances on the cloud are Managed Services. They do not give you root access to the MySQL Instance; otherwise, it can cause instability in their platform. So they do not allow you to explicitly define global variables in a SQL file. This becomes an inssue when you do an import and you get an error like this:

Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

If you are using MySQL and not MariaDB, you can set the set-gtid-purged flag to OFF to your mysqldump command to prevent such access denied errors:

mysqldump --set-gtid-purged=OFF -h [host] -u [uname] -p dbName > dump.sql

Auditorium

Answer 20

For example, using -A(--all-databases), you can export the schema and data of all databases including mysql system database to backup.sql as shown below. *backup.sql is created if it doesn't exist and -A(--all-databases) can also export mysql system database and generates the schema CREATE DATABASE <db>; and USE <db>; and my answer explains how to import the schema and data of all databases and my answer, my answer and my answer explain how to export the schema and data of multiple databases, a database and the tables of a database respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:

mysqldump -u john -p -A > backup.sql

Or:

mysqldump -u john -p --all-databases > backup.sql

In addition, you can export only the schema of all databases to backup.sql with -d(--no-data) as shown below. *My answer explains how to export only schema more:

mysqldump -u john -p -A -d > backup.sql

Or, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of all databases to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export only data more:

mysqldump -u john -p -A -t -c > backup.sql

Be careful, using -t(--no-create-info) and -c(--complete-insert), you cannot export only the data of the specific tables person and animal of all databases to backup.sql with INSERT statement which has column names as shown below:

So, these below export nothing without error:

mysqldump -u john -p -A -t -c person animal > backup.sql

mysqldump -u john -p -A -t -c --tables person animal > backup.sql

But, using -t(--no-create-info), -c(--complete-insert) and --ignore-table, you can export only the data of all databases except apple database's person table and orange database's animal table to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export databases except some tables more:

mysqldump -u john -p -A -t -c --ignore-table=apple.person --ignore-table=orange.animal > backup.sql

Burne

You really like to repeat yourself "...... and my answer, my answer and my answer explain ... ", do you ? – Wideawake

Answer 21

@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;

Wigeon