mysqlreport(1)

💖 mysqlreport — the manual page, but make it slay

okay bestie, welcome to mysqlreport(1) from the MYSQL section of your friendly neighbourhood man pages 🎀 this is the part where I tell you what this little script does and you go "oh word?" and then we both go get a snack.

✨ NAME ✨

mysqlreport — makes a friendly report of important MySQL status values 💅

🌸 SYNTAX 🌸

mysqlreport [options]

🦋 DESCRIPTION 🦋

so. mysqlreport makes a friendly report of important MySQL status values. and when I say friendly I mean it. it actually makes a friendly report of nearly every status value from SHOW STATUS. like, the whole thing. no cap.

here's the tea: SHOW STATUS just dumps over 100 values onto your screen in one long list and leaves you there, blinking, like 😭. mysqlreport does not do that to you. it interprets and formats the values, and presents the basic values plus many more inferred values in a human-readable format. literally iconic. there are numerous example reports available at the mysqlreport web page at http://hackmysql.com/mysqlreport 🫶

the whole benefit of mysqlreport is that it lets you very quickly see a wide array of performance indicators for your MySQL server that would otherwise need to be calculated by hand from all the various SHOW STATUS values. for example (and this is such a good example), the Index Read Ratio is an important value but it's not present in SHOW STATUS — it's an inferred value, aka the ratio of Key_reads to Key_read_requests. see? slay. ✨

this documentation outlines all the command line options in mysqlreport, most of which control which reports are printed. it does not address how to interpret these reports — that topic is covered in the document Guide To Understanding mysqlreport at http://hackmysql.com/mysqlreportguide. iykyk 💜

💅 OPTIONS 💅

technically, command line options are in the form --option, but -option works too. all options can be abbreviated if the abbreviation is unique. for example, option --host can be abbreviated --ho but not --h because --h is ambiguous: it could mean --host or --help. rude of it, honestly. 🙈

• --help — output help information and exit. the classic.
• --user USER — your user, bestie.
• --password — as of version 2.3 --password can take the password on the command line like "--password FOO". using --password alone without giving a password on the command line causes mysqlreport to prompt for a password. mysterious. 🎀
• --host ADDRESS — the host address.
• --port PORT — the port.
• --socket SOCKET — the socket.
• --no-mycnf — --no-mycnf makes mysqlreport not read ~/.my.cnf which it does by default otherwise. --user and --password always override values from ~/.my.cnf. always. periodt.
• --dtq — print the Distribution of Total Queries (DTQ) report (under Total in the Questions report). queries (or Questions) can be divided into four main areas: DMS (see --dms below), Com_ (see --com below), COM_QUIT (see COM_QUIT and Questions at http://hackmysql.com/com_quit), and Unknown. --dtq lists the number of queries in each of these areas in descending order. ✨
• --dms — print the Data Manipulation Statements (DMS) report (under DMS in the Questions report). DMS are those from the MySQL manual section 13.2. Data Manipulation Statements. (currently, mysqlreport considers only SELECT, INSERT, REPLACE, UPDATE, and DELETE.) each DMS is listed in descending order by count.
• --com N — print top N number of non-DMS Com_ status values in descending order (after DMS in the Questions report). if N is not given, the default is 3. such non-DMS Com_ values include Com_change_db, Com_show_tables, Com_rollback, etc. etc. etc.
• --sas — print the report for Select_ and Sort_ status values (after the Questions report). see MySQL Select and Sort Status Variables at http://hackmysql.com/selectandsort 🦋
• --tab — print Threads, Aborted, and Bytes status reports (after the Created temp report). as of mysqlreport v2.3 the Threads report reports on all Threads_ status values. all of them. obsessed.
• --qcache — print the Query Cache report. 🧚
• --all — equivalent to "--dtq --dms --com 3 --sas --qcache". (notice --tab is not invoked by --all. just so we're clear, bestie. 👀)
• --infile FILE — instead of getting SHOW STATUS values from MySQL, read values from FILE. FILE is often a copy of the output of SHOW STATUS including formatting characters (|, +, -). mysqlreport expects FILE to have the format " value number " where value is only alpha and underscore characters (A-Z and _) and number is a positive integer. anything before, between, or after value and number is ignored. mysqlreport also needs the following MySQL server variables: version, table_cache, max_connections, key_buffer_size, query_cache_size. these values can be specified in INFILE in the format "name = value" where name is one of the aforementioned server variables and value is a positive integer with or without a trailing M and possible periods (for version). for example, to specify an 18M key_buffer_size: key_buffer_size = 18M. or, a 256 table_cache: table_cache = 256. the M implies Megabytes not million, so 18M means 18,874,368 not 18,000,000. do not come for me about it, that's just the math. 😤 if these server variables are not specified the following defaults are used (respectively) which may cause strange values to be reported: 0.0.0, 64, 100, 8M, 0.
• --outfile FILE — after printing the report to screen, print the report to FILE too. internally, mysqlreport always writes the report to a temp file first: /tmp/mysqlreport.PID on *nix, c:sqlreport.PID on Windows (PID is the script's process ID). then it prints the temp file to screen. then if --outfile is specified, the temp file is copied to OUTFILE. after --email (below), the temp file is deleted. bye bye temp file 🫡
• --email ADDRESS — after printing the report to screen, email the report to ADDRESS. this option requires sendmail in /usr/sbin/, therefore it does not work on Windows. /usr/sbin/sendmail can be a sym link to qmail, for example, or any MTA that emulates sendmail's -t command line option and operation. the FROM: field is "mysqlreport", SUBJECT: is "MySQL status report". 💌
• --flush-status — execute a "FLUSH STATUS;" after generating the reports. if you do not have permissions in MySQL to do this an error from DBD::MariaDB::st will be printed after the reports. > bestie if you don't have the perms, the error is coming for you and it will be printed after the reports. you've been warned 💀

👑 AUTHORS 👑

Daniel Nichter 💖

if mysqlreport breaks, send me a message from http://hackmysql.com/feedback with the error. fr fr, tell me everything.

🌈 SEE ALSO 🌈

mytop(1) 🎀

the comprehensive Guide To Understanding mysqlreport at http://hackmysql.com/mysqlreportguide.

and that's the whole page, bestie ✨ this was written by Daniel Nichter, version 2.5, dated 2006-09-01 (docrev 2006-05-19) — so like, vintage, but in a main character way. go read your status values. go be free. 💜🦋