转储文件 MySQL 5.6.10

Dump File MySQL 5.6.10

提问by aboliveira

I was trying to create a dump file from my MySQL database, but when I try it always gives me this error

我试图从我的 MySQL 数据库创建一个转储文件，但是当我尝试时它总是给我这个错误

Operation failed with exitcode 2

操作失败，退出代码 2

16:06:07 Dumping proactivetraindb (userstate)

Running: mysqldump.exe --defaults-extra-file="h:\docume~1\anarita\locals~1\temp\tmpde5rwy.cnf"  --user=root --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 "proactivetraindb" "userstate"

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': 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 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

The version of MySQl is 5.6.10 but the version of MySQLDump is 5.5.16

MySQl 的版本是 5.6.10 但 MySQLDump 的版本是 5.5.16

Does someone know how to solve this?

有人知道如何解决这个问题吗？

采纳答案by Jason Maelstrom

As outlined by a previous answer, but just to clarify further: This is for MySQL in Windows.

正如之前的答案所概述的那样，但只是为了进一步澄清：这是针对 Windows 中的 MySQL。

I was having the exact same issue while trying to dump data out of a table on a local test server, and based on the previous answer, this is how I did it.

我在尝试从本地测试服务器上的表中转储数据时遇到了完全相同的问题，根据之前的答案，我就是这样做的。

1. Find your mysql original download folder, and look in the /binfolder, there should be a mysqldump.exe.
2. Go into Workbench Preferences: Edit -> Preferences -> Administrator -> Pathto mysqldump Tool, and enter in the path to the mysqldump.exe that you found.
3. Save by pressing "OK" and restarting Workbench.
4. Open up terminal, but typing in cmdin the Search area of the start menu (Windows 7), and find the sql folder you found in step 1, and cd into bin. It should look like C:\...\bin>. Here, type in mysqldump -t -u [username] -p [database name] [tablename]. This should dump the data from the [tablename]table into the dump folder as designated in Workbench.

1. 找到你的mysql原始下载文件夹，在/bin文件夹里看，应该有一个mysqldump.exe.
2. 进入 Workbench Preferences: Edit -> Preferences -> Administrator -> Pathto mysqldump Tool，然后输入您找到的 mysqldump.exe 的路径。
3. 通过按“确定”并重新启动 Workbench 来保存。
4. 打开终端，但在cmd开始菜单（Windows 7）的搜索区域中输入，然后找到您在步骤 1 中找到的 sql 文件夹，然后 cd 到 bin。它应该看起来像C:\...\bin>. 在这里，输入mysqldump -t -u [username] -p [database name] [tablename]. 这应该将[tablename]表中的数据转储到 Workbench 中指定的转储文件夹中。

Hope this helps!

希望这可以帮助！

回答by Vespucci75fr

I had the same error in MySQL Workbench.

我在 MySQL Workbench 中遇到了同样的错误。

1. Find out where is the mysqldump tools provided by your install of MySQL 5.6. On my mac it was at "usr/local/mysql-5.6.10-osx10.7-x86_64/bin/mysqldump".
2. Then I set up this path in MySQL Workbench/Preferences/Administrator/Path to mysqldump Tool
3. Give MySQL Workbench a restart.

1. 找出安装 MySQL 5.6 时提供的 mysqldump 工具在哪里。在我的 Mac 上，它位于“usr/local/mysql-5.6.10-osx10.7-x86_64/bin/mysqldump”。
2. 然后我在 MySQL Workbench/Preferences/Administrator/Path to mysqldump Tool 中设置这个路径
3. 重启 MySQL Workbench。

It should work.

它应该工作。

回答by user1973679

The reason for this is that MySQL 5.6 has removed support for "SET OPTION" and your mysql client tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on MySQL bugs website.

这样做的原因是 MySQL 5.6 已删除对“SET OPTION”的支持，并且您的 mysql 客户端工具可能是旧版本。最有可能是 5.5 或 5.1。MySQL 错误网站上有关于此问题的更多信息。

The quickest solution is to update your mysql client tools to 5.6 and your problem will be solved. If you are on Linux platform, here is a solution that worked great for me: http://www.markomedia.com.au/mysqldump-mysql-5-6-problem-solved/

最快的解决方案是将您的 mysql 客户端工具更新到 5.6，您的问题将得到解决。如果您使用的是 Linux 平台，这里有一个对我有用的解决方案：http: //www.markomedia.com.au/mysqldump-mysql-5-6-problem-solved/

回答by Angel T

You must use mysqldump 5.6.x to be able to dump mysql 5.6 database, because MySQL 5.6 discontinued and removed support for SET OPTION syntax.
Under 32/64bit Windows you must do:
1.1. (32bit win) Download Windows (x86, 32-bit), ZIP Archive 5.6.19 212.3M http://dev.mysql.com/downloads/mysql/(direct link http://dev.mysql.com/downloads/file.php?id=452188)
1.2. (64bit win) Download Windows (x86, 64-bit), ZIP Archive 5.6.19 217.2M http://dev.mysql.com/downloads/mysql/(direct link http://dev.mysql.com/downloads/file.php?id=452189)
2. Extract mysqldump.exe from just downloaded file mysql-5.6.19-win32(64).zip (from /mysql-5.6.19-win32(64)/bin/mysqldump.exe)
3. Replace this just extracted file with your already installed mysqldump.exe file (instalation dir/mysql/bin/mysqldump.exe)

