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 连接管理
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: