Mysql
恢復數據庫時 MySQLdump 失敗 - MySQL 5.6
我已經轉儲了一個大小為 5-6GB 的數據庫。我正在嘗試恢復到另一個實例，但一段時間後同樣失敗，並出現以下錯誤：
第 12006 行的錯誤 2013 (HY000)：查詢期間失去與 MySQL 伺服器的連接
版本 - Percona 5.6.30
/etc/my.cnf 內容
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 log-bin-trust-function-creators=1 max_allowed_packet=500M log=/var/lib/mysql/mysql.log #innodb_buffer_pool_size=3G innodb_fast_shutdown=0 innodb_use_native_aio = 0 innodb_flush_method=O_DIRECT [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid port = 3306我在 /etc/security/limits.conf 中將使用者 mysql 的文件大小設置為無限制。
超時值：
mysql> show global variables like '%timeout'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | thread_pool_idle_timeout | 60 | | wait_timeout | 28800 | +-----------------------------+----------+ 14 rows in set (0.00 sec)最大允許數據包：
mysql> show global variables like 'max_allowed_packet'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | max_allowed_packet | 536870912 | +--------------------+-----------+/var/log/mysql.log 沒有顯示任何類型的錯誤：
Version: '5.6.31-77.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 77.0, Revision 5c1061c 160726 17:46:58 mysqld_safe Number of processes running now: 0 160726 17:46:58 mysqld_safe mysqld restarted 2016-07-26 17:46:58 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-07-26 17:46:58 0 [Note] /usr/sbin/mysqld (mysqld 5.6.31-77.0) starting as process 53230 ... 2016-07-26 17:46:58 53230 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) 2016-07-26 17:46:58 53230 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000) 2016-07-26 17:46:58 53230 [Note] Plugin 'FEDERATED' is disabled. 2016-07-26 17:46:58 53230 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-07-26 17:46:58 53230 [Note] InnoDB: The InnoDB memory heap is disabled 2016-07-26 17:46:58 53230 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-07-26 17:46:58 53230 [Note] InnoDB: Memory barrier is not used 2016-07-26 17:46:58 53230 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-07-26 17:46:58 53230 [Note] InnoDB: Using Linux native AIO 2016-07-26 17:46:58 53230 [Note] InnoDB: Using CPU crc32 instructions 2016-07-26 17:46:58 53230 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-07-26 17:46:58 53230 [Note] InnoDB: Completed initialization of buffer pool 2016-07-26 17:46:58 53230 [Note] InnoDB: Highest supported file format is Barracuda. 2016-07-26 17:46:58 53230 [Note] InnoDB: Log scan progressed past the checkpoint lsn 42175779646 2016-07-26 17:46:58 53230 [Note] InnoDB: Database was not shutdown normally! 2016-07-26 17:46:58 53230 [Note] InnoDB: Starting crash recovery. 2016-07-26 17:46:58 53230 [Note] InnoDB: Reading tablespace information from the .ibd files... 2016-07-26 17:46:58 53230 [Note] InnoDB: Restoring possible half-written data pages 2016-07-26 17:46:58 53230 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 42181022208 InnoDB: Doing recovery: scanned up to log sequence number 42186265088 InnoDB: Doing recovery: scanned up to log sequence number 42191507968 InnoDB: Doing recovery: scanned up to log sequence number 42196750848 InnoDB: Doing recovery: scanned up to log sequence number 42201993728 InnoDB: Doing recovery: scanned up to log sequence number 42207236608 InnoDB: Doing recovery: scanned up to log sequence number 42212479488 InnoDB: Doing recovery: scanned up to log sequence number 42217722368 InnoDB: Doing recovery: scanned up to log sequence number 42222965248 InnoDB: Doing recovery: scanned up to log sequence number 42228208128 InnoDB: Doing recovery: scanned up to log sequence number 42228248041 2016-07-26 17:46:59 53230 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 2016-07-26 17:47:01 53230 [Note] InnoDB: 128 rollback segment(s) are active. 2016-07-26 17:47:01 53230 [Note] InnoDB: Waiting for purge to start 2016-07-26 17:47:01 53230 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.31-77.0 started; log sequence number 42228248041 2016-07-26 17:47:01 53230 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work. 2016-07-26 17:47:01 53230 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work. 2016-07-26 17:47:01 53230 [Note] Server hostname (bind-address): '*'; port: 3306 2016-07-26 17:47:01 53230 [Note] IPv6 is available. 2016-07-26 17:47:01 53230 [Note] - '::' resolves to '::'; 2016-07-26 17:47:01 53230 [Note] Server socket created on IP: '::'. 2016-07-26 17:47:01 53230 [Note] Event Scheduler: Loaded 0 events 2016-07-26 17:47:01 53230 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.31-77.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 77.0, Revision 5c1061c我也在所有數據庫上執行了 mysqlcheck，它為所有表返回了“OK”。
不知道從哪裡開始調試。有任何想法嗎？
通過在恢復數據庫時增加最大允許數據包的值，我已經多次解決了此類問題。
所以像：
mysql --max-allowed-packet =[1G or 2G] -u root -p < dump.sql應該管用。
儘管此錯誤可能還有其他原因。你仍然可以至少從上面開始調試。
顯然，問題出在作業系統變數上。
我嘗試將 mysql 使用者的打開文件參數從預設值增加到 5000。
恢復工作得很好。