Differences

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

Link to this comparison view

Next revision
Previous revision
howto:fix_self_corrupt_mysql [2016/03/20 16:01]
127.0.0.1 external edit
howto:fix_self_corrupt_mysql [2019/05/20 01:06]
tierce old revision restored (2018/12/08 22:28)
Line 1: Line 1:
-==== How to fix MySQL when it self corrupt ​====+==== 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. 
  
-In some situation (probably power failure), mysql get to self corrupt for very strange reason, it destroy it's own database called "​mysql"​. Here is a way to restore mysql in this situation (I've never managed to prevent that, mysql insisting on using MyISANE for this database): 
  
     /​etc/​init.d/​mysql stop  # to be sure, normally it's already stoped     /​etc/​init.d/​mysql stop  # to be sure, normally it's already stoped
Line 15: Line 21:
     # let's backup things     # let's backup things
     cd ..     cd ..
-    cp -rv mysql mysql.backup+    cp -rv mysql ../mysql.backup
     cd mysql     cd mysql
     ​     ​
Line 50: Line 56:
     mysql mysql  # yes twice     mysql mysql  # yes twice
     ​     ​
-    # for every corrupt table try this: (none of the table were able to be fixed here)+    # for every corrupt table try this: (although they will likely fail)
     mysql> repair table $TABLE_NAME;​     mysql> repair table $TABLE_NAME;​
     ​     ​
Line 61: Line 67:
     cat /​etc/​yunohost/​mysql     cat /​etc/​yunohost/​mysql
     ​     ​
-    # let's go back to mysql+    # let's go back to MySQL
     mysql 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,'',''​);​ +    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,'',''​);​ +    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,'',''​);​+    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     # from now on you should be able to rerun mysql normally and log as user
     # stop mysqld_safe (you need to kill it)     # stop mysqld_safe (you need to kill it)
     ps ax | grep mysql     ps ax | grep mysql
-    kill THE_TWO_PIDS_FOR_MYSQLD_SAFE+    ​kill THE_TWO_PIDS_FOR_MYSQLD_SAFE 
 +    ​
     /​etc/​init.d/​mysql start     /​etc/​init.d/​mysql start
     ​     ​
Line 77: Line 90:
     ​     ​
     # now, for every app you'll need to recreate the user in the user table     # now, for every app you'll need to recreate the user in the user table
-    ​# yes, this is boring as feuk +    # you can find how the password is stored ​in the installation script of the app 
-    ​# you can find how the password is store in the installation script of the app +    # let's just hope that the tables of the app aren'​t ​corrupted...
-    # let's just hope that the tables of the app aren'​t ​corruped ​  +
     ​     ​
     # for example for agendav you need to use this shell command to get the password     # for example for agendav you need to use this shell command to get the password
Line 85: Line 97:
     ​     ​
     # and to recreate agendav user     # 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,​NULL,''​);​+    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;​     GRANT ALL PRIVILEGES ON agendav.* TO '​agendav'​@localhost;​
     ​     ​
-    # and don't forget this otherwise nothing will work (yet, mysql is that stupid)+    # and don't forget this otherwise nothing will work
     FLUSH PRIVILEGES;     FLUSH PRIVILEGES;
     ​     ​
     # you can try a bash script like that:     # you can try a bash script like that:
     # BUT I HAVEN'​T TESTED IT     # 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,​NULL,''​);​ GRANT ALL PRIVILEGES ON $i.* TO '​$i'​@localhost;";​ done; ysql -p$(cat /​etc/​yunohost/​mysql) mysql -e "LUSH PRIVILEGES;"​ ======+    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