mysql

mysql connects to MySQL or MariaDB, inspects schema and rows, makes focused changes, exports tables, runs native SQL and creates SQL dumps.

DatabaseVersion 1.1.4StableAliases: mariadb
lil-terminal

Overview

Use mysql for both MySQL and MariaDB: passwords stay out of command history, common schema and row tasks have short flags, and native SQL remains available after :.

Tables, rows and native SQL

MySQL and MariaDB are client-server relational databases. Think in databases, tables, rows, columns, indexes and transactions: the table schema constrains the shape of data, while SQL describes sets of rows rather than a sequence of file edits. The mysql driver serves both MySQL and MariaDB; advanced engine-specific SQL remains available after :.

Syntax

lil-terminal
mysql [options] · mysql : <sql>

Reference

mysql

show the current MySQL / MariaDB connection

mysql -c <host> <user> [database] [-p <port>] [-s <socket>] [-charset <charset>] [-n] [-m [name]]

connect; password is requested separately unless -n is used

-use <database>

select another database

-l

list databases

-t

list tables and views

-s <table>

show columns and data types

-n <table> [rows]

preview table rows

-f <table> <text> [rows]

find text across table columns

-o <table> [-j|-csv] [file] [-f]

export table rows

-a <table> : <json>

insert one row from a JSON object

-w <table> <field> <value> : <json>

update matching rows from a JSON object

-d <table> <field> <value>

delete matching rows

-m [name]

save the current connection in .lil.key; default name is the database

-M <name>

remove a saved connection

-k [name]

list saved connections or inspect one without showing its password

-K <name>

connect using a saved connection

-e

disconnect and clear connection data

mysql : <sql>

execute native SQL

mysql load <file.sql>

execute SQL from a file

mysql dump [file.sql] [-f]

create a schema and data dump

mariadb [options]

alias for @mysql

What it does

mysql connects to a running MySQL or MariaDB server; unlike SQLite, it does not open a local database file. It is useful when a hosted site stores users, articles or orders in server-managed tables. mariadb is an alias for the same driver.

Practical steps

Connect with mysql -c localhost demo shop; the password is requested in a separate secret prompt, not written into command history. -n is specifically for connections without a password; -p selects the port, -s the socket and -charset the connection charset. Then use -t for tables, -s products for columns, -n products 2 for sample rows, -f for a text search or -o to export JSON/CSV. SQL after mysql : is executed as written. dump writes schema and rows to SQL; load executes a SQL file.

Limits and safety

Use a restricted account and inspect a SELECT before changing rows. -a inserts JSON fields, -w updates all matches and -d deletes all matches: these operations are not automatically reversible. A saved -m profile may contain a password in .lil.key. Exporting more than 100,000 rows is rejected; load rejects files over 32 MiB. PHP MySQLi and access to a MySQL/MariaDB server are required. The demonstration uses a fixed SQL fixture and is not a live connection.

Interactive sandbox preview

Run the prepared examples directly on the page. Their results are prebuilt and shown in the browser; no terminal command is executed on the server.

Inspect a MySQL / MariaDB table

Install and manage

Install the current compatible version. Choose Base for the complete feature set or Minimum for the reduced package; use upgrade to update a module that is already installed.

Base and Minimum are two packages of the same current module release, not separate command versions.

Base package

Install the complete package with local command reference and every published mode.

install mysql
Minimum package

Install the reduced package. Local reference is omitted; help <cmd> -i opens the current server reference.

install mysql -m
Exact public version

Install exactly this published version.

install mysql -v 1.1.4
Check or update

Ask upgrade to check compatibility and replace the installed block only when needed.

upgrade mysql
Remove module

Remove the extension block while leaving the core and unrelated modules intact.

install mysql -u

Examples

Examples are copyable command lines, not actions executed by this website. Review paths, permissions and destructive flags before running them on a real project.

