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 | +------+-----------+-------------------------------------------+