View All Products
Compute
Storage
Databases
Networking
Developer Tools
Delivery
Security
Services
Industries
Pricing
Community
Engage With Us
Community ▶ Questions ▶ Wordpress "error establishing a database connection" - Backup restore doesn´t fix
Log in to Ask a Question

Wordpress "error establishing a database connection" - Backup restore doesn´t fix

general

Hello, i´m having a problem and i would like to know if anyone can help me.

Three days ago my site went dead. I restored a backup from the day before and i got the message "error establishing a database connection".

I tried to restore a backup from the week before, i got the same error message.

I then restored a backup from a snapshot i took 2 months ago, same message!

Isn´t that strange?

  • I upgraded KVM and it´s shown as idle (don´t know if this is correct)

** I tried to do a "sudo apt-get upgrade" and i get the mysql error message bellow:

dpkg: error processing mysql-server-               5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.5; however:
  Package mysql-server-5.5 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.5
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

*** Why my old backups don´t fix the problem? The site went down just three days ago.

**** My setup is a LAMP - Ubuntu 12.04 LTS

26 Replies

Do you have multiple servers, or a single one? Is your database stored locally, or elsewhere?

Just one server and the database is stored locally.

I think you didn´t read my explanation, my site was working fine, it just stopped suddenly. But what i think is weird is that all my backups are not working.

Any help?

That last message was spam, there's been a lot of that lately.

Sounds like your database is corrupt in the backups which isn't unusual since the backups take file snapshots so the database can be mid-write while it's backing up. You should run mysqldump or something similar before a backup runs and restore that if this happens.

If you don't have a dump file to restore, check the mysql log file which most likely is either in /var/log, /var/log/mysql, /var/lib/mysql or the entries are in /var/log/syslog or /var/log/messages

The log should say exactly what's wrong, post the contents here.

Thanks obs for any help.

I try to do a mysqldump but i´m getting thir error message:

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

I figured out i don´t have a mysqld.sock in the /run/mysqld/ directory, is this normal?

Take my advice with a grain of salt- I'm a meatball Linux guy, and have broken more than my fair share, but because of the dpkg error, I would try :

sudo dpkg –configure -a

or

sudo apt-get -f install.

Hi Chris, thank you for your help, i tried sudo dpkg –configure -a

I got this:

Setting up mysql-server-5.5 (5.5.43-0ubuntu0.12.04.1) ...
start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing mysql-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.5; however:
  Package mysql-server-5.5 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.5
 mysql-server

Check my comment about log files, the lack of a mysql.sock just means mysql isn't running you need to find out why it's not running and the logs will tell you that.

Do you think these logs can give me a clue?

/var/log/mysql/error.log

150720 11:37:51 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150720 11:37:51 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
150720 11:37:51 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
150720 11:37:51 InnoDB: The InnoDB memory heap is disabled
150720 11:37:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150720 11:37:51 InnoDB: Compressed tables use zlib 1.2.3.4
150720 11:37:51 InnoDB: Initializing buffer pool, size = 128.0M
150720 11:37:51 InnoDB: Completed initialization of buffer pool
150720 11:37:51 InnoDB: highest supported file format is Barracuda.
150720 11:37:51  InnoDB: Waiting for the background threads to start
150720 11:37:52 InnoDB: 5.5.43 started; log sequence number 4919996271
150720 11:37:52 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150720 11:37:52 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150720 11:37:52 [Note] Server socket created on IP: '127.0.0.1'.
150720 11:37:52 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

/var/log/syslog

