MariaDB, or MySQL is stuck in the startup loop with a vague error: “Starting mysqld daemon with databases from /config/databases”. In fact, it is an error, where the InnoDB portion encountered corrupt data / tables.
What was the reason for it and how to resolve this issue? Read on.
The problem
I’m lazy. I’m impatient. I don’t document stuff, that I just play with. And so, I did not document the correct naming scheme for my DB docker containers. And so it happened, that I scaled up a production, non-cluster enabled, single instance MariaDB to a total number of 3. Guess what? I corrupted my DB. The problem did not appear right away, it took some time, but I got this:
p_db.1.9j4hoc3ck4e1@swarm3 | [s6-init] making user provided files available at /var/run/s6/etc...exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [s6-init] ensuring user provided files have correct perms...exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [fix-attrs.d] applying ownership & permissions fixes... p_db.1.9j4hoc3ck4e1@swarm3 | [fix-attrs.d] done. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] executing container initialization scripts... p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 01-envfile: executing... p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 01-envfile: exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 10-adduser: executing... p_db.1.9j4hoc3ck4e1@swarm3 | usermod: no changes p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | ------------------------------------- p_db.1.9j4hoc3ck4e1@swarm3 | _ () p_db.1.9j4hoc3ck4e1@swarm3 | | | ___ _ __ p_db.1.9j4hoc3ck4e1@swarm3 | | | / __| | | / \ p_db.1.9j4hoc3ck4e1@swarm3 | | | \__ \ | | | () | p_db.1.9j4hoc3ck4e1@swarm3 | |_| |___/ |_| \__/ p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | Brought to you by linuxserver.io p_db.1.9j4hoc3ck4e1@swarm3 | ------------------------------------- p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | To support LSIO projects visit: p_db.1.9j4hoc3ck4e1@swarm3 | https://www.linuxserver.io/donate/ p_db.1.9j4hoc3ck4e1@swarm3 | ------------------------------------- p_db.1.9j4hoc3ck4e1@swarm3 | GID/UID p_db.1.9j4hoc3ck4e1@swarm3 | ------------------------------------- p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | User uid: 911 p_db.1.9j4hoc3ck4e1@swarm3 | User gid: 911 p_db.1.9j4hoc3ck4e1@swarm3 | ------------------------------------- p_db.1.9j4hoc3ck4e1@swarm3 | p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 10-adduser: exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 30-config: executing... p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 30-config: exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 40-initialise-db: executing... p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 40-initialise-db: exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 99-custom-scripts: executing... p_db.1.9j4hoc3ck4e1@swarm3 | [custom-init] no custom files found exiting... p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] 99-custom-scripts: exited 0. p_db.1.9j4hoc3ck4e1@swarm3 | [cont-init.d] done. p_db.1.9j4hoc3ck4e1@swarm3 | [services.d] starting services p_db.1.9j4hoc3ck4e1@swarm3 | [services.d] done. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:22:27 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:22:27 mysqld_safe Starting mysqld daemon with databases from /config/databases p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:23:00 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:23:00 mysqld_safe Starting mysqld daemon with databases from /config/databases p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:23:33 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:23:33 mysqld_safe Starting mysqld daemon with databases from /config/databases p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:24:06 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:24:06 mysqld_safe Starting mysqld daemon with databases from /config/databases p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:24:39 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:24:39 mysqld_safe Starting mysqld daemon with databases from /config/databases p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:25:12 mysqld_safe Logging to syslog. p_db.1.9j4hoc3ck4e1@swarm3 | 210125 12:25:12 mysqld_safe Starting mysqld daemon with databases from /config/databases
This keeps on looping over and over again, as the daemon tries to start an SQL instance, but something is not quite right and it fails over and over again.
After some time, I kindof figured out, that it might be corruption of the tablespaces, or something similar, beats me. I did not have proper logging set up, as I figured out later. Yeah, docker…
The proposed solution
Once almost figured out what theproblem might be, I resorted to looking through https://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html where I found a hint of a way to make a backup of the data. The fact, that I will have to do that manually did not hurt that much, but the process is not that simple. First of all, putting the Innodb to recovery. There are different levels for that. Nothing lower than 6 worked for me, so I set
[mysqld]innodb_force_recovery =6
in the /config/custom.cnf
My MySQL runs on NFS, so I have the complete directory of /config mounted from outside. This includes data, configuration and logs.
After the recovery is set, the proposed solution is to make a DB dump and then recover the data in a new fresh install. Well, not quite for me. All I got were errors – possibly connected to the corruption itself
Lost connection to MySQL server during query when dumping table `wp_comments`...and
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */...The real solution that worked for me
As a dump of the whole database was not possible for me, but I could clearly list the contents of the whole database, all I did was to find all the tables in my database:
# to connect to the mysql docker container docker exec -it 2c6effa01d73 /bin/bash cd /config # connect to mysql and list the tables mysql -u <user> -p MariaDB [(none)]> use mydatabase MariaDB [mydatabase]> show tables; +-----------------------+ | Tables_in_mydatabase | +-----------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_termmeta | | wp_terms | | wp_usermeta | | wp_users | +-----------------------+ 12 rows in set (0.001 sec)
OK, got the list of all tables, cool. Now let’s try to save each single one.
tables='wp_commentmeta wp_comments wp_links wp_options wp_postmeta wp_posts wp_term_relationships wp_term_taxonomy wp_termmeta wp_terms wp_usermeta wp_users' for table in $tables; do mysqldump -u user -p mydatabase $table > mydatabase.$table.dmp # depending on your setup, you might need to type in a password several times, or specify it in the command itself ls -rw-r--r-- 1 abc abc 3802 Jan 25 13:10 custom.cnf drwxr-xr-x 1 abc abc 260 Jan 25 13:11 databases drwxr-xr-x 1 abc abc 10 Jan 25 13:10 log -rw-r--r-- 1 abc abc 3397 Jan 25 13:07 mydatabase.wp_commentmeta.dmp -rw-r--r-- 1 abc abc 17412 Jan 25 13:07 mydatabase.wp_comments.dmp -rw-r--r-- 1 abc abc 2797 Jan 25 13:07 mydatabase.wp_links.dmp -rw-r--r-- 1 abc abc 571141 Jan 25 13:07 mydatabase.wp_options.dmp -rw-r--r-- 1 abc abc 910387 Jan 25 13:07 mydatabase.wp_postmeta.dmp -rw-r--r-- 1 abc abc 294184 Jan 25 13:07 mydatabase.wp_posts.dmp -rw-r--r-- 1 abc abc 2191 Jan 25 13:07 mydatabase.wp_termmeta.dmp -rw-r--r-- 1 abc abc 4193 Jan 25 13:07 mydatabase.wp_term_relationships.dmp -rw-r--r-- 1 abc abc 4948 Jan 25 13:07 mydatabase.wp_terms.dmp -rw-r--r-- 1 abc abc 5126 Jan 25 13:07 mydatabase.wp_term_taxonomy.dmp -rw-r--r-- 1 abc abc 3989 Jan 25 13:07 mydatabase.wp_usermeta.dmp -rw-r--r-- 1 abc abc 2852 Jan 25 13:07 mydatabase.wp_users.dmp
I should now have all the files with table dumps in my /config directory. Let’s do the ugly part. Stop the docker container, move the DB files away from /config/databases. This path should now be empty.
Remove the line about innodb recovery mode from /config/custom.cnf or comment it out.
# innodb_force_recovery = 6
Start docker container, I should see a clean startup. This time, my DB container is named spoton_cz_DB 🙂 As you can see, there is some config t be desired, specifically for the root user, but more on that later.
spoton_cz_db.1.m0z67958dx89@swarm2 | [s6-init] making user provided files available at /var/run/s6/etc...exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [s6-init] ensuring user provided files have correct perms...exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [fix-attrs.d] applying ownership & permissions fixes... spoton_cz_db.1.m0z67958dx89@swarm2 | [fix-attrs.d] done. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] executing container initialization scripts... spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 01-envfile: executing... spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 01-envfile: exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 10-adduser: executing... spoton_cz_db.1.m0z67958dx89@swarm2 | usermod: no changes spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | ------------------------------------- spoton_cz_db.1.m0z67958dx89@swarm2 | _ () spoton_cz_db.1.m0z67958dx89@swarm2 | | | ___ _ __ spoton_cz_db.1.m0z67958dx89@swarm2 | | | / __| | | / \ spoton_cz_db.1.m0z67958dx89@swarm2 | | | \__ \ | | | () | spoton_cz_db.1.m0z67958dx89@swarm2 | |_| |___/ |_| \__/ spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | Brought to you by linuxserver.io spoton_cz_db.1.m0z67958dx89@swarm2 | ------------------------------------- spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | To support LSIO projects visit: spoton_cz_db.1.m0z67958dx89@swarm2 | https://www.linuxserver.io/donate/ spoton_cz_db.1.m0z67958dx89@swarm2 | ------------------------------------- spoton_cz_db.1.m0z67958dx89@swarm2 | GID/UID spoton_cz_db.1.m0z67958dx89@swarm2 | ------------------------------------- spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | User uid: 911 spoton_cz_db.1.m0z67958dx89@swarm2 | User gid: 911 spoton_cz_db.1.m0z67958dx89@swarm2 | ------------------------------------- spoton_cz_db.1.m0z67958dx89@swarm2 | spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 10-adduser: exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 30-config: executing... spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 30-config: exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 40-initialise-db: executing... spoton_cz_db.1.m0z67958dx89@swarm2 | Setting Up Initial Databases spoton_cz_db.1.m0z67958dx89@swarm2 | Installing MariaDB/MySQL system tables in '/config/databases' ... .... .... .... spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 40-initialise-db: exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 99-custom-scripts: executing... spoton_cz_db.1.m0z67958dx89@swarm2 | [custom-init] no custom files found exiting... spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] 99-custom-scripts: exited 0. spoton_cz_db.1.m0z67958dx89@swarm2 | [cont-init.d] done. spoton_cz_db.1.m0z67958dx89@swarm2 | [services.d] starting services spoton_cz_db.1.m0z67958dx89@swarm2 | [services.d] done. spoton_cz_db.1.m0z67958dx89@swarm2 | 210125 13:11:04 mysqld_safe Logging to syslog. spoton_cz_db.1.m0z67958dx89@swarm2 | 210125 13:11:04 mysqld_safe Starting mysqld daemon with databases from /config/databases
Then once again, connect to the instance and restore the tables with
root@2c6effa01d73:/config/databases# mysql -u ventil -p spotoncz < spotoncz.wp_commentmeta.dmp
For each table, mind you. And. DONE. You have a fresh DB, no corruption and the data have been saved. All data for a wordpress site, including users, passwords, comments, pages and what not are not lost forever.
Cheers.
