How to change USERNAME And PASSWORD of MySQL?

Question

I have installed MySQL Server 5.0 on my machine (Windows-7 OS).

My existing USERNAME And PASSWORD for MySQL is root. I want to change both USERNAME And PASSWORD.

Many people have asked this question before but none was useful for me. Can anybody
tell me HOW and from WHERE can I change it?

This would need a query to be solved so i think the question is in rifght place? — Mad Dog Tannen, Dec 13 '13 at 09:08
You should have ask the question to Google : http://dev.mysql.com/doc/refman/5.0/fr/passwords.html — Thibault, Dec 13 '13 at 09:10

score 41 · Accepted Answer · edited Dec 13 '13 at 09:15

41

Just execute this query from mysql console:

 UPDATE mysql.user SET user='newusername',
 password=PASSWORD('newpassword') WHERE user='root';
 FLUSH PRIVILEGES;

edited Dec 13 '13 at 09:15

Mad Dog Tannen

7,129
5
31
55

answered Dec 13 '13 at 09:12

Ghigo

2,312
1
18
19

Nice this one works excellent – Mad Dog Tannen Dec 13 '13 at 09:15
2

Renaming the root is debatable. Here's a great answer on another stackexchange community : http://security.stackexchange.com/questions/6785/beneficial-to-rename-mysql-root-user – foobar Dec 13 '13 at 09:19
6

the password column is now called authentication_string so authentication_string=PASSWORD('newpassword') – Braden Holt Dec 09 '17 at 21:01
you also may need to restart mysql server after this – Braden Holt Dec 09 '17 at 21:07
No you don't need to, because FLUSH PRIVILEGES; command will update users properly – Ghigo Dec 12 '17 at 14:22

score 8 · Answer 2 · edited May 29 '17 at 00:47

Instructions

Click the Windows "Start" button and type "cmd" in the search text box. Press Enter to open the Windows command line.
Type "mysql" and press Enter to start the MySQL command line utility. Using the MySQL command line, you can update the tables on the database.
Type the following SQL code to update the root user:
UPDATE mysql.user SET user='newuser' WHERE User = 'root';
Change newuser with the value you want to use in place of root
Type the following SQL code to change the default user's password:
UPDATE mysql.user SET authentication_string = password('pass') WHERE User = 'newuser';
Replace pass with the new password, and replace newuser with the name of the user you set up in the previous step.

To change UserName and Password from mysql console

Nice step by step! Upvote! – Mad Dog Tannen Dec 13 '13 at 09:16 — Mad Dog Tannen, Dec 13 '13 at 09:16
@KayNelson Thank You, this encourages me – Amarnath Balasubramanian Dec 13 '13 at 09:18 — Amarnath Balasubramanian, Dec 13 '13 at 09:18

score 4 · Answer 3 · answered Dec 13 '13 at 09:11

4

USE THIS CMD IN MYSQL CONSOLE OR CMD

STEP 1: mysql> use mysql;

STEP 2: CREATE USER 'username'@'localhost' IDENTIFIED BY 'mypass';

answered Dec 13 '13 at 09:11

Nambi

11,944
3
37
49

score 3 · Answer 4 · edited May 23 '17 at 12:02

I'll suggest to stay with a root user and change the password if you want. Having a root user is convenient and renaming it is debatable (see this conversation). Plus, calling it 'root' is not that "unsecured" if you respect good practices (such as IP/hostname restriction).

Conclusion : change the root password and secure access to your server if you still feel unconfortable. Then, create another user with USERNAME as the login and PASSWORD as the password.

To change the root password (in a MySQL Console) :

UPDATE mysql.user SET Password=PASSWORD('YOURPASSWORDHERE') WHERE User='root'; FLUSH PRIVILEGES;
To create a new user : http://dev.mysql.com/doc/refman/5.0/fr/create-user.html
If your root user has a lot of privileges you don't want to replicate manually to the newly created user, then using a stored procedure could be a good solution : Copy user privileges between databases on the same server

score 3 · Answer 5 · answered Dec 13 '13 at 09:16

3

There are special MySQL commands:

answered Dec 13 '13 at 09:16

Devart

119,203
23
166
186

score 1 · Answer 6 · answered Dec 13 '13 at 09:12

I not sure if you can change the username, you could drop it and create antoher one instead but not sure if update is possible.

Login to the commandline promt.

Set the mysql database

USE mysql;

Create the new user first if root is your only username

CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'newpass';

Then drop the old root

DROP user root;

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

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

How to change USERNAME And PASSWORD of MySQL?

6 Answers6

Linked