...
Jul 20 11:37:51 myname postfix/master[3281]: daemon started -- version 2.9.6, configuration /etc/postfix
Jul 20 11:37:51 myname ntpd[3310]: ntpd 4.2.6p3@1.2290-o Mon Apr 13 13:41:45 UTC 2015 (1)
Jul 20 11:37:51 myname ntpd[3311]: proto: precision = 0.347 usec
Jul 20 11:37:51 myname ntpd[3311]: ntp_io: estimated max descriptors: 1024, initial socket boundary: 16
Jul 20 11:37:51 myname ntpd[3311]: unable to bind to wildcard address 0.0.0.0 - another process may be running - EXITING
Jul 20 11:37:52 myname kernel: init: mysql main process (3131) terminated with status 1
Jul 20 11:37:52 myname kernel: init: mysql main process ended, respawning
Jul 20 11:37:53 myname kernel: init: mysql post-start process (3132) terminated with status 1
Jul 20 11:37:54 myname kernel: init: mysql main process (3447) terminated with status 1
Jul 20 11:37:54 myname kernel: init: mysql main process ended, respawning
Jul 20 11:37:55 myname kernel: init: mysql post-start process (3448) terminated with status 1
Jul 20 11:37:56 myname kernel: init: mysql main process (3512) terminated with status 1
Jul 20 11:37:56 myname kernel: init: mysql main process ended, respawning
Jul 20 11:37:57 myname kernel: init: mysql post-start process (3513) terminated with status 1
Jul 20 11:37:58 myname kernel: init: mysql main process (3575) terminated with status 1
Jul 20 11:37:58 myname kernel: init: mysql respawning too fast, stopped
Jul 20 11:38:02 myname ntpdate[3038]: adjust time server 129.6.15.29 offset 0.145989 sec
Jul 20 11:38:02 myname ntpd[3646]: ntpd 4.2.6p3@1.2290-o Mon Apr 13 13:41:45 UTC 2015 (1)
Jul 20 11:38:02 myname ntpd[3647]: proto: precision = 0.344 usec
Jul 20 11:38:02 myname ntpd[3647]: ntp_io: estimated max descriptors: 1024, initial socket boundary: 16
Jul 20 11:38:02 myname ntpd[3647]: Listen and drop on 0 v4wildcard 0.0.0.0 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen and drop on 1 v6wildcard :: UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen normally on 2 lo 127.0.0.1 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen normally on 3 eth0 198.74.52.6 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen normally on 4 lo ::1 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen normally on 5 eth0 fe80::f03c:91ff:fe6e:860 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: Listen normally on 6 eth0 2600:3c02::f03c:91ff:fe6e:860 UDP 123
Jul 20 11:38:02 myname ntpd[3647]: peers refreshed
Jul 20 11:38:02 myname ntpd[3647]: Listening on routing socket on fd #23 for interface updates
Jul 20 11:39:01 myname CRON[3652]: (root) CMD (  [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) ! -execdir fuser -s {} 2>/dev/null \; -delete)
Jul 20 11:42:27 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=162.248.10.134 DST=198.74.52.6 LEN=44 TOS=0x00 PREC=0x00 TTL=48 ID=63161 PROTO=TCP SPT=54420 DPT=22 WINDOW=4096 RES=0x00 SYN URGP=0 
Jul 20 11:42:27 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=162.248.10.134 DST=198.74.52.6 LEN=44 TOS=0x00 PREC=0x00 TTL=49 ID=3843 PROTO=TCP SPT=54421 DPT=22 WINDOW=1024 RES=0x00 SYN URGP=0 
Jul 20 11:45:01 myname CRON[3929]: (root) CMD (command -v debian-sa1 > /dev/null && debian-sa1 1 1)
Jul 20 11:45:30 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=186.227.209.122 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=120 ID=33346 DF PROTO=TCP SPT=2939 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 20 11:45:33 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=186.227.209.122 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=120 ID=33669 DF PROTO=TCP SPT=2939 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 20 11:49:05 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:41:08:00 SRC=162.252.86.85 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=56 ID=42322 PROTO=UDP SPT=5043 DPT=33434 LEN=28 
Jul 20 11:49:05 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=68.233.224.4 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=59 ID=42323 PROTO=UDP SPT=16292 DPT=33434 LEN=28 
Jul 20 11:49:05 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:41:08:00 SRC=104.156.63.21 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=55 ID=42324 PROTO=UDP SPT=51234 DPT=33434 LEN=28 
Jul 20 11:49:05 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=162.252.86.83 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=57 ID=42328 PROTO=UDP SPT=7911 DPT=33434 LEN=28 
Jul 20 11:49:05 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:41:08:00 SRC=104.156.63.21 DST=198.74.52.6 LEN=40 TOS=0x00 PREC=0x00 TTL=55 ID=42327 PROTO=TCP SPT=51249 DPT=33434 WINDOW=5840 RES=0x00 SYN URGP=0 
Jul 20 11:50:17 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=217.116.128.15 DST=198.74.52.6 LEN=40 TOS=0x00 PREC=0x00 TTL=119 ID=256 DF PROTO=TCP SPT=12200 DPT=5900 WINDOW=8192 RES=0x00 SYN URGP=0 
Jul 20 11:51:41 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:41:08:00 SRC=45.35.26.34 DST=198.74.52.6 LEN=40 TOS=0x00 PREC=0x00 TTL=109 ID=256 PROTO=TCP SPT=6000 DPT=3306 WINDOW=16384 RES=0x00 SYN URGP=0 
Jul 20 11:51:56 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=128.71.123.78 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=119 ID=36060 DF PROTO=TCP SPT=3930 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 20 11:51:59 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=128.71.123.78 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=119 ID=36916 DF PROTO=TCP SPT=3930 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 20 11:55:01 myname CRON[4067]: (root) CMD (command -v debian-sa1 > /dev/null && debian-sa1 1 1)

Looks like your database is a bit knackered. First backup the mysql directory tar -cvpf /root/mysql.tar /var/lib/mysql then try running mysql_upgrade && service mysql start

See if that helps

Hi obs, thank you for helping, i did the backup.

When trying to run "mysql_upgrade && service mysql start", i get:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

What do the logs say after you ran mysql_upgrade?

/var/log/syslog:

