Hang's 码场

mysql重置密码

2018/06/17

可登录

mysql 5.7之前

mysql -u root // 直接回车进入mysql控制台
mysql> use mysql;
mysql> update user set password=password('root') where user='root'; # (已创建)
mysql> select user,host,password from user;
mysql> delete from user where password="";
mysql> FLUSH PRIVILEGES;

mysql 5.7

mysql> use mysql;
mysql> update user set authentication_string=password('root') where user='root';
mysql> update user set plugin="mysql_native_password" where User='root';
mysql> update user set password_expired='N' where user='root';
mysql> flush privileges;

mysql 8.0

mysql> use mysql;
mysql> ALTER USER '用户名'@'localhost' IDENTIFIED WITH caching_sha2_password BY '新密码';
mysql> flush privileges;   --刷新MySQL的系统权限相关表

不可登录

方案1

  1. 关闭mysql服务
  2. 使用mysqld_safe启动服务
mysqld_safe --skip-grant-tables &

方案二

  1. 修改配置文件my.cnf 
    vim /etc/mysql/my.cnf
 [mysqld]
 skip-grant-tables
  2. 重启mysql服务 
    service mysqld restart
  3. 用户登录 
    mysql -uroot
  4. 登录后修改用户密码
  5. 退出mysql 
    mysql> quit;
  6. 将最开始修改的配置文件my.cnf中的skip-grant-tables删除
  7. 重启mysql

开放远程登录权限

mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

防火墙开放3306端口

vim /etc/sysconfig/iptables
  -A INPUT -p tcp -m state –state NEW -m tcp –dport 3306 -j ACCEPT
service iptables restart

目录