Dump File MySQL 5.6.10

Question

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

Operation failed with exitcode 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

Does someone know how to solve this?

Its your options file, for global option for the mysql dump command....the problem may be is in that file "--defaults-extra-file="h:\docume~1\anarita\locals~1\temp\tmpde5rwy.cnf" --"....saludos ;) — Hackerman, Mar 05 '13 at 15:23

score 43 · Accepted Answer · edited Jun 03 '15 at 10:29

43

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

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.

Find your mysql original download folder, and look in the /bin folder, there should be a mysqldump.exe.
Go into Workbench Preferences: Edit -> Preferences -> Administrator -> Path to mysqldump Tool, and enter in the path to the mysqldump.exe that you found.
Save by pressing "OK" and restarting Workbench.
Open up terminal, but typing in cmd in 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.

Hope this helps!

edited Jun 03 '15 at 10:29

bmscomp

777
1
11
21

answered May 17 '13 at 20:36

Jason Maelstrom

454
5
3

1

This also works by using the workbench export tool...very nifty!! Thanks Maelstrom! – pythOnometrist Jun 24 '13 at 11:21
6

I'm getting this error without Workbench involved at all. Just `mysqldump -u root [database name] > dump.sql` gives me this error. – laggingreflex Sep 21 '14 at 03:43
I had two versions of my sql server on my device. I tried the full path to the 4.1 mysqldump version which failed. Then I tried the full path the 5.6 mysqldump version which worked. – Keith John Hutchison Feb 03 '16 at 01:19

score 24 · Answer 2 · answered Mar 19 '13 at 17:26

24

I had the same error in MySQL Workbench.

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".
Then I set up this path in MySQL Workbench/Preferences/Administrator/Path to mysqldump Tool
Give MySQL Workbench a restart.

It should work.

answered Mar 19 '13 at 17:26

Vespucci75fr

338
2
7

Mine was in this location in Finder: `Mac HD: /Shared Items/Public/mysql-5.6.10-osx10.7-x86_64/bin` Thanks for the post Vespucci75fr – twleblanc Apr 18 '13 at 04:13
3

For Windows : %programfiles%\MySQL\MySQL Server 5.6\bin – Luke Marlin Apr 30 '13 at 14:18

score 20 · Answer 3 · answered Nov 15 '13 at 23:13

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.

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/

score 6 · Answer 4 · answered Jun 13 '14 at 14:04

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)

score 4 · Answer 5 · answered Dec 08 '14 at 21:14

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

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

Restart MySQL workbench and all should work fine.

score 1 · Answer 6 · answered Oct 01 '13 at 03:41

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

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

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

score 1 · Answer 7 · answered Feb 11 '16 at 08:29

If you are using Window 10. Your Mysql server would have been installed in C:\Program Files\MySQL directory 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.

Thanks, Happy coding!!

score 1 · Answer 8 · answered Feb 22 '16 at 21:28

1

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.

answered Feb 22 '16 at 21:28

Annia Martinez

2,442
1
12
9

score 0 · Answer 9 · answered Jan 03 '14 at 16:09

0

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

sudo apt-get upgrade

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

answered Jan 03 '14 at 16:09

Leo Medeiros

154
1
3

it didn't work for me and I wish I had that time back :( – pguardiario Apr 02 '14 at 00:55

score 0 · Answer 10 · answered Apr 01 '14 at 14:41

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/bin You will find mysqldump file under this folder. Now provide this location in your Preferences -> Administrator -> Path to mysqldump Tool as /usr/local/mysql/bin/mysqldump You should be good to export now.

score 0 · Answer 11 · answered May 22 '15 at 09:26

If you are on Linux, update mysql-client to latest version. On Ubuntu:

sudo apt-get upgrade mysql-client

did the job for me.

Setting up mysql-client-5.5 (5.5.43-0ubuntu0.12.04.1) ...

Mine got updated to 5.5 and mysqldump now works for me. This is on Amazon RDS with MySQL 5.6.x

score 0 · Answer 12 · answered Dec 02 '16 at 21:55

