First, check MySQL version:
SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name | Value |
+-------------------------+-------------------------+
| innodb_version | 5.7.25 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.25-0ubuntu0.16.04.2 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-------------------------+
There is no ‘password’ column in ‘user’ table in version 5.7 anymore, there is ‘authentication_string’ instead:
use mysql;
describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | 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 | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
There can be different passwords for different hosts:
select user, host, authentication_string from user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *EA591225EC0C668101C3F9C02DF9B6BB065A670D |
| root | london | *7BE5E534902659E09D2BFBC983AA7A991346D654 |
| root | 127.0.0.1 | *7BE5E534902659E09D2BFBC983AA7A991346D654 |
| root | ::1 | *7BE5E534902659E09D2BFBC983AA7A991346D654 |
+------+-----------+-------------------------------------------+
to change the password for all the hosts:
UPDATE user SET authentication_string=password('********') WHERE user='root';
FLUSH PRIVILEGES;
for ‘localhost’ only:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('********');
FLUSH PRIVILEGES;
On a fresh MySQL installation I set the root password by running mysql as root and then executing the following command:
sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
FLUSH PRIVILEGES;
mysql -u root -ptest
select user, host, authentication_string from user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
I’ve had difficulties in the past with changing the root password in MySQL, mainly because i forgot to execute the SQL statement FLUSH PRIVILEGES;