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.