• linux下mysql忘记密码怎么办


    前言

    今天在服务器安装mysql之后,登录发现密码错误,但是我没有设置密码呀,最后百度之后得知,mysql在5.7版本之后会自动创建一个初始密码。
    报错如下:

    [root@mytestlnx02 ~]# mysql -u root -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    

    修改密码

    1. 检查mysql服务是否启动,如果启动,关闭mysql服务

    //查看mysql服务状态
    [root@mytestlnx02 ~]# ps -ef | grep -i mysql
    root     22972     1  0 14:18 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
    mysql    23166 22972  0 14:18 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
    root     23237 21825  0 14:22 pts/0    00:00:00 grep -i mysql
    
    //关闭服务
    [root@mytestlnx02 ~]# service mysql stop
    [root@mytestlnx02 ~]#
    

    2. 修改mysql的配置文件my.cnf

    my.cnf配置文件的位置,一般在/etc/my.cnf,有些版本在/etc/mysql/my.cnf

    在配置文件中,增加2行代码

    [mysqld]
    
    skip-grant-tables
    

    作用是登录mysql的时候跳过密码验证

    然后启动mysql服务,并进入mysql

    [root@mytestlnx02 ~]# service mysqld start
    [root@mytestlnx02 ~]#
    [root@mytestlnx02 ~]# mysql -u root 
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
     
    mysql>
    

    3. 修改密码

    连接mysql这个数据库,修改用户密码

    mysql> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
     
    Database changed
    mysql> update mysql.user set authentication_string=password('root_password') where user='root';
    Query OK, 1 row affected, 1 warning (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 1
     
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> exit
    

    4. 重启mysql服务

    先将之前加在配置文件里面的2句代码注释或删除掉,然后重启mysql服务,就可以使用刚刚设置的密码登录了。

    [root@mytestlnx02 ~]# service mysql start
    [root@mytestlnx02 ~]#
    [root@mytestlnx02 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    

    p.s.

    CentOS上的操作方式有所不同。

    执行修改密码的命令一直报错

    mysql> update user set authentication_string=password('xxxxxxxx') where User='root';       
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('root_password') where User='root'' at line 1
    

    不可能是语法问题,检查了很多遍,最后发现CentOS下应该这样操作:

    查看初始密码

    [root@VM_0_8_centos ~]# grep 'temporary password' /var/log/mysqld.log
    2018-09-26T04:25:54.927944Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: DN34N/=?aIfZ
    

    可以看到初始密码为DN34N/=?aIfZ

    使用初始密码登录

    [root@VM_0_8_centos ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 8
    Server version: 8.0.12 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    

    修改密码

    
    mysql> ALTER USER 'root' IDENTIFIED BY 'xxxxxxxxx';  
    ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxxx';
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye
    

    重启服务就生效了

    [root@VM_0_8_centos ~]# service mysqld stop 
    Redirecting to /bin/systemctl stop  mysqld.service
    [root@VM_0_8_centos ~]# service mysqld start
    Redirecting to /bin/systemctl start  mysqld.service
    
  • 相关阅读:
    Visifire正式版(v1.1)发布
    [转]PSP机能强大!已能模拟运行WINDOWS系统?
    在Silverlight+WCF中应用以角色为基础的安全模式(一)基础篇之角色为基础的安全模式简介 Virus
    C#的加密解密算法,包括Silverlight的MD5算法 Virus
    MMORPG programming in Silverlight Tutorial (10)Implement the sprite’s 2D animation (Part IV)
    Game Script: Rescue Bill Gates
    MMORPG programming in Silverlight Tutorial (9)KeyFrame Animation
    MMORPG programming in Silverlight Tutorial (5)Implement the sprite’s 2D animation (Part II)
    MMORPG programming in Silverlight Tutorial (7)Perfect animation
    MMORPG programming in Silverlight Tutorial (3)Animate the object (Part III)
  • 原文地址:https://www.cnblogs.com/wuotto/p/9682400.html
Copyright © 2020-2023  润新知