返回列表 发帖

MySQL的SQL语句(二)

创建数据库用户zhuohua@localhost,并赋权:
mysql> grant all on *.* to zhuohua@localhost identified by '168';
Query OK, 0 rows affected (0.01 sec)

查看用户zhuohua@localhost的权限:
mysql> show grants for zhuohua@localhost;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@localhost                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'localhost' IDENTIFIED BY PASSWORD '*242E46A1E8D30FE06F7CE37B55BFC25BA981D70C' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)





显示数据库的版本:

方式一:
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -V
mysql  Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1


方式二:
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select version()"      
+------------+
| version()  |
+------------+
| 5.5.48-log |
+------------+


方式三:
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysqladmin -u"zhuohua" -p"168" -s version         
mysqladmin  Ver 8.42 Distrib 5.5.48, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.48-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 6 hours 40 min 49 sec

注释:这里也可以看到MySQL服务运行了多长时间(Uptime);一旦MySQL服务重启了,Uptime的值就会从0 sec重新开始。




显示数据库里的全部库:
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -sN -e "show databases"    
information_schema
discuz
mysql
performance_schema
wordpress
zabbix





显示库discuz的大小:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'discuz'"
+---------+
| data    |
+---------+
| 57.61MB |
+---------+


只取一个值:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'discuz'" |awk '{print $1}'|tail -1
57.61MB


只取一个值:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'') as data from information_schema.TABLES where table_schema = 'discuz'" |awk '{print $1}'|tail -1   
57.61


获取到的值进行比较:
[root@iZj6c1a39n0ss415rjbuoqZ ~]# a=$(mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'') as data from information_schema.TABLES where table_schema = 'discuz'" |awk '{print $1}'|tail -1)

[root@iZj6c1a39n0ss415rjbuoqZ ~]# aa=`echo $a |awk -F. '{print $1}'`
[root@iZj6c1a39n0ss415rjbuoqZ ~]# echo $aa
57
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
[root@iZj6c1a39n0ss415rjbuoqZ ~]# [ $aa -gt 50 ] && echo 'Big'
Big









显示库zabbix的大小:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'zabbix'"
+----------+
| data     |
+----------+
| 162.59MB |
+----------+


显示库zabbix的大小:(以GB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.TABLES where table_schema = 'zabbix'"
+--------+
| data   |
+--------+
| 0.16GB |
+--------+


显示库zabbix的大小:(以字节为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'zabbix'"
+-----------+
| data      |
+-----------+
| 170491904 |
+-----------+








查询所有库的总大小:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES"
+----------+
| data     |
+----------+
| 222.99MB |
+----------+







查看库(zabbix)里的表(users)的大小:(以MB为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='zabbix' and table_name='users'"
+--------+
| data   |
+--------+
| 0.02MB |
+--------+


查看库(zabbix)里的表(users)的大小:(以字节为单位)
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -u"zhuohua" -p"168" -e "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema='zabbix' and table_name='users'" |awk '{print $1}'|tail -1
16384



查看库(zabbix)里的表(users)的行数:
[root@centos8 ~]# mysql -u"zhuohua" -p"168" -e "select count(*) from zabbix.users"
+----------+
| count(*) |
+----------+
|        2 |
+----------+












###

删除数据库用户(zhuohua@localhost):

mysql> drop user zhuohua@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


用户删除后,再查询其权限,会有如下显示:
mysql> show grants for zhuohua@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'zhuohua' on host 'localhost'






相关文章:
Zabbix使用模板监控Linux下的MariaDB
Zabbix使用模板监控Linux下的MySQL
Zabbix使用模板监控Windows下的MySQL

Zabbix调用Python3脚本监控Linux下的MySQL
Zabbix调用Python3脚本监控Linux下的MariaDB

返回列表