Jul 21 15:37:13 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=85.97.107.120 DST=198.74.52.6 LEN=60 TOS=0x00 PREC=0x00 TTL=54 ID=50473 DF PROTO=TCP SPT=50741 DPT=23 WINDOW=5808 RES=0x00 SYN URGP=0 
Jul 21 15:37:16 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=85.97.107.120 DST=198.74.52.6 LEN=60 TOS=0x00 PREC=0x00 TTL=54 ID=50474 DF PROTO=TCP SPT=50741 DPT=23 WINDOW=5808 RES=0x00 SYN URGP=0 
Jul 21 15:37:22 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=85.97.107.120 DST=198.74.52.6 LEN=60 TOS=0x00 PREC=0x00 TTL=54 ID=50475 DF PROTO=TCP SPT=50741 DPT=23 WINDOW=5808 RES=0x00 SYN URGP=0 
Jul 21 15:37:37 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=80.72.77.137 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=116 ID=22695 DF PROTO=TCP SPT=29294 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 21 15:37:40 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=80.72.77.137 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=116 ID=23275 DF PROTO=TCP SPT=29294 DPT=445 WINDOW=65535 RES=0x00 SYN URGP=0 
Jul 21 15:38:58 myname kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:41:08:00 SRC=178.32.43.113 DST=198.74.52.6 LEN=44 TOS=0x00 PREC=0x00 TTL=50 ID=61092 PROTO=TCP SPT=80 DPT=20287 WINDOW=16384 RES=0x00 ACK SYN URGP=0 
Jul 21 15:39:01 myname CRON[6349]: (root) CMD (  [ -x /usr/lib/php5/maxlifetime ] && [ -d /var/lib/php5 ] && find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +$(/usr/lib/php5/maxlifetime) ! -execdir fuser -s {} 2>/dev/null \; -delete)
Jul 21 15:40:39 myname  kernel: iptables denied: IN=eth0 OUT= MAC=f2:3c:91:6e:08:60:84:78:ac:5a:19:c1:08:00 SRC=115.248.191.9 DST=198.74.52.6 LEN=48 TOS=0x00 PREC=0x00 TTL=120 ID=3889 PROTO=TCP SPT=59586 DPT=22 WINDOW=65535 RES=0x00 SYN URGP=0

/var/log/mysql/error.log:

BLANK!

Hrm well it's knackered and without poking around there's not much else I can do if there's no extra log info. This is why running dumps before a backup is recommended in the backup docs. You could try running mysqlinstalldb if you're lucky only the mysql internal tables are corrupt and that will create new ones which may allow mysql to start.

obs, is it possible to restore the database using the files under "/var/lib/mysql" ?

Possibly it depends how corrupt things are and what format the tables were in. InnoDB is a lot easier than MyISAM. Either way it's not something I can instruct over a forum.

Just wondering, maybe it´s a good idea to sign up for another linode, fresh WAMP setup and try to move all my site´s files and recover the database, what do you think?

My Ubuntu version is 12.04, do you think it is worth to install Ubuntu 14, would that make the migration harder?

How do you plan on recovering the database? Just copying /var/lib/mysql to a new node won't help and upgrading to 14.04 will make things more complicated

I was planning to do something like in this tutorial:

http://www.thegeekstuff.com/2014/04/rec … odb-mysql/">http://www.thegeekstuff.com/2014/04/recover-innodb-mysql/

What do you suggest? I´m lost.

Thank you.

That only applies if the innodb log file is corrupt which probably won't help you since you've not shown any errors about innodb and unless you know what innodb is I doubt you're using it. Without looking at the server I can't make any suggestions.

For the future you should test backups regularly.

What do you mean by testing backups?

I have backups enabled daily and always take a snapshot before an upgrade. It´s so strange that this time even my 2 months old snapshot didn´t work. The site "died" 2 weeks ago.

I can´t pay much obs, but could you help me?

Thanks

Testing backups means restoring them to another node and making sure they work. In your case you should at least have a cron that runs mysqldump --single-transaction -A > /root/backup.sql before the backup runs before the Linode backups that way you have an easily to restore backup of the database.

Ping me an email at admin@rwky.net with your ssh details and I'll take a quick look, odds are I'll be able to tell you how knackered it is within a few minutes.

Thanks a lot obs.

Sent you the email.

Thank you obs, you saved my life!

Where do i place:

mysqldump --single-transaction -A > /root/backup.sql

Is it inside "crontab -e"?

Yep that would do, for Ubuntu/Debian this would back up the database at 5am every day

0 5 * * * /usr/bin/mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --single-transaction -A > /root/backup.sql

Obviously you'll want to change the time of day so it's just before your Linode backup window.

Also on another note, Linode backups are stored in the same DC as the host server so if there's a major inccident i.e. a fire you can potentiallly lose both the live site. You should copy your site files and database dump perodically to somewhere off site. At a minimum using rsync or sftp to download them to your local machine would do.

Thanks Rowan, great tips too.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Log in to Reply
Community Code of Conduct