• 解决MySQL忘记root密码


    网上有很多关于忘记MySQL root密码的一些文章,里面都有写怎么去解决,但有时觉得写得太恶心,要么一字不漏的抄别人的,要么就说得不清不楚,好了,不吐槽了,以下是解决的整个过程。

    首先我们要知道忘记MySQL root密码后,能否重启mysql,能重启的操作是怎么样的?不能重启的操作又会是怎么样的?

    情况一:(能重启情况下)

    修改my.cnf配置文件,在mysqld栏下添加skip-grant-tables选项,意思是mysqld server启动之后并不使用权限系统(privilege system),也可以理解为跳过授权表。为了安全起见,通常加上skip-networking,mysqld不侦听任何TCP/IP连接请求。

    重启mysqld,然后空密码连接:

    复制代码
    [root ~]$mysql -uroot -S /data/mysql-5.5/mysql.sock
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 3
    Server version: 5.5.40-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    复制代码

    可以看到已经成功登录了,然后修改root密码:

    mysql> update mysql.user set password=password('123456') where user='root';    
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    mysql> flush privileges; 
    Query OK, 0 rows affected (0.01 sec)

    已经成功修改密码了,但还有事情要做,就是把刚刚添加到my.cnf里的skip-grant-tables和skip-networking删除掉或者注释掉。

    情况二:(不能重启mysql的情况)

    如果不能重启,mysql.user 刚好有权限比较低的用户,如果没有,你请神仙来帮你吧,哈哈

    1、为了测试,我自己创建一个用户,可以没什么权限

    mysql> create user xuanzhi@'localhost' identified by '123456';
    Query OK, 0 rows affected (0.00 sec)

    2、进到数据目录下:

    [root mysql-5.5]$ pwd
    /data/mysql-5.5
    [root mysql-5.5]$ cp mysql/user.* test/
    [root mysql-5.5]$ chown mysql.mysql test/user.*

    3、用权限比较小的用户登录:

    复制代码
    [root mysql-5.5]$mysql -uxuanzhi -p123456 -S /data/mysql-5.5/mysql.sock 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 3
    Server version: 5.5.40-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> use test
    Database changed
    mysql> update user set password=password('123123') where user='root';
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    复制代码

    4、把修改后的user.MYD和user.MYI复制到mysql目录下,记得备份之前的文件。

    [root mysql-5.5]$ pwd
    /data/mysql-5.5
    [root mysql-5.5]$ mv mysql/user.MYD mysql/user.MYD.bak
    [root mysql-5.5]$ mv mysql/user.MYI mysql/user.MYI.bak
    [root mysql-5.5]$ cp test/user.MY* mysql/
    [root mysql-5.5]$ chown mysql:mysql  mysql/user.*

    5.查找mysql进程号,并且发送SIGHUP信号,重新加载权限表。(有时加载一次不行的时候,再加载多一次@。@)

    复制代码
    [root mysql]$ pgrep -n mysql
    23166
    [root mysql]$ kill -SIGHUP 23166
    [root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root mysql]$ kill -SIGHUP 23166
    [root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 5
    Server version: 5.5.40-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    复制代码

    不重启的第二种方法

    1、创建新的数据目录,并给原来user相应的权限,忘记密码对应的实例它的user是mysql,所以把权限给mysql用户

    [root data]$ mkdir -pv /dbdata/datadir/
    mkdir: 已创建目录 "/dbdata"
    mkdir: 已创建目录 "/dbdata/datadir/"
    [root data]$ chown  -R mysql:mysql /dbdata/datadir/

    2、执行初始化操作:(报错了)

    复制代码
    [root scripts]$ pwd
    /usr/local/mysql-5.5.40/scripts
    [root scripts]$ ./mysql_install_db --datadir=/dbdata/datadir/ --user=mysql2
    
    FATAL ERROR: Could not find ./bin/my_print_defaults
    
    If you compiled from source, you need to run 'make install' to
    copy the software into the correct location ready for operation.
    
    If you are using a binary release, you must either be at the top
    level of the extracted archive, or pass the --basedir option
    pointing to that location.
    复制代码

    解决方法:

    复制代码
    [root scripts]$ /usr/local/mysql-5.6.10/scripts/mysql_install_db --datadir=/dbdata/datadir/ --user=mysql --datadir=/dbdata/datadir/ --basedir=/usr/local/mysql-5.6.10/
    Installing MySQL system tables...
    141210 16:09:24 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
    OK
    Filling help tables...
    141210 16:09:24 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
    OK
    复制代码

    3、启动一个新的进程,这里要注意一下port,sock文件,还有pid文件,这都是新的,user还是忘记密码实例的user,而不是忘记密码对应的那个数据库实例的,这里我们不需要用到InnoDB引擎,设置默认引擎为MyISAM:

    复制代码
    [root ~]$  /usr/local/mysql-5.6.10/bin/mysqld_safe --datadir=/dbdata/datadir --plugin-dir=/usr/local/mysql-5.6.10/lib/plugin/  --skip-innodb 
    > --default-storage-engine=myisam --socket=/dbdata/datadir/mysql2.sock --user=mysql --port=3305 --log-error=/dbdata/datadir/error2.log --pid-file=/data/mysql-5.6/mysql.pid &
    [1] 21204
    [root ~]$ 141210 16:56:11 mysqld_safe Logging to '/dbdata/datadir/error2.log'. 141210 16:56:11 mysqld_safe Starting mysqld daemon with databases from /dbdata/datadir
    复制代码

    4、登录新启动的mysql实例,此时密码为空密码:

    复制代码
    root datadir]$ /usr/local/mysql-5.6.10/bin/mysql -S /dbdata/datadir/mysql2.sock 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 1
    Server version: 5.5.30-log Source distribution
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> flush tables;
    Query OK, 0 rows affected (0.00 sec)
    复制代码

    修改root密码:

    复制代码
    mysql> select user, host, password from user where user like 'root';
    +------+-----------------------+----------+
    | user | host                  | password |
    +------+-----------------------+----------+
    | root | localhost             |          |
    | root | localhost.localdomain |          |
    | root | 127.0.0.1             |          |
    | root | ::1                   |          |
    +------+-----------------------+----------+
    4 rows in set (0.02 sec)
    
    mysql> update mysql.user set password=password('654321') where user='root'; 
    Query OK, 4 rows affected (0.03 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    复制代码

    5、拷备新的user表到忘记密码的实例数据库的mysql目录下

    复制代码
    [root mysql]$ pwd
    /dbdata/datadir/mysql
    [root mysql]$ cp user.* /data/mysql-5.6/mysql/
    cp:是否覆盖"/data/mysql-5.6/mysql/user.frm"? y
    cp:是否覆盖"/data/mysql-5.6/mysql/user.MYD"? y
    cp:是否覆盖"/data/mysql-5.6/mysql/user.MYI"? y
    [root mysql]$ chown -R mysql5.6:mysql5.6 /data/mysql-5.6/
    [root mysql]$ chmod 660 /data/mysql-5.6/mysql/user.*
    复制代码

    6、我们需要到mysqld发送一个sighup信号,MySQL响应这个信号加载授权表,刷新表,日志,线程缓存。
    如果是单个MySQL实例,可以用这样的方法去重新加载

    [root ~]$ kill -1 $(/sbin/pidof mysqld)

    如果是多个MySQL实例在一台服务器上的话,就要注意点了,可以通过这样的方法找到pid,我旧实例的端口是3306:

    复制代码
    [root mysql-5.6.10]$ netstat -nltp
    Active Internet connections (only servers)
    Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
    tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      8414/mysqld         
    tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      6430/mysqld         
    tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1144/sshd           
          
    tcp        0      0 :::3310                     :::*                        LISTEN      17151/mysqld        
    tcp        0      0 :::22                       :::*                        LISTEN      1144/sshd           
    tcp        0      0 ::1:631                     :::*                        LISTEN      1109/cupsd          
    tcp        0      0 :::3306                     :::*                        LISTEN      2091/mysqld
    [root mysql-5.6.10]$ kill -1 2091
    复制代码

    有时kill -1一次不行,再执行一次就可以了:

    复制代码
    [root mysql-5.6.10]$ kill -1 2091
    [root mysql-5.6.10]$ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock 
    Warning: Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root mysql-5.6.10]$ kill -1 2091
    [root mysql-5.6.10]$ /usr/local/mysql-5.6.10/bin/mysql -uroot -p654321 -S /data/mysql-5.6/mysql.sock 
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 13
    Server version: 5.6.10-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    复制代码

    OK,已经成功登录了,如果有更多好的方法,我们可以再一起讨论下

    总结:

    1)第一种方法简单,但需要重启MySQL,重启会影响线上业务,一般不建议重启

    2)第二种方法比较好,不用重启MySQL实例,修改密码,只修改root用户的,而且其它保持不变

    3)第三种方法也不需要重启,但是新的user表里,只有root一个用户,如果之前服务器还存在别的用户及权限,这就比较麻烦了

     参考资料:http://www.percona.com/blog/2014/12/10/recover-mysql-root-password-without-restarting-mysql-no-downtime/

    PS:本人也是参考别人的博客做的,但我没有照搬别人的东西,太恶心了,希望大家有自己的风格。^.^

  • 相关阅读:
    PAT (Basic Level) Practise 1013 数素数
    PAT (Basic Level) Practise 1014 福尔摩斯的约会
    codeforces 814B.An express train to reveries 解题报告
    KMP算法
    rsync工具
    codeforces 777C.Alyona and Spreadsheet 解题报告
    codeforces 798C.Mike and gcd problem 解题报告
    nginx + tomcat多实例
    MongoDB副本集
    指针的艺术(转载)
  • 原文地址:https://www.cnblogs.com/hmysql/p/5105005.html
Copyright © 2020-2023  润新知