MySQL stores credentials in the user table in the mysql system database. Operations that assign or modify passwords are permitted only to users with the CREATE USER privilege, or, alternatively, privileges for the mysql database ( INSERT privilege to create new accounts, UPDATE privilege to modify existing accounts).
mysql> CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'password'; This command will allow the user with username local_user to access the MySQL instance from the local machine (localhost) and prevent the user from accessing it directly from any other machine.
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
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.
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';
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'
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
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:
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
sudo /usr/local/mysql/bin/mysql -u root
UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
FLUSH PRIVILEGES;
\q
For MySQL version 5.7 and up
'System Prefrences' > MySQL > 'Stop MySQL Server'
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
sudo /usr/local/mysql/bin/mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';
FLUSH PRIVILEGES;
\q
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With