mysql [options] [database_name]

# Connect to local MySQL as root (password prompt appears)
mysql -u root -p

# Connect and select a database immediately
mysql -u appuser -p myapp_db

# Connect to a remote server
mysql -h db.example.com -P 3306 -u appuser -p myapp_db

# Connect via Unix socket (fastest for localhost on Linux/macOS)
mysql -u root -p -S /var/run/mysqld/mysqld.sock

# Execute a single statement and exit (useful in shell scripts)
mysql -u root -p -e "SHOW DATABASES;"

# Create ~/.my.cnf with your connection defaults
# Add these lines to the file:
# [client]
# user=appuser
# password=SecurePassword123!
# host=localhost
# database=myapp_db

# Secure the file — readable only by your user
chmod 600 ~/.my.cnf

# Now connect without flags — credentials come from the file
mysql

-- List all databases on the server
SHOW DATABASES;

-- Switch to a database
USE myapp_db;

-- Show which database is currently selected
SELECT DATABASE();

-- List all tables in the current database
SHOW TABLES;

-- Show tables matching a pattern
SHOW TABLES LIKE 'order%';

-- Describe a table (column names, types, constraints)
DESCRIBE users;
DESC users;

-- Show the full CREATE TABLE statement
SHOW CREATE TABLE users;

-- List all indexes on a table
SHOW INDEX FROM users;

-- Show running queries and connections
SHOW PROCESSLIST;

-- Show server status variables
SHOW STATUS LIKE 'Threads_connected';

-- Show server configuration variables
SHOW VARIABLES LIKE 'max_connections';

# Method 1: Redirect from the shell (fastest, works in scripts)
mysql -u appuser -p myapp_db < schema.sql

# With verbose output to see each statement
mysql -u root -p --verbose myapp_db < migration_001.sql

# Capture output and errors to a log file
mysql -u root -p myapp_db < migration.sql > migration.log 2>&1

-- Method 2: source command inside the mysql prompt
source /path/to/migration.sql;

-- Shorthand for source
. /path/to/migration.sql

# Tab-separated (easy to process with awk or Python)
mysql -u root -p --batch -e "SELECT id, email FROM users LIMIT 5;" myapp_db

# Silent mode — just the value, no headers
mysql -u root -p --silent --skip-column-names   -e "SELECT COUNT(*) FROM users;" myapp_db

-- Vertical output with G
SELECT * FROM users WHERE id = 1G

-- \! runs a shell command without leaving mysql
\! ls /var/log/mysql/
\! date

-- \e opens your $EDITOR to write the query, then executes on save
\e

DELIMITER //

CREATE PROCEDURE GetUserCount()
BEGIN
  SELECT COUNT(*) AS user_count FROM users;
END //

DELIMITER ;

-- Call the procedure
CALL GetUserCount();

#!/bin/bash
# Count rows in a table — reads credentials from ~/.my.cnf
DB="myapp_db"
TABLE="users"

COUNT=$(mysql --silent --skip-column-names "$DB"   -e "SELECT COUNT(*) FROM `$TABLE`;")

echo "Table $TABLE has $COUNT rows."

# Check if a migration has already run
APPLIED=$(mysql --silent --skip-column-names "$DB" -e "
  SELECT COUNT(*) FROM migrations
  WHERE migration_name = 'add_phone_column_to_users';
")

if [ "$APPLIED" -eq "0" ]; then
  echo "Running migration..."
  mysql "$DB" < add_phone_column_to_users.sql
  mysql "$DB" -e "
    INSERT INTO migrations (migration_name, applied_at)
    VALUES ('add_phone_column_to_users', NOW());
  "
else
  echo "Migration already applied, skipping."
fi

# Export a full database to a SQL dump
mysqldump -u root -p myapp_db > myapp_db_backup.sql

# Export compressed (much smaller files for large databases)
mysqldump -u root -p myapp_db | gzip > myapp_db_backup.sql.gz

# Export specific tables only
mysqldump -u root -p myapp_db users orders > users_orders.sql

# Export schema only — no INSERT statements
mysqldump -u root -p --no-data myapp_db > schema_only.sql

# Export with consistent snapshot (no table locks, InnoDB only)
mysqldump -u root -p --single-transaction myapp_db > myapp_db_backup.sql

# Import a SQL dump
mysql -u root -p myapp_db < myapp_db_backup.sql

# Import a compressed dump
gunzip -c myapp_db_backup.sql.gz | mysql -u root -p myapp_db

# Require SSL for the connection
mysql -u appuser -p   --ssl-mode=REQUIRED   --ssl-ca=/etc/mysql/ssl/ca.pem   --ssl-cert=/etc/mysql/ssl/client-cert.pem   --ssl-key=/etc/mysql/ssl/client-key.pem   -h db.example.com myapp_db

# Verify that SSL is active after connecting
mysql> SHOW STATUS LIKE 'Ssl_cipher';