Differences
This shows you the differences between two versions of the page.
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;" | ||