MySQL 5.6 Mysqldump error : Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':

Are you getting this below error in MySQL 5.6. while taking backup:

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)


Solution:

Use the mysqldump path to resolve this issue.

#usr/local/mysql/bin/mysqldump -u root -p --all-databases > databases.sql.


Now its solved.

Create a new MySQL Server with existing MySQL data directory

Do you like to create a new server with existing MySQL data directory ?

Taking dump and import in new server will take so much time to do the process.

So better copy the data directory to new server is a good way.Below I have given the steps that how to copy the data directory and set up a new server.


1.Create a new MySQL Server.

2.Stop this newly created MySQL server.

3.Move this server data directory to some other location.now nothing should not be there in new server data directory.

4.Now copy the data directory files from the existing server it should includes ibdata,log file 0 and 1.(This server should be stop while copying the files)

5.After copied all the files, Check the owner and group name for the data directory files.If the owner and group name are in root then change it to mysql.

7.Start this new MySQL server.

8.After started check the new MySQL error logs to confirm that there is no issues.

9.Login to new MySQL server.

10.Now use the same databases and tables like existing server.

There is a tool to copy the files by taking the data directory backup with "xtrabackup" and restore it in new server. 

 

Note :

If you get this below error:

" ERROR 1146 (42S02): Table 'database.tablename' doesn't exist"

 It means that you have not copied all the files properly from the existing server.

 Now copy the all files properly and start the server and now it will works fine.