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.
218 Answers
First check if your command line recognizes mysql command. If not go to command & type in:
set path=c:\wamp\bin\mysql\mysql5.1.36\binThen use this command to export your database:
mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sqlYou will then be prompted for the database password.
This exports the database to the path you are currently in, while executing this command
Note: Here are some detailed instructions regarding both import and export
5Simply 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.
2Well 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 :)
3First 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_filepress 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.
2Go 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
Locate your mysql instance with:
which mysqlIf 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.sqlAnd if you wish to zip it at the sametime:
mysqldump -u [username] -p [password] [db] | gzip > filename.sql.gzYou can then move this file between servers with:
scp :/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]To export PROCEDUREs, FUNCTIONs & TRIGGERs too, add --routines parameter:
mysqldump -u YourUser -p YourDatabaseName --routines > wantedsqlfile.sql
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.sqlUsing 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.sqlNote: 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.sqlGive 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)
1I 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 phpmyadminpassword 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
2mysqldump -h [host] -p -u [user] [database name] > filename.sqlExample in localhost
mysqldump -h localhost -p -u root cookbook > cookbook.sqlmysqldump --no-tablespaces -u username -p pass database_name > db_backup_file.sqlSyntax
(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.sqlwhere 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.
I have used wamp server. I tried on
c:\wamp\bin\mysql\mysql5.5.8\bin\mysqldump -uroot -p db_name > c:\somefolder\filename.sqlroot is my username for mysql, and if you have any password specify it with:
-p[yourpassword]Hope it works.
1For 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.sqlI 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!
mysql -u -p databaseName>fileToPutDatabase
1You can use this script to export or import any database from terminal given at this link:
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
