Connecting
mysql -u USER -p # asks for the password
mysql -u USER -p DATABASE # and opens a database
mysql -h db.example.com -P 3306 -u USER -p DATABASE
sudo mysql # root via socket (Ubuntu/Debian/MariaDB)| Option | Meaning |
|---|---|
-u | user name |
-p | ask for password (never write -pSecret: it ends up in shell history and ps) |
-h | host (default: local socket) |
-P | port (default 3306) |
-D or last argument | database |
-e "SQL" | run one statement and exit |
-B / -N | batch output (tab-separated) / no column names |
On MariaDB the client is also called mariadb; mysql works as an alias.
Storing credentials safely
Create ~/.my.cnf, readable only by you:
[client]
user=shop
password=a-long-random-password
host=localhostchmod 600 ~/.my.cnfNow mysql shop logs in without asking. Oracle MySQL also offers an obfuscated store:
mysql_config_editor set --login-path=local --host=localhost --user=shop --password
mysql --login-path=local shopInside the client
| Command | What it does |
|---|---|
SHOW DATABASES; | list databases |
USE shop; | switch database |
SHOW TABLES; | list tables |
DESCRIBE customers; | columns of a table |
SHOW CREATE TABLE customers\G | full table definition |
\G instead of ; | show each row vertically – great for wide rows |
\s | status: version, user, connection, character set |
\c | cancel the current statement |
SOURCE file.sql | run a SQL file |
\! ls | run a shell command |
pager less -S | page long output (nopager to turn off) |
exit or \q | leave |
Statements end with ;. If the prompt shows ->, MySQL is waiting for the rest of the statement – usually a missing ; or quote.
Running SQL files
mysql -u shop -p shop < schema.sql # import
mysql -u shop -p shop < dump.sql
gunzip < dump.sql.gz | mysql -u shop -p shopUsing mysql in shell scripts
# one value into a variable
COUNT=$(mysql -N -B -e "SELECT COUNT(*) FROM shop.orders")
echo "Orders: $COUNT"
# tab-separated output into a file
mysql -B -e "SELECT id, email FROM shop.customers" > customers.tsv
# loop over rows
mysql -N -B -e "SELECT id, email FROM shop.customers" |
while IFS=$'\t' read -r id email; do
echo "$id -> $email"
doneCredentials come from ~/.my.cnf, so no password appears in the script.
Other useful tools
| Tool | Use |
|---|---|
mysqladmin status | quick health check |
mysqladmin processlist | running queries |
mysqldump | logical backups – see MySQL Backup and Restore |
mysqlcheck | check and repair tables |
mysqlsh | MySQL Shell (JavaScript/Python/SQL, dump utilities), separate package |
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.