Setting MySQL username and password

[desiana]

Hi, I am new to mySQL and currently I am able to go to myySQL without having to key in username and password. What I want to know is how to set mySQL so that everytime users want to use it, they have to enter username and password ? Thanks. Please reply ASAP

 

[Broccoli2]

You need to alter the entries in the

user

table of the

mysql

database - in particular, you need to get rid of the entry which has a '

%

' in the

User

column and a blank password.

You can change this table directly, or you can use the

GRANT

command - see the online manual for help at

http://www.mysql.com/doc

Note 1: make sure you have at least one entry in the User column so that you can get back in!

Note 2: you'll need to issue a '

FLUSH PRIVILEGES

' command before your changes will take effect (or restart the server)

-Rob

 

[desiana]

Now I have done what you have told, but the problem is I cannot go in to mysql anymore even though I still have one user in the user table. In which table is the user coloumn located ?

 

[desiana]

I have set the username and password in the user table already, but still I can get into mysql without supplying username and password, why ?

 

[Broccoli2]

You might also want to check (and remove if necessary) records from the other privilege tables, particularly

Host

- if you see any rows with wildcards ('

%

') or '

localhost

' in there you should delete them.

Have you issued a '

FLUSH PRIVILEGES

' command?

The privilege system is described on the mysql site -

http://www.mysql.com/doc

-Rob

 

[desiana]

What does this error :"ERROR 1130: Host 'localhost' is not allowed to connect to this MySQL server" mean ?

 

[desiana]

In my user table, I have 2 records : root (pwd=one) and kun (pwd=two). But when I try to execute this command :
mysql -u root -p
Enter password : *****
this error will be prompted : "ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)" What might be the problems ?

 

[Broccoli2]

To connect from '

localhost

' - ie the same machine that is running the server, the '

Host

' column in the '

user

' table must specifically have an entry for '

localhost

' - the wildcard '

%

' doesn't apply. This means if you have two accounts that you want to enable on the server machine and everywhere else, you need four rows - two for each account.

Let me summarise:
1. You can get into the database to perform surgery on the tables by shutting down the server and restarting it with the

--skip-grant-tables

option, then you can connect without a username or password.

2.

Host

table
No entries needed - if you have no other concerns you can delete all the rows here.

3.

User

table

mysql> SELECT Host, User, Password FROM user;

+-----------+-----------+------------------+
| Host      | User      | Password         |
+-----------+-----------+------------------+
| localhost | root      | 27427c4764cd551d |
| %         | root      | 27427c4764cd551d |
| localhost | kun       | 287368c779c87hk3 |
| %         | kun       | 287368c779c87hk3 |
+-----------+-----------+------------------+

Note that what is listed under 'Password' is encrypted - you add new entries to the

user

table using

INSERT INTO user(Host,User,Password)
VALUES ('localhost','root',

PASSWORD(

'mypassword'

)

);

- '

PASSWORD()

' is the inbuilt encryption function.

4. Once your

Host

and

User

tables look like this, shutdown and restart the server again (obviously without the

--skip-grant-tables

option this time).

5. To connect from the same machine that is running the server, use

mysql -uroot -p

or from another computer use

mysql -uroot -p --host=192.168.0.1

(replace with the IP address of your MySQL server)

If you are still having trouble, please post your

host

and

user

tables here, along with the command you are using to connect.

Hope this helps

-Rob

 

[desiana]

what is the command to shutdown and restart the mysql server ? my user table looks like this :
+------------------+------------------+
| user | host | password |
+------+-----------+------------------+
| root | localhost | 5c1fb21a20d15f82 |
| root | % | 6f0631ce5f462279 |
+------+-----------+------------------+
2 rows in set (0.11 sec)
while my host table is empty.
This is the steps I took everytime I want to use mysql :
1. Go to MS-DOS prompt
2. Change the directory to c:\mysql\bin3. Type mysqld (I think it's to activate the server, isn't it)
4. Type mysql (I always can get in to mysql by typing this, which I don't want. I want everybody who wants to access to mysql must supply a password)
5. I am in mysql already.
If I type mysql -u root -p after typing mysqld, the error "ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)" will still appear.
One more thing : After typing mysqld, I will get this message :
"Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add for example,
innodb_data_file_path = /mysql/data/ibdata1:20M
More information on setting the parameters you find in the
manual." What should I do ? Thanks for your help

 

[yuyup]

I encountered this error and warning when trying to connect to MySQL :
"011026 11:24:41 Warning: listen() on TCP/IP failed with error 10055"
"ERROR 2003: Can't connect to MySQL server on 'localhost' (10055)"
What might be the problems ?

 

[Broccoli2]

desiana,

To take down the MySQL server I suggest you use:

mysqladmin shutdown

More information about mysqladmin is available at

http://www.mysql.com/doc/m/y/mysqladmin.html

To start the server, you are correct in using

mysqld

. However, you only need to do this once - the server then continues to run in the background. You can then connect/disconnect through the

mysql

console (or other methods) as much as you like, without having to run

mysqld

again.

Do you have a particular reason for using InnoDB tables? If you are just trying to get started, maybe the straightforward ISAM format would be best.

Have you modified mysql.ini/my.cnf in any way? What platform are you running the server on (Linux? WinNT/2K?)

yuyup,

It sounds like you are not running a MySQL server on the machine that the client (

mysql

) is looking at. If you are trying to connect from the same machine that the server is running on, make sure

mysqld

is running (how you do this depends on the platform). If you are trying to connect to a MySQL server on another machine, make sure you are using

mysql --host=192.168.0.1

(or the IP address of your server)

-Rob