注释:库zabbix的字符集为 utf8_general_ci
显示所有库的总大小:(以GB为单位)
mysql> select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.TABLES;
+--------+
| data |
+--------+
| 0.01GB |
+--------+
1 row in set (0.08 sec)
mysql>
显示所有库的总大小:(以MB为单位)
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES;
+---------+
| data |
+---------+
| 9.78MB |
+---------+
1 row in set (0.40 sec)
mysql>
显示库zabbix的大小:(以MB为单位)
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'zabbix';
+--------+
| data |
+--------+
| 9.23MB |
+--------+
1 row in set (0.01 sec)
mysql>
查看所有数据库用户及其主机信息:
select user,host from mysql.user;
######
MySQL服务器授权数据库用户zhuohua@'192.168.168.132'(密码1234),仅仅在使用IP地址192.168.168.132时可以进行访问,对库zabbix有完全控制的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "grant all on zabbix.* to zhuohua@'192.168.168.132' identified by '1234';"
[root@Zabbix_server_01 ~]#
查看数据库用户zhuohua@'192.168.168.132'的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show grants for zhuohua@'192.168.168.132';"
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@192.168.168.132 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'192.168.168.132' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
再次查看所有数据库用户及其主机信息:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "select user,host from mysql.user;"
笺注:由于权限问题,数据库用户zhuohua@'192.168.168.132'此时是看不到、管理不了库data2的。
######
MySQL服务器再授权数据库用户zhuohua@'192.168.168.132',对库data2有完全控制的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "grant all on data2.* to zhuohua@'192.168.168.132';"
再查看数据库用户zhuohua@'192.168.168.132'的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show grants for zhuohua@'192.168.168.132';"
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@192.168.168.132 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'192.168.168.132' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' |
| GRANT ALL PRIVILEGES ON `data2`.* TO 'zhuohua'@'192.168.168.132' |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
此时,数据库用户zhuohua@'192.168.168.132'可以使用Navicat远程管理库zabbix、库data2:
######
撤销数据库用户zhuohua@'192.168.168.132'对库data2的全部权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "revoke all on data2.* from zhuohua@'192.168.168.132';flush privileges;"
再查看数据库用户zhuohua@'192.168.168.132'的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show grants for zhuohua@'192.168.168.132';"
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@192.168.168.132 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'192.168.168.132' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
######
撤销数据库用户zhuohua@'192.168.168.132'对库zabbix的全部权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "revoke all on zabbix.* from zhuohua@'192.168.168.132';flush privileges;"
再查看数据库用户zhuohua@'192.168.168.132'的权限:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show grants for zhuohua@'192.168.168.132';"
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@192.168.168.132 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'192.168.168.132' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
######
删除数据库用户zhuohua@'192.168.168.132':
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "drop user zhuohua@'192.168.168.132';flush privileges;"
再次查看所有数据库用户及其主机信息:
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "select user,host from mysql.user;"