Last edit: July 07, 2019 10:07:57 AM CDT
List of all cheatsheets
See also Jamf Pro for additional information
MySQL
Don't forget to close with the ;
\G rather than ; can make for cleaner output
Caps are a convention, not a requirement
Is MySQL running
netstat -ntlap | awk '/3306/ && /LISTEN/'
Get MySQL performance stats
mysqladmin -u root -p status
• Threads: The number of active threads (clients)
• Questions: Number of queries since the server was started
• Open: number of tables the server has opened
• Open tables: number of currently open tables
What plugins and storage engines are installed
use information_schema; show plugins;
View active connections in mysql
show processlist;
show processlist full;
View active connections from CLI
mysqladmin -u root -p processlist
MySQL uptime
show status like 'Uptime';
Show config settings
show variables;
show variables like '[term]'; e.g. show variables like 'max_connections';
Dump current mysql values (what's in the conf not necessarily what the service allows)
mysqld --verbose --help
What database engine is in use
select table_name,engine from information_schema.tables where table_schema='[database name]';
Get engine information
show engines; See information about installed engines
use [database]; show table status; See information about engine used by a particular database
Get InnoDB engine information
show engine innodb status \G
Get database size
select table_schema "db name", round(sum(data_length + index_length) / 1024 / 1024, 1) "db size in mb" from information_schema.tables group by table_schema;
Sort and size tables
select table_schema as db,table_name, round((data_length+index_length) / 1048576,1) as size from information_schema.tables order by data_length+index_length desc limit 20;
Get a count of all tables in all databases in MySQL
select count(*) from information_schema.tables;
See connection values
show global status like '%connections%';
Get threads connected count
show global status like '%Threads_connected%';
Get open tables values
show global status like 'open%';
Get statistics about a table
show table status like '[table name]' \G
View configuration settings
show variables like '[term from my.cnf]';
What settings values was mysqld started with
mysqld --print-defaults
See memory allocation in MySQL 5.7+
select thread_id tid, user, current_allocated ca, total_allocated from sys.memory_by_thread_by_current_bytes;
Get thread-specific process information
use performance_schema;select thread_id, processlist_id, thread_os_id, type, name, processlist_db, processlist_user, processlist_command, processlist_state from threads;
Comparison operators that can be used in statements
= | != or <> | <, <=, >, >= | between (a range) | like (look for pattern) | is null | is not null | order by
Logical operators that can be used in statements
all | and | any | between (a range) | like (look for pattern) | not | or | exists
Search Operators
* (wildcard), % (multiple characters, e.g. MacBook%), _ (One wild character, e.g. _hicago)
Show all databases on server
show databases;
Create a database
create database [database name];
create database if not exists [database name];
Delete a database
drop database [database name];
drop database if exists [database name];
Delete a table
drop table [table name];
drop table if exists [table name];
Switch databases
use [database name];
Current working database
select database();
See all tables in a database
show tables;
See field formats (aka column names or keys)
describe [table name];
See how a table was created, including column names and default values
show create table [table name];
Add a column from a table
alter table [table name] add column [column name] [data_type];
Remove a column from a table
alter table [table name] drop column [column name];
Get a list of MySQL users
select user FROM mysql.user;
select user,host from mysql.user;
Find the privilege(s) granted to a particular MySQL account
show grants for 'UserName'@'HostName';
Create a new user
create user 'newuser'@'localhost' identified by 'password';
Grant access to a specific database
grant all privileges on 'db name between single quotes'.* to 'username'@'localhost';
flush privileges;
flush hosts;
Grant access to all databases
grant all privileges on *.* to 'username'@'%' with grant option;
flush privileges;
flush hosts;
Create a new user and grant privileges all in one
grant all on DataBaseName.* to 'username'@'hostname' identified by 'password';
Change a user's password
set password for 'user'@'localhost' = password('[password value to use]');
flush privileges;
Remove access rights
revoke all privileges on DataBaseName.* from 'UserName'@'HostName';
Remove a user
drop user 'UserName'@'HostName';
Change mysql root user's password in 5.7.6+
alter user 'root'@'localhost' identified by 'mynewpass';
Change mysql root user's password'
mysqladmin -u root -p [oldpassword] [newpassword]
Show all data in a table
select * from [table name];
Get the sum, average or max count from items in a table
select sum([column name]) from [table name];
select avg([column name]) from [table name];
select max([column name]) from [table name];
Show unique records
select distinct [column name] FROM [table name];
Mulitple where selection criteria
select column(s) where (column = '1st condition') and/or (column = '2nd condition')
Using WHERE to find matches
select */[column] from [table];
WHERE [column you want to search through][operator][value]
e.g. select * from princeNames; where lastName != Nevermind;
Basic Join/Inner Join
select [column(s)] from [1st table] JOIN [2nd table] ON [1stdb.matching column] = [2nddb.matching column]
Subquery
select [column(s) you want] from [table 1] where [column that exists in both tables] in (select [column that exists in both tables] from [table 2] where [criteria]);
Export MySQL query to a text file
select [criteria] from [table] into outfile '/path/to/file.txt';
Export MySQL query to a text file without logging into MySQL
mysql -u root -p -e "use [database]; select [columns] from [table];" > /path/to/export.txt
Dump a single database for backup in a script
mysqldump -u username -p[password right next to the “P”] [database name] >/path/to/[database name].sql
Dump multiple database for backup in a script
mysqldump -u username -p[password right next to the “P”] --databases [database names] >/path/to/[database name].sql
Restore a database from a dump backup
mysql -u username -p password [database name] < /path/to/[database name].sql
Set child node in a clustered environment rights
grant insert, select, update, delete, lock tables on DataBaseName.* to 'UserName'@'HostName' identified by 'Password';
View connections from cluster nodes with different users
grant process on 8.8 to 'UserName'@'HostName' identified by 'Password'; (needs to be master db user)
Repair a database that uses MyiSAM Engine
mysqlcheck --verbose --repair [database name] -u root -p
Repair a database that uses InnoDB Engine
mysqlcheck will not work properly with InnoDB. What can potentially work is:
1. Stop mysqld
2. Edit my.cnf and add the new line: innodb_force_recovery = 1
3. Restart mysqld
4. Dump the database: mysqldump [database name] --verbose (--force) > /path/todump.sql
5. Restore the data into new database: mysql db_name < dump.sql
MySQL on macOS
Basic Control
/usr/local/mysql/support-files/mysql.server start|stop|restart
macOS mysql best practice
Install MySQL and confirm works
+ Restart and check
Change mysql_root password: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';
+ Restart and check
Edit my.conf
+ Restart and check
Edit LaunchDaemon with Port
+ Restart and check
Uninstall mysql on macOS
/usr/local/mysql/support-files/mysql.server stop
edit /etc/hostconfig and remove the line MYSQLCOM=-YES- if present
rm /etc/my.cnf
rm /Library/LaunchDaemons/com.mysql.mysql.plist
rm -rf /Library/StartupItems/MySQLCOM if present
rm -rf /usr/local/mysql*
rm -rf ~/Library/PreferencePanes/My*
pkgutil —forget com.mysql.*
sqlite
sqlite can use MySQL query syntax but also has it's own unique commands
sqlite, unlike MySQL, does not have a server:client relationship
Files
.db or .sqlite = data fiile
.wal = Write Ahead Log
.shm = shared memory
Get a listing of tables in the database
.tables
Get a listing of tables in the database
.tables
Dump the database (ala select * from)
.dump
Get information about the database
.dbinfo
Equivalent of describe
.schema
Change how query results are presented
.mode line | column | list | quote
Write queries to a file
.output /path/to/file.txt (must be full path)
n.b. all queries will go to the file rather than standard output
Export query to CSV and open in Excel or Numbers
.excel
[query to run and export]
Close the existing database and open another database without closing current session
.open /path/to/file.db (must be full path)
Tomcat
Tomcat is nested objects all the way down
Structural Overview
Tomcat Server
\_Service (includes Listener)
Connectors ------>
\_Engine (includes Realms and Valves)
\_Host
\_Context
\_WebApp
This structure is mimicked in the server.xml config file:
<Server>
<Listerner>
<Service>
<Connector>
<Engine>
<Realm>
<Valve>
Clients communicate over Connectors. Requests coming in over a Connector are matched to an appropriate Container group, starting with the proper Engine for the request. The Engine in turn hands the request down to the proper Host, then Context and Webapp
Server: Overall application server. Controls startup and shutdown of the environment. Interacts with the JVM. Can contain multiple Services. Default port is 8005
Listeners: Listens for and responds to specific program events
Connectors: Handles communications between a Service and clients. There are multiple Connector types, including:
• HTTP/1.1 (aka Coyote) - Port 8080
• AJP - Port 8009
• SSL - Port 8443
n.b. “Catalina” is used as the name for a Container, a Service and an Engine as well as an object for Java to work with/address. These are all different things despite sharing the same “Catalina” name. The Catalina Service and Catalina Engine are the default Service and the primary Engine in Tomcat. In a way, Catalina is Tomcat but Tomcat is not just Catalina.
Container: An Engine, Host and Context group. Services are elements of a Containers.
Service: Combination of one or more Connector components that share a single Engine. Maps Connectors to Engines.
Engine: Processor that takes input from Connectors and directs requests to the proper Host (Tomcat container host, not a compute/server host).
n.b. Jasper is the JSP engine that parses and compiles Java code into items to be handled by the Catalina engine.
Realm: Per container user authentication mechanism. In Jamf Pro, this functionality is handled by the webapp and webapp database.
Valves: Intercepts incoming HTTP requests that are bound for a particular application, host or engine and preprocesses those requests. Valves are essentially filters.
Hosts: Represents a virtual machine within the Tomcat server that associates a network name (www.example.com) to the server. The default configuration of Tomcat includes the host named localhost. Keys that define application behavior (file system location, WAR unpacking) are defined per-host.
Context: Represents a single web application.
Tomcat Directory Structure
• /bin: Tomcat binaries and control scripts
• /conf: Tomcat global config files. Applicable to Tomcat Server and all webapps.
• /lib: Java files shared by all webapps
• /logs: Server/Service-level logs
• /webapps: Default application directory
• /work: Compiled source files in use by web apps
• /temp: Temp service files
Jamf Pro Webapp Directory Structure
• META-INF: Contains webapp-specific config files, including an app-level context.xml file. Required by Tomcat.
• WEB-INF: Contains application-specific config files (including web.xml), classes files and libraries used by the web app. Is not directly accessible by web clients. Required by Tomcat.
• api: API UI and backend elements.
• bin: jamf, jamfAgent, jamfHeper, jamfNotificationService, quickadd and SelfService binaries
• javascripts: Javascript libraries
• stylesheets: Site stylesheets
• ui: GUI for site
• favicon.ico, index.html, robots.txt, uapisamlfail.jsp and uapisamlsuccess.jsp: Extra bits
Tomcat Config Files
• server.xml: Main Tomcat config file. Defines how server presents itself to clients, how clients communicate with it and the capabilities (Engines) of the server. Applies to all webapps.
+ Threadpool in Server.xml is the maximum amount of threads that Tomcat can create for incoming connections to a webapp
• catalina.policy: Tomcat security policy
• catalina.properties: Sets some class loader paths, security package lists, and some tunable performance properties.
• context.xml: Global Tomcat-specific configuration options. Can also set per-webapp context.xml files.
• logging.properties: Defines logging options for Tomcat and webapps. Webapps can also have specific logging settings defined in per-app logging.properties file.
• tomcat-users.xml: Defines Tomcat-specific users and access rights. If using Tomcat Manager, configure credentials and access here.
• web.xml: Defines application needs to engine; sets how pages are presented and includes things like time out value. This is a global file, but webapps can have their own web.xml to define app-specific values.
• /path/to/tomcat/tomcat/bin/setenv.sh: Set global Tomcat Java custom configuration values.
+ CATALINA_OPS values are specific to Catalina. JAVA_OPS values will apply to all Java applications.
+ JAVA_OPS/CATALINA_OPS MaxPermSize or MaxMetasize value in setenv.sh: how much memory to use to load all components
Key Jamf Pro Webapp Config Files
Basepath: /path/to/tomcat/webapps/[$webAppName]/WEB-INF/
• /classes/log4j.properties: App-specific logging properties
• /classes/dal/cache.properties: Sets what type of cache type the JSS will use (ehcache or memcache).
• /classes/dal/ehcache.properties:ehcache service configuration. Ehcache is used in a single server environment.
+ If restoring from a backup, strip "DBAPPLIED_" from the contents
• /classes/dal/memcached.properties: memecache service configuration. Memcache is used in a clustered environment.
+ If restoring from a backup, strip "DBAPPLIED_" from the contents
• /xml/DataBase.xml: Defines database connection credentials and performance values; defines number of allowed MySQL connections from webapps to the database
+ MaxPoolSize is the number of allowed MySQL connections from webapps to the database
+ What Tomcat calls "pool size" MySQL calls “connections”
• /xml/JAMFSoftwareServerDatabaseSchema.xml: Jamf's current database schema
• web.xml: Defines application needs to engine; sets how pages are presented and includes things like time out value. Values here will override values set for global web.xml.
Tomcat Log Files
• catalina.[$dateStamp].log: Log rolled from catalina.out
• catalina.out: stdin and stderr from Tomcat processes. Covers all webapps. Includes logging of user access to webapps.
+ If the JSS is not loading, check this log for details.
+ Should list line and column numbers for errors in server.xml
• host-manager.[$dateStamp].log: Log for Tomcat Host-Manager webapp. Used to manage Tomcat clusters, ala the Tomcat Manager app.
• localhost.[$dateStamp].log: General Tomcat logging.
• manager.[$dateStamp].log: Log for Tomcat Manager webapp
• tomcatinit.log: Runtime info for Tomcat, including version
Jamf Pro Webapp Log Files
• JAMFChangeManagement.log: Tracks app-wide changes if Change Management is enabled in JSS.
• JAMFSoftwareServer: Webapp application log.
+ If web app isn't starting but Tomcat is up, check this log for details
• JSSAccess.log: User access logging. Includes remote IP values.
• jssinstaller.log: If using JAMF installer, install details logging
MySQL and Tomcat Configuration Settings for Jamf Pro
MySQL
# max_connections #
• How many connections will server support
• max_connections recommendation from Jamf:
(Number of webapps x [MaxPoolSize]) + 1
+ MaxPoolSize of 90 is the usual Jamf default
+ +1 should allow for a single CLI connection in the event of the service getting overwhelmeds
• Alternate max_connections recommendation:
([total MaxPoolSize of all webapps]) x 2 +1
# table_open_cache #
• How many open tables will be allowed
• table_open_cache is the new name for table_open
• Jamf Pro v10.x databases have an average of 435 tables per webapp database
• Calculate table cache hit rate:
table_open_cache x 100/Opened Tables
+ Show open tables values: show global status like 'open%';
n.b. open_table and opened_tables are two different values. opened_tables can increase over time as mysqld is running
+ Calculate this value to help figure out a starting point for table_open_cache
+ Should be over than 50% for optimal use
• table_open_cache recommendation from MySQL doc:
max_connections x N (where N is the maximum number of tables per join in any query)
• Alternate table_open_cache
Use table cache hit rate plus different values to calculate an expected hit rate over 50%
+ Perhaps start with (max_connections x total number of webapps) x .10
+ it's a guestimate based on a point in time. Adjust after system has been in use if needed
• Alternate table_open_cache recommendation from Percona:
10 x max_connections
+ Cap at 10,000
• Alternate table_open_cache recommendation:
(total number of tables) x threads_connected x .50
+ See thread count: show global status like '%Threads_connected%';
+ Can vary over mysqld operating time
# open_files_limit #
• How many files can be open at one time
• open_files_limit recommendation:
table_open_cache x 2
# table_definition_cache #
• “The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache”
• table_definition_cache recommendation from MySQL doc:
(table_open_cache / 2) + 400
# innodb_buffer_pool_size #
• How much memory can InnoDB use for operational in-memory cache
• Buffer Pool Size: 80% of total memory not allocated to OS and other services (including mysqld)
# innodb_flush_log_at_trx_commit #
• "=1" safest for data safety but slow performance
• “=2” should be best setting for performance without completely giving up on data safety
# innodb_log_file_size #
• InnoDB maintains a redo log (should be ib_logfile0 and ib_logfile1 in mysql directory) that maintain transaction information for use in recovery operations
• Minimum/default is 48MB. 256MB or more is recommended. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O
• n.b. the larger the log file the longer attempted recovery will take
Tomcat
# MaxPoolSize #
• Tomcat pool size is the equivalent of MySQL connections. Defined per-webapp in the Database.xml file
• Number of allowed connections from webapps to the database
• MaxPoolSize should always be less than max_connections value
• MaxPoolSize of 90 is the usual Jamf default
# maxThreads #
• maxThreads, in Server.xml, is the number of allowed connections coming into Tomcat
• From docs: “The maximum number of request processing threads to be created by this Connector, which therefore determines the maximum number of simultaneous requests that can be handled. If not specified, this attribute is set to 200.”
• There doesn't seem to be an upper limit in Tomcat, but is limited by OS filesystem resources and hardware resources. Might be best to cap at 2000. Also keep in mind that 100% utilitization from every web app is likely to never happen.
• Recommendation from Jamf:
(max_connections x 2.5) x (number of webapps)
# Setenv.sh #
• Catalina_opts will affect settings only for Tomcat
• Java_opts will change Java settings for all applications on the system
• "-Xms" is the minimm heap size for memory used, aka minimum amount of memory used by Tomcat
• "-Xmx" is the maximum heap size, aka maximum amount of memory used by Tomcat
• “-XX:PermSize” and “-XX:MaxPermSize” set permanent generation settings. Permanent generation is basically how much memory to allocate to Java virtual machine object description. 256MB default is OK.
Java Keytool
Generate a Java keystore and private key
keytool -genkey -alias [name] -keyalg RSA -keystore keystore.jks -keysize 2048
Generate a certificate signing request (CSR) for an existing Java keystore
keytool -certreq -keyalg [RSA] -alias [name] -keystore /path/to/keystore.jks -file [name].csr
Import a root or intermediate CA certificate to an existing Java keystore
keytool -import -alias [root/name] -keystore /path/to/keystore.jks -trustcacerts -file /path/to/cacer.pem
Add child cert to keystore
keystone -import [sitename] -keystore /path/to/keystore.jks -trustcacerts -file /path/to/cert.pem
Check a stand-alone certificate
keytool -printcert -v -file [cert].crt
View contents of keystore
keytool -list -v -keystore keystore.jks [optional -alias [name]]
Delete a certificate from a Java Keytool keystore
keytool -delete -alias [domain] -keystore keystore.jks
Change a Java keystore password
keytool -storepasswd -new new_storepass -keystore keystore.jks
Export a certificate from a keystore
keytool -export -alias [domain] -file mydomain.crt -keystore keystore.jks
List Trusted CA Certs
keytool -list -v -keystore $JAVA_HOME/jre/lib/security/cacerts
memcached
Get version
echo version | nc 127.0.0.1 11211
Get current settings
echo stats settings | nc localhost 11211
Get performance stats
echo stats | nc 127.0.0.1 11211
The most useful statistics here are the number of hits, misses, and evictions.
How much memory (in bytes) is memcached using
echo stats slabs | nc 127.0.0.1 11211 | awk '/total_malloced/ {print $3}'
How many items are currently cached
echo stats | nc 127.0.0.1 11211 | awk '/curr_items/'
How many items memcache has had to remove to make space for newer items
echo stats | nc 127.0.0.1 11211 | awk '/evictions/'
If high number, might need to allocate more memory to memcached
How many an item wasn't in the cache
echo stats | nc 127.0.0.1 11211 | awk '/get_misses/'
How many an item wasn't in the cache
echo stats | nc 127.0.0.1 11211 | awk '/get_misses/'
Number of bytes memached has supplied to the network
echo stats | nc 127.0.0.1 11211 | awk '/bytes_written/'
Get performance stats with libmemcached-tools installed (Ubuntu)
memcstat --servers=127.0.0.1
The most useful statistics here are the number of hits, misses, and evictions.