MySQL: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1
執行mysqldump報錯
[root@t91~]# mysqldump -d --socket=/usr/local/mysql/mysql.sock orcl_db >orcl_db.sql
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)
看到報錯資訊,有說MySQL server version for the right syntax to user ....,猜測可能是工具的版本問題導致的,所以需要check 檢視mysqldump版本和路徑
[root@t91~]# mysqldump --version
mysqldump Ver 10.13 Distrib 5.1.66, for redhat-linux-gnu (x86_64)
[root@t91~]#
[root@t91~]# type mysqldump
mysqldump is hashed (/usr/bin/mysqldump)
[root@t91~]#
再檢視mysql版本是5.6.12
[root@t91~]# mysql --version
Warning: Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using EditLine wrapper
[root@t91~]#
[root@t91~]# type mysql
mysql is aliased to `/usr/local/mysql/bin/mysql -uroot --password= -S /usr/local/mysql/mysql.sock'
[root@t91~]#
看到問題所在了,mysqldump的版本和資料庫的版本不一致,/usr/bin下面的是5.1.66,/usr/local/mysql/bin/下面的是5.6.12的版本。mysql能連線到5.6.12版本的是因為做了alias對映的關係,mysqldump沒有做對映,那為啥會出現這個問題呢?看環境變數裡面的配置
[root@t91~]# more /etc/profile |grep mysql
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@t91~]#
看到了有載入/usr/bin; 和 /usr/local/mysql/bin; /usr/bin;在/usr/local/mysql/bin;的前面進行載入,所以生效的是/usr/bin下面的mysqldump。
所以解決問題的辦法有如下幾種: (1)將/usr/bin;放在/usr/local/mysql/bin;後面,重新登陸 (2)刪除/usr/bin下面的老版本的MySQL,執行命令rm -rf /usr/bin/mysql*
這裡採取的是第一種辦法,因為不知道這個老的mysql版本是否會被別的應用使用,暫時為了快速解決問題,自動載入新版本/usr/local/mysql/bin
[root@t91~]# more /etc/profile |grep mysql
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/root/bin:/usr/local/mysql/bin:/usr/bin
[root@t91~]#
修改完配置後,重新登陸,檢視配置,再檢視mysqldump的路徑,然後執行mysqldump操作,一切OK,如下所示:
[root@t91~]# more /etc/profile |grep mysql
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/root/bin:/usr/local/mysql/bin:/usr/bin
[root@t91~]#
[root@t91~]# type mysqldump
mysqldump is /usr/local/mysql/bin/mysqldump
[root@t91~]# mysqldump -d --socket=/usr/local/mysql/mysql.sock orcl_db >orcl_db.sql
[root@t91~]#
————————————————
About Me
........................................................................................................................ ● 本文作者:小麥苗,部分內容整理自網路,若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號( xiaomaimiaolhr)上有同步更新 ● 本文itpub地址: http://blog.itpub.net/26736162 ● 本文部落格園地址: http://www.cnblogs.com/lhrbest ● 本文CSDN地址: https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址: http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答: http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址: ........................................................................................................................ ● QQ群號: 230161599 、618766405 ● 微 信群:可加我微 信,我拉大家進群,非誠勿擾 ● 聯絡我請加QQ好友 ( 646634621 ),註明新增緣由 ● 於 2019-12-01 06:00 ~ 2019-12-31 24:00 在西安完成 ● 最新修改時間:2019-12-01 06:00 ~ 2019-12-31 24:00 ● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解 ● 版權所有,歡迎分享本文,轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店: ● 小麥苗出版的資料庫類叢書: http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班: http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁: https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號( xiaomaimiaolhr)及QQ群(DBA寶典)、新增小麥苗微 信, 學習最實用的資料庫技術。
........................................................................................................................ |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2668405/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- couldn't set locale correctly報錯解決
- Starting MySQL * Couldn't find MySQL server (/usr/bin/mysqld_safe)MySqlServer
- Mysql 報The MySQL server is running with the --secure-file-priv option so it cannot execute this stat...MySqlServer
- vncserver: couldn't find "xauth" on your PATHVNCServer
- mysql遇到Variable can’t be set to the value of ‘NULL’MySqlNull
- vncserver: couldn't find "xauth" on your PATH.VNCServer
- mysql:Variable 'character_set_client' can't be set to the value of 'NULL'解決MySqlclientNull
- Couldn't be opened because you don't have permission to view it 執行時報錯View
- [mysql] 批量匯入提示: Variable ‘character_set_client’ can’t be set to the value of ‘NULLMySqlclientNull
- Flutter雜症(couldn't find "libflutter.so")Flutter
- 有用過grpc的嗎?Couldn't find descriptorRPC
- Android Studio:couldn't find .so檔案Android
- SharePoint Excel Service - Couldn't Open the Workbook.Excel
- 【iOS報錯】“The operation couldn’t be completed (LaunchServicesError erroiOSError
- 【vnc】vncserver: couldn't find "xauth" on your PATH 問題VNCServer
- iOS:The operation couldn’t be completed. (DVTCoreSimulatorAdditionsErrorDomain eiOSErrorAI
- Linux Couldn’t resolve host 解決辦法Linux
- Mysql:Variable 'XXXt' can't be set to the value of 'NULL'解決MySqlNull
- Understanding How to Set the SQL Server I/O Affinity OptionSQLServer
- MySQL:MGR 學習(1):寫集合(Write set)MySql
- mysql匯入報錯Variable 'sql_notes' can't be set to the value of 'NULL'MySqlNull
- scala中的option[T]、Any、Nothing、Null和NilNull
- Android 4.2.2_r1 online-gcm-ref及couldn't locate source file編譯錯誤解決AndroidGC編譯
- Variable 'character_set_client' can't be set to the value of Null的解決方法clientNull
- AT&T has not set cheap wow gold an end dateGo
- _get_usbfs_fd libusb couldn‘t open the usb 許可權不夠
- Error: couldn't connect to server 127.0.0.1:27017 src/mongo/shell/mongo.js:91ErrorServer127.0.0.1GoJS
- Oracle 12c In-Memory Option - 1Oracle
- 使用wireshark出現Couldn't run /usr/bin/dumpcap in child process: Permission denied解決辦法PCA
- MySQL 5.7 PREPARE、EXECUTE、DEALLOCATE語句介紹MySql
- 【已解決】報錯 NVIDIA-SMI has failed because it couldn‘t communicate with the NVIDIA driverAI
- Flutter: 解決執行時錯誤 java.lang.UnsatisfiedLinkError...couldn't find "libflutter.so"FlutterJavaError
- MongoDB 備份恢復啟動後執行操作報錯:Error:couldn't add user:not masterMongoDBErrorAST
- kettle3使用mysql5.6出現OPTION SQL_SELECT_LIMIT=1的問題MySqlMIT
- DtypeWarning: Columns () have mixed types. Specify dtype option on import or set low_memory=False.ImportFalse
- ORA-01720: grant option does not exist for 'HWCUST.H_OKC_REGION_TERRITORY_T'
- 'mysql.column_stats' doesn't exist and Table 'mysql.index_stats' doesn't existMySqlIndex
- MySQL 預處理語句prepare、execute、deallocate的使用MySql