mysqldump: Got error: 1168 differently defined non-MyISAM LOCK TABLES

starive發表於2014-10-15
MySqlGoError


mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES




備份其他的資料庫可以成功。但是備份下面這個資料庫starive就失敗了。

備份資料庫starive:
sudo mysqldump -uroot -p密碼 -l starive > /home/***/mysql/backup/starive.sql

出現瞭如下錯誤:
mysqldump: Got error: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist when using LOCK TABLES

檢視錯誤日誌 /usr/local/mysql/data/***-VirtualBox.err, 最新的內容如下：
141015 11:15:34 [Note] Plugin 'FEDERATED' is disabled.
141015 11:15:34 InnoDB: The InnoDB memory heap is disabled
141015 11:15:34 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
141015 11:15:34 InnoDB: Compressed tables use zlib 1.2.3
141015 11:15:34 InnoDB: Using Linux native AIO
141015 11:15:34 InnoDB: Initializing buffer pool, size = 128.0M
141015 11:15:35 InnoDB: Completed initialization of buffer pool
141015 11:15:35 InnoDB: highest supported file format is Barracuda.
141015 11:15:36  InnoDB: Waiting for the background threads to start
141015 11:15:37 InnoDB: 5.5.39 started; log sequence number 44666057
141015 11:15:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141015 11:15:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
141015 11:15:37 [Note] Server socket created on IP: '0.0.0.0'.
141015 11:15:37 [Note] Event Scheduler: Loaded 1 event
141015 11:15:37 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.39-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Se
rver (GPL)


解決方法及其過程：

把原來的starive2.sql 刪除掉，然後再來備份：

sudo mysqldump -uroot -p*** starive2 > /home/*****/mysql/backup/starive2.sql
成功了。

 

 

重新啟動Ubuntu：

參考書籍《深入淺出MySQL 資料庫開發、優化與管理維護（第2版）》 P 438: 加入 --single-transaction       ----》加這個引數後立馬找到原因了

*****@*****-VirtualBox:/usr/local/mysql/bin$ sudo mysqldump -uroot -pgao --single-transaction starive > /home/*****/mysql/backup/starive.sql

[sudo] password for *****: 

mysqldump: Couldn't execute 'show create table `payment_all`': Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist (1168)

 

去資料庫starive查詢表：payment_all, 果然沒有這個表。

那就把這個表刪除掉：

  1. drop table payment_all;


*****@*****-VirtualBox:/usr/local/mysql/bin$ sudo mysqldump -uroot -p*** --single-transaction starive > /home/*****/mysql/backup/starive.sql*****@*****-VirtualBox:/usr/local/mysql/bin$ 

成功了！！！

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26435490/viewspace-1299449/，如需轉載，請註明出處，否則將追究法律責任。

相關文章