Wordpress "error establishing a database connection" - Backup restore doesn´t fix
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
Any help?
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.
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?
sudo dpkg –configure -a
or
sudo apt-get -f install.
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
/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)
See if that helps
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
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!
My Ubuntu version is 12.04, do you think it is worth to install Ubuntu 14, would that make the migration harder?
What do you suggest? I´m lost.
Thank you.
For the future you should test backups regularly.
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
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
Sent you the email.
Where do i place:
mysqldump --single-transaction -A > /root/backup.sql
Is it inside "crontab -e"?
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.