One of my local dev environments run WinNMP – a Windows development stack package with Nginx, MariaDB, PHP etc. Great little tool to hack with.
Over the course of around 3 years my database has grown to around 300-400MB. In itself its relatively small for a database, but for test data is quite chunky.
Now, more recently MariaDB has started to play up, when running WinNMP often MariaDB would enter a crash recovery cycle, never actually starting up properly. mysql.log would read as below, with not much more to explain what is going on.
2019-12-05 17:59:26 0 [Note] Recovering after a crash using wt-nmp-bin
2019-12-05 17:59:26 0 [Note] Starting crash recovery…
2019-12-05 17:59:26 0 [Note] Crash recovery finished.
PHP would also throw errors
[05-Dec-2019 17:59:34 UTC] PHP Warning: mysqli_connect(): MySQL server has gone away in index.php on line 10
[05-Dec-2019 17:59:34 UTC] PHP Warning: mysqli_connect(): Error while reading greeting packet. PID=7336 in index.php on line 10
[05-Dec-2019 17:59:34 UTC] PHP Warning: mysqli_connect(): (HY000/2006): MySQL server has gone away in index.php on line 10
[05-Dec-2019 17:59:34 UTC] PHP Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in index.php on line 10
These errors simply make effort to describe what is going on. After plenty of Googling, and even some desperate Bing searches. I came across an almost decade old post that outlined an issue around replication. This unfortunately is no longer available, which is why i decided to write this one up – least not for my own reference.
Turns out that WinNMP – formerly WT-NMP runs replication locally, and some action is causing this to break between the master and slave.
Luckily, with WinNMP being portable, and the database being stored neatly within its own folder, I was able to make plenty of backups and play around with recovery with little chance of losing any actual data.
To restore service to MariaDB, firstly I kill any running applications related to the package. Backup the data directory. Remove multi-master.info, all files beginning with relay-log*, then finally all files that have *-relay-bin*
Simply spin the server back up and the MariaDB/MySQL works again.
I still continue to have the same issue about once a month, so its not likely a permanent fix. So I would certainly recommend rebuilding the database server at this point.