使用Navicat远程管理MySQL
笺注：这是在 [url=http://blog.zhuohua.store/viewthread.php?tid=331&extra=page%3D1]CentOS6.9编译安装Zabbix4.4.5[/url] 的基础上进行的。服务器本地登录MySQL数据库：（使用的是数据库管理员root@localhost，密码888）
mysql -u"root" -p"[color=DarkRed]888[/color]"
[attach]19396[/attach]
显示数据库里的全部库，以及库zabbix的字符集：
show databases;
show create database [color=Blue]zabbix[/color];
[attach]19397[/attach]
注释：库zabbix的字符集为 [color=Blue]utf8_general_ci[/color]
显示所有库的总大小：（以GB为单位）
mysql> select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'[color=DarkRed]GB[/color]') as data from information_schema.TABLES;
+--------+
| data |
+--------+
| [color=Purple]0.01GB[/color] |
+--------+
1 row in set (0.08 sec)
mysql>
显示所有库的总大小：（以MB为单位）
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'[color=DarkRed]MB[/color]') as data from information_schema.TABLES;
+---------+
| data |
+---------+
| [color=Purple]9.78MB[/color] |
+---------+
1 row in set (0.40 sec)
mysql>
显示库zabbix的大小：（以MB为单位）
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'[color=DarkRed]MB[/color]') as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';
+--------+
| data |
+--------+
| [color=Purple]9.23MB[/color] |
+--------+
1 row in set (0.01 sec)
mysql>
查看所有数据库用户及其主机信息：
select user,host from mysql.user;
[attach]19398[/attach]
查看数据库本地用户zabbix@localhost的权限：
mysql> show grants for zabbix@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for zabbix@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY PASSWORD '*DEEF4D7D88CD046ECA02A80393B7780A63E7E789' |
| [color=Purple]GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zabbix'@'localhost' [/color] |
+---------------------------------------------------------------------------------------------------------------+
注释：数据库本地用户zabbix@localhost只对库zabbix有完全控制的权限。
使用数据库本地用户zabbix@localhost进行操作：
显示数据库的版本：（方式一）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"[color=DarkRed]zabbix[/color]" [color=Blue]-V[/color]
mysql Ver 14.14 Distrib [color=Purple]5.5.48[/color], for Linux (x86_64) using readline 5.1
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -V |awk '{print $5}' |awk -F, '{print $1}'
[color=Purple]5.5.48[/color]
[root@Zabbix_server_01 ~]#
注释：数据库本地用户zabbix@localhost的密码为[color=DarkRed]zabbix[/color]
显示数据库的版本：（方式二）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "[color=Blue]select version();[/color]"
+------------+
| version() |
+------------+
| [color=Purple]5.5.48-log[/color] |
+------------+
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "select version();" |tail -1 |awk -F- '{print $1}'
[color=Purple]5.5.48[/color]
[root@Zabbix_server_01 ~]#
使用参数“[color=DarkRed]-sN[/color]”：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select version();"
[color=Purple]5.5.48-log[/color]
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select version();" |awk -F- '{print $1}'
[color=Purple]5.5.48[/color]
[root@Zabbix_server_01 ~]#
显示数据库的版本：（方式三）
[root@Zabbix_server_01 ~]# mysqladmin
[color=Red]-bash: mysqladmin: command not found[/color]
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# find / -name mysqladmin
[color=Purple]/usr/local/mysql/bin/mysqladmin[/color]
[root@Zabbix_server_01 ~]#
[root@Zabbix_server_01 ~]# ln -s /usr/local/mysql/bin/mysqladmin [color=Blue]/usr/bin/[/color]
[root@Zabbix_server_01 ~]# [color=DarkRed]mysqladmin[/color] -u"zabbix" -p"zabbix" [color=Blue]-s version[/color]
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 [color=Purple]5.5.48[/color]-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
[color=Blue]Uptime[/color]: [color=Purple]28 min 26 sec[/color]
Threads: 13 Questions: 19635 Slow queries: 0 Opens: 98 Flush tables: 1 Open tables: 64 Queries per second avg: 11.509
[root@Zabbix_server_01 ~]#
注释：这里可以看到MySQL服务运行了多长时间（[color=Blue]Uptime[/color]）；一旦MySQL服务重启了，Uptime的值就会从 [color=Purple]0 sec[/color] 重新开始。
显示数据库里的全部库：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "[color=Blue]show databases;[/color]"
+--------------------+
| Database |
+--------------------+
| information_schema |
| [color=Purple]zabbix[/color] |
+--------------------+
[root@Zabbix_server_01 ~]#
使用参数“[color=DarkRed]-sN[/color]”：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "show databases;"
information_schema
[color=Purple]zabbix[/color]
[root@Zabbix_server_01 ~]#
注释：数据库本地用户zabbix@localhost只对库zabbix有完全控制的权限，所以一些没有权限的库是看不到的。
显示库zabbix的大小：（以MB为单位）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'[color=DarkRed]MB[/color]') as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
+--------+
| data |
+--------+
| [color=Purple]9.31MB[/color] |
+--------+
[root@Zabbix_server_01 ~]#
使用参数“[color=DarkRed]-sN[/color]”：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'[color=DarkRed]MB[/color]') as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
[color=Purple]9.31MB[/color]
[root@Zabbix_server_01 ~]#
显示库zabbix的大小：（以GB为单位）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'[color=DarkRed]GB[/color]') as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
[color=Purple]0.01GB[/color]
[root@Zabbix_server_01 ~]#
结果不显示单位：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024/1024/1024),2)) as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
[color=Purple]0.01[/color]
[root@Zabbix_server_01 ~]#
显示库zabbix的大小：（以KB为单位）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024),2),'[color=DarkRed]KB[/color]') as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
[color=Purple]9616.00KB[/color]
[root@Zabbix_server_01 ~]#
结果不显示单位：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024),2)) as data from information_schema.TABLES where table_schema = '[color=Blue]zabbix[/color]';"
[color=Purple]9616.00[/color]
[root@Zabbix_server_01 ~]#
查看库zabbix里的表users的大小：（以MB为单位）
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'[color=DarkRed]MB[/color]') as data from information_schema.TABLES where table_schema='[color=Blue]zabbix[/color]' and table_name='[color=Blue]users[/color]';"
[color=Purple]0.02MB[/color]
[root@Zabbix_server_01 ~]#
查看库zabbix里的表users的行数：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "select count(*) from zabbix.users;"
+----------+
| count(*) |
+----------+
| [color=Purple]2[/color] |
+----------+
使用参数“-sN”：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" [color=DarkRed]-sN[/color] -e "select count(*) from zabbix.users;"
[color=Purple]2[/color]
[root@Zabbix_server_01 ~]#
Zabbix的数据库配置文件：（数据库本地用户zabbix@localhost的密码为zabbix）
[root@Zabbix_server_01 ~]# cd /home/wwwroot/zabbix.zhuohua.store/conf/
[root@Zabbix_server_01 conf]# vi zabbix.conf.php
[attach]19399[/attach]
注释：
这里的主机地址为[color=Blue]localhost[/color]，即本地连接；假如不需要外部程序直接连接到库zabbix，就无需打开防火墙的TCP 3306端口；
打开防火墙的TCP 80端口，客户端就可以正常访问Zabbix的管理页面了；
######
编辑防火墙规则：（打开TCP 3306端口）
[root@Zabbix_server_01 ~]# vi /etc/sysconfig/iptables
修改：
-A INPUT -p tcp -m tcp --dport [color=DarkRed]3306[/color] -j [color=Blue]ACCEPT[/color]
[attach]19400[/attach]
重启iptable服务：
[root@Zabbix_server_01 ~]# service iptables restart
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则：[确定]
iptables：正在卸载模块：[确定]
iptables：应用防火墙规则：[确定]
查看防火墙的filter表的规则：
iptables -nL --line
[attach]19401[/attach]
######
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 [color=Blue]zabbix[/color].* 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' |
| [color=Purple]GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' [/color] |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
再次查看所有数据库用户及其主机信息：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "select user,host from mysql.user;"
[attach]19402[/attach]
Navicat 是一个图形化管理多种数据库的工具。
下载Navicat Premium： [url]https://pan.baidu.com/s/1CqL1u7KxDttqR5HL5EQucw[/url] 提取码：0iux
Windows客户端使用Navicat，远程访问MySQL数据库：
[attach]19403[/attach]
[attach]19404[/attach]
注释：MySQL服务器的IP地址为 [color=Blue]192.168.168.130[/color]
按前面的配置，数据库用户zhuohua@'192.168.168.132'只能管理库zabbix：
[attach]19405[/attach]
使用Navicat在库zabbix里新建一个表：
[attach]19406[/attach]
[attach]19407[/attach]
注释：字段id为主键、标识符列，会自动增长。
[attach]19408[/attach]
创建表成功：
[attach]19409[/attach]
在MySQL服务器本地查看表zabbix.my_table_01的表结构：
[root@Zabbix_server_01 ~]# mysql -u"zabbix" -p"zabbix" -e "desc zabbix.my_table_01;"
[attach]19410[/attach]
############
再新建一个库data2：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "create database [color=Blue]data2[/color] character set utf8mb4 collate utf8mb4_general_ci;"
[root@Zabbix_server_01 ~]#
mysql -u"root" -p"888" -e "show create database data2;"
[attach]19411[/attach]
注释：库data2的字符集为 [color=Blue]utf8mb4_general_ci[/color]
数据库用户zhuohua@'192.168.168.132'使用Navicat远程访问MySQL数据库：
[attach]19412[/attach]
笺注：由于权限问题，数据库用户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 [color=Blue]data2[/color].* 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' |
|[color=Purple] GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' [/color] |
| [color=Purple]GRANT ALL PRIVILEGES ON `data2`.* TO 'zhuohua'@'192.168.168.132' [/color] |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
此时，数据库用户zhuohua@'192.168.168.132'可以使用Navicat远程管理库zabbix、库data2：
[attach]19413[/attach]
######
撤销数据库用户zhuohua@'192.168.168.132'对库data2的全部权限：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "revoke all on [color=Blue]data2[/color].* 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' |
| [color=Purple]GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zhuohua'@'192.168.168.132' [/color] |
+----------------------------------------------------------------------------------------------------------------------+
[root@Zabbix_server_01 ~]#
######
撤销数据库用户zhuohua@'192.168.168.132'对库zabbix的全部权限：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "revoke all on [color=Blue]zabbix[/color].* 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;"
[attach]19414[/attach]
相关文章：
[url=http://blog.zhuohua.store/viewthread.php?tid=230&extra=page%3D1]使用SQLyog远程管理MySQL[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=234&extra=page%3D1]MySQL的字符集[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=152&page=1&extra=#pid153]Python3脚本管理Linux下的MySQL[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=332&page=1&extra=#pid407]Zabbix使用模板监控Linux下的MySQL[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=334&page=1&extra=#pid409]Zabbix使用模板监控Linux下的MariaDB[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=336&page=1&extra=#pid411]Zabbix创建模板监控Windows下的MariaDB[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=412&extra=page%3D1]Zabbix创建模板监控Linux下的MariaDB[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=363&extra=page%3D1]Zabbix调用Python3脚本监控Linux下的MySQL[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=409&page=1&extra=#pid837]Zabbix调用Python3脚本监控Linux下的MariaDB[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=407&page=1&extra=#pid835]使用Navicat远程管理MySQL8.0[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=199&extra=page%3D1]使用Navicat远程管理MSSQL2014[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=229&page=1&extra=#pid230]使用Navicat连接Oracle11gR2[/url]
#################################
#################################
[url=https://weidian.com/?userid=823531601&wfr=wx&sfr=app&source=shop]亲，学习研究也要劳逸结合哦，来我微店逛逛，买点东西好好犒劳犒劳自己和家人吧^_^^_^[/url]
[url=https://weidian.com/item.html?itemID=905482571141741391616&wfr=wx&sfr=app&source=goods_home]电蚊拍可充电式LED灭苍蝇拍大号网面强力灭蚊拍电蚊子拍[/url]
[url=https://weidian.com/item.html?itemID=905482571141741391616&wfr=wx&sfr=app&source=goods_home][attach]4162[/attach][/url]
[url=https://weidian.com/item.html?itemID=905482571141728238395&wfr=wx&sfr=app&source=goods_home]苏泊尔榨汁机杯家用全自动果蔬多功能迷你学生小型便携式炸果汁机[/url]
[url=https://weidian.com/item.html?itemID=905482571141728238395&wfr=wx&sfr=app&source=goods_home][attach]4160[/attach][/url]
页:
[1]