MySQL

This app installs the official MySQL 9.7.1 server (mysqld) and client tools (mysql, mysqladmin, mysqldump, mysqlshow) on the host and fronts them as typed methods. The bundle is the upstream MySQL suite (sha-pinned per OS/arch, fetched from the Pilot artifact registry at install) plus a tiny mysqlctl dispatcher that makes the server relocatable — it finds its plugins and error messages wherever the bundle is staged.

MySQL is a real client/server RDBMS — the database behind a huge share of the web. Unlike a hosted MySQL, there is no cloud account, no credentials to provision, and no network egress: the agent runs mysql.initialize once to lay down a data directory, mysql.start to bring up a server on 127.0.0.1:<port>, and then creates databases and runs SQL — all locally, in its own sandbox. It is the transactional, server-grade complement to the embedded io.pilot.sqlite and the analytics-focused io.pilot.duckdb, and a sibling to io.pilot.postgres.

Why an agent wants this

- A full local MySQL, zero provisioning. No RDS, no docker run, no credentials. initialize → start → createdb → query, all on 127.0.0.1. - Real server semantics. Transactions, InnoDB, foreign keys, triggers, stored routines, JSON columns, window functions, full-text indexes — the actual MySQL engine, not an emulation. - The MySQL dialect + wire protocol. Test and run exactly what your production MySQL will see: same SQL, same mysqldump format, same client tools. - Durable + portable. The data directory is a real MySQL instance the agent can stop, restart, back up with mysql.dump, or hand off. - Agent-friendly output. mysql.query returns an aligned table; mysql.query_tsv returns tab-separated values for clean parsing.

Methods

- mysql.initialize — create a new data directory (system tables + insecure root). Run once. - mysql.start / mysql.stop — bring a server on 127.0.0.1:<port> up (detached) / down (clean shutdown). - mysql.ping — is the server accepting connections? - mysql.createdb — create a database. - mysql.query — run SQL, get an aligned table. mysql.query_tsv — same, as TSV. - mysql.databases / mysql.tables — list databases / tables. - mysql.dump — mysqldump a database to portable SQL. - mysql.exec — run any bundled tool with a verbatim argv (+ optional stdin) for anything the curated methods don't cover (--vertical, mysqlshow, SOURCE script.sql, a different output mode, …). - mysql.mysql_help — the full mysql client help. mysql.version — the delivered version. mysql.help — the self-describing method list.

How to use it (typical flow)

1. Initialize once: mysql.initialize { "datadir": "/work/mysql-data" }. 2. Start the server: mysql.start { "datadir": "/work/mysql-data", "port": "13306" } (then mysql.ping). 3. Create a database: mysql.createdb { "port": "13306", "dbname": "app" }. 4. Run SQL: mysql.query { "port": "13306", "database": "app", "sql": "CREATE TABLE t(id INT PRIMARY KEY, v TEXT); INSERT INTO t VALUES (1,'hi'); SELECT * FROM t;" }. 5. Back up / stop: mysql.dump { "port": "13306", "database": "app" }, then mysql.stop { "port": "13306" }.

Configuration & connection

- Connection is always local: 127.0.0.1:<port>, user root. After initialize the root password is empty; set one with mysql.exec (ALTER USER 'root'@'localhost' IDENTIFIED BY '…') and pass it to later calls via the MYSQL_PWD environment variable (opted into env_passthrough). - Data directory — each datadir is an independent MySQL instance; run several on different ports. - X protocol is disabled (--mysqlx=OFF) for a lean local footprint; the classic protocol is on. - Anything else — mysql.exec gives you the raw tools: --vertical/--html/--xml output, mysqlshow, SOURCE a script over stdin, or mysqld/mysqladmin flags the curated methods don't expose.

Good to know

- On a non-zero exit (SQL error) the reply is {stdout, stderr, exit} so the caller sees everything the tool produced. - Runs on macOS and Linux (arm64 + amd64); binaries come from the Pilot artifact registry, sha-pinned on install. The server must not run as OS root — the Pilot daemon runs unprivileged, which is exactly right. - MySQL Community Server is GPL-2.0 licensed. mysql.help lists every method with its latency class.

## MySQL client help (mysql.mysql_help) ``` /tmp/mysql-reloc/mysql-9.7.1-darwin-arm64/bin/mysql Ver 9.7.1 for macos15.7 on arm64 (conda-forge) Copyright (c) 2000, 2026, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Usage: /tmp/mysql-reloc/mysql-9.7.1-darwin-arm64/bin/mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --bind-address=name IP address to bind to. --binary-as-hex Print binary data as hex. Enabled by default for interactive terminals. --character-sets-dir=name Directory for character set files. --column-type-info Display column type information. --commands Enable or disable processing of local mysql commands. -c, --comments Preserve comments. Send comments to the server. The default is --comments (keep comments), disable with --skip-comments. (Defaults to on; use --skip-comments to disable.) -C, --compress Use compression in server/client protocol. -#, --debug[=#] This is a non-debug version. Catch this and exit. --debug-check This is a non-debug version. Catch this and exit. -T, --debug-info This is a non-debug version. Catch this and exit. -D, --database=name Database to use. --default-character-set=name Set the default character set. --delimiter=name Delimiter to be used. --enable-cleartext-plugin Enable/disable the clear text authentication plugin. -e, --execute=name Execute command and quit. (Disables --force and history file.) -E, --vertical Print the output of a query (rows) vertically. -f, --force Continue even if we get an SQL error. --histignore=name A colon-separated list of patterns to keep statements from getting logged into syslog and mysql history. -G, --named-commands Enable named commands. Named commands mean this program's internal commands; see mysql> help . When enabled, the named commands can be used from any line of the query, otherwise only from the first line, before an enter. Disable with --disable-named-commands. This option is disabled by default. -i, --ignore-spaces Ignore space after function names. --init-command=name Single SQL Command to execute when connecting to MySQL ```