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 用户管理
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)
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)
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)
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 连接管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mq3Qbawa-1592143072914)(3. 基础管理/image-20200611213313225.png)]

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:
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值