如何解决MySQL的root密码忘记的问题_MySQL
如果哪天你忘记了线上MySQL数据库的root密码,怎么办?
大家往往会想到skip-grant-tables参数,具体步骤如下:
1. 关闭MySQL数据库,因为root密码忘记了,mysqladmin无法使用,此时,只能通过kill pid关闭程序。
在这里,科普一下kill 和kill -9的区别
默认参数下,kill 发送SIGTERM信号给进程,告诉进程,你需要被关闭,请自行停止运行并退出。
kill -9 发送SIGKILL信号给进程,告诉进程,你被终结了,请立刻退出。与SIGTERM相比,这个信号不能被捕获或忽略,同时接收这个信号的进程在收到这个信号时不能执行任何清理
所以,万不得已,不要通过kill -9杀掉进程,这可能导致MySQL数据库的物理结构损坏,无法重新启动。
2. 在my.cnf文件[mysqld]部分添加skip-grant-tables参数
3. 登录数据库,修改root账户的密码
以下是修改root密码的三种方式:
1> mysql> set password for 'root'@'localhost'=password('123'); 无需刷新权限表
2> mysql> update mysql.user set password=password("456") where user="root" and host="localhost";
mysql> flush privileges;
3> # mysqladmin -u root password "123"
4. 关闭数据库,注释掉skip-grant-tables参数,重新启动数据库。
上面这种方式虽然不错,但是有个问题,你必须重启数据库,对于线上环境,这可能是不被允许的。
下面来谈谈另一种方法,有点“黑暗科技”的味道
这个方法利用的是mysql.user表还是MyISAM引擎的特性。
1. 将该实例的mysql.user表copy到另一个实例的目录下,譬如,test数据库的目录下

2. 登录另一个实例数据库,修改上述三个文件的权限,并修改root密码
mysql> select user,host,password from test.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> update test.user set password=password("hello") where user="root" and host="localhost";
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3. 将上述三个文件copy回源数据库

4. 获取mysqld的pid,通过kill -HUP `pidof mysqld`方式让mysqld进程重新加载配置文件

[root@keepalived01 ~]# mysql -phello
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@keepalived01 ~]# kill -HUP 4283
[root@keepalived01 ~]# mysql -phello
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 2528
Server version: 5.6.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
通过上述输出可以看出,kill -HUP之前,直接用密码hello登录被拒绝,kill -HUP之后,就可以直接登录了。
当然,以上方法仅供参考,在生产上慎用,毕竟安全压倒一切,天晓得哪里会出现问题。
以上就是本文的全部内容,希望可以帮助大家解决root密码忘记的困扰,谢谢大家的阅读。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings
