Mysql Intro - Change Root Password

 

1. clean previous mysql

 

sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean

 

2. for mysql 5.7 or above

The following command is ok

mysql -u root -p

But the following command is not ok.

mysql -u root -p 'rootpassword'

 

3. mysql change root password

264down voteaccepted

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.

First choose the database:

mysql>use mysql;

And then show the tables:

mysql>show tables;

You will find the user table, now let's see its fields:

mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

Surprise！There is no field named 'password', the password field is named ' authentication_string'. So, just do this:

update user set authentication_string=password('1111') where user='root';

Now, everything will be ok.

Compared to MySQL 5.6, the changes are quite extensive: What’s New in MySQL 5.7

share|improve this answer

edited Jul 26 '16 at 10:45

answered Jun 29 '15 at 17:47 nodejh 2,8341512

9

You sir made my day. – Bisonfan95 Nov 17 '15 at 18:56

2

This method works even with the --skip-grant-tables restriction when starting mysqld. Since you are an anonymous user you can't change passwords with: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password' ALTER USER 'root'@'*' IDENTIFIED BY 'new-password' It'll give you an anonymous user permissions error. – Bisonfan95 Nov 17 '15 at 19:06

After ruining my whole day...... – Sandeep Singh Rana Nov 19 '15 at 11:36

Thanks man :) Saved me a lot of time – minion Jan 14 '16 at 16:40

thank you for your answer.. – Young Jan 28 '16 at 23:11

 |  show 10 more comments

up vote14down vote

One pitfall I fell into is there is no password field now, it has been renamed so:

update user set password=PASSWORD("YOURPASSWORDHERE") where user='root';

Should now be:

update user set authentication_string=password('YOURPASSWORDHERE') where user='root';

share|improve this answer

edited Aug 7 '16 at 22:49 Alexander Wigmore 2,46322251

answered Aug 7 '16 at 20:49 John C 14112

Thanks for update answer b – Juneyoung Oh Aug 8 '16 at 2:33

This is the correct answer for me! – lean Mar 20 at 4:12

add a comment | 

up vote12down vote

Use the ALTER USER command rather than trying to update a USER row. Keep in mind that there may be more than one 'root' user, because user entities are qualified also by the machine from which they connect

https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

For example.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password' 
ALTER USER 'root'@'*' IDENTIFIED BY 'new-password' 

share|improve this answer

answered Jun 7 '15 at 11:38

community wiki O. Jones

2

For MySQL 5.7.9, I have to ALTER USER to work properly. Thanks. – shin Oct 30 '15 at 0:09

we've needed to user \g followed by the above command. Using MySql 5.7.12. ty – bobsouza Apr 20 '16 at 21:46

this answer only worked for me, on Ubuntu 16.04, mysql 5.7.19; – Mahdi Parsa Jul 31 at 18:49

Did they change the wildcard from @ to *? Grr... now I have more questions than when I came here. – pguardiario Aug 16 at 5:29

add a comment | 

up vote2down vote

It only worked with me when I "flushed" after the commands mentioned here. Here's the full list of commands I used:

Previous answers might not work for later mysql versions. Try these steps if previous answers did not work for you:

1- Click on the wamp icon > mysql > mysql console

2- write following commands, one by one

use mysql;
update user set authentication_string=password('your_password') where user='root';
FLUSH PRIVILEGES;
quit

share|improve this answer

answered Apr 30 '16 at 18:24 Amgad 767

add a comment | 

up vote2down vote

Thank you for your help. Just in case if people are still having problems, try this.

For MySQL version 5.6 and under

Have you forgotten your Mac OS X 'ROOT' password  and need to reset it?  Follow these 4 simple steps:

1.  Stop the mysqld server.  Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2.  Start the server in safe mode with privilege bypass      From a terminal:      sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3.  In a new terminal window:      sudo /usr/local/mysql/bin/mysql -u root      UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';      FLUSH PRIVILEGES;      \q
4.  Stop the mysqld server again and restart it in normal mode.

For MySQL version 5.7 and up

1.  Stop the mysqld server.  Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
2.  Start the server in safe mode with privilege bypass      From a terminal:      sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3.  In a new terminal window:            sudo /usr/local/mysql/bin/mysql -u root      UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';      FLUSH PRIVILEGES;      \q      
4.  Stop the mysqld server again and restart it in normal mode.

share|improve this answer

edited Sep 19 '16 at 10:10 Mbrevda 1,8321323

answered May 21 '16 at 7:28 eli 262

add a comment | 

up vote1down vote

This error happens if you did not set the password on install, in this case the mysql using unix-socket plugin.

But if delete the plugin link from settings (table mysql.user) will other problem. This does not fix the problem and creates another problem. To fix the deleted link and set password ("PWD") do:

1) Run with --skip-grant-tables as said above.

If it doesnt works then add the string skip-grant-tables in section [mysqld] of /etc/mysql/mysql.conf.d/mysqld.cnf. Then do sudo service mysql restart.

2) Run mysql -u root -p, then (change "PWD"):

update mysql.user 
    set authentication_string=PASSWORD("PWD"), plugin="mysql_native_password" 
    where User='root' and Host='localhost';    
flush privileges;

quit

then sudo service mysql restart. Check: mysql -u root -p.

Before restart remove that string from file mysqld.cnf, if you set it there.

share|improve this answer

edited Apr 19 at 14:43

answered Apr 19 at 14:32 bl79 537

add a comment | 

up vote0down vote

For this problem, I used a simple and rude method, rename the field name to password, the reason for this is that I use the mac navicat premium software in the visual operation error: Unknown column 'password' in 'field List ', the software itself uses password so that I can not easily operate. Therefore, I root into the database command line, run Use mysql; And then modify the field name: ALTER TABLE user CHANGE authentication_string password text; After all normal.