博主熬夜整理出来的MySQL之原理，操作，和如何对数据库当中的数据的增删改查，复制数据库和表，以及抽取数据，和在忘记数据库密码的时候，修改密码。

文章目录

一：数据库1.1数据库概述1.2：非关系型数据库1.21：非关系型数据库存储方式1.22：非关系型数据库(统称NOSQL)：1.23：非关系型数据库的优点： 二：MySQL2.1：登录数据库2.2：查看数据库2.4：查看数据库中的数据表信息3、显示数据表的结构 三、SQL语句3.1、SQL语言分类3.2、DDL语句3.2：管理表中的数据3.3：向数据表中插入新的数据记录（DML）3.4：修改、更新数据表中的数据记录3.5：设置用户权限的命令3.6：在数据库表中删除指定的数据记录3.7：DQL是数据查询语句，只有SELECT3.8：清空表3.9：临时表3.10：临时表3.11：create方法3.12：LIKE方法 四：数据库用户授权4.1：查看用户的权限4.2：撤销用户权限的命令 五：密码如果是遗忘密码怎么修改？

一：数据库

1.1数据库概述

数据库作用：数据持久化保存，高可靠，高可用，数据的快速提取。数据库存储类型：关系型数据库 非关系型数据库。 数据保存在内存优点：存取速度快缺点：数据不能永久保存

数据保存在文件

优点：数据永久保存缺点：速度比内存操作慢，频繁的IO操作。2）查询数据不方便

数据保存在数据库

数据永久保存使用SQL语句，查询方便效率高。管理数据方便

1.2：非关系型数据库

1.21：非关系型数据库存储方式

键-值方式(key–value),以键为依据存储、删、改数据列存储(Column-oriented), 将相关的数据存储在列族中文档的方式，数据库由-系列数据项组成，每个数据项都有名称与对应的值

1.22：非关系型数据库(统称NOSQL)：

redis(内存数据库/缓存数据库)： K-V键值对 key-value 变量名-值memcache (内存数据库/缓存数据库) ：K-V键值对

1.23：非关系型数据库的优点：

数据库高并发读写的需求对海量数据高效率存储与访问数据库的高扩展性与高可用性的需求

二：MySQL

2.1：登录数据库

注意MySQL的初始密码为空，通过mysql -u root -p进入回车，输入两次新密码即可设置新密码。登录数据库格式：mysql -h主机地址 -u用户名 -p用户密码注：u与root可以不加空格 [root@server3 ~]# mysql -u root -p123123 mysql: [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 5 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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>

修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

2.2：查看数据库

mysql> SHOW DATABASES; #大小写皆可，分号不能忘 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)

2.4：查看数据库中的数据表信息

use 数据库名 #切换到库 show tables 查看库中的表 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> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | ……省略内容 | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)

Mysql数据库的数据文件都存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于储存数据表文件。每一个数据表对应为三个文件，后缀名分别为’’.frm “.myd” 和“.myi"。当然也有少数以opt、csm、csv、ibd结尾的。

3、显示数据表的结构

describe [数据库名.]表名 例如： describe mysql.time_zone; 或者 先切换到mysql库，再显示表结构。 mysql> describe mysql.time_zone; +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | Use_leap_seconds | enum('Y','N') | NO | | N | | +------------------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)

三、SQL语句

3.1、SQL语言分类

数据库目前标准的指令是SQL。SQL是Structured Querl Language的缩写，即结构化查询语言。它是1974年有Boyce和Chamberlin提出来的，1975-1979年，IBM公司研制的关系数据库管理系统原型System R实现了这种语言，经过多年的发展，SQL语言得到了广泛的应用. 1、SQL语言

是Structured Query Language的缩写，即结构化查询语言是关系型数据库的标准语言用于维护管理数据库,如数据查询、数据更新、访问控制、对象管理等功能 2、SQL分类DDL:数据定义语言DML:数据操纵语言DQL:数据查询语言DCL:数据控制语言

3.2、DDL语句

DDL语句用于创建数据库对象，如库、表、索引等

1、使用DDL语句新建库、表 创建数据库

mysql> create database student; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student | #已经创建成功 | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use student; Database changed mysql> show tables; Empty set (0.00 sec)

刚创建的数据库是空的，其中不包含任何表，在/usr/local/mysql/data目录下会自动生成一个与新建的库名相同的空文件。

创建数据表

CREATE TABLE 表名（字段定义...）

create table 表名（字段01名称 字段01类型 字段01约束，字段02名 字段02类型 字段02约束）存储引擎 字符集

【多个字段用，隔开】

字段01名称：属性名称，自定义

字段01类型：

