MySQL/MariaDB

Common commands/use

mysql -e presumes that you have a .my.cnf in your user directory populated with the username and password of a user that has the privileges to do what you are doing. Such as: /root/.my.cnf

Otherwise, you would need to log into MySQL/MariaDB then execute the queries that are within the quotes.

Time and Date in MySQL/MariaDB

mysql -e "SELECT NOW();"

Show size of databases

mysql -e "SELECT table_schema AS 'Database Name', SUM(data_length + index_length) / 1024 / 1024 AS 'Database Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;" | grep 'Database\|USERNAME_'

MyISAM table size & InnoDB table size

MYD+MYI = total MyISAM, ibdata1+ibd = total InnoDB. MYI are the MyISAM indexes, MYD is the MyISAM data.

lsof -u mysql|awk '$9~/\/var\/lib\/mysql\// {print $7, $9}'|awk -F'[/,.]' '{print $1, $NF}'|sort -k2|awk '{array[$2]+=$1} END {for (i in array) {print array[i]"\t"i}}'

You can also do this query, but it can trash the cache:

mysql -e "SELECT ENGINE,ROUND(SUM(data_length) /1024/1024, 1) AS 'Data MB', ROUND(SUM(index_length)/1024/1024, 1) AS 'Index MB', ROUND(SUM(data_length + index_length)/1024/1024, 1) AS 'Total MB', COUNT(*) 'Num Tables' FROM  INFORMATION_SCHEMA.TABLES WHERE  table_schema not in ('information_schema', 'performance_schema') GROUP BY ENGINE;"

Max Connections used

mysql -e 'show status;' | grep Max

Show compiled-in options

Binary options plus my.cnf and other option files:

mysqld --verbose --help

Binary options ignoring my.cnf and other option files:

mysqld --no-defaults --verbose --help

This also works for mysqldump, myisamchk, and anything else that you'll see as a header in my.cnf. For example:

mysqldump --verbose --help

Handy to see if "Dump Completed" message will be appended to .sql dump files:

mysqldump --verbose --help | grep 'comments\|dump-date'