---mySQL Commands---
Take note, some of these commands use a backtick (also called grave accent) " " and not a single quote " ' " --selectingdatabase,tables, andcolumnsuse backtick, matching 'field' string values use single quote --there are ways to select databases, tables, and columns, with and without backtics:librenms.ports` OR librenms.ports Backticks are needed for reserved words like "columns" and "databases", these words should be avioded in mySQL since their use is already defined. If it cannot be avoided you must use backticks to select databases, tables, or columns. Commands (SHOW, SELECT, FROM, WHERE, etc.) do not need to be capitalized but typically they are presented that way for easy human interpretation of the syntax
$mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"' ##show location of mySQL database, USER is database username::
mysql -u librenms -p ##log into mySQL database using -u user 'librenms' and ask for -p password
pager less -SFX ##DO THIS BEFORE READING ANY TABLE!!! Set output of mySQL queries to pipe through less, use after logging into mySQL, makes database queries easily readable.
use librenms; ##select 'librenms' database
show tables; ##show tables, after database 'librenms' has been selected
CREATE DATABASE pydio; ##create database
GRANT ALL PRIVILEGES ON pydio.* TO 'pydio'@'localhost' IDENTIFIED BY 'pydio'; ##assign user and password to database
SHOW COLUMNS FROM librenms.devices; ##shows all columns from the librenms.devices table
SELECT * FROM librenms.ports WHERE ifOperStatus = 'down' AND ifAdminStatus = 'up' AND ifDescr LIKE 'WAN%'; ##select librenms database ports and search for port statuse, without backtick syntax version
SELECT * FROM librenms.ports WHERE ifOperStatus = 'down' AND ifAdminStatus = 'up' AND ifDescr LIKE 'WAN%'; ##select librenms database ports and search for port statuses, works the same as above but uses backtick syntax
SELECT status FROM librenms.devices WHERE device_id = '11'; ##show output of librenms.devices.status where the device_id=11
mysql --batch -u root --password=librenms -e "SELECT status FROM librenms.devices WHERE device_id = 11;" | grep -v status ##run from $bash command line, show output of librenms.devices.status where the device_id=11, --batch strips mySQL ---+ formatting and grep -v removes 'status' column header
mysql --batch --skip-column-names -u root --password=librenms -e "SELECT status FROM librenms.devices WHERE hostname = 'nms-nas';" ##same as above but using --skip-column-names to strip column names from the output
mysql --batch -u root --password=librenms -e "SELECT * FROM librenms.devices WHERE device_id = 11;"
SHOW TABLES LIKE '%state'; ##show database tables using the LIKE command, LIKE allows you to use wildcard (%) matches
mysqlcheck -u root -p --optimize --all-databases ##run from bash, optimizes all tables, same as OPTIMIZE TABLE
################################ ######---mySQL Optimize---###### ################################
SHOW GLOBAL STATUS; ##show mysql database statistics
SHOW VARIABLES LIKE 'query_cache_size'; ##see if the query cache is enabled, 0 is disabled, if not see next command
SET GLOBAL query_cache_size = 65536000; ##set the query_cache_size in bytes, should be a mutiple of 1024 bytes
SHOW STATUS LIKE 'Qcache%'; ##show query cache stats
SET GLOBAL thread_cache_size = 4; ##set re-usable threads, re-uses server connections when possible
SHOW VARIABLES LIKE 'thread_cache_size'; ##show 'thread_cache_size'
SET GLOBAL join_buffer_size = 262144; ##set the join_buffer_size in bytes, buffer size used for queries that can't use an index
SHOW VARIABLES LIKE 'join_buffer_size'; ##show the current 'join_buffer_size'