Mysqldump failing while restoring database – MySQL 5.6

MySQLmysql-5.6mysqldumpperconapercona-server

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?

Best Answer

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.

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Best Answer

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

MySQL – Differences Between mysqld_safe and mysqld

Related Question