On Monday 16 February 2009, Robert L Cochran wrote:
That is to say, on the initial installation of a MySQL server, there
is
indeed a 'root'@'localhost' account, but it has no password. See page
609 of MySQL, Third Edition, by Paul DuBois and read through the next
several pages.
I just did this for a new MySQL installation on one of my test boxes and
was able to set the root password with only a little fussiness from
mysqladmin. I first set the password for localhost, then logged into the
mysql server as 'root'@'localhost' and set the password for
'root'(a)'my.box.name'. You need to set both these passwords for root.
Then I tested my login and did a few easy queries such as 'show
databases;' and I was all set.
Bob
Robert L Cochran wrote:
> There is a 'root'@'localhost' account, but it has no password. For
more
> information see the MySQL knowledgebase at
www.mysql.com.
>
> Been there, done that.
>
> Bob
>
> Craig White wrote:
>> On Mon, 2009-02-16 at 11:06 -0500, Robert L Cochran wrote:
>>> All the heavier-weight database engines have their own user accounts, so
>>> they can grant or restrict permissions to various databases and tables
>>> based on who the user is. MySQL does this. Even though mysql has a root
>>> user that user is totally separate from the OS root account. You can
>>> also have a mysql user account named mickey even though your host box
>>> does not have such a user. So think only in terms of the defined MySQL
>>> users.
>>>
>>> You need to reset the MySQL root user password.
I have done this 5 times now.
>>> There may be no password to start with. I wonder what
happens if you
>>> just press enter when prompted for the password. If there is no
>>> password, then you can set one using mysqladmin.
mysqladmin is also being denied access.
[root@coyote /]# mysqladmin
mysqladmin Ver 8.41 Distrib 5.0.45, for redhat-linux-gnu on i386
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
-c, --count=# Number of iterations to make. This works with -i
(--sleep) only.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'.
-f, --force Don't ask for confirmation on drop database; with
multiple commands, continue even if an error occurs.
-C, --compress Use compression in server/client protocol.
--character-sets-dir=name
Directory where character sets are.
--default-character-set=name
Set the default character set.
-?, --help Display this help and exit.
-h, --host=name Connect to host.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, --relative Show difference between current and previous values when
used with -i. Currently works only with extended-status.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
-s, --silent Silently exit if one can't connect to server.
-S, --socket=name Socket file to use for connection.
-i, --sleep=# Execute commands again and again with a sleep between.
--ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-u, --user=name User for login if not current user.
-v, --verbose Write more information.
-V, --version Output version information and exit.
-E, --vertical Print output vertically. Is similar to --relative, but
prints output vertically.
-w, --wait[=#] Wait and retry if connection is down.
--connect_timeout=#
--shutdown_timeout=#
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
count 0
force FALSE
compress FALSE
character-sets-dir (No default value)
default-character-set (No default value)
host (No default value)
port 0
relative FALSE
socket (No default value)
sleep 0
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
user (No default value)
verbose FALSE
vertical FALSE
connect_timeout 43200
shutdown_timeout 3600
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf /etc/my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
Where command is a one or more of: (Commands may be shortened)
create databasename Create a new database
debug Instruct server to write debug information to log
drop databasename Delete a database and all its tables
extended-status Gives an extended status message from the server
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
flush-privileges Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
password new-password Change old password to new-password, MySQL 4.1 hashing.
old-password new-password Change old password to new-password in old format.
ping Check if mysqld is alive
processlist Show list of active threads in server
reload Reload grant tables
refresh Flush all tables and close and open logfiles
shutdown Take server down
status Gives a short status message from the server
start-slave Start slave
stop-slave Stop slave
variables Prints variables available
version Get version info from server
[root@coyote /]# mysqladmin ping
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password:
NO)'
[root@coyote /]# mysqladmin create mythtv
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password:
NO)'
>>> When you first start
>>> the mysqld server using 'service start mysqld' the syntax of the
command
>>> is explained to you right on the terminal window.
Not here:
[root@coyote /]# service mysqld start
Starting MySQL: [ OK ]
[root@coyote /]#
>>> One more point. If you want to assign a password to a
user on a specific
>>> host machine, such as 'mickey'(a)'mickeymouse.m1.org' then I
believe that
>>> at the time someone attempts to log in with that username the actual
>>> machine name must resolve correctly on dns to 'mickeymouse.m1.org'
or
>>> the user 'mickey' must have a password defined for the localhost
machine
>>> ('mickey'@'localhost').
>>>
>>> To do reset the root password correctly, you can find copious details on
>>> the MySQL knowledgebase. Go to
www.mysql.com and search off their
>>> knowledgebase. There is a method described for changing the password for
>>> the root user, but it is fairly complicated. I've used it successfully
>>> once or twice before when I made a mess of my own mysql root password.
>>>
>>> Another great resource is to read Paul DuBois book "MySQL". It is
really
>>> the bible of all things MySQL. If you intend to use MySQL seriously then
>>> this book is mandatory purchasing and reading.
>>
That book is a minimum of 60 miles away. But I did install the man pages,
which one might be helpful?
>> ----
>> I think original setup for mysql is for root user via local socket and
>> not via localhost so there actually isn't an account for root@localhost
>> thus attempting to connect via tcp/ip as root is doomed to fail out of
>> the box.
>>
>> Craig
--
Cheers, Gene
"There are four boxes to be used in defense of liberty:
soap, ballot, jury, and ammo. Please use in that order."
-Ed Howdershelt (Author)
Mal: "Jayne! Your mouth is talking, you'd might wanna look to that."
--Episode #1, "Serenity"