如何使用命令提示导出mysql数据库?

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.

我用里面。

15 个解决方案

#1

First check if your command line recognizes mysql command. If not go to command & type in:

首先检查命令行是否识别mysql命令。如果不去命令和输入:

set path=c:\wamp\bin\mysql\mysql5.1.36\bin

Then use this command to export your database:

然后使用此命令导出数据库:

mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql

You 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

注:这里有一些关于进出口的详细说明。

#2

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.

注意:在关键字“-p”和您的密码之间没有空格。

#3

Well you can use below command,

你可以使用下面的命令，

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

mysqldump——数据库——user=root——密码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 or from this

您可以在mysqldump的官方引用中找到更多:导入导出MySQL DB或从这里。

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

注意:使用数据库而不是数据库，因为最后一个数据库不再支持。

Enjoy :)

享受:)

#4

Go to command prompt at this path,

在这条路径上命令提示符，

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

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

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

然后使用这个命令导出数据库(在-p之后没有空格)

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

mysqldump -u[用户名]-p[userpassword] yourdatabase > [filepath]wantedsqlfile.sql。

#5

Locate your mysql instance with:

将mysql实例定位为:

which mysql

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

如果这是正确的，那么导出如下(在bin中导航到您的mamp文件夹中的mysql实例):

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

And if you wish to zip it at the sametime:

如果你想把它压缩到sametime:

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

You can then move this file between servers with:

然后可以在服务器之间移动这个文件:

scp [e-mail]:/path_to_your_dump/filename.sql.gz your_detination_path/

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

(xxx.xxx.xxx。xxx是服务器IP地址

And then import it with:

然后导入:

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

#6

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.

首先打开命令提示符，然后在cmd中打开bin目录(我希望您知道cmd命令)在WAMP程序文件中访问MySql文件夹的bin目录。

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.

按enter系统将导出特定的数据库并创建到给定位置的sql文件。

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

如果你有什么问题，请告诉我。

#7

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

要导出程序、函数和触发器，还需要添加—例程参数:

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

mysqldump -u YourUser -p YourDatabaseName—例程> wantedsqlfile.sql。

#8

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)

我已经安装了我的wamp服务器在D:驱动器，所以你必须从你的命令行->到下面的路径(如果你已经安装了你的wamp在c:驱动器然后只是替换D: wtih c:这里)

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

这里root是phpmyadmin密码的用户，这是phpmyadmin的密码，所以如果您没有为root用户设置任何密码，那么db_name就是数据库(用于备份的数据库u r)，backupfile。sql是你想要备份你的数据库的文件，你也可以将备份文件的位置(d:\backupfile.sql)从你电脑上的任何其他地方更改。

#9

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

Example in localhost

在本地主机

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

#10

The answer of Opentuned is great. One small thing is removing the actual password from the command line:

Opentuned的答案很好。一件小事就是从命令行中删除实际的密码:

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

This prevents people searching your history for the password.

这可以防止人们搜索您的历史密码。

#11

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.

mysql在d:\ wamp \ bin \ \ mysql5.6.12 \ bin \,mysqldump。exe将是您实际的mysqldump。exe路径，mydbname是您想要导出的数据库名称和d:\mydb。sql是您希望存储导出数据库的路径。

#12

I have used wamp server. I tried on

我使用了wamp服务器。我试着在

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:

root是mysql的用户名，如果你有任何密码，请用:

-p[yourpassword]

Hope it works.

希望它的工作原理。

#13

For windows OS :

对于windows操作系统:

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

当您在尝试执行mysqldump命令时，错误1064 mysql(42000)，从当前终端退出。和执行,mysqldump命令。

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

#14

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

您可以使用此脚本来导出或导入任何来自该链接的终端的数据库: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

#15

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)

(没有空间后- p)