mysql connect problems

[John DeStefano]

On Fri, 10 Dec 2004 16:09:20 +0100, Jorn Argelo <jorn at wcborstel.nl> wrote:
> You have to make sure that the user has access to login. Unless you are using
> this database on an important machine, you can always change the root password
> like this:
> 
> set password = password("yournewpassword");
> 
> Like that, you won't have problems with permissions and such, but people who
> put security at a top priority will not like this method.

I logged into mysql as root over a PuTTy/SSH connection and performed
this command, specifying a new password.  But the result was "Query
OK, 0 rows affected (0.01 sec)".  I believe this is because there are
multiple 'root' entries in the user table with different 'host'
values.  I was able to change these values when I specified which
entry I wanted to change:

mysql> UPDATE user SET password=PASSWORD(''new_password')
    -> WHERE user='root' and host='host_entry';

> > Yes: I'm running 5.0.0-alpha (at least that's what I get back from
> > "mysqladmin -u root -p version").  So, do you recommend I try to
> > remove those extra root entries?
> 
> It's probably the best thing not to touch anything regarding the MySQL
> configuration unless you're sure what you're doing.

Are these extra root user entries in the mysql database, which I
believe I've entered myself while trying different solutions,
considered part of the MySQL configuration?

"select user, password, host from user;" on 'mysql' returned 3 'root'
entries using 2 different passwords (localhost, %, and the actual host
name), 2 anon entries (localhost and host name), 2 'mtuser' entries
(one on localhost w/o pw, one on '%'), and one 'wikiuser' entry
(localhost w/o pw).  I changed the root passwords so they all use the
same one, and changed the 'mtuser' entry that didn't have a password
so its password matches that of the other entry.

'mtuser' can not log in to mysql locally:
# mysql -u mtuser -p
Enter password:
ERROR 1045 (28000): Access denied for user: 'mtuser'@'localhost'
(Using password: YES)

A similar error is returned by the Movable Type System Loader page
(which is to be expected, since he/she can't log in locally):
Access denied for user: 'mtuser'@'%' to database 'mtdb' at
/usr/www/mt-static/mt-load.cgi line 195.

'wikiuser' can log in to mysql locally, but the MediaWiki 1.3.8
installation page reports it "Couldn't connect to database", no mater
whether I specify localhost, the actual host name, or leave the 'MySQL
server' field blank.

> If you want more information regarding the MySQL console, I would suggest you
> try the MySQL documentation located on their website. It's just as great as
> the FreeBSD handbook is ;)

I agree: the MySQL docs are comprehensive, extensive, and very
well-organized.  But for an inexperienced user like me, browsing
through the troubleshooting sections to find an answer is like...
well, trying to install Mobile Type. ;)