{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2025-04-12 13:11:40","updateBy":"admin","updateTime":"2025-04-12 13:11:40","remark":null,"id":107,"articleTitle":"MySQL（十五）重置root密码","articleUrl":"mysql_reset_password","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"0","articleSummary":"我们无法通过常规方式登录MySQL来修改root密码，因为需要密码才能登录。但可以通过特殊方法跳过权限验证来重置密码。必须要停止mysql才能操作，所以需要在业务较少的时候操作。","articleContent":"## 修改密码\n\n### 1.常规ALTER USER的方式\n\n```sql\n-- 修改本地root密码\nALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePass123!';\n\n-- 修改远程用户密码\nALTER USER 'app_user'@'%' IDENTIFIED BY 'App@SecurePwd456';\n```\n\n**高级选项**\n\n```sql\n-- 修改密码并指定认证插件\nALTER USER 'admin'@'localhost'\nIDENTIFIED WITH caching_sha2_password \nBY 'Admin!Pass789'\nREPLACE 'OldPass123';  -- 要求提供旧密码\n\n-- 设置密码过期策略\nALTER USER 'user'@'localhost' \nIDENTIFIED BY 'TempP@ss' \nPASSWORD EXPIRE INTERVAL 90 DAY;\n```\n\n### 2.兼容方式SET命令\n\n```sql\n-- MySQL 5.7.5及以下版本\nSET PASSWORD FOR 'user'@'host' = PASSWORD('new_password');\n\n-- MySQL 8.0+ 版本（PASSWORD()函数已移除）\nSET PASSWORD FOR 'user'@'host' = 'new_password';\n\n-- 修改当前用户密码，无需指定用户名\nSET PASSWORD = 'CurrentUserNewPass!';\n```\n\n### 3.直接修改系统表\n\n```sql\n-- 所有MySQL版本通用（需刷新权限）\nUPDATE mysql.user \nSET authentication_string = PASSWORD('NewPass123!') \nWHERE User = 'root' AND Host = 'localhost';\n\nFLUSH PRIVILEGES;\n```\n\n**MySQL 8.0+ 注意事项**：\n\n```sql\n-- 8.0+ 必须使用此语法\nUPDATE mysql.user \nSET authentication_string = 'NewPass123!' \nWHERE User = 'root' AND Host = 'localhost';\n\nFLUSH PRIVILEGES;\n```\n\n### 4.命令行工具（无需登录）\n\n```bash\n# 交互式修改\nmysqladmin -u root -p password\n\n# 非交互式修改（脚本中使用）\nmysqladmin -u root -p'old_password' password 'new_password'\n```\n\n## 忘记root密码重置\n\n我们无法通过常规方式登录MySQL来修改root密码，因为需要密码才能登录。但可以通过特殊方法跳过权限验证来重置密码。必须要停止mysql才能操作，所以需要在业务较少的时候操作。\n\n### 1. 停止MySQL服务\n\n```bash\nsystemctl stop mysqld.service\n```\n\n### 2. 以跳过授权表启动MySQL\n\n```bash\n# 在启动命令中加入--skip-grant-tables选项跳过权限认证\nmysqld_safe --skip-grant-tables &\n\n# 在配置文件中加入skip-grant-tables并启动mysql，以此方式启动命令中无需加入--skip-grant-tables选项\nvim my.cnf\n\n[mysqld]\nskip-grant-tables\n```\n\n### 3. 登录并刷新权限\n\n```bash\n# 登录mysql无需输入密码\nmysql -u root\n```\n\n```sql\nALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';\nFLUSH PRIVILEGES；\n```\n\n### 4. 重启MySQL服务\n\n```bash\n# 去掉--skip-grant-tables选项，或者把配置文件中新增的选项去掉\nsystemctl restart mysqld.service\n```\n\n","categoryId":4,"viewCount":124,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}