Ubuntu 下如何修改 MySQL 密码

hedeqiang

发布于 2019-12-18 11:21:59

3.9K0

发布于 2019-12-18 11:21:59

Ubuntu 下如何修改 MySQL 密码呢？搜集大致有以下几种方式、应该是比较常用的方法

登录控制台

mysql -uroot -p

输入密码，进入 MySQL

use mysql;
update user set password=password('123456') where user='root' and host='localhost';

你会发现并没有成功，会报出如下错误

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

就是说字段 password并不存在，，原来 MySQL5.7 以后并没有 password 字段了使用 show create table user 查看

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'

新版的 MySQL 使用 authentication_string 来代替 password ,ok,既然知道了字段，那么接下来就好修改密码了

update user set authentication_string=PASSWORD("这里输入你要改的密码") where user='root';

flush privileges;

ok,这样就成功修改了 MySQL 的密码了。

上面的方法前提是你记得原来的密码可以登录到 MySQL 中：但是如果忘记密码怎么修改呢？

MySQL 5.7 忘记密码、重新修改密码

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf 文件在[mysqld]区域添加 skip-grant-tables 配置

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf;

之后重启 MySQL 服务

sudo service mysql restart

接下来就可以免密码登录到 MySQL 中了.

mysql -uroot -p 
# 出现输入密码、直接回车即可

接下来就可以修改密码了，其实方法和上面的一样

use mysql;
update user set authentication_string=PASSWORD("这里输入你要改的密码") where user='root';

flush privileges;

修改完记得将 skip-grant-tables 去掉。之后重启 MySQL 服务即可。

MySQL 8 修改密码

同理编辑配置文件添加 skip-grant-tables ,重启 MySQL 将密码置空

use mysql;
update user set authentication_string='' where user='root';
exit;

ALTER USER "root"@"localhost" IDENTIFIED  BY "你的新密码";

将 skip-grant-tables 去掉，重启 MySQL。

mysql -uroot -p; 
# 出现输入密码、直接回车即可
use mysql;
ALTER USER "root"@"localhost" IDENTIFIED  BY "你的新密码";

MySQL 8 ,密码规则使用强校验，密码过于简单可能会导致报错。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

数据库

sql

登录后参与评论

0 条评论

热度