Hello,
I just experienced bad situation this week-end with mysql and thought this procedure might interest other people.
My yunohost server rebooted serveral times due to electrical outage and finally mysql could not start anymore due to corrupted databases.
This is how I managed to get back to normal.
After starting the server I had this kind of traces when trying to run mysqld service
/etc/init.d/mysql[9489]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
/etc/init.d/mysql[9489]: [61B blob data]
/etc/init.d/mysql[9489]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
/etc/init.d/mysql[9489]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
/etc/init.d/mysql[9489]:
mysql[8630]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
systemd[1]: mysql.service: control process exited, code=exited status=1
systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
systemd[1]: Unit mysql.service entered failed state.
First thing to do is to check which database is corrupted and forbid mysqld to start. You can use mysqlcheck to do so, but you need first to have mysqld running, even in failsafe mode.
You can launch these commands
// start mysqld even if mysql.user table is corrupted
mysqld_safe --skip-grant-tables
// check all db
mysqlcheck -c -u root --all-databases
Once here, first thing is to make a backup of all databases. Of course you certainly have a daily backup of your db. But if you don’t, you can dump each db like this:
// dump database myDB to myDB.sql file
mysqldump myDB > myDB.sql
Then you can try to repair the corrupted databases.
In my case, the issue was on mysql.user so I tried to repair the tables with command
mysqlcheck --repair --use-frm mysql
Finally mysqld service was able to start but I realized that mysql user db had been clean and so I had no more users declared in mysql. So no app was able to connect, even root user was broken.
I tried several time to reset root password with
sudo dpkg-reconfigure mariadb-server-10.0
but it failed.
So finally, I moved /var/lib/mysql folder and then had to reinstall mariadb-server
sudo apt-get install --reinstall mariadb-server-10.0
So here I had a clean mysql but then I realized that in yunohost, each app has its own user and password to access its own db. So for each installed app I did the following:
// Example with ttrss:
// backup the app in the case of...
zip -r ~/ttrss.zip /var/www/ttrss/
// remove the installed app (after checking several times you saved the db !!)
sudo yunohost app remove ttrss
// reinstall the app
sudo yunohost app install ttrss
// Check the app is working OK now
// Delete the empty db and create a new one (root mysql password is in /etc/yunohost/mysql)
mysql -u root -p
> DROP database ttrss;
> CREATE database ttrss;
// and then import the backup db
mysql -u root -p ttrss < ttrss.sql
This worked great for the following apps: ttrss, wallabag2, baikal, rainloop, nextcloud, gogs.
Please comment and don’t forget to make backup !!