MySQL从零到有2：常用命令和系统管理

常用命令

打开CMD命令窗口（记住使用管理员身份运行），我们就可以在命令窗口中做一些MySQL的命令操作了：

服务启动和关闭

这个我们上一个章节使用过了：net start mysql，这是服务命令，不是语句命令，后面不需要加引号

net stop mysql --代表停止MySQL服务
net start mysq --代表启动MySQL服务

MySQL登录命令

 这个我们前面一章我们也了解过，使用过了：mysql -h 主机名 -P 端口 -u 用户名 -p，前面一章有对-h，-u，-p 作过解释，这边 -P 是指Port 端口。

mysql -h localhost -P 3306 -u root -p

 默认情况下登陆本机，其实端口和主机都可以省略的：

mysql -u 用户名 -p

查看数据库版本 

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30    |
+-----------+
1 row in set (0.00 sec)

显示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

使用某个库：use关键字

mysql> use mysql;
Database changed

显示所选库中的所有表信息

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

从其他数据库中查询表

show tables from databasename

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| 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> show create table columns_priv;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| columns_priv | CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表结构 

以表格的信息输出表的结构，一目了然

mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(32)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

查看当前所在库

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

查看所有库引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

创建用户 

create user user[@host] [identified by 'password'];

方括号中的内容是可以省略的，比如设置默认主机%，这样允许用户从其他主机登录，比如不设置密码，这样默认无需密码登录。

我们创建一个用户并登录试试，红色字体是关键语句：

mysql> create user user1@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

D:\Setup\mysql-5.7.30-winx64\bin>mysql -u user1 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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.

修改用户密码：4种方式

登录MySQL并修改

格式如下： set password for 用户名@主机 = password('新密码');

mysql> set password for root@localhost = password('Helenlyn');

使用 mysqladmin 操作

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

mysqladmin -u root -p Helenlyn password Helenlyn1

使用脚本语句修改 user 表

打开mysql数据库，直接更新user表的authentication_string字段。这边localhost可以用%代替。

需要注意：flush privileges是刷新权限，必须执行，才能对用户生效；

我们用的是5.7.30版本，所以密码字段是authentication_string，5.7之前的版本一般来说是password。

mysql> use mysql; 
mysql> update user set authentication_string=password('1234567') where user='user1' and host='localhost'; 
mysql> flush privileges;

直接设置密码

创建用户的时候同时设置密码，或者登录的用户修改自己的密码，这个在前面部署服务和创建用户的时候都有说明过了：

create user user[@host] [identified by 'pwd'];
或 
set password = password('pwd');

  

删除用户：2种方式

直接drop

格式如下：drop user 'username'[@'host']

mysql> drop user user1@localhost;
Query OK, 0 rows affected (0.00 sec)

 删除完，用户重启就生效了，可以创建个用户试试。

删除user表的用户

同样的，执行完需要执行 flush privileges刷新权限才会对用户生效，格式如下：

delete from user where user='用户名' and host='主机';

flush privileges; 

mysql> use mysql;
Database changed

mysql> delete from user where user='user1' and host='localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

给用户授权

创建用户之后，需要给不同的用户分别授权，这样才能根据不同用户的角色来界定职责和管理范畴。

grant privilegesCate on database.table to 'uname'[@'host'] [with grant option]

说明：

1、grant 是授权的关键字。

2、privilegesCate 代表劝降类型，包含：all privileges：所有权限；select：读权限；delete：删除权限；update：更新权限；create：创建权限；drop：删除数据库、数据表权限。

3、on 用来表实授权的范围具体到那些库和表，看示例中，格式为数据库.表名 ，点号前面指的是数据库名，后面指的是表名，如果想要授权所有库表，可以用 *.* 表示。

4、to 表示权限授予的具体用户, 格式：uname@host，uname即用户名，host指的是主机，可以是IP、域名等，如果不做host限制，我们之前的文章也说过了，使用%表示。

5、with grant option 这个选项表示该用户可以将自己拥有的权限授权给别人。如果不加这一项，用户只有使用权限的权力，而没有使用grant命令创建并给其它用户授权的。

6、权限取并集，如果对用户授权了 select+ update，后面又对用户授予了 select + delete, 那么用户同时拥有 select + update + delete的权限。

mysql> create user brand identified by '123456';
Query OK, 0 rows affected

mysql> grant all on *.* to brand@'%';
Query OK, 0 rows affected

说明：这边是创建了一个用户并把所有的权限都授予他。如果要做限制，可以把 *.* 改成具体的数据库或者表，也可以吧%改成具体的host。如果要开放授权能力，可以在末尾加上 with grant option ，这样就基本等同于dba的权限了。

查看用户的权限

show grants for 'uname'[@'host']

这边需注意，主机可以省略，默认值为%，测试下：

mysql> show grants for brand;
+---------------------------------------------------------------------------------------------------------------+
| Grants for brand@%                                                                                            |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set

当前登录者的权限，如：

mysql> show grants
;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set

取消用户的权限

revoke privilegesCate on database.table FROM 'uname'[@'host'];

可以先查show grants，再撤销revoke，再查show grants，来理解整个过程，示例：

mysql> show grants for brand;
+---------------------------------------------------------------------------------------------------------------+
| Grants for brand@%                                                                                            |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> revoke delete on *.* from brand;
Query OK, 0 rows affected

mysql> show grants for brand;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for brand@%                                                                                                                                                                                                                                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

上面我们先查询用户的权限，是all的权限，然后撤销他的delete的权限，再查询，发现它确实少了delete的选项，这个符合我们的预期，说明授权操作没有问题。

关于授权的一些注意点

1、权限最小化原则，有需要再去开通，如果只有查询，就只开通select权限即可

2、读写用户分离，读用户只需给select权限，不要赋予update、insert、delete甚至drop之类的权限

3、尽量设置复杂密码或者让使用者重置密码

4、没有特定情况，一般不需要授予 WITH GRANT OPTION

5、定期清理垃圾用户，回收权限或者删除用户

总结

还有很多常用的命令操作，后续再融入到他章节 一 一 解读。命令是用户进入正式数据库编程之前应知应会的部分，所以需要熟练掌握。

使用过程中需要注意以下细节：

命令的方式操作用户和权限不需要刷新，下一次登录就会自动生效，在mysql库表进行修改的，需要调用flush privileges; 刷新一下，才会在下次登录生效。

文中所说的host部分可以省略，默认值为%，表示所有机器，这个文中反复说过了。

mysql中用户名、密码和权限的信息存储在库名为mysql的user表中，可以打开看看。