快乐的味道

常用mysql相关命令 连接数据库 # mysql -uroot -ppassword mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9792 Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, 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. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 查看版本，时间 mysql> select version(),current_date; +-------------------------+--------------+ | version() | current_date | +-------------------------+--------------+ | 5.7.24-0ubuntu0.16.04.1 | 2018-11-27 | +-------------------------+--------------+ 1 row in set (0.00 sec) 查看有哪些数据库 mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | sys | | zabbix | +--------------------+ 6 rows in set (0.00 sec) 查看现在使用的数据库和登录用户： mysql> select database(); +------------+ | database() | +------------+ | zabbix | +------------+ 1 row in set (0.00 sec) mysql> select user() -> ; +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> 使用zabbix数据库，查看有哪些数据表 mysql> use zabbix Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_zabbix | +----------------------------+ | acknowledges | | actions | | alerts | | application_discovery | | application_prototype | | application_template | | applications | | auditlog | | auditlog_details | | autoreg_host | | conditions | | config | | corr_condition | | corr_condition_group | | corr_condition_tag | | corr_condition_tagpair | | corr_condition_tagvalue | | corr_operation | | correlation | | dashboard | | dashboard_user | | dashboard_usrgrp | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | event_recovery | | event_suppress | | event_tag | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | history | | history_log | | history_str | | history_text | | history_uint | | host_discovery | | host_inventory | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | housekeeper | | hstgrp | | httpstep | | httpstep_field | | httpstepitem | | httptest | | httptest_field | | httptestitem | | icon_map | | icon_mapping | | ids | | images | | interface | | interface_discovery | | item_application_prototype | | item_condition | | item_discovery | | item_preproc | | items | | items_applications | | maintenance_tag | | maintenances | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | mappings | | media | | media_type | | opcommand | | opcommand_grp | | opcommand_hst | | opconditions | | operations | | opgroup | | opinventory | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | problem | | problem_tag | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | rights | | screen_user | | screen_usrgrp | | screens | | screens_items | | scripts | | service_alarms | | services | | services_links | | services_times | | sessions | | slides | | slideshow_user | | slideshow_usrgrp | | slideshows | | sysmap_element_trigger | | sysmap_element_url | | sysmap_shape | | sysmap_url | | sysmap_user | | sysmap_usrgrp | | sysmaps | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | tag_filter | | task | | task_acknowledge | | task_check_now | | task_close_problem | | task_remote_command | | task_remote_command_result | | timeperiods | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemaps | | widget | | widget_field | +----------------------------+ 144 rows in set (0.00 sec) 查看某个表的格式（这里看users表） mysql> describe users; +----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | userid | bigint(20) unsigned | NO | PRI | NULL | | | alias | varchar(100) | NO | UNI | | | | name | varchar(100) | NO | | | | | surname | varchar(100) | NO | | | | | passwd | varchar(32) | NO | | | | | url | varchar(255) | NO | | | | | autologin | int(11) | NO | | 0 | | | autologout | varchar(32) | NO | | 15m | | | lang | varchar(5) | NO | | en_GB | | | refresh | varchar(32) | NO | | 30s | | | type | int(11) | NO | | 1 | | | theme | varchar(128) | NO | | default | | | attempt_failed | int(11) | NO | | 0 | | | attempt_ip | varchar(39) | NO | | | | | attempt_clock | int(11) | NO | | 0 | | | rows_per_page | int(11) | NO | | 50 | | +----------------+---------------------+------+-----+---------+-------+ 16 rows in set (0.00 sec) 查看表里的内容，下面表示看前几行的， 如果看全部用 select * from users;即可 mysql> select * from users order by userid limit 0,2; +--------+-------+--------+---------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+------------+---------------+---------------+ | userid | alias | name | surname | passwd | url | autologin | autologout | lang | refresh | type | theme | attempt_failed | attempt_ip | attempt_clock | rows_per_page | +--------+-------+--------+---------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+------------+---------------+---------------+ | 1 | Admin | Zabbix | Administrator | 5fce1b3e34b520afeffb37ce08c7cd66 | | 1 | 0 | en_GB | 30s | 3 | default | 0 | | 0 | 50 | | 2 | guest | | | d41d8cd98f00b204e9800998ecf8427e | | 0 | 15m | en_GB | 30s | 1 | default | 0 | | 0 | 50 | +--------+-------+--------+---------------+----------------------------------+-----+-----------+------------+-------+---------+------+---------+----------------+------------+---------------+---------------+ 2 rows in set (0.00 sec) 查看数据看状态： mysql> status; -------------- mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper Connection id: 9948 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 1 day 17 hours 23 min 26 sec Threads: 41 Questions: 1604992 Slow queries: 0 Opens: 83156 Flush tables: 1 Open tables: 416 Queries per second avg: 10.771 -------------- 更改root或其他用户密码： # mysqladmin -uroot -ppassword password zabbix123 mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. # mysql -uroot -pzabbix123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9939 Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, 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. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. zabbix比较关键的几个表： hosts: 主机表 mysql> describe hosts; +--------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+-------+ | hostid | bigint(20) unsigned | NO | PRI | NULL | | | proxy_hostid | bigint(20) unsigned | YES | MUL | NULL | | | host | varchar(128) | NO | MUL | | | | status | int(11) | NO | MUL | 0 | | | disable_until | int(11) | NO | | 0 | | | error | varchar(2048) | NO | | | | | available | int(11) | NO | | 0 | | | errors_from | int(11) | NO | | 0 | | | lastaccess | int(11) | NO | | 0 | | | ipmi_authtype | int(11) | NO | | -1 | | | ipmi_privilege | int(11) | NO | | 2 | | | ipmi_username | varchar(16) | NO | | | | | ipmi_password | varchar(20) | NO | | | | | ipmi_disable_until | int(11) | NO | | 0 | | | ipmi_available | int(11) | NO | | 0 | | | snmp_disable_until | int(11) | NO | | 0 | | | snmp_available | int(11) | NO | | 0 | | | maintenanceid | bigint(20) unsigned | YES | MUL | NULL | | | maintenance_status | int(11) | NO | | 0 | | | maintenance_type | int(11) | NO | | 0 | | | maintenance_from | int(11) | NO | | 0 | | | ipmi_errors_from | int(11) | NO | | 0 | | | snmp_errors_from | int(11) | NO | | 0 | | | ipmi_error | varchar(2048) | NO | | | | | snmp_error | varchar(2048) | NO | | | | | jmx_disable_until | int(11) | NO | | 0 | | | jmx_available | int(11) | NO | | 0 | | | jmx_errors_from | int(11) | NO | | 0 | | | jmx_error | varchar(2048) | NO | | | | | name | varchar(128) | NO | MUL | | | | flags | int(11) | NO | | 0 | | | templateid | bigint(20) unsigned | YES | MUL | NULL | | | description | text | NO | | NULL | | | tls_connect | int(11) | NO | | 1 | | | tls_accept | int(11) | NO | | 1 | | | tls_issuer | varchar(1024) | NO | | | | | tls_subject | varchar(1024) | NO | | | | | tls_psk_identity | varchar(128) | NO | | | | | tls_psk | varchar(512) | NO | | | | | proxy_address | varchar(255) | NO | | | | | auto_compress | int(11) | NO | | 1 | | +--------------------+---------------------+------+-----+---------+-------+ 41 rows in set (0.00 sec) mysql> 主要字段： hostid：主机id proxy_hostid：代理主机id host：主机ip status：主机状态：0被监控，1未监控，3模板主机 name：主机别名/昵称 flags：否启用：0启用，1未启用 templateid：模板id items：监控项 mysql> desc items; +-----------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------+-------+ | itemid | bigint(20) unsigned | NO | PRI | NULL | | | type | int(11) | NO | | 0 | | | snmp_community | varchar(64) | NO | | | | | snmp_oid | varchar(512) | NO | | | | | hostid | bigint(20) unsigned | NO | MUL | NULL | | | name | varchar(255) | NO | | | | | key_ | varchar(255) | NO | | | | | delay | varchar(1024) | NO | | 0 | | | history | varchar(255) | NO | | 90d | | | trends | varchar(255) | NO | | 365d | | | status | int(11) | NO | MUL | 0 | | | value_type | int(11) | NO | | 0 | | | trapper_hosts | varchar(255) | NO | | | | | units | varchar(255) | NO | | | | | snmpv3_securityname | varchar(64) | NO | | | | | snmpv3_securitylevel | int(11) | NO | | 0 | | | snmpv3_authpassphrase | varchar(64) | NO | | | | | snmpv3_privpassphrase | varchar(64) | NO | | | | | formula | varchar(255) | NO | | | | | error | varchar(2048) | NO | | | | | lastlogsize | bigint(20) unsigned | NO | | 0 | | | logtimefmt | varchar(64) | NO | | | | | templateid | bigint(20) unsigned | YES | MUL | NULL | | | valuemapid | bigint(20) unsigned | YES | MUL | NULL | | | params | text | NO | | NULL | | | ipmi_sensor | varchar(128) | NO | | | | | authtype | int(11) | NO | | 0 | | | username | varchar(64) | NO | | | | | password | varchar(64) | NO | | | | | publickey | varchar(64) | NO | | | | | privatekey | varchar(64) | NO | | | | | mtime | int(11) | NO | | 0 | | | flags | int(11) | NO | | 0 | | | interfaceid | bigint(20) unsigned | YES | MUL | NULL | | | port | varchar(64) | NO | | | | | description | text | NO | | NULL | | | inventory_link | int(11) | NO | | 0 | | | lifetime | varchar(255) | NO | | 30d | | | snmpv3_authprotocol | int(11) | NO | | 0 | | | snmpv3_privprotocol | int(11) | NO | | 0 | | | state | int(11) | NO | | 0 | | | snmpv3_contextname | varchar(255) | NO | | | | | evaltype | int(11) | NO | | 0 | | | jmx_endpoint | varchar(255) | NO | | | | | master_itemid | bigint(20) unsigned | YES | MUL | NULL | | | timeout | varchar(255) | NO | | 3s | | | url | varchar(2048) | NO | | | | | query_fields | varchar(2048) | NO | | | | | posts | text | NO | | NULL | | | status_codes | varchar(255) | NO | | 200 | | | follow_redirects | int(11) | NO | | 1 | | | post_type | int(11) | NO | | 0 | | | http_proxy | varchar(255) | NO | | | | | headers | text | NO | | NULL | | | retrieve_mode | int(11) | NO | | 0 | | | request_method | int(11) | NO | | 0 | | | output_format | int(11) | NO | | 0 | | | ssl_cert_file | varchar(255) | NO | | | | | ssl_key_file | varchar(255) | NO | | | | | ssl_key_password | varchar(64) | NO | | | | | verify_peer | int(11) | NO | | 0 | | | verify_host | int(11) | NO | | 0 | | | allow_traps | int(11) | NO | | 0 | | +-----------------------+---------------------+------+-----+---------+-------+ 63 rows in set (0.00 sec) mysql> itemid：监控项id type：类型：比如agent，SNMP，agent(active) 等 hostid：关联的主机id name：监控项的名称 key_ :监控项item的key值 history：历史表中保留的时间天数 trends：在trends表中保留的天数 status：状态：0能够使用，1不能够使用 value_type：有五个值： 0：numeric float 1：character 2：log 3：numeric unsigned 4：text 分别代表监控项不同的返回类型，表示监控项的数据会存储在哪张历史表中。5张历史表分别为： history history_log history_str history_text history_uint hostgroup：主机群组表； application：应用集表； 。。。 推荐百度文库有一篇写zabbix数据表的文章：Zabbix数据库表结构解析