I have dumped one of my databases of size 5-6GB. I'm trying to restore to another instance but the same is getting failed after sometime with the following error:
ERROR 2013 (HY000) at line 12006: Lost connection to MySQL server during query
Version - Percona 5.6.30
/etc/my.cnf contents
[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
I have set the file size for the user mysql as unlimited in /etc/security/limits.conf.
Timeout values:
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)
Max Allowed Packet:
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 536870912 |
+--------------------+-----------+
/var/log/mysql.log does not show any sort of errors:
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
I have run mysqlcheck on all the databases as well which returned 'OK' for all the tables.
Not sure where to start debugging. Any ideas?
