Import MySQL 5.6 database into 5.5 using MySQL 5.6 client tools

Question

My local dev environment currently uses MySQL Server 5.5. However, one of my clients hosts decided to upgrade their server to 5.6. I sync my clients databases remotely using mysqldump over ssh. I have other clients using MySQL server 5.5.

The databases I am syncing don't use any specific 5.6 features (all WordPress which is fully compatible with 5.5). In order to work with both of my clientgroups on 5.5 and 5.6, is it possible to upgrade my local dev environment to MySQL 5.6 and then sync 5.5 databases using the 5.6 client tools?

FYI: It is not possible to sync a 5.6 database using my 5.5 client tools as I receive the following error when trying to do so:

 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) (Wordmove::ShellCommandError)

Some additional information for clarification. I am aware that there are some table scheme features been introduced in 5.6 that are not compatible with 5.5. However, are these features automatically applied to 5.5 tables on upgrade, or is a 5.5 database imported into a 5.6 database basically still fully compatible with 5.5, until the new features from 5.6 are actively used on it.

Solution

This is an error due to a change in MySQL Server 5.6 : unsupport keyword SET OPTION which is used in mysqldump on MySQL 5.5.

You can see this ticket for more information : https://bugs.mysql.com/bug.php?id=67507

To fix this problem, I think you can do one of these solutions :

1. Upgrade mysqlclient ( mysqldump) to 5.6
2. A Manual way : Edit file mysqldump on the Client ( using Mysql 5.5)

cp /usr/bin/mysqldump /root/mysqldump

vim -b /root/mysqldump

Looking for SET OPTION ( use the '/' command)

Comment( or delete) this commands/ lines

Save the file

use /root/mysqdump instead

Hope this works for you

Comments

[yagmoth555]

Can you tell/assist your customer to go back at 5.5 ?

[Daniel Klose]

Hi Summer, Thanks for your answer. This is exactly why I asked this question :) However in order to answer my question I need to know if there are problems arising when using 5.6 mysqldump on 5.5 databases and if i can use a 5.6 mysqldumped database and import it back into a 5.5 db if the app that creates the db is 5.5 compatible.

[Summer Nguyen]

as my understand, there will be no further error. You can check the default variables sql_quote_show_create description on both MySQL 5.5 and 5.6 ( dev.mysql.com/doc/refman/5.5/en/… and dev.mysql.com/doc/refman/5.6/en/… ) . The default value is 1 which is safely set on mysqldump . Further more, this options does not affect the real data exported.

[Daniel Klose]

Good news! I updated my local dev environement to 5.6 and have been able to successfully sync from 5.6 to 5.5 databases! Thanks for your help!