Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | |||
|
howto:fix_self_corrupt_mysql [2019/05/03 15:19] jjlrm d |
howto:fix_self_corrupt_mysql [2019/05/20 01:06] (current) tierce old revision restored (2018/12/08 22:28) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | <Lord> rhaaa mais en plus de nous faire chier sur le chat il pourri le wiki ! | + | ==== 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;" | ||
| + | |||