恢复数据库时Mysqldump失败- MySQL 5.6

Question

我已经转储了一个大小为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

我已经将用户mysql的文件大小设置为/etc/security/Lims.conf中的无限大小。

超时值：

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‘。

不知道从哪里开始调试。有什么想法吗？

Answer 1

通过在还原数据库时增加允许的最大数据包的值，我已经多次解决了这样的问题。

所以，就像：

mysql --max-allowed-packet =[1G or 2G] -u root -p < dump.sql   

应该行得通。

不过，可能还有其他原因造成此错误。不过，至少可以用上面的方法开始调试。

Answer 2

显然，问题在于操作系统变量。

我尝试将mysql用户的打开文件参数从默认的5000增加到5000。

修复效果很好。