[mythtv-users] What to do when you can't remember the password for mysql

Michael T. Dean mtdean at thirdcontact.com
Fri Apr 20 14:46:09 UTC 2007


On 04/20/2007 03:24 AM, David Campbell wrote:
> The correct way
>
> stop mysql
>   
mysqld
> /usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
> mysql -u root
> use mysql;
> UPDATE user SET Password=PASSWORD("somepassword") WHERE User="root";
>   
exit

stop mysqld
> start mysql
mysqld


However, the correct correct way is:

stop mysqld (as appropriate for your system, i.e. using init scripts or 
whatever)

touch /srv/mysql/tmpinit.sql &&
chmod 600 /srv/mysql/tmpinit.sql &&
cat > /srv/mysql/tmpinit.sql << EOF
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('not this password');
SET PASSWORD FOR 'root'@'`hostname`' = PASSWORD('not this password');
EOF
mysqld_safe --user=mysql \
            --init-file=/srv/mysql/tmpinit.sql 2>&1 >/dev/null &
rm /srv/mysql/tmpinit.sql

The "other" correct way runs mysqld without a password /and/ bypasses 
the permissions system, which is not desirable.  The other approach 
requires restarting mysqld twice (there should be a "stop mysqld" after 
the UPDATE)--this one only requires one restart.  The other approach 
puts root's password in the ~/.mysql_history of the user running the 
mysql command-line client.

With the approach I recommend, it's desirable to write the tmpinit.sql 
file to a location that is not generally accessible by users, so there 
is no indication that someone is hacking the DB (which could encourage 
attempted timing attacks).  However, by touching the file first and then 
explicitly setting its permissions, we minimize the chances of someone 
seeing the actual password.  Because the password is only in a here 
document, it is not a part of the "ps" command list or put into 
~/.bash_history or ...

But, I don't need to worry about security!  So, why are you even running 
mysql with a password.

On my systems, every single mysql server has a different root password.  
No one (not even me) knows those passwords.  This wouldn't be possible 
if I needed 24/7/52 access, but, hey, my hardware's not that reliable.  
As a matter of fact, it's really not even necessary to log in as root.

Huh?  How can I allow access from other hosts without logging in as 
root?  Well, if you don't know the root password and you don't want to 
know it, you can just replace the "SET PASSWORD" lines with:

GRANT ALL ON mythconverg.* TO mythtv@"%" IDENTIFIED BY "mythtv";
FLUSH PRIVILEGES;

However, I highly recommend setting the password to a nice complex 
password at least once.  To do that, you can just add the GRANT and 
FLUSH lines to the here document after the SET PASSWORD lines and do 
them all at once.

Mike


More information about the mythtv-users mailing list