mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
[Português](COOKBOOK.pt-BR.md)

# mariadb-cli cookbook


## Getting connected
- Start here, because every other recipe needs a destination

### Store a destination and confirm it
- Run `mariadb-cli conn add production --host db.example --user reader --password-stdin`
- Type the password into standard input, so it never reaches your shell history
- Run `mariadb-cli conn test production` to open, confirm and close
- Read the exit number, because `0` is the only proof of a working destination
- Run `mariadb-cli conn default production` to stop repeating `--profile`
- Run `mariadb-cli conn show production` to confirm the host with the secret redacted
- Run `mariadb-cli conn list` whenever you forget which destinations exist


## Exploring structure
- Walk down from the whole database to a single column

### Explore an unknown database from the map to the column
- Run `mariadb-cli schema databases` to see what this credential can reach
- Run `mariadb-cli schema map --database shop` for the shape on one screen
- Run `mariadb-cli schema tables --database shop` for the full list of tables
- Run `mariadb-cli schema columns --table orders` to describe the table you chose
- Run `mariadb-cli schema keys --table orders` to see what that table points at
- Run `mariadb-cli schema indexes --table orders` to see what the reads can use
- Run `mariadb-cli schema search order` when you only remember half a name
- Run `mariadb-cli schema grants` first when a listing comes back suspiciously empty


## Reading data
- Cut the result at the source instead of trimming it afterwards

### Read data with fewer columns and a row ceiling
- Run `mariadb-cli query sample --table orders` to feel the data first
- Run `mariadb-cli query run 'SELECT id, total FROM orders' --select id,total --limit 50`
- Use `--select` to drop every field you will not read
- Use `--limit` to cap the rows before they cross the channel
- Use `--filter` to keep only the rows that match
- Use `--count-only` when the number of rows is the whole answer
- Use `--max-output-bytes` when you must guarantee a small payload

### Consume a result one row at a time in ndjson
- Run `mariadb-cli query run 'SELECT id, total FROM orders' --output-format ndjson`
- Each line is one complete document, so you parse as the rows arrive
- Never wait for the closing bracket of a large document again
- Add `--cursor` to resume where a previous invocation stopped
- Add `--output rows.ndjson` to keep the stream on disk
- Run `mariadb-cli meta schema --types` to know the shape of each line


## Changing data
- Rehearse first, then commit, and always cap the damage

### Rehearse a change before you run it
- Run `mariadb-cli query run 'UPDATE orders SET total = 0 WHERE id = 1' --dry-run`
- The rehearsal shows the intent without touching the server
- Run the same statement without `--dry-run` once the intent is right
- Add `--max-affected 1` so a wrong restriction cannot reach a thousand rows
- Add `--row-count changed` to count what the server really altered
- Keep `sql.require_where` on, so a change without a restriction is refused
- Add `--read-only` to any exploratory invocation that must not change anything

### Group changes in one atomic block
- Run `mariadb-cli query tx --help` to see how the statements are supplied
- Either every statement in the block lands, or none of them does
- Rehearse the block with `--dry-run` before you let it run
- Cap it with `--max-affected` exactly as you would cap a single change
- Read the exit number, because `9` means a lock conflict worth retrying


## Moving data
- Files are how a copy leaves one destination and enters another

### Export a table to a file
- Run `mariadb-cli data export --table orders --to orders.sql`
- The flag is `--to` and NOT `--output`, and the difference is not cosmetic
- `--output` is the global flag for the answer of the invocation itself
- Passing `--output` here writes the receipt over the copy you just made
- Add `--structure-only` when you want the definition without the rows
- Add `--max-bytes` to stop a copy before it fills the disk
- Set `data.export_lock` first if the copy must be consistent

### Load a delimited file into a table
- Run `mariadb-cli data import --file rows.csv --table orders --separator , --header`
- Import reads a delimited file, so it is NOT the reverse of `data export`
- To replay what `data export` wrote, run `mariadb-cli query file orders.sql`
- Tune `import.batch_size` when the load is slow or the packets are large
- Turn `data.stop_on_refusal` on, because it is off by default
- With it off one bad row is skipped and the load reports success
- Run `mariadb-cli schema count --table orders` to confirm what arrived


## Watching the server
- Find the connection that is holding everybody else still

### Find what blocks the server and end the guilty connection
- Run `mariadb-cli server processes` to list every active connection
- Run `mariadb-cli server locks` to see who waits on whom
- Run `mariadb-cli server queries` to see which pattern costs the most
- Run `mariadb-cli query plan --help` to inspect the plan of a running statement
- Run `mariadb-cli server kill --help` for the identifier the leaf expects
- Confirmation is demanded while `server.kill_foreign_requires_yes` is on
- Run `mariadb-cli server status` afterwards to confirm the counters calmed down


## Reusing work
- Store a statement once and stop pasting it

### Store and reuse a statement
- Run `mariadb-cli query named save daily-orders 'SELECT id, total FROM orders'`
- Run `mariadb-cli query named list` to see what is already stored
- Run `mariadb-cli query named run daily-orders` whenever you need it again
- The stored text runs under the same classification as a fresh statement
- Run `mariadb-cli query named remove daily-orders` when it stops being useful


