How to admin user in MySQL 5.0

[AnhTai]

Hi all,

I've just installed MySQL 5.0 on my sun box (runing Solaris 10, install
from blastwave). This is my first time with MySQL so I don't have any
exp with it.

I have some troubles as:
- I set password for root account via this command:

# mysql -h atlantis -u root mysql
mysql> update user set password=PASSWO RD('123456') where user = 'root';
mysql> exit

However, I can login again with root account without pass check ????

- Next, I create new user name admin
# mysql -h atlantis -u root
mysql> create user admin identified by 'admin' ;
mysql> create database newdb ;
mysql> GRAND ALL ON newdb.* TO 'admin' IDENTIFIED BY 'admin' ;

Once again, i can't use admin account to login to newdb database??

Please help me to study them.

Thanks in advance.

[Markus Popp]

Re: How to admin user in MySQL 5.0

Hi,
[color=blue]
> # mysql -h atlantis -u root mysql
> mysql> update user set password=PASSWO RD('123456') where user = 'root';
> mysql> exit[/color]

you need to flush the privileges to make MySQL read the grant tables into
memory.
[color=blue]
> mysql> GRAND ALL ON newdb.* TO 'admin' IDENTIFIED BY 'admin' ;[/color]


Don't you mean GRANT?
[color=blue]
> mysql> create user admin identified by 'admin' ;
> mysql> create database newdb ;
> mysql> GRAND ALL ON newdb.* TO 'admin' IDENTIFIED BY 'admin' ;[/color]

You should also specify the host to which the user belongs. For example, if
user admin should be able to log in from localhost, specify the user as
admin@localhost (or admin@'%' for all hosts, admin@'192.168. 0.%' for all
hosts within the network range 192.168.0.x a.s.o.). So you could write

