[mysql] How to export a mysql database using Command Prompt?

I have a database that is quite large so I want to export it using Command Prompt but I don't know how to.

I am using WAMP.

I was trying to take the dump of the db which was running on the docker and came up with the below command to achieve the same:

docker exec <container_id/name> /usr/bin/mysqldump -u <db_username> --password=<db_password> db_name > .sql

Hope this helps!

I have used wamp server. I tried on

c:\wamp\bin\mysql\mysql5.5.8\bin\mysqldump -uroot -p db_name > c:\somefolder\filename.sql

root is my username for mysql, and if you have any password specify it with:

-p[yourpassword]

Hope it works.

The problem with all these solutions (using the > redirector character) is that you write your dump from stdout which may break the encoding of some characters of your database.

If you have a character encoding issue. Such as :

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

then, you MUST use -r option to write the file.

MySQL

mysqldump -u user -pyour-password-without-space-between-letter-p-and-your-password --default-character-set=utf8 --host $HOST database-name -r dump.sql

Using Docker

docker exec --rm -v $pwd:dump -it mysql:5:7 mysqldump -u user -pyour-password-without-space-between-letter-p-and-your-password --default-character-set=utf8 --host $HOST database-name -r dump/dump.sql

Note: this mounts the current path as dump inside the instance.

We found the answer here

Conversely, don't use < to import your dump into your database, again, your non-utf8 characters may not be passed; but prefer source option.

mysql -u user -pYourPasswordYouNowKnowHow --default-character-set=utf8 your-database
mysql> SET names 'utf8'
mysql> SOURCE dump.sql

For windows OS :

When you get error 1064 mysql (42000) while trying to execute mysqldump command, exit from current terminal. And execute mysqldump command.

mysql>exit 
c:\xampp\mysql\bin>mysqldump -uroot -p --databases [database_name] > name_for_export_db.sql

Go to command prompt at this path,

C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>

Then give this command to export your database (no space after -p)

mysqldump -u[username] -p[userpassword] yourdatabase > [filepath]wantedsqlfile.sql

mysql -u -p databaseName>fileToPutDatabase

Locate your mysql instance with:

which mysql

If this is correct then export with the following (else navigate to the mysql instance in your mamp folder in bin):

mysqldump -u [username] -p [password] [dbname] > filename.sql

And if you wish to zip it at the sametime:

mysqldump -u [username] -p [password] [db] | gzip > filename.sql.gz

You can then move this file between servers with:

scp [email protected]:/path_to_your_dump/filename.sql.gz your_detination_path/

(where xxx.xxx.xxx.xxx is the server IP address)

And then import it with:

gunzip filename.sql.gz | mysql -u [user] -p [password] [database]

First of all open command prompt then open bin directory in cmd (i hope you're aware with cmd commands) go to bin directory of your MySql folder in WAMP program files.

run command

mysqldump -u db_username -p database_name > path_where_to_save_sql_file

press enter system will export particular database and create sql file to the given location.

i hope you got it :) if you have any question please let me know.

Give this command to export your database, this will include date as well

mysqldump -u[username] -p[userpassword] --databases yourdatabase | gzip > /home/pi/database_backup/database_`date '+%m-%d-%Y'`.sql.gz

(no space after -p)

Simply use the following command,

For Export:

mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz] 

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename] 

Note: There is no space between the keyword '-p' and your password.

You can use this script to export or import any database from terminal given at this link: https://github.com/Ridhwanluthra/mysql_import_export_script/blob/master/mysql_import_export_script.sh

echo -e "Welcome to the import/export database utility\n"
echo -e "the default location of mysqldump file is: /opt/lampp/bin/mysqldump\n"
echo -e "the default location of mysql file is: /opt/lampp/bin/mysql\n"
read -p 'Would like you like to change the default location [y/n]: ' location_change
read -p "Please enter your username: " u_name
read -p 'Would you like to import or export a database: [import/export]: ' action
echo

mysqldump_location=/opt/lampp/bin/mysqldump
mysql_location=/opt/lampp/bin/mysql

if [ "$action" == "export" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysqldump that you want to use: ' mysqldump_location
        echo
    else
        echo -e "Using default location of mysqldump\n"
    fi
    read -p 'Give the name of database in which you would like to export: ' db_name
    read -p 'Give the complete path of the .sql file in which you would like to export the database: ' sql_file
    $mysqldump_location -u $u_name -p $db_name > $sql_file
elif [ "$action" == "import" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysql that you want to use: ' mysql_location
        echo
    else
        echo -e "Using default location of mysql\n"
    fi
    read -p 'Give the complete path of the .sql file you would like to import: ' sql_file
    read -p 'Give the name of database in which to import this file: ' db_name
    $mysql_location -u $u_name -p $db_name < $sql_file
else
    echo "please select a valid command"
fi

Well you can use below command,

mysqldump --databases --user=root --password your_db_name > export_into_db.sql

and the generated file will be available in the same directory where you had ran this command.

You could find more on the official reference for mysqldump: Import Export MySQL DB

Note: use --databases instead of --database since the last one is no more supported.

Enjoy :)

Syntax

(mysqldump.exe full path) -u (user name) -p (password) (database name) > (export database file full path)

Example

c:>d:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe -u root -p mydbname > d:\mydb.sql

where d:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe will be your actual mysqldump.exe path, mydbname is the name of database which you want to export and d:\mydb.sql is the path where you want to store the exported database.

mysqldump -h [host] -p -u [user] [database name] > filename.sql

Example in localhost

mysqldump -h localhost -p -u root cookbook > cookbook.sql

I have installed my wamp server in D: drive so u have to go to the following path from ur command line->(and if u have installed ur wamp in c: drive then just replace the d: wtih c: here)

D:\>cd wamp
D:\wamp>cd bin
D:\wamp\bin>cd mysql
D:\wamp\bin\mysql>cd mysql5.5.8 (whatever ur verserion will be displayed here use keyboard Tab button and select the currently working mysql version on your server if you have more than one mysql versions)
D:\wamp\bin\mysql\mysql5.5.8>cd bin
D:\wamp\bin\mysql\mysql5.5.8\bin>mysqldump -u root -p password db_name > "d:\backupfile.sql"

here root is user of my phpmyadmin password is the password for phpmyadmin so if u haven't set any password for root just nothing type at that place, db_name is the database (for which database u r taking the backup) ,backupfile.sql is the file in which u want ur backup of ur database and u can also change the backup file location(d:\backupfile.sql) from to any other place on your computer

To export PROCEDUREs, FUNCTIONs & TRIGGERs too, add --routines parameter:

mysqldump -u YourUser -p YourDatabaseName --routines > wantedsqlfile.sql

mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql