UBUNTU: MySQL databases disappeared - returned after restarting service

Question

Tonight my MySQL databases disappeared. I'm speaking of SHOW DATABASES; returning only the "default" databases (information_shema, mysql and dbispconfig [my configuration panel]).

I looked at /var/data/mysql and all data was still there. So, after backuping, I did a service mysql restart and everything returned normal.

Now, why did this happen? cat syslog.1 | grep mysql shows

Jun 15 00:03:30 ns222201 named[3699]: built with '--prefix=/usr' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc/bind' '--localstatedir=/var' '--enable-threads' '--enable-largefile' '--with-libtool' '--enable-shared' '--enable-static' '--with-openssl=/usr' '--with-gssapi=/usr' '--with-gnu-ld' '--with-dlz-postgres=no' '--with-dlz-mysql=no' '--with-dlz-bdb=yes' '--with-dlz-filesystem=yes' '--with-dlz-ldap=yes' '--with-dlz-stub=yes' '--with-geoip=/usr' '--enable-ipv6' 'CFLAGS=-fno-strict-aliasing -DDIG_SIGCHASE -O2' 'LDFLAGS=-Wl,-Bsymbolic-functions' 'CPPFLAGS='
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3732]: Upgrading MySQL tables if necessary.
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3735]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3735]: Looking for 'mysql' as: /usr/bin/mysql
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3735]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3735]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock'
Jun 15 00:03:31 ns222201 /etc/mysql/debian-start[3735]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock'
...
[restarting with the wrong tables]

I got about 15 data-driven websites on that machine and I don't want something like this to happen again.

Any idea how to investigate further?

Answer 1

Seems like you've upgraded your mysql.

mysql_upgrade is typically run when DB server is upgraded. It checks table for consistency, fixes and then upgrades database.

Mostly old tables are fine, but sometimes you need to run upgrade command. I only had this issue few times for specific upgrade.

Probably you've upgraded mysql binaries and libraries, mysql process was not yet restarted and it was working as old version, then you've restarted the process and it started working as new one, so db upgrade was required at that point.

Probably this is how ubuntu mysql works. RHEL/Centos/Fedora upgrades tables at the time of package update and reloads processes.