必须要掌握的MySQL基操和概念
创始人
2025-05-28 06:14:19
作为DBA需要注意的五个原则：
• 只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给
select权限就可以了，不要给⽤户赋予update、insert或者delete权限
• 创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定IP或者内⽹IP段
• 初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤
户，这些⽤户默认没有密码
• 为每个⽤户设置满⾜密码复杂度的密码
• 定期清理不需要的⽤户，回收权限或者删除⽤户
1.登录数据库
[root@hadoop1013 ~]# mysql -h localhost -P 3306 -u root -p
Enter password:
mysql> 2.查看MySQL版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hadoop |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)查看表
mysql> desc books;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| b_id | int | NO | PRI | NULL | auto_increment |
| bt_id | int | NO | | NULL | |
| number | varchar(30) | NO | | NULL | |
| b_name | varchar(50) | NO | | NULL | |
| author | varchar(20) | NO | | NULL | |
| publisher | varchar(100) | NO | | NULL | |
| total | int | NO | | NULL | |
| rental_unit | int | NO | | NULL | |
| over_due_unit | int | NO | | NULL | |
| status | tinyint | NO | | 1 | |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)mysql> show create table books;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| books | CREATE TABLE `books` (`b_id` int NOT NULL AUTO_INCREMENT COMMENT '图书类别ID',`bt_id` int NOT NULL COMMENT '图书类别id',`number` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书的编号',`b_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名称',`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',`publisher` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',`total` int NOT NULL COMMENT '总数量',`rental_unit` int NOT NULL COMMENT '租金/天 多少分钱',`over_due_unit` int NOT NULL COMMENT '逾期租金/天 多少分钱',`status` tinyint NOT NULL DEFAULT '1' COMMENT '图书的状态（true/false）',PRIMARY KEY (`b_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> 查看某个系统变量：SHOW VARIABLES like ‘变量名’
mysql> SHOW VARIABLES like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
SQL的语⾔分类
• DQL（Data Query Language）：数据查询语⾔ select 相关语句
• DML（Data Manipulate Language）：数据操作语⾔ insert 、update、delete 语句
• DDL（Data Define Languge）：数据定义语⾔ create、drop、alter 语句
• TCL（Transaction Control Language）：事务控制语⾔ set autocommit=0、start
transaction、savepoint、commit、rollback
MySQL的数据类型
• 整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
• 浮点数类型：float、double、decimal
• 字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、
tinytext、text、mediumtext、longtext
• ⽇期类型：Date、DateTime、TimeStamp、Time、Year
• 其他数据类型：暂不介绍，⽤的⽐较少
数据类型选择的⼀些建议
• 选⼩不选⼤：⼀般情况下选择可以正确存储数据的最⼩数据类型，越⼩的数据类型通
常更快，占⽤磁盘，内存和CPU缓存更⼩。
• 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型⽐字符操作
代价要⼩得多，因为字符集和校对规则(排序规则)使字符⽐整型⽐较更加复杂。
• 尽量避免NULL：尽量制定列为NOT NULL，除⾮真的需要NULL类型的值，有NULL的
列值会使得索引、索引统计和值⽐较更加复杂。
• 浮点类型的建议统⼀选择decimal
• 记录时间的建议使⽤int或者bigint类型，将时间转换为时间戳格式，如将时间转换为
秒、毫秒，进⾏存储，⽅便⾛索引
Mysql管理员常用的一些命令
Mysql权限验证分为2个阶段：
- 阶段1：连接数据库，此时mysql会根据你的⽤户名及你的来源（ip或者主机名称）判
断是否有权限连接 - 阶段2：对mysql服务器发起请求操作，如create table、select、delete、update、
create index等操作，此时mysql会判断你是否有权限操作这些指令
权限⽣效时间
⽤户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此
时⽣效，所以如果通过直接操作这些表来修改⽤户及权限信息的，需要重启mysql或者执
⾏flush privileges;才可以⽣效。
查看mysql中所有⽤户
用户表存放在mysql库中，要切到mysql库查看：
mysql> use mysql
Database changed
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)mysql> select Host,User from user;
+-----------+------------------+
| Host | User |
+-----------+------------------+
| % | book |
| % | hadoop |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
7 rows in set (0.00 sec)创建用户：
创建用户，有时要限制用户的网段，不限制默认是所有网段，所以在设置用户的时候网段权限要控制好。
#默认所有网段的客户端可连接
mysql> create user hadoop2 identified by 'hadoop2';
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql
mysql> select Host,User from user;
+-----------+------------------+
| Host | User |
+-----------+------------------+
| % | book |
| % | hadoop |
| % | hadoop2 |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
8 rows in set (0.00 sec)
[root@hadoop1013 ~]# mysql -h127.0.0.1 -P 3306 -uhadoop2 -p'hadoop2'
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 26
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.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> create user 'hadoop3'@'localhost' identified by 'hadoop3';
Query OK, 0 rows affected (0.02 sec)#本机测试连接成功
[root@hadoop1013 ~]# mysql -hlocalhost -uhadoop3 -p'hadoop3'
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 39
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.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@hadoop1010 bin]# mysql -h192.168.10.13 -uhadoop3 -p'hadoop3'
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
[root@hadoop1010 bin]#
#客户端版本和服务端版本一致
[root@hadoop1010 bin]# ./mysql -h192.168.10.13 -uhadoop3 -p'hadoop3'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'hadoop3'@'hadoop1010' (using password: YES)#限制网段
mysql> create user 'hadoop3'@'192.168.%' identified by 'hadoop3';;
Query OK, 0 rows affected (0.00 sec)#测试连接成功
[root@hadoop1010 bin]# ./mysql -h192.168.10.13 -uhadoop3 -p'hadoop3'
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 43
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.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> 修改密码【3种⽅式】
注意：通过表的⽅式修改之后，需要执⾏flush privileges;才能对⽤户⽣效。
⽅式1：通过管理员修改密码
SET PASSWORD FOR '⽤户名'@'主机' = PASSWORD('密码');
⽅式2：create user ⽤户名[@主机名] [identi>ied by ‘密码’];
set password = password('密码');
方式3：通过修改mysql.user表修改密码
use mysql;
update user set authentication_string = password('321') where user =
'test1' and host = '%';
flush privileges;
用户授权
grant privileges ON database.table TO 'username'[@'host'] [with grant option]
grant命令说明：
• priveleges (权限列表)，可以是all，表⽰所有权限，也可以是select、update等权
限，多个权限之间⽤逗号分开。
• ON ⽤来指定权限针对哪些库和表，格式为数据库.表名 ，点号前⾯⽤来指定数据库
名，点号后⾯⽤来指定表名，. 表⽰所有数据库所有表。
• TO 表⽰将权限赋予某个⽤户, 格式为username@host，@前⾯为⽤户名，@后⾯接限
制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅。
• WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。
注意：经常有⼈在创建操作⽤户的时候不指定WITH GRANT OPTION选项导致后来该⽤户不能使⽤GRANT命令创建⽤户或者给其它⽤户授权。
备注：可以使⽤GRANT重复给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个select权限，然后又给⽤户添加⼀个insert权限，那么该⽤户就同时拥有了select和insert权限
#非localhost的用户登录，授权不了
[root@hadoop1013 ~]# mysql -h192.168.10.13 -uroot -p'123456'
mysql> grant select(user,host) on mysql.user to test;
ERROR 1142 (42000): GRANT command denied to user 'root'@'hadoop1013' for table 'user'[root@hadoop1013 ~]# mysql -h127.0.0.1 -uroot -p'123456'
mysql> grant select(user,host) on mysql.user to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)查看⽤户有哪些权限
show grants for '⽤户名'[@'主机'];查看当前⽤户的权限
show grants;
撤销⽤户的权限
语法
revoke privileges ON database.table FROM '⽤户名'[@'主机']
[root@hadoop1013 ~]# mysql -h127.0.0.1 -uroot -p'123456'
mysql> revoke select(host) on mysql.user from test2@%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1
mysql> revoke select(host) on mysql.user from 'test2'@'%';
Query OK, 0 rows affected (0.00 sec)[root@hadoop1013 ~]# mysql -h192.168.10.13 -utest2 -p'test2'
mysql> select host,user from user;
ERROR 1046 (3D000): No database selected
mysql> use mysql
Database changed
mysql> desc user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| User | char(32) | NO | PRI | | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)mysql> select user from user;
+------------------+
| user |
+------------------+
| book |
| hadoop |
| hadoop2 |
| root |
| test |
| test2 |
| hadoop3 |
| hadoop3 |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
12 rows in set (0.00 sec)mysql> 删除⽤户【2种⽅式】
方式1：
drop user ‘⽤户名’[@‘主机’]，⽰例：
mysql> drop user 'test2'@'%';
Query OK, 0 rows affected (0.00 sec)
drop的⽅式删除⽤户之后，⽤户下次登录就会起效。
方式2：
通过删除mysql.user表数据的⽅式删除，如下：
delete from user where user='⽤户名' and host='主机';
flush privileges;
注意: 通过表的⽅式删除的，需要调⽤flush privileges;刷新权限信息（权限启动的时
候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）
上一篇：kafka：各组件概念
相关内容
热门资讯
cad打印线条粗细设置 cad...
004-线型（下）打印样式设置和线型文件使用一、线宽设置方法制图规范里边的线宽要求，我们已经定义好，...
阿西吧是什么意思 阿西吧相当于...
即使你没有受到过任何外语培训，你也懂四国语言。汉语：你好英语：Shit韩语：阿西吧（아，씨발! ）日...
长白山自助游攻略 吉林长白山游...
昨天介绍了西坡的景点详细请看链接：一个人的旅行，据说能看到长白山天池全凭运气，您的运气如何？今日介绍...
荼蘼什么意思 岁月缱绻葳蕤生香...
感谢作者【辰夕】的原创独家授权分享编辑整理：【多肉植物百科】百科君坐标：云南 曲靖春而至，季节流转，...
应用未安装解决办法 平板应用未...
---IT小技术，每天Get一个小技能！一、前言描述苹果IPad2居然不能安装怎么办？与此IPad不...