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?

Answer 1

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)

Answer 2

just upgrade your mysql client How to upgrade MySQL in Linux such as Fedora with Yum which is faster and convenient

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

1. Find your mysql original download folder, and look in the /bin folder, there should be a mysqldump.exe.
2. Go into Workbench Preferences: Edit -> Preferences -> Administrator -> Path to 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 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!