mysql: Connect and Run SQL in CI

mysql -h host -u user -p -e "SQL" runs a single statement against MySQL and exits.

The mysql command-line client drives a MySQL or MariaDB server. In CI you run one statement with -e or a script on stdin, and you pass the password through the environment so it does not prompt.

What it does

mysql connects to a server and executes SQL. With -e it runs one statement and exits; without it and without piped input it starts an interactive shell. Note that -p with no value prompts for a password, while -pSECRET (no space) or the MYSQL_PWD environment variable supplies it non-interactively.

Common usage

Terminal

# one statement; MYSQL_PWD avoids the insecure -p on the command line
MYSQL_PWD=secret mysql -h db -u app -e "SELECT 1" appdb
# select a database and query it
MYSQL_PWD=secret mysql -h db -u app appdb -e "SHOW TABLES"
# password inline (visible to ps: prefer MYSQL_PWD)
mysql -h db -u app -psecret -e "SELECT VERSION()"

Options

Flag	What it does
-h <host>	Server host
-P <port>	Server port (default 3306)
-u <user>	User to connect as
-p[password]	Password; empty prompts, -pSECRET supplies it inline
-e "<sql>"	Execute one statement and exit
-D <db> / <db>	Database to use
-N / --batch	Skip column names / tab-separated output for scripts

In CI

Prefer MYSQL_PWD (or a ~/.my.cnf with a [client] password) over -p so the secret is not visible in the process list, and so the client never blocks on a prompt. Point -h at the MySQL service-container hostname and wait for it with mysqladmin ping before running statements.

Common errors in CI

"ERROR 2002 (HY000): Can't connect to local MySQL server through socket \"/var/run/mysqld/mysqld.sock\"" means you used the local socket (no -h or -h localhost) but the server is elsewhere; pass -h 127.0.0.1 or the service hostname to force TCP. "ERROR 1045 (28000): Access denied for user \"app\"@\"...\" (using password: YES)" is a wrong user or password. "ERROR 1049 (42000): Unknown database \"appdb\"" means the database does not exist.