MySQL之基础管理

1. 用户管理

1.1 用户的作用

• 登录MySQL数据
• 管理MySQL对象：表

1.2 用户的定义

• 用户名@‘白名单’

-- 白名单支持的方式？
wordpress@'10.0.0.%'    
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'

1.3 用户管理

1. 查

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost |                                           |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

2. 增

mysql> create user awei@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| awei          | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

3. 改

mysql> alter user awei@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost |                                           |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| awei          | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

4. 删

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

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

2. 权限管理

2.1 权限的作用

• 用户对数据库对象，有哪些管理能力

2.2 权限的表现方式

-- 查看所有权限
mysql> mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

2.3 授权、回收权限操作

• 语法：

  -- 8.0以前：
  grant 权限 on 对象 to 用户 identified by ‘密码’;
  -- 8.0以后：
  create user 用户 identified by ：密码;
  grant 权限 on 对象 to 用户;
  

• 权限：

  ALL						 ：管理员
  权限1，权限2，权限3...		：普通用户（业务用户、开发用户）
  Grant optino			 ：给别的用户授权
  

• 对象：

  *.*						：管理员
  awei.*					：普通用户
  awei.t1					：一般不用
  

• 授权的例子

  • 创建并授权管理员用户awei，能够通过192.168.159.%网段登录并管理数据库

    mysql> grant all on *.* to awei@'192.168.159.%' identified by '123' with grant option;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show grants for awei@'192.168.159.%';
    +-------------------------------------------------------------------------+
    | Grants for awei@192.168.159.%                                           |
    +-------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'awei'@'192.168.159.%' WITH GRANT OPTION |
    +-------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from mysql.user\G
    --------------------------------------------------------------
    *************************** 4. row ***************************
                      Host: 192.168.159.%
                      User: awei
               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: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
          password_expired: N
     password_last_changed: 2020-06-11 20:51:49
         password_lifetime: NULL
            account_locked: N
    --------------------------------------------------------------
    

  • 创建并授权一个app@‘192.168.159.%’的业务用户，能够对app库下的所有对象进行create、select、update、delete、insert操作

    mysql> grant create,update,select,insert,delete on app.* to app@'192.168.159.%' identified by '123';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show grants for app@'192.168.159.%';
    +----------------------------------------------------------------------------------+
    | Grants for app@192.168.159.%                                                     |
    +----------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'app'@'192.168.159.%'                                      |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `app`.* TO 'app'@'192.168.159.%' |
    +----------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    

• MySQL授权表

  mysql库下：
  user					: *.*
  db						: app.*
  tables_priv				: app.tl
  columns_priv			: 列
  

• 回收权限

  mysql> revoke create on app.* from app@'192.168.159.%';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show grants for app@'192.168.159.%';
  +--------------------------------------------------------------------------+
  | Grants for app@192.168.159.%                                             |
  +--------------------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'app'@'192.168.159.%'                              |
  | GRANT SELECT, INSERT, UPDATE, DELETE ON `app`.* TO 'app'@'192.168.159.%' |
  +--------------------------------------------------------------------------+
  2 rows in set (0.00 sec)
  

2.4 root密码忘记了

• --skip-grant-tables：跳过授权表
• --skip-networking：跳过TCP/IP连接，只能用本地socket连接

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
# 手工加载授权表
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start mysqld

3 连接管理

3.1 mysql自带客户端

3.1.1 mysql

-u                   用户名
-p                   密码
-h                   数据库IP地址，必须提前创建好考研远程连接的用户
-P                   数据库端口号
-S                   本地socket文件位置，数据库种必须事先授权用户
-e                   免交互执行数据库命令
<                    导入SQL脚本

[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
mysql> select @@socket;
+-----------------+
| @@socket        |
+-----------------+
| /tmp/mysql.sock |
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| abc          | 10.0.0.%  |
| app          | 10.0.0.%  |
| root          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys    | localhost |
| root          | localhost |
+---------------+-----------+
[root@db01 ~]#
[root@db01 ~]# mysql -uroot -p <world.sql
Enter password: