How to change MySQL password under Ubuntu
How to modify MySQL password under Ubuntu? There are roughly the following ways to collect, which should be more commonly used methods
Log in to the console
mysql -uroot -p
Enter the password and enter MySQL
use mysql;
update user set password=password('123456') where user='root' and host='localhost';
You will find that it is not successful, and the following error will be reported
ERROR 1054(42S22): Unknown column 'password'in'field list'
That is to say, the field password does not exist. It turns out that there is no password field after MySQL5.7. Use show create table user to view
user | CREATE TABLE `user`(`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',`User`char(32) COLLATE utf8_bin NOT NULL DEFAULT '',`Select_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Insert_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Update_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Delete_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Drop_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Reload_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Shutdown_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Process_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`File_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Grant_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`References_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Index_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Alter_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Show_db_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Super_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_tmp_table_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Lock_tables_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Execute_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Repl_slave_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Repl_client_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_view_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Show_view_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_routine_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Alter_routine_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_user_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Event_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Trigger_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`Create_tablespace_priv`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`ssl_type`enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',`ssl_cipher` blob NOT NULL,`x509_issuer` blob NOT NULL,`x509_subject` blob NOT NULL,`max_questions`int(11) unsigned NOT NULL DEFAULT '0',`max_updates`int(11) unsigned NOT NULL DEFAULT '0',`max_connections`int(11) unsigned NOT NULL DEFAULT '0',`max_user_connections`int(11) unsigned NOT NULL DEFAULT '0',`plugin`char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',`authentication_string` text COLLATE utf8_bin,`password_expired`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',`password_last_changed` timestamp NULL DEFAULT NULL,`password_lifetime`smallint(5) unsigned DEFAULT NULL,`account_locked`enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY(`Host`,`User`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
The new version of MySQL uses authentication_string instead of password, ok, now that the field is known, then it is time to change the password
update user set authentication_string=PASSWORD("Enter the password you want to change here") where user='root';
flush privileges;
ok, so the MySQL password has been successfully changed.
The premise of the above method is that you can log in to MySQL if you remember the original password: but how to change it if you forget the password?
MySQL 5.7 Forgot password, change password again####
Edit the /etc/mysql/mysql.conf.d/mysqld.cnf file and add the skip-grant-tables configuration in the [mysqld] area
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf;
Then restart the MySQL service
sudo service mysql restart
Then you can log in to MySQL without a password.
mysql -uroot -p
# Enter the password and press Enter when it appears
Then you can change the password, in fact, the method is the same as above
use mysql;
update user set authentication_string=PASSWORD("Enter the password you want to change here") where user='root';
flush privileges;
Remember to remove skip-grant-tables after modification. Then restart the MySQL service.
MySQL 8 change password####
Similarly edit the configuration file to add skip-grant-tables, restart MySQL and leave the password blank
use mysql;
update user set authentication_string='' where user='root';
exit;
ALTER USER "root"@"localhost" IDENTIFIED BY "Your new password";
Remove skip-grant-tables and restart MySQL.
mysql -uroot -p;
# Enter the password and press Enter when it appears
use mysql;
ALTER USER "root"@"localhost" IDENTIFIED BY "Your new password";
MySQL 8, the password rules use strong verification, too simple password may cause errors.
Recommended Posts