mysql Client and Shell

The mysql Client and MySQL Shell

Keep passwords off the mysql command line, where ps and shell history see them: mysql_config_editor set --login-path=dev97 --host=127.0.0.1 --port=3301 --user=webdev --password prompts once and stores them, obfuscated, in ~/.mylogin.cnf. Here webdev is an account on the Docker 514 server, which holds the shop example database:

mysql --login-path=dev97 shop -N -B -e "SELECT sku, price FROM products ORDER BY id LIMIT 3" \
  | tr '\t' ','
mysql --login-path=dev97 shop -e 'SELECT id, sku FROM products ORDER BY id LIMIT 1\G'
mysql --login-path=dev97 shop --commands \
  -e 'SELECT id, sku FROM products ORDER BY id LIMIT 1\G'

Output

BK-PHP-01,39.90
BK-SQL-01,44.50
BK-SQL-02,29.00
ERROR at line 1: Unknown command '\G'.
*************************** 1. row ***************************
 id: 1
sku: BK-PHP-01

-N -B prints bare tab-separated rows for scripts, -t draws tables and \G one column per line. The second command catches people out: in batch mode (-e, a pipe, a script) the 9.7 client refuses its own commands such as \G, status and source unless you pass --commands. Interactively they work, and \s reports the connection type, TLS cipher and character sets.

MySQL Shell 524 (mysqlsh) adds JavaScript and Python modes (\js, \py) with a session object for scripting, the X DevAPI on port 33060, and utilities such as util.checkForServerUpgrade() and util.dumpInstance() (Logical Backups). Since 26.7 one Shell version serves every server from 8.4 up. For Ubuntu 26.04 225 Oracle publishes it only in the Innovation component, so this book installed the downloaded mysql-shell_26.7.1-1ubuntu26.04_amd64.deb with sudo apt-get install ./<file>, leaving the server on 9.7 LTS. Here $PW holds the account's password:

MySQL Shell as a script-friendly client that speaks JSONShell

echo "$PW" | mysqlsh --sql --uri webdev@127.0.0.1:3301/shop --passwords-from-stdin \
  --result-format=json/array -e "SELECT sku, price FROM products ORDER BY price DESC LIMIT 2"

Output

[
{"sku":"BK-LAR-01","price":49},
{"sku":"BK-SQL-01","price":44.5}
]

MENU

mysql Client and Shell