Backup of MySQL database

Submitted by cyrus on Fri, 01/30/2015 - 12:37 Pro Licensee

I recently upgraded my OS to CentOS 7 from 6. My domains were successfully backed up however with one I get this error:

     Creating MySQL database <i>databasename</i> ..
    .. done

.. load failed!

ERROR 2006 (HY000) at line 816: MySQL server has gone away

Please advise

Status: 
Active

Comments

Submitted by andreychek on Fri, 01/30/2015 - 12:53

Howdy -- it sounds like MySQL stopped working during the restore.

Do you see any errors in the MySQL error log?

Also, how much RAM does your server have? You can determine that with this command:

free -m

Also, do any errors show up in your dmesg output? You can see that with this command:

dmesg | tail -30

Submitted by cyrus on Fri, 01/30/2015 - 13:05 Pro Licensee

root@host ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          1840       1629        211         25         39        671
-/+ buffers/cache:        918        921
Swap:         2047         52       1995
[root@host ~]#

and

[root@host ~]# dmesg | tail -30
[    7.117554] lpc_ich: Resource conflict(s) found affecting gpio_ich
[    7.155135] ppdev: user-space parallel port driver
[    7.239987] leds_ss4200: no LED devices found
[    7.316149] kvm: disabled by bios
[    7.322774] kvm: disabled by bios
[    7.340632] iTCO_vendor_support: vendor-support=0
[    7.363060] iTCO_wdt: Intel TCO WatchDog Timer Driver v1.10
[    7.363099] iTCO_wdt: Found a ICH7 or ICH7R TCO device (Version=2, TCOBASE=0x1060)
[    7.363187] iTCO_wdt: initialized. heartbeat=30 sec (nowayout=0)
[    7.484860] coretemp coretemp.0: Using relative temperature scale!
[    7.484871] coretemp coretemp.0: Using relative temperature scale!
[    7.855383] Adding 2097148k swap on /dev/sda3.  Priority:-1 extents:1 across:2097148k FS
[    7.883210] EXT4-fs (sda2): mounted filesystem with ordered data mode. Opts: (null)
[    8.044706] EXT4-fs (sda1): mounted filesystem with ordered data mode. Opts: (null)
[    8.255174] systemd-journald[334]: Received request to flush runtime journal from PID 1
[    8.615513] type=1305 audit(1422604819.374:3): audit_pid=431 old=0 auid=4294967295 ses=4294967295 res=1
[    9.211478] sd 3:0:0:0: Attached scsi generic sg0 type 0
[   17.189260] e1000e 0000:0d:00.0: irq 68 for MSI/MSI-X
[   17.290078] e1000e 0000:0d:00.0: irq 68 for MSI/MSI-X
[   17.290697] IPv6: ADDRCONF(NETDEV_UP): enp13s0: link is not ready
[   17.380948] e1000e 0000:0e:00.0: irq 69 for MSI/MSI-X
[   17.481101] e1000e 0000:0e:00.0: irq 69 for MSI/MSI-X
[   17.481599] IPv6: ADDRCONF(NETDEV_UP): enp14s0: link is not ready
[   18.840618] e1000e: enp13s0 NIC Link is Up 100 Mbps Full Duplex, Flow Control: None
[   18.840793] e1000e 0000:0d:00.0 enp13s0: 10/100 speed: disabling TSO
[   18.840877] IPv6: ADDRCONF(NETDEV_CHANGE): enp13s0: link becomes ready
[ 1775.542133] ip_tables: (C) 2000-2006 Netfilter Core Team
[ 3570.072148] EXT4-fs (sda5): re-mounted. Opts: grpquota,usrquota,data=ordered
[ 3584.962627] perf samples too long (2510 > 2500), lowering kernel.perf_event_max_sample_rate to 50000
[30291.091056] systemd-journald[334]: Vacuuming done, freed 0 bytes
[root@host ~]#

Can't find the MySQL error log! Where is it?

Submitted by andreychek on Fri, 01/30/2015 - 13:14

