mtdean at thirdcontact
Apr 20, 2007, 7:46 AM
Post #8 of 9
(7148 views)
Permalink
|
|
Re: What to do when you can't remember the password for mysql
[In reply to]
|
|
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
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
|
