mysql

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

mysql [options] · mysql : <sql>

Reference

Inspection

-l lists databases, -t lists tables/views, -s shows column definitions, -n previews rows and -f searches text across a table. -o exports one table as JSON or CSV.

Editing

-a inserts a row from a JSON object. -w updates rows matching one field/value pair. -d deletes rows matching one field/value pair. For complex changes use native SQL after :.

Dump and restore

dump creates a SQL schema/data backup without shell tools. load executes an SQL file. Large backups are written directly to disk rather than passed through terminal output redirection.

Connection

-c accepts host, user and optional database. The password is requested separately. -n explicitly requests a passwordless connection. -p changes the TCP port, -s selects a socket and -charset changes the connection charset.

Safety

mysql can execute arbitrary SQL. Use a restricted database account and inspect destructive statements before execution.

-charset selects the connection charset. Table export refuses more than 100000 rows instead of writing a partial file.

Saved connections

Without -m, connection data remains scoped to the current PHP session. -m [name] explicitly stores the current profile in the protected .lil.key file (0600), -K <name> reconnects from it, -k lists or inspects profiles without exposing a stored password, and -M <name> removes one. kill removes .lil.key together with the other core state. Treat the file as sensitive because an opted-in profile can contain database credentials.

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.

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.

Database  shop

products
orders

id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.90	1
2	SHOE-2	Trail shoe	shoes	109.00	1
3	BAG-1	Canvas bag	bags	44.50	1

Status    connected
Server    MySQL / MariaDB
Host      localhost:3306
User      app
Database  shop
Charset   utf8mb4

information_schema
shop

Field     Type          Null  Key
id        bigint        NO    PRI
sku       varchar(64)   NO    UNI
name      varchar(180)  NO
category  varchar(80)   NO
price     decimal(10,2) NO
active    tinyint(1)    NO

id	sku	name	category	price
1	SHOE-1	City shoe	shoes	79.90
2	SHOE-2	Trail shoe	shoes	109.00

Saved  products.json

Inserted  1

Updated  1

Deleted  1

products
4

Saved   shop.sql
Tables  2
Views   0

Affected  1
Affected  0
Affected  0
Affected  2
Time      2 ms

+

Table not found: missing_table

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