int （5） 整型 00000-99999double 浮点型decimal（5,2） 有效数字是5位，小数点后面保留2位100.00 088.45fioat 单精度浮点 4字节varchar（50） 可变长度字符串char（10） 固定长度字符串

字段01约束：

1、主键 PK primary key 2、唯一约束 UK unique key 3、外键约束 FK foreign key 4、非空约束 NN not null 5、检查约束 CK check 6、默认值约束 default

存数引擎：myisam innodb

字符集：UTF-8

mysql> create table info (id int(3) not null primary key,name char(10) not null,address varchar(50) default 'nj'); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | info | +-------------------+ 1 row in set (0.00 sec) #查看表结构 mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | | address | varchar(50) | YES | | nj | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

删除指定的数据库：DROP DATABASE 数据库名

mysql> create database tom; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student | | sys | | tom | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database tom; Query OK, 0 rows affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student | #tom已删除 | sys | +--------------------+ 5 rows in set (0.00 sec)

3.2：管理表中的数据

DML语句用于对表中的数据进行管理

包括的操作

INSERT:插入新数据UPDATE：更新原有数据DELETE：删除不不需要的数据

3.3：向数据表中插入新的数据记录（DML）

#第一种插入记录的方法： insert into info (id,name,address) values (2,'lisi','上海'),(3,'wangwu','北京'); #第二种插入记录方式： insert into info values (4,'yiyi','北京'); mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(3) | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | | address | varchar(50) | YES | | nj | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into info (id,name,address) values (2,'lisi','上海'),(3,'tom','杭州'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from info; +----+------+---------+ | id | name | address | +----+------+---------+ | 2 | lisi | 上海 | | 3 | tom | 杭州 | +----+------+---------+ 2 rows in set (0.00 sec)

#第二种方法

mysql> insert into info values (1,'潘玉彬','广州'); Query OK, 1 row affected (0.01 sec) mysql> select * from info; +----+-----------+---------+ | id | name | address | +----+-----------+---------+ | 1 | 潘玉彬 | 广州 | | 2 | lisi | 上海 | | 3 | tom | 杭州 | +----+-----------+---------+ 3 rows in set (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | info | | tom | +-------------------+ 2 rows in set (0.00 sec) mysql> describe tom; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(3) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | | address | varchar(50) | YES | | 未知 | | +---------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into tom (id,name,score,address) values (1,'pyb',90.2,'hangzhou'); mysql> select * from tom; +----+------+-------+----------+ | id | name | score | address | +----+------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | +----+------+-------+----------+ 这边不加字段名称也可直接添加字段的值（默认是所有字段） mysql> insert into tom values (2,'lisi',88,'shanghai'); mysql> select * from tom; +----+------+-------+----------+ | id | name | score | address | +----+------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 88.00 | shanghai | +----+------+-------+----------+ 2 rows in set (0.00 sec) #default定义未知 mysql> insert into tom (name,score,address) values ('luoli',77,'shanxi'),('meimei',55,default); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tom; +----+--------+-------+----------+ | id | name | score | address | +----+--------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 88.00 | shanghai | | 3 | luoli | 77.00 | shanxi | | 4 | meimei | 55.00 | 未知 | +----+--------+-------+----------+ mysql> insert into tom values (5,'wudi',75,'suzhou'); Query OK, 1 row affected (0.01 sec) mysql> select * from tom; +----+--------+-------+----------+ | id | name | score | address | +----+--------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 88.00 | shanghai | | 3 | luoli | 77.00 | shanxi | | 4 | meimei | 55.00 | 未知 | | 5 | wudi | 75.00 | suzhou | +----+--------+-------+----------+ 5 rows in set (0.00 sec) #筛选大于75的信息 mysql> select * from tom where score > 75; +----+-------+-------+----------+ | id | name | score | address | +----+-------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 88.00 | shanghai | | 3 | luoli | 77.00 | shanxi | +----+-------+-------+----------+ 3 rows in set (0.00 sec) #将tom表中大于80的数据，放入新建的jerry表 mysql> create table jerry as select * from tom where score > 80; Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from jerry; +----+------+-------+----------+ | id | name | score | address | +----+------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 88.00 | shanghai | +----+------+-------+----------+ 2 rows in set (0.00 sec) #查看jerry表数据 已经克隆过来了 mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | jerry | | tom | +-------------------+ 2 rows in set (0.00 sec)

3.4：修改、更新数据表中的数据记录

UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE条件表达式 #将tom表中lisi的分数替换为55 mysql> update tom set score=55 where name='lisi'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tom; +----+--------+-------+----------+ | id | name | score | address | +----+--------+-------+----------+ | 1 | pyb | 90.20 | hangzhou | | 2 | lisi | 55.00 | shanghai | | 3 | luoli | 77.00 | shanxi | | 4 | meimei | 55.00 | 未知 | | 5 | wudi | 75.00 | suzhou | +----+--------+-------+----------+ 5 rows in set (0.00 sec) 通过修改数据库的方式实现修改数据库管理员密码 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> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | …… | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec) mysql> select user from user; +---------------+ | user | +---------------+ | bbsuser | | root | | bbsuser | | mysql.session | | mysql.sys | | root | | root | +---------------+ 7 rows in set (0.06 sec)

3.5：设置用户权限的命令

若是用户已存在，则会更改用户密码若是用户不存在，则是新建用户GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码′ ]

