MySQL存储引擎全表查看
mysql >SHOW ENGINES;
单表查看
mysql >SHOW TABLES STATUS IN TABLEBNAME;
用户账户管理：
MariaDB [(none)]> HELP CONTENTS
MariaDB [(none)]> HELP Account Management
You asked for help about help category: "Account Management"
For more information, type 'help ', where is one of the following
topics: CREATE USER 创建 DROP USER 删除 RENAME USER 修改账户名称 SET PASSWORD
权限管理：GRANTREVOKE授权
MariaDB [(none)]> CREATE USER linuxea@'192.168.%.%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
登陆查看
[root@mysql-master ~]# mysql -ulinuxea -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 4
Server version: 5.5.44-MariaDB-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| CLIENT_STATISTICS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
查看自己的权限
MariaDB [(none)]> SHOW GRANTS FOR linuxea@'192.168.%.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for linuxea@192.168.%.% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linuxea'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
修改授权过的用户信息：修改完成后不会立即退出，重新登陆则需要新的信息验证
MariaDB [(none)]> RENAME USER 'linuxea'@'192.168.%.%' to 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
如下所示：
[root@mysql-master ~]# mysql -ulinuxea -h192.168.0.99 -ppassword
ERROR 1045 (28000): Access denied for user 'linuxea'@'192.168.0.99' (using password: YES)
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 6
Server version: 5.5.44-MariaDB-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>
MySQL权限类型：库，表，字段，管理
查看线程：
MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
| 6 | linux | 192.168.0.99:54201 | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 |
+----+-------+--------------------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)
MariaDB [(none)]>
施加锁
MariaDB [(none)]> use zabbix
MariaDB [zabbix]> LOCK TABLES history_log READ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [zabbix]> SHOW PROCESSLIST;
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
| 83773 | zuser | 10.215.60.55:56991 | zabbix | Sleep | 22 | | NULL | 0.000 |
| 83774 | zuser | 10.215.60.55:56992 | zabbix | Sleep | 1 | | NULL | 0.000 |
| 83775 | zuser | 10.215.60.55:56993 | zabbix | Sleep | 4 | | NULL | 0.000 |
| 83776 | zuser | 10.215.60.55:56994 | zabbix | Sleep | 27 | | NULL | 0.000 |
| 83777 | zuser | 10.215.60.55:56995 | zabbix | Sleep | 12 | | NULL | 0.000 |
| 83778 | zuser | 10.215.60.55:56996 | zabbix | Sleep | 2 | | NULL | 0.000 |
| 83779 | zuser | 10.215.60.55:56997 | zabbix | Sleep | 1 | | NULL | 0.000 |
| 83781 | zuser | 10.215.60.55:56999 | zabbix | Sleep | 7 | | NULL | 0.000 |
| 83787 | zuser | 10.215.60.55:57005 | zabbix | Sleep | 40 | | NULL | 0.000 |
| 83788 | zuser | 10.215.60.55:57006 | zabbix | Sleep | 19 | | NULL | 0.000 |
| 83843 | zuser | 10.215.60.55:57076 | zabbix | Sleep | 33 | | NULL | 0.000 |
| 122824 | zuser | 10.215.60.55:58775 | zabbix | Sleep | 27664 | | NULL | 0.000 |
| 149732 | zuser | 10.215.60.55:57764 | zabbix | Sleep | 4 | | NULL | 0.000 |
| 157414 | root | localhost | zabbix | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 |
+--------+-------+--------------------+--------+---------+-------+-------+------------------+----------+
14 rows in set (0.00 sec)
MariaDB [zabbix]>
管理类权限
create temporary tables 临时表
create user
file 允许用户读或者写某些文件
lock tables 添加显式锁
process:查看用户的线程
reload：相当于执行flush和reset
replication client 查询有哪些复制客户端
replication slave 赋予用户复制权限
show databases
shutdown
super
数据库访问权限
alter
alter routine 存储历程
create
create routine 存储过程，存储函数
create view
delete
drop
execute
grant option 将自己的权限复制给别的用户
index 索引
show view
数据操作类权限（表级别）：
select
insert
update
delete
字段级别：
select（col1,....）
update（col1,....）
insert（col1,....）
所有权限：
ALL.ALL
//授权linux用户对linux表有创建修改任何的权限：MariaDB [(none)]> GRANT CREATE ON linuxea.* To 'linux'@'192.168.%.%';Query OK, 0 rows affected (0.00 sec)
使用linux登陆
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
查看权限
MariaDB [test]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT CREATE ON `linuxea`.* TO 'linux'@'192.168.%.%' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
创建linux表
MariaDB [test]> CREATE DATABASE linuxea;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxea |
| test |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [test]>
创建表，查看需要查看权限（上面授权REATE表），否则不能查看等其他命令的权限使用，如下：
MariaDB [test]> use linuxea;
Database changed
MariaDB [linuxea]> CREATE TABLE t1 (ID TINYINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)
MariaDB [linuxea]> DESC t1;
ERROR 1142 (42000): SELECT command denied to user 'linux'@'192.168.0.99' for table 't1'
MariaDB [linuxea]>
如果需要查看，或者删除其他权限，则需要授权，如下：授权删除
MariaDB [(none)]> GRANT DROP ON linuxea.* To 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
在查看，则DROP删除权限会附加到CREATE
MariaDB [linuxea]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT CREATE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [linuxea]>
重新登陆，验证DROP权限
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> DROP DATABASE linuxea;
Query OK, 1 row affected (0.05 sec)
MariaDB [(none)]>
///查看，插入，修改，删除，创建如果需要对表进行查看，插入，修改，删除，创建，则需要添加如下：
MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON linuxea.* To 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
在查看
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
这里的授权权限是无法授权给其他人。
*收回权限收回单个权限
MariaDB [(none)]> REVOKE CREATE ON linuxea.* FROM 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
查看
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `linuxea`.* TO 'linux'@'192.168.%.%' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
收回所有权限
MariaDB [(none)]> REVOKE ALL ON linuxea.* FROM 'linux'@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
查看
[root@mysql-master ~]# mysql -ulinux -h192.168.0.99 -ppassword
MariaDB [(none)]> SHOW GRANTS FOR linux@'192.168.%.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for linux@192.168.%.% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linux'@'192.168.%.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
MySQL表
MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
MariaDB [mysql]>
用户授权相关的表DB：库级别权限HOST：主机级别（已经废弃）tables_priv:表级别权限colomns_priv:列级别权限procs_priv:存储过程和存储函数相关的权限proxies_priv：代理用户