On CentOS 7, the MySQL log file (well, technically, it's MariaDB) would be located here:

/var/log/mariadb/mariadb.log

Submitted by cyrus on Fri, 01/30/2015 - 13:27 Pro Licensee

/var/log/mariadb/mariadb.log

150130 02:59:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150130  2:59:09 InnoDB: The InnoDB memory heap is disabled
150130  2:59:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150130  2:59:09 InnoDB: Compressed tables use zlib 1.2.7
150130  2:59:09 InnoDB: Using Linux native AIO
150130  2:59:09 InnoDB: Initializing buffer pool, size = 128.0M
150130  2:59:09 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
150130  2:59:09  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
150130  2:59:09  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
150130  2:59:10  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
150130  2:59:10  InnoDB: Waiting for the background threads to start
150130  2:59:11 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 0
150130  2:59:11 [Note] Plugin 'FEEDBACK' is disabled.
150130  2:59:11 [Note] Server socket created on IP: '0.0.0.0'.
150130  2:59:11 [Note] Event Scheduler: Loaded 0 events
150130  2:59:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.40-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

Submitted by andreychek on Fri, 01/30/2015 - 13:38

I don't see any errors in those messages. Is "2:59" the time that the restore was performed? If not, you may want to review the logs at the time the restore was performed.

If it is, you may just need to look up a little above that in the logs to see if any errors are showing up.

Submitted by cyrus on Fri, 01/30/2015 - 13:47 Pro Licensee

just backed up with a similar error, looked at the logs and there is no change.

Submitted by andreychek on Fri, 01/30/2015 - 13:53

Doing some Googling on that error, it sounds like that can happen when exceeding the database maximum packet size.

What you may want to try is to edit /etc/my.cnf file, and under the "[mysqld]" section, add a line like the following:

max_allowed_packet=64M

Then, restart MySQL:

service mariadb restart

After that, are you able to restore that backup?

Submitted by cyrus on Fri, 01/30/2015 - 14:05 Pro Licensee

Same error

Re-loading MySQL database promotio_e107 ..

    Creating MySQL database promotio_e107 ..
    .. done

.. load failed!
er restarting
ERROR 2006 (HY000) at line 816: MySQL server has gone away

and from the db log aftter restarting

150130 13:58:54  InnoDB: Starting shutdown...
150130 13:58:55  InnoDB: Shutdown completed; log sequence number 1952234
150130 13:58:55 [Note] /usr/libexec/mysqld: Shutdown complete

150130 13:58:55 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
150130 13:58:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150130 13:58:56 InnoDB: The InnoDB memory heap is disabled
150130 13:58:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150130 13:58:56 InnoDB: Compressed tables use zlib 1.2.7
150130 13:58:56 InnoDB: Using Linux native AIO
150130 13:58:56 InnoDB: Initializing buffer pool, size = 128.0M
150130 13:58:56 InnoDB: Completed initialization of buffer pool
150130 13:58:56 InnoDB: highest supported file format is Barracuda.
150130 13:58:56  InnoDB: Waiting for the background threads to start
150130 13:58:57 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 1952234
150130 13:58:57 [Note] Plugin 'FEEDBACK' is disabled.
150130 13:58:57 [Note] Server socket created on IP: '0.0.0.0'.
150130 13:58:58 [Note] Event Scheduler: Loaded 0 events
150130 13:58:58 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.40-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

Submitted by andreychek on Fri, 01/30/2015 - 14:54

Can you paste in the current contents of your /etc/my.cnf file?

And just to clarify, you're receiving this particular error on your new CentOS 7 server, is that correct?

Submitted by cyrus on Fri, 01/30/2015 - 15:29 Pro Licensee

yes, receiving this error on trying to restore this domain's tar.gz file that includes the database.

mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table = 1





[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

max_allowed_packet=64M


!includedir /etc/my.cnf.d

Submitted by andreychek on Fri, 01/30/2015 - 15:34

Ah, that "max_allowed_packet=64M" line should be in the "[mysqld]" section, and not the "[mysqld_safe]" section.

Try moving that into the mysqld section, and then restart MySQL.

Submitted by cyrus on Fri, 01/30/2015 - 15:44 Pro Licensee

Thank you. That did it.

It's a site of mine with over 2500 articles since 2004 or so :) Thanks again