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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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:\

Answer 7

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

Answer 8

For some versions of MySQL try.

sudo mysqldump [database name] > db_backup.sql

Answer 9

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.

Answer 10

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

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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 15

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

Answer 16

If you are running the MySQL other than default port:

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

Answer 17

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

Answer 18

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 19

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.

Answer 20

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 21

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