User Tools

Site Tools


docs:tips_n_tricks:mysql.html

MySQL

Change your password

The tradtional way of changing passwords on UNIXoid systems is an command line tool, that asks you old and new passwords without echoing or logging the typed characters. Unfortunately, this is not common and bad documented for MySQL administration, most documentations point you to SQL or CLI commands that tend to be logged in history files containing the clear text password. However, secretly changing your password is possible since MySQL 5.5.3:

mysqladmin -p -u <user> -h <host> password

will ask you for the old and new password without echoing or logging.

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.* proc.* procs_priv.* tables_priv.* columns_priv.*
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. (The privileges applied where taken from a running MySQL on another Debian system)

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.

References

docs/tips_n_tricks/mysql.html.txt · Last modified: 13.05.2013 16:35 by peter