Ubuntu – Error establishing a database connection several times a week

Apache2MySQL

I have a client I do web development for who had a basic low traffic WordpRess site running on Ubuntu 12.0.4 with Digitalocean.

Basic LAMP stack that they provide pre built with WordPress installed.

About 3-4 times a week, the MySQL DB goes down and shows this message on the site:

Error establishing a database connection

I then have to SSH into the box and reboot MySQL. Takes all but 15 seconds however can be down for hours if I don't notice it. It;s not fair to my client as it is random and up to 6 times a week sometimes!

I am a PHP and JavaScript developer so my Server admin skills are limited.

Please can someone assist in how I can find the problem, source of problem I should say and hopefully fix it permanently?

UPDATE

I have a log file here /var/log/mysql.log that has a last modified date/time around the time the MySQL server crashed today. It is an empty file though.

Sorting files in that directory /var/log/ by DateTime I can see one of the most recent modified files is called syslog

I have pasted the contents of syslog below as near the bottom it mentions MySQL so I feel it may be relevant if someone could try to help me make sense of it please?

/var/log/syslog 

Jun 24 16:17:01 thomaslastname CRON[928]: (root) CMD (   cd / && run-parts --report /etc/cron.hourly)
Jun 24 16:32:27 thomaslastname kernel: [2738822.445529] type=1400 audit(1435177947.529:25): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=1048 comm="apparmor_parser"
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1102]: Upgrading MySQL tables if necessary.
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: Looking for 'mysql' as: /usr/bin/mysql
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: This installation of MySQL is already upgraded to 5.5.38, use --force if you still need to run mysql_upgrade
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1116]: Checking for insecure root accounts.
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1122]: Triggering myisam-recover for all MyISAM tables
Jun 24 16:39:01 thomaslastname CRON[1208]: (root) CMD (  [ -x /usr/lib/php5/maxlifetime ] && [ -x /usr/lib/php5/sessionclean ] && [ -d /var/lib/php5 ] && /usr/lib/php5/sessionclean /var/lib/php5 $(/usr/lib/php5/maxlifetime))
Jun 24 16:39:01 thomaslastname postfix/pickup[32544]: 5F48363D60: uid=0 from=<root>
Jun 24 16:39:01 thomaslastname postfix/cleanup[1221]: 5F48363D60: message-id=<20150624203901.5F48363D60@WP-NewBase-052814>
Jun 24 16:39:01 thomaslastname postfix/qmgr[1002]: 5F48363D60: from=<root@WP-NewBase-052814>, size=866, nrcpt=1 (queue active)
Jun 24 16:39:01 thomaslastname postfix/local[1223]: 5F48363D60: to=<root@WP-NewBase-052814>, orig_to=<root>, relay=local, delay=0.02, delays=0.01/0/0/0, dsn=2.0.0, status=sent (delivered to mailbox)
Jun 24 16:39:01 thomaslastname postfix/qmgr[1002]: 5F48363D60: removed

/var/log/mysql/error.log 

150624 16:32:27 [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.
150624 16:32:27 [Note] Plugin 'FEDERATED' is disabled.
150624 16:32:27 InnoDB: The InnoDB memory heap is disabled
150624 16:32:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150624 16:32:27 InnoDB: Compressed tables use zlib 1.2.8
150624 16:32:27 InnoDB: Using Linux native AIO
150624 16:32:27 InnoDB: Initializing buffer pool, size = 128.0M
150624 16:32:27 InnoDB: Completed initialization of buffer pool
150624 16:32:27 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 18880361123
150624 16:32:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 18880542657
150624 16:32:27  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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
150624 16:32:27  InnoDB: Waiting for the background threads to start
150624 16:32:28 InnoDB: 5.5.38 started; log sequence number 18880542657
150624 16:32:28 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150624 16:32:28 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150624 16:32:28 [Note] Server socket created on IP: '127.0.0.1'.
150624 16:32:28 [Note] Event Scheduler: Loaded 0 events
150624 16:32:28 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
150624 16:32:29 [ERROR] /usr/sbin/mysqld: Table './wordpress/wp_aiowps_login_activity' is marked as crashed and should be repaired
150624 16:32:29 [Warning] Checking table:   './wordpress/wp_aiowps_login_activity'

Best Answer

Unfortunately, there isn't enough information here to fully diagnose the connection issues. Your MySQL error log says that MySQL crashed but nothing about why.

You can continue investigating if you'd like, but there's a solution you can apply in the meantime.

Every five minutes, automatically restart MySQL if it's not accepting connections

Note: this will take MySQL out of service for about six minutes.

  1. Open a MySQL prompt or run a command in phpMyAdmin (choose a better alphanumeric, no-spaces password than abcdefg):

    CREATE USER 'hang-check'@'localhost' IDENTIFIED BY 'abcdefg';

  2. Ensure that you can log in to the database as hang-check and that they can't see any of your WordPress databases.

  3. Open a terminal on the server, and run:

    sudo -i
EDITOR=nano crontab -e

  4. In the crontab file, insert the following line (change abcdefg to the password you chose earlier), save it (Ctrl+O then Enter), and exit back to the terminal (Ctrl+X):

     */5 * * * * /usr/bin/mysql --host=127.0.0.1 --port=3306 --connect_timeout=30 --user=hang-check --password='abcdefg' -e '' || (/usr/bin/killall -9 mysqld_safe mysqld; /bin/sleep 15; /usr/sbin/service mysql start)

  5. Run this in the same terminal:

     sudo -i
 service mysql stop
 killall -9 mysqld_safe mysqld
 sleep 360; ps -umysql

  6. Wait six minutes until it finishes.

  7. Ensure that the last command responds with a line that ends with mysqld. If it doesn't, run service mysql start and comment here.
  8. Close the terminal.
Related Question