#创建用户

mysql> grant all privileges on *.* to 'liu'@'loaclhost' identified by 'abc123'; Query OK, 0 rows affected, 1 warning (0.06 sec) #mysql数据库的内置库提取到内存里 mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) #登录用户成功 mysql> select user from user; +---------------+ | user | +---------------+ | bbsuser | | root | | liu | | bbsuser | | mysql.session | | mysql.sys | | root | | root | +---------------+ 8 rows in set (0.00 sec) mysql> grant all privileges on *.* to 'liu'@'localhost' identified by '123123'; Query OK, 0 rows affected, 1 warning (0.00 sec) #mysql数据库的内置库提取到内存里 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #登录用户成功 [root@server3 ~]# mysql -u liu -p123123 mysql: [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 25 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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.

3.6：在数据库表中删除指定的数据记录

DELETE FPOM 表名 WHERE 条件表达式 mysql> select * from tom; +----+--------+-------+----------+ | id | name | score | address | +----+--------+-------+----------+ | 1 | pyb | 55.00 | hangzhou | | 2 | lisi | 55.00 | shanghai | | 3 | luoli | 77.00 | shanxi | | 4 | meimei | 55.00 | 未知 | | 5 | wudi | 75.00 | suzhou | +----+--------+-------+----------+ 5 rows in set (0.00 sec) mysql> delete from tom where score <=60; Query OK, 3 rows affected (0.01 sec) mysql> select * from tom; +----+-------+-------+---------+ | id | name | score | address | +----+-------+-------+---------+ | 3 | luoli | 77.00 | shanxi | | 5 | wudi | 75.00 | suzhou | +----+-------+-------+---------+ 2 rows in set (0.00 sec) #真谨慎操作，删除全部内容 mysql> delete from tom; Query OK, 0 rows affected (0.00 sec） mysql> select * from tom; Empty set (0.00 sec)

3.7：DQL是数据查询语句，只有SELECT

用于从数据表中查找符合条件的数据记录 查询时不指定条件

#SELECT 字段名1，字段名2....FROM表名 WHERE 条件表达式 mysql> select name,score from jerry where address='hangzhou'; +------+-------+ | name | score | +------+-------+ | pyb | 90.20 | +------+-------+ 1 row in set (0.00 sec)

3.8：清空表

DELETE FROM tablenameTRUNCATE TABLE tablenametruncate与drop区别

drop是删除表 truncate 是情况里面的数据

mysql> truncate table jerry; Query OK, 0 rows affected (0.01 sec) mysql> select * from jerry; Empty set (0.00 sec) mysql>

3.9：临时表

临时建立的表，用于保存一些临时数据，不会长期存在

