0

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?

1

I have resolved such issues multiple times by increasing the value of max allowed packet while restoring the databases.

So something like:

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

should work.

Though there can be another reasons for this error. Still you can start debugging with above at least.

1

Apparently, the issue was with the OS variables.

I tried increasing open files parameter for mysql user from the default to 5000.

Restoration worked just fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.