linux下MySQL忘记root密码#yyds干货盘点#

mysql忘记root密码

如果使用 MySQL 数据库忘记了root账号密码,可以通过调节配置文件,跳过密码的方式登数据库,在数据库里面修改账号密码,一般默认的账号是 root

1.问题现象

在登录数据库过程中,数据库系统如果遇到忘记root密码时,该如何解决?

2.解决方法

2.1.安装mariadb

通过yum安装mariadb-server,默认依赖安装mariadb,linux命令一个是服务端、一个是客户端。linux删除文件命令

yum -y install mariadb mariadb-server

2.2.配置mariadb

#安装完成后首先要把MariaDB服务开启icon是什么意思英语,并设置为开机启动systemctl start mariadb #iconslab 开启服务systemiconsctl enablmysql数据库e mariadb # 设置为开机自启动服务#首次安装需要进行数据库的配置,命令都和mysql的一样mysql_secure_installation

[root@localhost ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables
.. ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyoneto
log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

解释说明:
Enter current password for root (enter for none): # 输入数据库超级管理员root的密码(注意不是系统root的密码),第一次进入还没有设置密码则直接回车
Set root password? [Y/n] # 设置密码,y
New password: # 新密码Re-enter new password: # 再次输入密码
Remove anonymous users? [Y/n] # 移除匿名用户,y
Disallow root login remotely? [Y/n] # 拒绝root远程登录,n,不管y/n,都会拒绝root远程登录
Remove test database and access to it? [Y/n] # 删除test数据库,y:删除。n:不删除,数据库中会有一个test数据库,一般不需要
Reload privilege tables now? [Y/n] # 重新加载权限表,y。或者重启服务也行

2.3.数据库是什么测试是否能够登录成功

出现 MariaDB [(none)]> 就表示已经能够正常登录使用MariaDB数据库了

[root@localhost ~]# mysql -uroot -pEnter password:Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 19Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

3.设置mariadb字符集为utf-8

3.1.修改my.cnf文件

在 [mysqld] 标签下添加init_connect='SET collatio数据库系统的核心是n_connection = utf8_unicode_ci'init_conmysql创建表nect='SET NAMES utf8linux系统'character-siconet-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake

3.2.修改my.cnf.d/client.cnf文件

在 [client] 标签下添加default-character-set=utf8

3.3./my.cnf.d/mysql-clients.cnf 文件

在 [mysql] 标签系统运维工资一般多少下添加default-charactermysql安装配置教程-set=utf8

4.重启服务

systemctl restart mariadb

5.进入mariadb查看字符集

MariaDB [(none)]> sh系统/运维ow variables lik数据库系统工程师e "%character%";show variables lmysql安装ike "%collation%";+--------------------------+--------------icon啥意思--------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8 || chariconslabacter_set_connection | utf8 |linux必学的60个命令|icons chalinux必学的60个命令ract数据库系统er_set_databaselinux | utf8 || character_set_filesystem | bin系统运维工作内容ary || character_set_results | uticon是什么意思英语f8 || character_set_server | utf8 || character_set_system | utf8 || character_icon啥意思sets_dir | /usr/share/mysqlinuxl/charsets/ |+----------------------mysql安装----+----------------------------+8 rows in set (0.00 sec)+----------------------+-----------------+| Variable_name |mysql索引 Value |+----------------------+------数据库系统工程师-----------+| collation_conneclinux操作系统基础知识tion | utf8_unicode_ci || collation_database | utf8_unicode数据库设计_ci || collation_server | utf8_unicod系统运维工作内容e_ci |+----------------------+-----------------+3 rows in set (mysql数据库命令大全0.00 sec)linux必学的60个命令MariaDB [(none)]>

6.忘记mariadb数据库root密码

经测试,此方法在centos7和centos8上都可成icon是什么意思英语功执行恢复

版本:Server verlinux操作系统基础知识sion: 10.3.17-MariaDB MariaDB Servermarlinux是什么操作系统iadb-5.5.68-1.elicon是什么牌子7.x86_64mariadb-server-5.5.68-1.el7.x86_64

解释:在mariadb/mysql中,官方保留了一个特权模式

6.1.停止数据库服务

root@localhost ~]# systemctl stopmysql数据库命令大全 mariadb[root@localhost ~]# ps -ef | g数据库系统工程师rep mariadbroot 26997 370 0 15:14 pts/3 00:00:00 grep --color=auto marialinux必学的60个命令db[root@localhost ~]# netstat -lnpt | grep maria数据库系统概论第五版课后答案db

停止服务后,需要通过ps查看是否保留相关进程,然后使用netstat命令查看端口占用情况,从上面的反馈信息来看,mariadb服务已经被杀的一干二净了,这时候就可以使用特权模式进行密码修改了。

6.2.启动特权模式

[root@localhost ~]# micooysqld_safe --skip-grant-tables &[1] 27480[root@localhost ~]# 211228 15:16:13 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.211228 15:16:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysqllinux是什么操作系统

6.3.出现上面那些后数据库技术直接输出mysql,然后回车,就系统运维工作内容会登录到mariadb数据库

[root@localhost ~]# mysqld_safe --skip-grant-tables &[1] 27480[root@localhost ~]# 211228 15:1mysql增删改查语句6:1linux系统3 mysqld_safemysql索引 Logging ticon是什么牌子o '/var/log/mariadb/mariadb.log'.211228 15:16:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysqlmysql //此处输入后回车,下面内容可能不会显示,但只要进入数据库就是成功Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2数据库技术000, 2018, Oracle, Malinux删除文件命令riaDB Corporation Ab and others.Type 'help;'linux系统 or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]&g数据库系统的核心是t;

6.4.选择mysql数据库

MariaDB [(none)]>linux; use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

6.5.更新root密码

MariaDB [mysql]> update user set password=password('mariadb123') where user='linux删除文件命令root';Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changemysql安装d: 3 Warnicouldings: 0密码改为了mariadb123

6.6.刷新权linux是什么操作系统限并退mysql密码忘记了怎么办出登录

Malinux操作系统基础知识riaDB [mysql]> flush privileges;Query OK, 0 rows affected (0.00 sec)mysqlMariaDB [mysql]> exit;Bye

6.7.停止特权模式

[root@localhost ~]# pkil数据库查询语句l mysqld

6.8.启动mariadb服务

[root@localhost ~]# systemctl sta数据库技术rt mariadb启动成功之后,尝试使用特权模式的登录命令进行登录,然后发现登录失败,说明特权模式已经关闭了[root@localhost ~]# mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

7.使用新密码登录

[root@localhost ~]# mysql -uroot -pEnter password:Welcome to the MariaDB monitor. Commandsmysql增删改查语句 end wit数据库管理系统h ; or \g.Your MariaDB connection id is 5Server version: 5.5.68-Marilinux删除文件命令aDB MariaDB ServerCopyright (icouldc) 2000, 2018, Oracle, MariaDB Corporatilinux系统安装on Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.Maria系统运维工程师DB [(none)]>mysql创建表