This is an old revision of the document!


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.

Here is a way to restore MySQL in this situation. I've never managed to prevent that, but backing up this DB would likely allow simpler restorations.

  /etc/init.d/mysql stop  # to be sure, normally it's already stoped
  
  # try that to have the errors
  mysqld_safe
  tail -n 40 /var/lib/mysql/olinux.err
  
  # in our situation we have [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'
  
  cd /var/lib/mysql/mysql  # here you'll find the database files
  
  # let's backup things
  cd ..
  cp -rv mysql mysql.backup
  cd mysql
  
  # let's use the standard command for that (while it has proven not to be really useful, we never know)
  myisamchk -r *.MYI
  myisamchk -a *.MYI
  
  # the "myisamchk: error: 'XXX.MYI' is not a MyISAM-table" are the broken table generally
  
  # if you don't have any error, try mysqld_safe again
  # I've got this error this time
  
  > /usr/sbin/mysqld: Incorrect file format 'plugin'
  > 151031 16:16:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
  
  # here, mysql_upgrade isn't really useful
  
  root@olinux:~# mysql_upgrade
  Looking for 'mysql' as: mysql
  Looking for 'mysqlcheck' as: mysqlcheck
  FATAL ERROR: Upgrade failed
  
  # at this point, I've done mysqld_safe --skip-grant-tables, it failed firts because he didn't liked innodb for a strange reason
  # then start to work I don't know why
  # I've been running /etc/init.d/mysql --skip-grant-tables at some point, maybe it fixed it
  
  # in one shell
  mysqld_safe --skip-grant-tables
  
  # in another one, get the list of tables to fix
  myisamchk -a *.MYI  | grep error 
  
  # this open a mysql shell
  mysql mysql  # yes twice
  
  # for every corrupt table try this: (although they will likely fail)
  mysql> repair table $TABLE_NAME;
  
  # if this doesn't work, do
  # WARNING: THIS WILL CRUNCH THE TABLE AND DESTROY THE DATA
  mysql> repair table $TABLE_NAME use_frm;
  
  # now you probably don't have any users in the table, so let's recreate them if we can ...
  # start by getting the password of mysql like that
  cat /etc/yunohost/mysql
  
  # let's go back to 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,'','','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,'','','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,'','','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 mysql-server-5.5
  
  # from now on you should be able to rerun mysql normally and log as user
  # stop mysqld_safe (you need to kill it)
  ps ax | grep mysql
  # kill THE_TWO_PIDS_FOR_MYSQLD_SAFE
  
  /etc/init.d/mysql start
  
  # and you can log using this
  mysql -p$(cat /etc/yunohost/mysql) mysql
  
  # now, for every app you'll need to recreate the user in the user table
  # you can find how the password is stored in the installation script of the app
  # let's just hope that the tables of the app aren't corrupted...
  
  # for example for agendav you need to use this shell command to get the password
  yunohost app setting agendav mysqlpwd
  
  # 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,'');
  GRANT ALL PRIVILEGES ON agendav.* TO 'agendav'@localhost;
  
  # and don't forget this otherwise nothing will work
  FLUSH PRIVILEGES;
  
  # you can try a bash script like that:
  # 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; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"
  • howto/fix_self_corrupt_mysql.1469713705.txt.gz
  • Last modified: 2018/12/08 22:28
  • (external edit)