mysql> create temporary table cdc (id int(3) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8; Query OK, 0 rows affected (0.00 sec) mysql> describe cdc; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(3) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | hobby | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into cdc (name,hobby) values ('boy','dog'); Query OK, 1 row affected (0.00 sec) mysql> select * from cdc; +----+------+-------+ | id | name | hobby | +----+------+-------+ | 1 | boy | dog | +----+------+-------+ 1 row in set (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | info | | jerry | | tom | +-------------------+ 3 rows in set (0.00 sec) #不在硬盘上，在内存上

测试临时表断开连接会自动删除

[root@server3 ~]# mysql -u liu -p123321 ……省略 mysql> use cdc; ERROR 1049 (42000): Unknown database 'cdc' mysql> select * from cdc; ERROR 1046 (3D000): No database selected

3.10：临时表

3.11：create方法

#查看hei表信息结构 mysql> select * from hei; +----+--------+-------+-----------+ | id | name | score | address | +----+--------+-------+-----------+ | 1 | lisi | 44.50 | doongjing | | 2 | wangwu | 77.00 | xiamen | +----+--------+-------+-----------+ 2 rows in set (0.00 sec) #查询的语句生成一张新表hh mysql> create table hh as select * from hei; #已经生成 mysql> select * from xiao; +----+------+-------+ | id | name | hobby | +----+------+-------+ | 1 | q | run | | 2 | x | fly | +----+------+-------+ 2 rows in set (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | hai | | info | | jerry | | tom | | xiao | +-------------------+ 5 rows in set (0.00 sec) mysql> select xiao; ERROR 1054 (42S22): Unknown column 'xiao' in 'field list' mysql> select * from xiao; +----+------+-------+ | id | name | hobby | +----+------+-------+ | 1 | q | run | | 2 | x | fly | +----+------+-------+ 2 rows in set (0.00 sec) mysql> create table hai as select * from xiao; mysql> select * from hai; +----+------+-------+ | id | name | hobby | +----+------+-------+ | 1 | q | run | | 2 | x | fly | +----+------+-------+ 2 rows in set (0.00 sec) #查看hai表结构 可以看出跟xiao表一样 mysql> desc hai; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | NO | | 0 | | | name | varchar(10) | NO | | NULL | | | hobby | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

克隆也可分为两部分：第一步复制结构生成表 （结构没有数据）第二步 导入数据

3.12：LIKE方法

从hei表完整复制结构生成gg表

mysql> create table jerry like tom; Query OK, 0 rows affected (0.00 sec) #查看所有表 mysql> show tables; +---------------+ | Tables_in_mei | +---------------+ | jerry | | tom | +---------------+ 2 rows in set (0.00 sec) #新表jerry是没有数据的 mysql> select * from jerry; Empty set (0.00 sec) 先查看hei表完整结构，根据结构创建名字不同结构相同的表gg，再导入数据

四：数据库用户授权

DCL语句设置用户权限（用户不存在时；则会新建用户）

若用户已存在，则更改用户密码若用户不存在，则新建用户 GRANT 权限列表 ON 数据库名，表名 TO 用户名@来源地址 [IDENTILED BY '密码’] GRANT 权限列表 ON 数据库名，表名 TO 用户名@来源地址 [IDENTILED BY '密码’] 1 # *.*：所有数据库所有表 mysql> grant all on *.* to 'tom'@'locahost' identified by 'abc123'; Query OK, 0 rows affected, 1 warning (0.00 sec) ##mysql数据库的内置库提取到内存里。 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #all privileges：所有权限，%：所有终端' mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123'; Query OK, 0 rows affected, 1 warning (0.00 sec)

4.1：查看用户的权限

SHOW GRANTS FOR 用户名@来源地址

查看用户权限

mysql> show grants for 'root'@'%'; +-------------------------------------------+ | Grants for root@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' | # 拥有所有权限 +-------------------------------------------+ 1 row in set (0.00 sec)

查看数据库有哪些用户

mysql> select user from mysql.user; +---------------+ | user | +---------------+ | bbsuser | | root | | liu | | tom | | bbsuser | | liu | | mysql.session | | mysql.sys | | root | | root | +---------------+ 10 rows in set (0.00 sec)

4.2：撤销用户权限的命令

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址 mysql> revoke all privileges on *.* from 'root'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'root'@'%'; +----------------------------------+ | Grants for root@% | +----------------------------------+ | GRANT USAGE ON *.* TO 'root'@'%' | +----------------------------------+ 1 row in set (0.00 sec)

五：密码如果是遗忘密码怎么修改？

[root@server3 ~]# vi /etc/my.cnf [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock server-id = 1 skip-grant-tables #添加此行，跳过表的加载 skip-grant-tables [root@server3 ~]# systemctl restart mysqld.service #重启服务 [root@server3 ~]# netstat -anpt | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1282/mysqld [root@server3 ~]# mysql -u root -p Enter password: #回车直接登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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账户也同样 [root@server3 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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@server3 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20 Source distribution Coperight (c) 2000, 2017, 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> update mysql.user set authentication_string=password('123321') where user='liu'; Query OK, 2 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 1 mysql> quit Bye [root@server3 ~]# vi /etc/my.cnf #取消注释 [root@server3 ~]# systemctl restart mysqld.service [root@server3 ~]# mysql -u liu -p123321 mysql: [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 4 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, 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> 下面的文件为MySQL中储存用户和密码以及其他信息的地方，替换当中的字段，重启服务就可以使用新密码登录

#截取一段做分析 mysql> select * from user\G; *************************** 1. row *************************** Host: localhost User: root #用户名 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 #密码字段 password_expired: N password_last_changed: 2020-10-16 16:05:55 password_lifetime: NULL account_locked: N

如果本篇文章对您有用的话，欢迎点赞，评论，收藏呀！！