CentOS7中MariaDB重置密码

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 虚拟机里的MariaDB忘记密码了,连接不上,只能重置MariaDB的密码,步骤参考网络[root@localhost ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'l...

虚拟机里的MariaDB忘记密码了,连接不上,只能重置MariaDB的密码,步骤参考网络

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

环境

系统版本

[root@localhost ~]# cat /etc/redhat-release         # 查看系统版本
CentOS Linux release 7.4.1708 (Core) 

MariaDB版本

Server version: 5.5.56-MariaDB MariaDB Server

重置密码的步骤

1.重启,以安全模式进行MariaDB

[root@localhost ~]# systemctl status mariadb            # 查看MariaDB数据库状态
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2018-07-12 17:42:01 CST; 4min 37s ago
  Process: 1043 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 989 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 1042 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─1042 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─1141 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin ...

Jul 12 17:41:56 localhost.localdomain systemd[1]: Starting MariaDB database server...
Jul 12 17:41:57 localhost.localdomain mariadb-prepare-db-dir[989]: Database MariaDB is probably initialized in /...ne.
Jul 12 17:41:57 localhost.localdomain mariadb-prepare-db-dir[989]: If this is not the case, make sure the /var/l...ir.
Jul 12 17:41:58 localhost.localdomain mysqld_safe[1042]: 180712 17:41:58 mysqld_safe Logging to '/var/lib/mysql...rr'.
Jul 12 17:41:58 localhost.localdomain mysqld_safe[1042]: 180712 17:41:58 mysqld_safe Starting mysqld daemon wit...ysql
Jul 12 17:42:01 localhost.localdomain systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost ~]# systemctl stop mariadb              # 停止MariaDB数据库
[root@localhost ~]# mysqld_safe --skip-grant-tables     # 安全模式进行MariaDB,并且跳过授权表
180706 17:51:16 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
180706 17:51:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

2.新建一个ssh连接窗口,进入bash,连接密码库

[root@localhost ~]# mysql -uroot -p                 # 连接数据库
Enter password:                                     # 不要输入密码,直接回车
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3.重置密码,退出MariaDB数据库

MariaDB [(none)]> 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
MariaDB [mysql]> update user set password=PASSWORD("admin@1234") where user='root';         # 重置数据库密码
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [mysql]> flush privileges;                              # 刷新权限
Query OK, 0 rows affected (0.02 sec)

MariaDB [mysql]> quit
Bye

4.重启MariaDB数据库,使用密码进入MariaDB数据库

[root@localhost ~]# systemctl restart mariadb                   # 重启MariaDB数据库
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| netperformance     |
| performance_schema |
| test               |
+--------------------+
15 rows in set (0.01 sec)

MariaDB [(none)]> 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
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [mysql]> select User,Host from user;
+---------+-----------------------+
| User    | Host                  |
+---------+-----------------------+
| root    | %                     |
| root    | 127.0.0.1             |
| root    | ::1                   |
|         | localhost             |
| root    | localhost             |
|         | localhost.localdomain |
| root    | localhost.localdomain |
+---------+-----------------------+
8 rows in set (0.03 sec)

MariaDB [mysql]> exit
Bye

5.查看MariaDB进程是否正常

[root@localhost ~]# ps aux | grep mysql                     # 查看MariaDB数据库的进程
root       6122  0.0  0.1 113260  1592 pts/0    S+   17:51   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql      6206  0.4  9.2 922568 92556 pts/0    Sl+  17:51   0:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=localhost.localdomain.pid
root       6295  0.0  0.0 112660   976 pts/2    R+   17:55   0:00 grep --color=auto mysql
[root@localhost ~]# ss -tunlp | grep 3306                   # 查看MariaDB数据库的端口
tcp    LISTEN     0      50        *:3306                  *:*                   users:(("mysqld",pid=1141,fd=14))
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
centos编译安装mariadb
一般我不太愿意用mysql,那个玩意,有的时候不太友好。 我还是比较喜欢mariadb。
83 0
|
4月前
|
关系型数据库 MySQL Linux
Linux(centos 7.5)服务器安装MariaDB
Linux(centos 7.5)服务器安装MariaDB
94 0
|
5月前
|
关系型数据库 MySQL Linux
如何在CentOS 7服务器上配置使用MariaDB的Galera集群:
如何在CentOS 7服务器上配置使用MariaDB的Galera集群:
|
8月前
|
关系型数据库 Linux 网络安全
【主从数据库?】centos7,快速配置Mariadb主从
【主从数据库?】centos7,快速配置Mariadb主从
184 0
|
10月前
|
机器学习/深度学习 Oracle 安全
MariaDB Centos7 下安装MariaDB
MariaDB Centos7 下安装MariaDB
256 0
QGS
|
存储 Oracle 关系型数据库
CentOS 7 安装MariaDB10.3
记CentOS 7 安装MariaDB10.3
QGS
539 0
  CentOS 7 安装MariaDB10.3
|
关系型数据库 MySQL Linux
CentOS 7.x 卸载删除MariaDB,重新安装
CentOS 7.x 卸载删除MariaDB,重新安装
187 0
|
SQL 关系型数据库 MySQL
CentOS 7.2搭建MariaDB数据库服务器应用与管理
CentOS 7.2搭建MariaDB数据库服务器应用与管理
228 0
CentOS 7.2搭建MariaDB数据库服务器应用与管理
LXJ
|
关系型数据库 Linux
centos7安装mariadb脚本
centos7安装mariadb脚本
LXJ
116 0
|
关系型数据库 Linux 网络安全