MySQL全面瓦解2:常用命令和系統管理

常用命令

打開CMD命令窗口(記住使用管理員身份運行),我們就可以在命令窗口中做一些MySQL的命令操作了:

  

服務啟動和關閉

這個我們上一個章節使用過了:net start mysql,這是服務命令,不是語句命令,後面不需要加引號

1 net stop mysql --代表停止MySQL服務
2 net start mysq --代表啟動MySQL服務

MySQL登錄命令

 這個我們前面一章我們也了解過,使用過了:mysql -h 主機名 -P 端口 -u 用戶名 -p,前面一章有對-h,-u,-p 作過解釋,這邊 -P 是指Port 端口。

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

 

 默認情況下登陸本機,其實端口和主機都可以省略的:

1 mysql -u 用戶名 -p

查看數據庫版本 

1 mysql> select version();
2 +-----------+
3 | version() |
4 +-----------+
5 | 5.7.30    |
6 +-----------+
7 1 row in set (0.00 sec)

 

顯示所有數據庫

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9 +--------------------+
10 4 rows in set (0.00 sec)

使用某個庫:use關鍵字

1 mysql> use mysql;
2 Database changed

顯示所選庫中的所有表信息

 1 mysql> show tables;
 2 +---------------------------+
 3 | Tables_in_mysql           |
 4 +---------------------------+
 5 | columns_priv              |
 6 | db                        |
 7 | engine_cost               |
 8 | event                     |
 9 | func                      |
10 | general_log               |
11 | gtid_executed             |
12 | help_category             |
13 | help_keyword              |
14 | help_relation             |
15 | help_topic                |
16 | innodb_index_stats        |
17 | innodb_table_stats        |
18 | ndb_binlog_index          |
19 | plugin                    |
20 | proc                      |
21 | procs_priv                |
22 | proxies_priv              |
23 | server_cost               |
24 | servers                   |
25 | slave_master_info         |
26 | slave_relay_log_info      |
27 | slave_worker_info         |
28 | slow_log                  |
29 | tables_priv               |
30 | time_zone                 |
31 | time_zone_leap_second     |
32 | time_zone_name            |
33 | time_zone_transition      |
34 | time_zone_transition_type |
35 | user                      |
36 +---------------------------+
37 31 rows in set (0.00 sec)

 

其他數據庫中查詢表

show tables from databasename

 1 mysql> show tables from mysql;
 2 +---------------------------+
 3 | Tables_in_mysql           |
 4 +---------------------------+
 5 | columns_priv              |
 6 | db                        |
 7 | engine_cost               |
 8 | event                     |
 9 | func                      |
10 | general_log               |
11 | gtid_executed             |
12 | help_category             |
13 | help_keyword              |
14 | help_relation             |
15 | help_topic                |
16 | innodb_index_stats        |
17 | innodb_table_stats        |
18 | ndb_binlog_index          |
19 | plugin                    |
20 | proc                      |
21 | procs_priv                |
22 | proxies_priv              |
23 | server_cost               |
24 | servers                   |
25 | slave_master_info         |
26 | slave_relay_log_info      |
27 | slave_worker_info         |
28 | slow_log                  |
29 | tables_priv               |
30 | time_zone                 |
31 | time_zone_leap_second     |
32 | time_zone_name            |
33 | time_zone_transition      |
34 | time_zone_transition_type |
35 | user                      |
36 +---------------------------+
37 31 rows in set (0.00 sec)

 

輸出表的創建語句腳本 

