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/29 19:34]
bram this was breaking mysql because he couldn't read those file due to permission problems
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 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.1469813681.txt.gz
  • Last modified: 2018/12/08 22:28
  • (external edit)