User Tools

Site Tools


docs:tips_n_tricks:mysql.html

This is an old revision of the document!


MySQL

Recover on Debian

Note: These steps worked for me. You might experience another problem. If you don't understand, what I am doing here, read the docs! You will lose data!

Recover system privileges

I was suffering from the error

~ # mysqld
101106 15:14:21  InnoDB: Started; log sequence number 0 43655
101106 15:14:21 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
101106 15:14:21 [Note] Starting crash recovery...
101106 15:14:21 [Note] Crash recovery finished.
101106 15:14:21 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

After googling, I did the following steps to get it up an running again:

mysqld --skip-grant-tables

did start a server. I could do a

mysqldumb mythconverg > /tmp/mythconverg.sql
mysqladmin shutdown

in another shell to backup my MythTV database and shut down the insecure running server. After this, I had to remove some corrupted tables, and recreate them:

cd /var/lib/mysql/mysql
rm -v host.* user.* db.* func.* time_zone.*
install_mysql_db

As the MySQL account debian-sys-maint is not available at this point, using /etc/init.d/mysqld is not recommended yet. Start MySQL in you shell

mysqld

and proceed in another shell. As I am using ~/.my.conf, I had to disable it by renaming. Otherwise, the stored password would be used which do not work yet.

mv -iv  ~/.my.conf ~/.my.conf.disabled
mysqladmin -u root password "<your root password from .my.conf here>"
mv -iv  ~/.my.conf.disabled ~/.my.conf
mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER, CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'debian-sys-maint'@localhost IDENTIFIED BY '<password>' WITH GRANT OPTION;
mysql> quit
mysqladmin shutdown
/etc/init.d/mysql start

where <password> must be the same as in /etc/mysql/debian.cnf.

Recover MythTV privileges

mysql
mysql> GRANT ALL ON mythconverg.* TO mythtv@localhost IDENTIFIED BY '<password>'
mysql> GRANT ALL ON mythconverg.* TO mythtv IDENTIFIED BY '<password>'  mysql> quit

where <password> should be the password you used before as it is found e.g. in ~/.mythtv/mysql.txt of your mythfrontend user.

docs/tips_n_tricks/mysql.html.1289061354.txt.gz · Last modified: 06.11.2010 17:35 CET by peter

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki