Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Last revision Both sides next revision
howto:fix_self_corrupt_mysql [2018/12/08 22:28]
127.0.0.1 external edit
howto:fix_self_corrupt_mysql [2019/05/03 15:19]
jjlrm d
Line 1: Line 1:
-==== How to fix MySQL when it corrupts itself ==== +<Lordrhaaa mais en plus de nous faire chier sur le chat il pourri le wiki !
- +
-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;"​ +
- +
  • howto/fix_self_corrupt_mysql.txt
  • Last modified: 2019/05/20 01:06
  • by tierce