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:34]
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 104: Line 106:
     # 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,'','','​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.1469720057.txt.gz
  • Last modified: 2018/12/08 22:28
  • (external edit)