您必须使用 mysqldump 5.6.x 才能转储 mysql 5.6 数据库，因为 MySQL 5.6 停止并删除了对 SET OPTION 语法的支持。
在 32/64 位 Windows 下你必须做：
1.1. (32bit win) 下载 Windows (x86, 32-bit), ZIP Archive 5.6.19 212.3M http://dev.mysql.com/downloads/mysql/(直接链接http://dev.mysql.com/downloads/ file.php?id=452188)
1.2. (64bit win) 下载 Windows (x86, 64-bit), ZIP Archive 5.6.19 217.2M http://dev.mysql.com/downloads/mysql/(直接链接http://dev.mysql.com/downloads/ file.php?id=452189)
2. 从刚刚下载的文件 mysql-5.6.19-win32(64).zip (from /mysql-5.6.19-win32(64)/bin/mysqldump.exe) 中提取 mysqldump.exe
3.用你已经安装好的mysqldump.exe文件（安装目录/mysql/bin/mysqldump.exe）替换这个刚刚解压的文件

回答by Stephen

Using Windows 7, a far easier solution for me was to find mysqldump.exe from; C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe

使用 Windows 7，对我来说更简单的解决方案是从以下位置找到 mysqldump.exe；C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe

copy it and then replace the MySQL workbench mysqldump.exe which is located in the root of workbench C:\Program Files\MySQL\MySQL Workbench CE 5.2.47\mysqldump.exe

复制它，然后替换位于工作台C:\Program Files\MySQL\MySQL Workbench CE 5.2.47\mysqldump.exe根目录中的MySQL 工作台 mysqldump.exe

Restart MySQL workbench and all should work fine.

重新启动 MySQL 工作台，一切都应该正常工作。

回答by Ankur Mahajan

If you are using Window 10. Your Mysql serverwould have been installed in C:\Program Files\MySQLdirectory and rest all components are installed in C:\Program Files (x86)\MySQL. Go to MySql Workbench>Edit>Preferences. A window will open, click the Administrator tab and mention the path of your mysqldump.exe(which is present in C:\Program Files\MySQL). Restart your workbench.

如果您正在使用Window 10. 您Mysql server将安装在C:\Program Files\MySQL目录中，其余所有组件都安装在C:\Program Files (x86)\MySQL. 转到MySql Workbench> Edit> Preferences。将打开一个窗口，单击“管理员”选项卡并提及您的路径mysqldump.exe（出现在 中C:\Program Files\MySQL）。重新启动您的工作台。

Thanks, Happy coding!!

谢谢，快乐编码！！

回答by Annia Martinez

In Workbench look for Preferences: Edit -> Preferences -> Administrator -> Path to the mysqldump Tool. Browse your MySQL folder inside the bin folder and look for mysqldump.exe.

在 Workbench 中查找首选项：编辑 -> 首选项 -> 管理员 -> mysqldump 工具的路径。浏览 bin 文件夹内的 MySQL 文件夹并查找 mysqldump.exe。

回答by fedmich

You are probably using Amazon RDS, right? You can just get the newer version of mysqldump, v5.6 and use it instead.

您可能正在使用 Amazon RDS，对吗？您可以获取较新版本的 mysqldump v5.6 并使用它。

Download the portable zip here and extract just the mysql\mysqldump.exe there. http://sourceforge.net/projects/xampp/files/XAMPP%20Windows/1.8.3/

在此处下载可移植的 zip 并在此处解压 mysql\mysqldump.exe。 http://sourceforge.net/projects/xampp/files/XAMPP%20Windows/1.8.3/

I tried it just now and it worked fine for me.

我刚才试过了，对我来说效果很好。

回答by Leo Medeiros

I had this problem on the Amazon EC2 acessing a RDS Mysql 5.6 database. I just ran

我在访问 RDS Mysql 5.6 数据库的 Amazon EC2 上遇到了这个问题。我刚跑

sudo apt-get upgrade

sudo apt-get 升级

It didn't update mysql client to 5.6, but mysqldump now is working fine.

它没有将 mysql 客户端更新到 5.6，但 mysqldump 现在工作正常。

回答by Sabarish

For Mac it depends on your installation. If you used a dmg file to install it, you need to find the bin directory. mine was under /usr/local/mysql/binYou will find mysqldump file under this folder. Now provide this location in your Preferences -> Administrator -> Path to mysqldump Tool as /usr/local/mysql/bin/mysqldumpYou should be good to export now.

对于 Mac，这取决于您的安装。如果使用dmg文件安装，则需要找到bin目录。我的在/usr/local/mysql/bin你会在这个文件夹下找到mysqldump文件。现在在您的首选项 -> 管理员 -> mysqldump 工具的路径中提供此位置，因为/usr/local/mysql/bin/mysqldump您现在应该可以导出了。