0

If you're on a linux machine, this is provided by the mysql-client package. Installing that fixed the problem for me.

answered Dec 02 '16 at 21:55

Kenny Loveall

519
4
16

SherylHohman · Answer 13 · 2018-10-18T16:45:17.117

For compatibility, I needed MySQL 5.6.41 on my Windows 10 machine, so I used the 5.6.41 MySQL installer to install "All" (My Workbench, MySQL, connectors, etc).

Even though I used the installer for version 5.6.41, it installed Workbench 8.0, as well as version 8.0 of the various connectors, etc.
So of course, I got a similar error.

There seems to be no easy way to force lower version of MySQL Workbench to be installed instead (On my previous machine, before workbench 8.0, did NOT have this conflict when installed).

Solution that worked for me:

Find the version of mysqldump.exe that matches the version of MySQL you're using:
- From Windows Explorer, I searched my machine for all versions of mysqldump.exe
- Right-clicked each mysqldump.exe it found -> Properties -> Details tab.
- Found the version 5.6.41 (matching the MySQL version I'm running)
- Clicked back to the General tab, to see the folder path that that version of mysqldump.exe was installed in
- C:\Program Files\MySQL\MySQL Server 5.6\bin, in my case (version 8.0 was installed in C:\Program Files\MySQL\MySQL Workbench 8.0 CE)
Now open MySQL Workbench..
- Edit -> Preferences -> Administration -> Path to mysqldump Tool
- Paste in the file path for the appropriate version of mysqldump.exe (including the file name!)
- Click OK
- Restart MySQL Workbench

This worked for me. However, I have no idea how to make this work if you have two versions of MySQL on your machine, say MySQL 5.6 and 5.7, or 8.0.
So when I upgrade (any/some/all of) my cloud servers to version 5.7, this looks to be another challenge to overcome. Since Workbench only allows a single mysqldump.exe file, to be used no matter which version of my local MySQL server is running... Or perhaps manually change the path location, each time open a connection that uses the Port to the other MySQL version (and remember to change it back after?).

For this reason, I Removed MySQL 5.7 completely, and will hence keep all cloud (and my local) servers at 5.6 for the time being - a Project for another day.

score 0 · Answer 14 · answered Jun 13 '19 at 05:19

0

For Ubuntu users to save your time, mysqldump is in "mysql-community-client_5.7.26-1ubuntu16.04_i386.deb " package

answered Jun 13 '19 at 05:19

Impermanence

146
1
4

score 0 · Answer 15 · answered Jun 27 '20 at 12:56

I just had the same problem and I put a couple of different answers together.

The pro for me was, that I could switch easily between 5.6, 8, ... without having to up/downgrade ubuntu dependencies each time.

The easiest way for me on Ubuntu was to just download the 5.6 (or 5.7, whatever you want) mysqldump binary and change that in workbench. Everything went well after that:

go to https://downloads.mysql.com/archives/community/ and download the correct version you want (for me: 5.6.47 for 64x)
Unpack the archive somewhere, you can find later
Change the mysqldump binary in mysql workbench: Edit -> Preferences -> Administrator -> "Path to mysqldump Tool" to {{YOUR_UNPACKED_FOLDER}}/bin/mysqldump
Save it with Ok

Now you should be able to just export and it will use mysqldump 5.x.

score -1 · Answer 16 · edited Apr 23 '14 at 06:56

-1

I have Mysql 5.6.12 version. SET SQL_QUOTE_SHOW_CREATE=1 is working fine in my machine. now i am able to take a dump of sql.

mysql> SET SQL_QUOTE_SHOW_CREATE=1; Query OK, 0 rows affected (0.00 sec)

worked for me.

edited Apr 23 '14 at 06:56

The Heist

1,444
1
16
32

answered Apr 23 '14 at 06:36

Suhas holla

1
1

The unfortunate mysqldump versions try to execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1' , notice the extra 'OPTION' word. – MKaama Mar 01 '16 at 10:24

Dump File MySQL 5.6.10

16 Answers16

Linked