Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
howto:fix_self_corrupt_mysql [2016/07/28 17:28]
bram
howto:fix_self_corrupt_mysql [2019/05/20 01:06] (current)
tierce old revision restored (2018/12/08 22:28)
Line 2: Line 2:
  
 In some situations (typically power failure), MySQL corrupts its own database (called "​mysql"​) and won't start again. This breaks any application using MySQL. 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. 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.
Line 19: 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 95: 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,'','​N','​N'​);​+    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;​
     ​     ​
Line 103: Line 105:
     # 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,'','​N','​N'​);​ GRANT ALL PRIVILEGES ON $i.* TO '​$i'​@localhost;";​ done; mysql -p$(cat /​etc/​yunohost/​mysql) mysql -e "FLUSH 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.1469719714.txt.gz
  • Last modified: 2018/12/08 22:28
  • (external edit)