Log in Sign up

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)
OptionMeaning
-uuser name
-pask for password (never write -pSecret: it ends up in shell history and ps)
-hhost (default: local socket)
-Pport (default 3306)
-D or last argumentdatabase
-e "SQL"run one statement and exit
-B / -Nbatch 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=localhost
chmod 600 ~/.my.cnf

Now 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 shop

Inside the client

CommandWhat it does
SHOW DATABASES;list databases
USE shop;switch database
SHOW TABLES;list tables
DESCRIBE customers;columns of a table
SHOW CREATE TABLE customers\Gfull table definition
\G instead of ;show each row vertically – great for wide rows
\sstatus: version, user, connection, character set
\ccancel the current statement
SOURCE file.sqlrun a SQL file
\! lsrun a shell command
pager less -Spage long output (nopager to turn off)
exit or \qleave

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 shop

Using 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"
done

Credentials come from ~/.my.cnf, so no password appears in the script.

Other useful tools

ToolUse
mysqladmin statusquick health check
mysqladmin processlistrunning queries
mysqldumplogical backups – see MySQL Backup and Restore
mysqlcheckcheck and repair tables
mysqlshMySQL Shell (JavaScript/Python/SQL, dump utilities), separate package

Next: MySQL Databases, Tables and Data Types

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.