==== 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 [[howto:daily_backup|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;"