Differences

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

Link to this comparison view

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:
-<Lordrhaaa 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;"​ 
 + 
  • howto/fix_self_corrupt_mysql.txt
  • Last modified: 2019/05/20 01:06
  • by tierce