How to fix MySQL when it corrupts itself
In some situations (typically power failure), MySQL corrupts its own database (called “mysql”) and won't start again. This breaks any application using MySQL.
You can try this script by Bram that should normally auto-fix this issue but need more testing (and we are looking for testing) https://github.com/labriqueinternet/auto-fix-mysql Plz contact us if you intent to do so
Here is a way to restore MySQL in this situation. I've never managed to prevent that, but backing up this DB would likely allow simpler restorations.
/etc/init.d/mysql stop # to be sure, normally it's already stoped
# try that to have the errors
mysqld_safe
tail -n 40 /var/lib/mysql/olinux.err
# in our situation we have [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'
cd /var/lib/mysql/mysql # here you'll find the database files
# let's backup things
cd ..
cp -rv mysql ../mysql.backup
cd mysql
# let's use the standard command for that (while it has proven not to be really useful, we never know)
myisamchk -r *.MYI
myisamchk -a *.MYI
# the "myisamchk: error: 'XXX.MYI' is not a MyISAM-table" are the broken table generally
# if you don't have any error, try mysqld_safe again
# I've got this error this time
> /usr/sbin/mysqld: Incorrect file format 'plugin'
> 151031 16:16:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
# here, mysql_upgrade isn't really useful
root@olinux:~# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed
# at this point, I've done mysqld_safe --skip-grant-tables, it failed firts because he didn't liked innodb for a strange reason
# then start to work I don't know why
# I've been running /etc/init.d/mysql --skip-grant-tables at some point, maybe it fixed it
# in one shell
mysqld_safe --skip-grant-tables
# in another one, get the list of tables to fix
myisamchk -a *.MYI | grep error
# this open a mysql shell
mysql mysql # yes twice
# for every corrupt table try this: (although they will likely fail)
mysql> repair table $TABLE_NAME;
# if this doesn't work, do
# WARNING: THIS WILL CRUNCH THE TABLE AND DESTROY THE DATA
mysql> repair table $TABLE_NAME use_frm;
# now you probably don't have any users in the table, so let's recreate them if we can ...
# start by getting the password of mysql like that
cat /etc/yunohost/mysql
# let's go back to MySQL
mysql mysql
mysql> insert into user values('localhost','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
mysql> insert into user values('::1','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
mysql> insert into user values('127.0.0.1','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
# resurrect debian-sys-maint as well, by running the following command in a shell
# (press enter when prompted for a new password)
dpkg-reconfigure mariadb-server-10.0
# or this one if you are still using mysql (old yunohost stable)
dpkg-reconfigure mysql-server-5.5
# from now on you should be able to rerun mysql normally and log as user
# stop mysqld_safe (you need to kill it)
ps ax | grep mysql
# kill THE_TWO_PIDS_FOR_MYSQLD_SAFE
/etc/init.d/mysql start
# and you can log using this
mysql -p$(cat /etc/yunohost/mysql) mysql
# now, for every app you'll need to recreate the user in the user table
# you can find how the password is stored in the installation script of the app
# let's just hope that the tables of the app aren't corrupted...
# for example for agendav you need to use this shell command to get the password
yunohost app setting agendav mysqlpwd
# and to recreate agendav user
insert into user values('localhost','agendav', PASSWORD('put the password here'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N');
GRANT ALL PRIVILEGES ON agendav.* TO 'agendav'@localhost;
# and don't forget this otherwise nothing will work
FLUSH PRIVILEGES;
# you can try a bash script like that:
# BUT I HAVEN'T TESTED IT
for i in app1 app2 app3; do mysql -p$(cat /etc/yunohost/mysql) mysql -e "insert into user values('localhost','$i', PASSWORD('$(yunohost app setting $i mysqlpwd)'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N'); GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"
—
On another weird situation, the DB permissions disappeared and we were unable to re-grant permissions to the app because root was not allowed to give permissions… And it seemed not possible to grant it permissions for *.* for some really weird reasons. ljf was able to hand-craft a request to manually give the permissions to root on all tables, allowing to grant permissions to specific users.
I think we started with a mysqld_safe –skip-grant-tables in one terminal, and then in another :
for i in app1 app2 app3; do mysql -p$(cat /etc/yunohost/mysql) mysql -e "delete from user WHERE User='$i'; insert into user values('localhost','$i', PASSWORD('$(yunohost app setting $i mysqlpwd)'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N'); INSERT INTO db VALUES('localhost','$i','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"