用於獲取腳本進行表結構遷移之類的

 1 mysql> show create table columns_priv;
 2 +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 3 | Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
 4 +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 5 | columns_priv | CREATE TABLE `columns_priv` (
 6   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 7   `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 8   `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
 9   `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
10   `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
11   `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
12   `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
13   PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
14 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' |
15 +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16 1 row in set (0.00 sec)

 

查看錶結構 

以表格的信息輸出表的結構,一目了然

 1 mysql> desc columns_priv;
 2 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
 3 | Field       | Type                                         | Null | Key | Default           | Extra                       |
 4 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
 5 | Host        | char(60)                                     | NO   | PRI |                   |                             |
 6 | Db          | char(64)                                     | NO   | PRI |                   |                             |
 7 | User        | char(32)                                     | NO   | PRI |                   |                             |
 8 | Table_name  | char(64)                                     | NO   | PRI |                   |                             |
 9 | Column_name | char(64)                                     | NO   | PRI |                   |                             |
10 | Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
11 | Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
12 +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
13 7 rows in set (0.00 sec)

 

查看當前所在庫

1 mysql> select database();
2 +------------+
3 | database() |
4 +------------+
5 | mysql      |
6 +------------+
7 1 row in set (0.00 sec)

 

查看所有庫引擎

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

創建用戶 

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

方括號中的內容是可以省略的,比如設置默認主機%,這樣允許用戶從其他主機登錄,比如不設置密碼,這樣默認無需密碼登錄。

我們創建一個用戶並登錄試試,紅色字體是關鍵語句:

 1 mysql> create user user1@localhost identified by '123456';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 D:\Setup\mysql-5.7.30-winx64\bin>mysql -u user1 -p
 5 Enter password: ******
 6 Welcome to the MySQL monitor.  Commands end with ; or \g.
 7 Your MySQL connection id is 16
 8 Server version: 5.7.30 MySQL Community Server (GPL)
 9 
10 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
11 
12 Oracle is a registered trademark of Oracle Corporation and/or its
13 affiliates. Other names may be trademarks of their respective
14 owners.
15 
16 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

修改用戶密碼:4種方式

登錄MySQL並修改

格式如下: set password for 用戶名@主機 = password(‘新密碼’);

1 mysql> set password for root@localhost = password('Helenlyn');
使用 mysqladmin 操作

格式如下:mysqladmin 用戶名 舊密碼 password 新密碼

1 mysqladmin -u root -p Helenlyn password Helenlyn1

 

使用腳本語句修改 user 表

打開mysql數據庫,直接更新user表的authentication_string字段。這邊localhost可以用%代替。

需要注意:flush privileges是刷新權限,必須執行,才能對用戶生效;

我們用的是5.7.30版本,所以密碼字段是authentication_string,5.7之前的版本一般來說是password。

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

 

直接設置密碼

創建用戶的時候同時設置密碼,或者登錄的用戶修改自己的密碼,這個在前面部署服務和創建用戶的時候都有說明過了:

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

  

刪除用戶:2種方式

直接drop

格式如下:drop user ‘username‘[@’host’]

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

 刪除完,用戶重啟就生效了,可以創建個用戶試試。

 

刪除user表的用戶

同樣的,執行完需要執行 flush privileges刷新權限才會對用戶生效,格式如下:

delete from user where user=用戶名and host=主機;
flush privileges;

1 mysql> use mysql;
2 Database changed
3 
4 mysql> delete from user where user='user1' and host='localhost';
5 Query OK, 0 rows affected (0.00 sec)
6 
7 mysql> flush privileges;
8 Query OK, 0 rows affected (0.00 sec)

 

給用戶授權

創建用戶之後,需要給不同的用戶分別授權,這樣才能根據不同用戶的角色來界定職責和管理範疇

1 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的權限。

 

1 mysql> create user brand identified by '123456';
2 Query OK, 0 rows affected
3 
4 mysql> grant all on *.* to brand@'%';
5 Query OK, 0 rows affected

說明:這邊是創建了一個用戶並把所有的權限都授予他。如果要做限制,可以把 *.* 改成具體的數據庫或者表,也可以吧%改成具體的host。如果要開放授權能力,可以在末尾加上 with grant option ,這樣就基本等同於dba的權限了。

 

查看用戶的權限

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

 

這邊需注意,主機可以省略,默認值為%,測試下:

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

當前登錄者的權限,如:

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

 

取消用戶的權限

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

 

可以先查show grants,再撤銷revoke,再查show grants,來理解整個過程,示例:

 1 mysql> show grants for brand;
 2 +---------------------------------------------------------------------------------------------------------------+
 3 | Grants for brand@%                                                                                            |
 4 +---------------------------------------------------------------------------------------------------------------+
 5 | GRANT ALL PRIVILEGES ON *.* TO 'brand'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
 6 +---------------------------------------------------------------------------------------------------------------+
 7 1 row in set
 8 
 9 mysql> revoke delete on *.* from brand;
10 Query OK, 0 rows affected
11 
12 mysql> show grants for brand;
13 +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14 | Grants for brand@%                                                                                                                                                                                                                                                                                                                                                                                                 |
15 +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16 | 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' |
17 +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 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表中,可以打開看看。