create user admin@localhost identified by 'admin';
create database newdb;
grant all on newdb.* to admin@localhost ; (here you don't need 'identified
by' anymore, because you already specified it in the CREATE USER command)

You should also take care that there are no anonymous users (users without a
name, therefore take a look into the mysql.user table).

Find detailed information here:

http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html

http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html

Markus

[AnhTai]

Re: How to admin user in MySQL 5.0

Oh my god, after i flushed the privileges, I can't login into mysql
again, I get this error message although my password is correct surely.

ERROR 1251: Client does not support authentication protocol requested
by server; consider upgrading MySQL client

????

[Bill Karwin]

Re: How to admin user in MySQL 5.0

AnhTai wrote:[color=blue]
> Oh my god, after i flushed the privileges, I can't login into mysql
> again, I get this error message although my password is correct surely.
>
> ERROR 1251: Client does not support authentication protocol requested
> by server; consider upgrading MySQL client[/color]

Read these pages:

http://dev.mysql.com/doc/refman/5.0/en/php-problems.html

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

PHP does not support the enhanced password encryption format of MySQL
4.1 and later (astonishing but true!). You can run the MySQL server
with the --old-passwords option, and MySQL will use the 4.0-compatible
password encryption that PHP uses. If you do that, I expect you
probably will have to reset passwords again, for them to be encrypted in
the old format.

Regards,
Bill K.

[AnhTai]

Re: How to admin user in MySQL 5.0

However, I'm running MySQL 5.0 now and connect to mysql directly from
console (not used php). I got this error message when i tried:

#mysql -h atlantis -u root -p mysql
password: <my password>

Can I reset password via the other way?

[Bill Karwin]

Re: How to admin user in MySQL 5.0

AnhTai wrote:[color=blue]
> However, I'm running MySQL 5.0 now and connect to mysql directly from
> console (not used php). I got this error message when i tried:
>
> #mysql -h atlantis -u root -p mysql
> password: <my password>
>
> Can I reset password via the other way?[/color]

Have you read this documentation page:

http://dev.mysql.com/doc/refman/5.0/en/passwords.html

It shows several methods for assigning passwords.

Regards,
Bill K.

[AnhTai]

Re: How to admin user in MySQL 5.0

I have read that page and understood how to set password for users.
However, I can't login into mysql console admin via the command:

# mysql -h atlantis -u root -p mysql
password: <my password>

Although I typed my password correctly, i got the error message:

ERROR 1251: Client does not support authentication protocol requested
by server; consider upgrading MySQL client

[Mike A.]

Re: How to admin user in MySQL 5.0

On Sun, 27 Nov 2005 18:20:36 -0800, AnhTai wrote:
[color=blue]
> Oh my god, after i flushed the privileges, I can't login into mysql again,
> I get this error message although my password is correct surely.
>
> ERROR 1251: Client does not support authentication protocol requested by
> server; consider upgrading MySQL client
>
> ????[/color]

I think you need to download the mysql-clients package (you must be using
rpm or some other package manager). You seem to be using a client from a
4.0 package.


Mike A.

MySQL 5.0.15 on FreeBSD 5.4

[Mike A.]

Re: How to admin user in MySQL 5.0

On Sun, 27 Nov 2005 18:20:36 -0800, AnhTai wrote:
[color=blue]
> Oh my god, after i flushed the privileges, I can't login into mysql
> again, I get this error message although my password is correct surely.
>
> ERROR 1251: Client does not support authentication protocol requested by
> server; consider upgrading MySQL client
>
> ????[/color]


I think you need to download the mysql-clients package. You seem to be
using a client from a 4.0 package. The password encryption has changed.


Mike A.

MySQL 5.0.15 on FreeBSD 5.4

[Bill Karwin]

Re: How to admin user in MySQL 5.0

AnhTai wrote:[color=blue]
> I have read that page and understood how to set password for users.
> However, I can't login into mysql console admin via the command:
>
> # mysql -h atlantis -u root -p mysql
> password: <my password>
>
> Although I typed my password correctly, i got the error message:
>
> ERROR 1251: Client does not support authentication protocol requested
> by server; consider upgrading MySQL client[/color]

I see you're specifying the host using the -h flag. Is there a chance
you're running this command on a machine installed with an older version
of MySQL (4.0 or earlier) and that this old client does not support the
authentication protocol requested by the server?

Have you read this page:

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

Can you connect to a database running the command-line tool on the
"atlantis" server? What version of MySQL client is installed there?

Have you tried running the MySQL 5.0 server with the --old-passwords
option, as suggested in that page? You'll have to do this anyway, if
you want to use PHP.

Regards,
Bill K.

[AnhTai]

Re: How to admin user in MySQL 5.0

Thanks Mike and Bill,

Some info about "my lab". I have only one Sun box, running Solaris 10
and installed package mysql5 from blastwave.org.

I connect to mysql directly from command line prompt at Sun box. When
the password of root isn't set, i can connect easily but after I set
password for root account, reboot the server, i get the ERROR 1251 when
I try to connect again.

I see in your replies, possibly, my mysql client is too old and need to
be upgraded. I will install mysqlclient5 from blastwave.org, hope that
"fix" my problem.

Thanks all again.

[Mike A.]

Re: How to admin user in MySQL 5.0

On Tue, 29 Nov 2005 08:08:30 -0800, AnhTai wrote:
[color=blue]
> Thanks Mike and Bill,
>
> Some info about "my lab". I have only one Sun box, running Solaris 10
> and installed package mysql5 from blastwave.org.
>
> I connect to mysql directly from command line prompt at Sun box. When
> the password of root isn't set, i can connect easily but after I set
> password for root account, reboot the server, i get the ERROR 1251 when
> I try to connect again.
>
> I see in your replies, possibly, my mysql client is too old and need to
> be upgraded. I will install mysqlclient5 from blastwave.org, hope that
> "fix" my problem.
>
> Thanks all again.[/color]

There are Solaris (Sparc 32/64 bit and intel 32/64 bit) binaries
available on the MySQL site:

http://dev.mysql.com/downloads/mysql/5.0.html