## Reading the past
- A versioned table answers what it looked like before

### Read the history of a table
- Run `mariadb-cli history tables --database shop` to find the versioned tables
- Run `mariadb-cli history as-of --table orders --help` for the instant argument
- Use `history as-of` to answer what a report said at one moment
- Run `mariadb-cli history between --table orders --help` for the interval arguments
- Use `history between` to explain a number that moved without an obvious cause
- Combine either one with `--select` and `--limit`, exactly as you would a read


## Settings
- No value here is read from the environment, so the file is the whole truth
- The host still chooses which file that is, through `XDG_CONFIG_HOME`

### Read where the settings live and change one
- Run `mariadb-cli config path` to learn which file is in force
- Run `mariadb-cli config list` for the effective value and origin of every key
- Run `mariadb-cli config get sql.require_where` when one key is the question
- Run `mariadb-cli config set sql.require_where true` to write it
- Run `mariadb-cli config unset sql.require_where` to fall back to the default
- Prefer unsetting to writing the default, because origin is part of the answer

### Diagnose a configuration that stopped working
- Run `mariadb-cli config doctor` before you suspect the server
- It names an invalid key, an unreadable file and a wrong permission
- Read the exit number, because a clean run is the only proof of a sane file
- Run `mariadb-cli config path` next when the answer surprises you
- A second file in a second location is the usual cause


## Managing destinations
- A destination outlives the session that created it

### Correct a destination without typing it again
- Run `mariadb-cli conn show production` to see what is stored, redacted
- Run `mariadb-cli conn edit production --host db2.example` to move it
- Run `mariadb-cli conn edit production --port 3307` when only the port moved
- Run `mariadb-cli conn test production` after every edit
- Run `mariadb-cli conn remove staging` to delete the profile and its secret
- Removal takes the stored secret with it, so it is not an undo


## Understanding a statement
- Ask the server what it plans before you let it work

### See the plan before you pay for the statement
- Run `mariadb-cli query explain 'SELECT id, total FROM orders WHERE id = 1'`
- The plan comes back without the statement ever being executed
- Run `mariadb-cli query analyze 'SELECT id, total FROM orders WHERE id = 1'` for real numbers
- Analysis executes the statement, so it counts as a change
- Compare the two when the estimate and the clock disagree
- Run `mariadb-cli query plan --help` for the plan of a statement already running

### Trace where each column really came from
- Run `mariadb-cli query lineage 'SELECT id, total FROM orders'`
- It answers the physical origin of every column in the result
- Use it before you trust a column that a view or a join produced
- A column whose origin surprises you is a bug waiting for a report

### Run a file of statements and call a stored routine
- Run `mariadb-cli query file migration.sql` to run a file of statements
- Rehearse it with `--dry-run` exactly as you would rehearse one statement
- Cap it with `--max-affected`, because a file can reach further than you meant
- Run `mariadb-cli query call daily_report` to call a stored routine
- The output parameters come back in the payload, not on the auxiliary channel


## The rules a table carries
- A table refuses more than its columns admit

### Read the restrictions and the triggers a table enforces
- Run `mariadb-cli schema checks --database shop` for the value restrictions
- Each condition comes back in full, so you can read what it refuses
- Run `mariadb-cli schema triggers` for what fires on its own
- A trigger explains a change that no statement of yours accounts for
- Read both before you conclude that a write was lost

### Read the re-creatable definition of an object
- Run `mariadb-cli schema ddl orders` for the textual definition of the object
- The object is a positional argument here, never a flag
- Run `mariadb-cli schema views --database shop` to list the views
- The materialised ones are cut out and named separately
- Run `mariadb-cli schema routines --database shop` for the routines and functions


## Space and schedules
- Growth and automation are the two things nobody watches

### Find where the space went
- Run `mariadb-cli schema size --database shop` for space and the largest objects
- Run `mariadb-cli schema stats --database shop` for the table statistics
- Run `mariadb-cli schema partitions --database shop` when one table dwarfs the rest
- Stale statistics explain a plan that turned bad without a schema change
- Combine any of these with `--sort` and `--limit` to rank what you got

### Inventory what runs without you
- Run `mariadb-cli schema events --database shop` for the scheduled events
- A scheduled event is a change you did not make and did not see
- Run `mariadb-cli schema sequences --database shop` for the sequences
- Read both before you migrate a database you did not build


## The server itself
- Know the instance before you blame the query

### Know the server you are talking to
- Run `mariadb-cli server capabilities` for the variant and its optional features
- A feature absent here explains a command that refuses on this instance
- Run `mariadb-cli server variables` for the configuration variables in force
- Run `mariadb-cli server replica` for the replication situation
- A replica behind its source explains a read that looks stale but is not wrong


## Tooling
- Teach your shell and your reader about this binary

### Generate shell completions and the manual
- Run `mariadb-cli meta completions --help` to see the shells offered
- Run `mariadb-cli meta completions bash` for your shell and install the script once
- Run `mariadb-cli meta manual` to read the manual on the primary channel
- Run `mariadb-cli meta manual --output mariadb-cli.1` to write it to a file
- Run `mariadb-cli meta commands` when you need the tree in structured form
- Run `mariadb-cli meta version` before you report any problem