溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

【MySQL】mysqldump備份失敗與解決方案合集

發(fā)布時(shí)間：2020-08-10 23:27:29 來源：ITPUB博客 閱讀：704 作者：wg0411 欄目：MySQL數(shù)據(jù)庫
〇 mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces
〇 mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$tb_name` at row: xxxx

版本：
MySQL 5.7.8+
原因：
max_execution_time過小
處理思路：
① 通過hints，增大N值（文檔說，在hints用法中，將N改為0為無限制，但我測(cè)下來不生效，可設(shè)置成一個(gè)較大值如999999解決）
SELECT /*+ MAX_EXECUTION_TIME(N) */ * FROM t1 LIMIT 100000;
② 修改max_execution_time值，將該值設(shè)置為較大一個(gè)值，或設(shè)置為0（不限制）
附錄：
該參數(shù)5.7.8被添加，單位為ms，動(dòng)態(tài)參數(shù)，默認(rèn)為0，設(shè)置為0時(shí)意味著SELECT超時(shí)不被設(shè)置（不限制超時(shí)時(shí)間）。不作用于存儲(chǔ)過程中的SELECT語句，并且只作用于只讀的SELECT，如INSERT ... SELECT ... 是不被作用的。
for more information:
http://blog.itpub.net/29773961/viewspace-2150443/


〇 mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: View $db_name.$view_name references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

原因：
該view引用了無效的表，列，函數(shù)或者定義者。
處理思路：
可以根據(jù)報(bào)錯(cuò)信息，進(jìn)入db，執(zhí)行SHOW CREATE VIEW $view_name\G，查看該view的定義，逐一檢查該view的基表，列，或相關(guān)函數(shù)與用戶是否具有相關(guān)權(quán)限?？紤]重建或刪除視圖。


〇 mysqldump: Couldnt execute show create table `$view_name`: Illegal mix of collations for operation UNION (1271)

原因：
創(chuàng)建view時(shí)，使用UNION時(shí)存在非法的排序規(guī)則組合。
處理思路：
檢查該視圖定義，檢查字符集，考慮重建或刪除視圖。


〇 mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: The user specified as a definer ($user@$host) does not exist (1449)
〇 mysqldump: Couldnt execute show table status like $view_name: SELECT command denied to user @% for column $col_name in table $tb_name (1143)

原因：
該視圖的定義者$user@$host不存在。
處理思路：
檢查mysql.user表，確認(rèn)用戶是否存在，考慮重建或刪除視圖。


〇 Error: Couldnt read status information for table Income_config ()mysqldump: Couldnt execute show create table `Tser_table`: Table $db_name.test_table doesnt exist (1146)
〇 mysqldump: Got error: 1049: Unknown database $db_name when selecting the database

原因一：
從lower_case_table_names的0設(shè)置成1，導(dǎo)致部分原來含有大寫字母的庫表“找不到”。
處理思路：
將lower_case_table_names設(shè)置回0。
若有必須將lower_case_table_names設(shè)置為1，需先設(shè)置為0，并將含有大寫字母的庫表改成小寫，再設(shè)置為1。

原因二（MySQL 5.5及以下版本可能出現(xiàn)）：
表損壞導(dǎo)致該表找不到（InnoDB）。frm和ibd文件都在，但無法SHOW CREATE TABLE xxx\G
error log一則：
  1. 170820 17:43:17 [Note] Event Scheduler: scheduler thread started with id 1
  2. 170820 17:44:48 InnoDB: error: space object of table '$db_name/$tb_name',
  3. InnoDB: space id 4335 did not exist in memory. Retrying an open.
  4. 170820 17:44:48 InnoDB: Error: tablespace id and flags in file './$db_name/$tb_name.ibd' are 0 and 0, but in the InnoDB
  5. InnoDB: data dictionary they are 4335 and 0.
  6. InnoDB: Have you moved InnoDB .ibd files around without using the
  7. InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
  8. InnoDB: Please refer to
  9. InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
  10. InnoDB: for how to resolve the issue.
  11. 170820 17:44:48 InnoDB: cannot calculate statistics for table $db_name/$tb_name
  12. InnoDB: because the .ibd file is missing. For help, please refer to
  13. InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
  14. 170820 17:44:48 [ERROR] MySQL is trying to open a table handle but the .ibd file for
  15. table $db_name/$tb_name does not exist.
  16. Have you deleted the .ibd file from the database directory under
  17. the MySQL datadir, or have you used DISCARD TABLESPACE?
  18. See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
  19. how you can resolve the problem.
處理思路：
從完整備份+binlog還原，對(duì)于有主或從的實(shí)例，可通過物理備份還原。


〇 mysqldump: Error 2020: Got packet bigger than max_allowed_packet bytes when dumping table `$tb_name` at row: xxxx

原因：
默認(rèn)的max_allowed_packet過小
處理思路：
在mysqldump時(shí)增加max_allowed_packet的大小，如mysqldump --max-allowed-packet=268435456


〇 mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `$tb_name` at row: 0

原因：
在備份該表時(shí)，表定義被修改。FLUSH TABLE WITH READ LOCK只保證數(shù)據(jù)一致性，并不保證schema不被修改。
處理思路：
備份時(shí)期不做DDL操作。
復(fù)現(xiàn)一：
  1. ① session1> CREATE TABLE a (id int) ENGINE=InnoDB;
  2. ② session2> START TRANSACTION WITH CONSISTENT SNAPSHOT;
  3. ③ session1> ALTER TABLE a ADD COLUMN name varchar(32);
  4. ④ session2> SELECT * FROM a;
  5. ERROR 1412 (HY000): Table definition has changed, please retry transaction
p.s. 如果③和④調(diào)換順序，則ALTER TABLE無法成功，則會(huì)等待MDL。
復(fù)現(xiàn)二：
  1. ① session1> START TRANSACTION WITH CONSISTENT SNAPSHOT;
  2. ② session2> CREATE TABLE b (id int) ENGINE=InnoDB;
  3. ③ session1> SELECT * FROM b;
  4. ERROR 1412 (HY000): Table definition has changed, please retry transaction


〇 mysqldump: Couldnt execute show create table `$tb_name`: Unable to open underlying table which is differently defined or of non-MyISAM type or doesnt exist (1168)

原因：
出現(xiàn)在表引擎為MERGE時(shí)，備份到該表時(shí)，發(fā)現(xiàn)該表定義存在問題?？赡躮erge的表不存在，或者該表合并的基表包含非MyISAM引擎的表。
處理思路：
刪除或者重建該MERGE表。
復(fù)現(xiàn)一（merge表中定義包含了非MyISAM表）：
  1. CREATE TABLE t1(id int) ENGINE=InnoDB;
  2. CREATE TABLE t2(id int) ENGINE=MyISAM;
  3. CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2);
  4. SELECT * FROM merge_t;
  5. ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
復(fù)現(xiàn)二（表不存在）：
  1. CREATE TABLE t1(id int) ENGINE=MyISAM;
  2. CREATE TABLE t2(id int) ENGINE=MyISAM;
  3. CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2);
  4. SELECT * FROM merge_t;
  5. Empty set (0.00 sec)    -- 正常返回
  6. DROP TABLE t1;
  7. SELECT * FROM merge_t;
  8. ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
附錄：
通過check table merge_t可以檢查是哪張表有問題，如此處是t1：
  1. [15:20:12] root@localhost [test]> check table merge_t\G
  2. *************************** 1. row ***************************
  3.   Table: test.merge_t
  4.      Op: check
  5. Msg_type: Error
  6. Msg_text: Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist
  7. *************************** 2. row ***************************
  8.   Table: test.merge_t
  9.      Op: check
  10. Msg_type: Error
  11. Msg_text: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
  12. *************************** 3. row ***************************
  13.   Table: test.merge_t
  14.      Op: check
  15. Msg_type: error
  16. Msg_text: Corrupt
  17. 3 rows in set (0.00 sec)
通過cat表MGR定義結(jié)構(gòu)文件可以檢查MERGE表的基表：
  1. [root@host test]# pwd
  2. /data/mysql-data/mysql57/data/test
  3. [root@host test]# cat merge_t.MRG
  4. t1
  5. t2


〇 mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and last (automatic?) repair failed (144)
〇 mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and should be repaired (145)
〇 mysqldump: Error 1194: Table throne_tower is marked as crashed and should be repaired when dumping table `$tb_name` at row: xxxxx

原因：
mysqldump在拉取表定義時(shí)報(bào)錯(cuò)，表損壞。
處理思路：
該損壞發(fā)生在非事務(wù)表如MyISAM，通過mysqlcheck或者repair table修復(fù)即可。

〇 mysqldump: Couldnt execute SHOW FUNCTION STATUS WHERE Db = $db_name: Cannot load from mysql.$tb_name. The table is probably corrupted (1728)

原因：
字典表不正確，可能是表本身損壞，也有可能是導(dǎo)入了其他版本的mysql schema蓋掉了字典表。
處理思路：
repair table修復(fù)，若仍無用，則可以嘗試mysql_upgrade來修復(fù)，或找到對(duì)應(yīng)版本的mysql_system_tables_fix.sql來導(dǎo)入。

〇 mysqldump: Couldnt execute show events: Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

原因：
字典表不正確，極大可能是導(dǎo)入了其他版本的mysql schema蓋掉了字典表。
處理思路：
嘗試mysql_upgrade來修復(fù)，或找到對(duì)應(yīng)版本的mysql_system_tables_fix.sql來導(dǎo)入。該報(bào)錯(cuò)可能在upgrade操作之后重啟實(shí)例。


〇 mysqldump: Error: Got error 28 from storage engine when trying to dump tablespaces
mysqldump: Couldnt execute show fields from `$tb_name`: Got error 28 from storage engine (1030)

原因：
@@tmpdir滿了。
處理思路：
清除@@tmpdir，可以通過SELECT @@tmpdir;檢查具體目錄。


〇 mysqldump: Lost connection to MySQL server during query (2013)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '@@socket' (111)

原因：
mysqldump執(zhí)行過程中mysqld被關(guān)閉。
處理思路：
檢查mysqld被關(guān)閉的原因，一般常見原因是發(fā)生OOM。


〇 mysqldump: Couldn't execute 'SHOW SLAVE STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227)

原因：
mysqldump加了--dump-slave參數(shù)，缺少SUPER或REPLICATION CLIENT來執(zhí)行SHOW SLAVE STATUS。
處理思路：
檢查mysqldump的用戶權(quán)限。


〇 mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user 'dump'@'localhost' (using password: YES) (1045)

原因：
mysqldump加了--dump-slave參數(shù)，缺少SUPER權(quán)限使用STOP SLAVE SQL_THREAD。
處理思路：
檢查mysqldump的用戶權(quán)限。




向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手