Inspect a MySQL / MariaDB table
mysql -c localhost demo shop
Status    connected
Server    MySQL / MariaDB
Host      localhost:3306
User      demo
Database  shop
Charset   utf8mb4
Connect; password is requested separately unless -n is used
mysql -c localhost demo shop -n
Status    connected
Server    MySQL / MariaDB
Host      localhost:3306
User      demo
Database  shop
Charset   utf8mb4
Connect; password is requested separately unless -n is used
mysql -c localhost demo shop -p 3307 -charset utf8mb4
Status    connected
Server    MySQL / MariaDB
Host      localhost:3307
User      demo
Database  shop
Charset   utf8mb4
Connect; password is requested separately unless -n is used
mysql -c localhost demo shop -s /tmp/mysql.sock -n
Status    connected
Server    MySQL / MariaDB
Host      localhost:3306
User      demo
Database  shop
Charset   utf8mb4
Show the current MySQL / MariaDB connection
mysql
Status    connected
Database  shop
Charset   utf8mb4
Select another database
mysql -use shop
Status    connected
Database  shop
List databases
mysql -l
shop
List tables and views
mysql -t
products	BASE TABLE
Show columns and data types
mysql -s products
Field	Type	Null	Key	Default	Extra
id	bigint unsigned	NO	PRI	NULL	auto_increment
sku	varchar(64)	NO	UNI	NULL	
name	varchar(180)	NO		NULL	
category	varchar(80)	NO		NULL	
price	decimal(10,2)	NO		NULL	
active	tinyint(1)	NO		1
Preview table rows
mysql -n products 2
id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.90	1
2	BAG-1	Canvas bag	bags	44.50	1
Find text across table columns
mysql -f products "shoe"
id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.90	1
Export table rows
mysql -o products -j
[
  {
    "id": 1,
    "sku": "SHOE-1",
    "name": "City shoe",
    "category": "shoes",
    "price": "79.90",
    "active": 1
  },
  {
    "id": 2,
    "sku": "BAG-1",
    "name": "Canvas bag",
    "category": "bags",
    "price": "44.50",
    "active": 1
  }
]
Export table rows
mysql -o products -csv tmp/products.csv
Saved  sandbox/tmp/products.csv
Handle a missing table
mysql -o products -j tmp/products.json -f
Saved  sandbox/tmp/products.json
Insert one row from a JSON object
mysql -a products : {"sku":"NEW-1","name":"Sample","category":"test","price":5,"active":1}
Inserted  1
ID        3
Update matching rows from a JSON object
mysql -w products sku SHOE-1 : {"price":75}
Updated  1
Delete matching rows
mysql -d products sku SHOE-1
Deleted  1
Execute native SQL
mysql : SELECT COUNT(*) AS total FROM products
total
2
Time  (varies) ms
Create a schema and data dump
mysql dump tmp/shop.sql
Saved   sandbox/tmp/shop.sql
Tables  1
Views   0
Execute SQL from a file
mysql load db/mysql.sql
MySQL error 1050: Table 'products' already exists
Save the current connection in .lil.key; default name is the database
mysql -m demo
Saved  demo
List saved connections or inspect one without showing its password
mysql -k
No saved connections
Connect using a saved connection
mysql -K missing
Saved connection not found: missing
Remove a saved connection
mysql -M missing
Saved connection not found: missing
Disconnect and clear connection data
mysql -e
+
Alias for @mysql
mariadb -t
products	BASE TABLE
Connect; password is requested separately unless -n is used
mysql -c localhost demo shop -p 99999
Invalid database port

File used in this example: sandbox/db/mysql.sql

The example uses a file or directory from the protected /sandbox/ documentation workspace.

CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4;
USE shop;
CREATE TABLE products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(64) UNIQUE NOT NULL, name VARCHAR(180) NOT NULL, category VARCHAR(80) NOT NULL, price DECIMAL(10,2) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1);
INSERT INTO products(sku,name,category,price,active) VALUES ('SHOE-1','City shoe','shoes',79.90,1),('BAG-1','Canvas bag','bags',44.50,1);

Related documentation

Which databases can I work with from the terminal? →Is lil-terminal safe to leave on a production site? →

Current public release

v1.1.42026-11-01

Production refinement

Current stable release of mysql. mysql connects to MySQL or MariaDB, inspects schema and rows, makes focused changes, exports tables, runs native SQL and creates SQL dumps.

# Version history