## Query and administer MySQL and MariaDB from the terminal

# Connect and be prompted for the password
mysql -u root -p

# Connect to a database directly
mysql -u merab -p mydb

# Connect to a remote host
mysql -h db.example.com -u merab -p mydb

# Connect on a non-default port
mysql -h db.example.com -P 3307 -u merab -p mydb

# Connect over a socket
mysql -S /var/run/mysqld/mysqld.sock -u root -p

# Local root login via the auth socket
sudo mysql

# Run one query and exit
mysql -u root -p -e 'SHOW DATABASES'

# Run a query against a database
mysql -u merab -p -D mydb -e 'SELECT count(*) FROM users'

# Run a script file
mysql -u merab -p mydb < schema.sql

# Run a script and stop at the first error
mysql -u merab -p --force=0 mydb < migration.sql

# Batch mode: tab-separated, no table borders
mysql -B -u merab -p -e 'SELECT id, email FROM users' mydb

# No column headers, for scripting
mysql -N -B -u merab -p -e 'SELECT id FROM users' mydb

# Vertical output, readable for wide rows
mysql -E -u merab -p -e 'SELECT * FROM users LIMIT 1' mydb

# Raw output, without escaping
mysql -r -B -u merab -p -e 'SELECT notes FROM posts' mydb

# Write output to a file
mysql -u merab -p -e 'SELECT * FROM users' mydb > users.tsv

# Export a query as CSV
mysql -B -u merab -p -e 'SELECT * FROM users' mydb | sed 's/\t/,/g' > users.csv

# Import a CSV into a table
mysqlimport --local --fields-terminated-by=, -u merab -p mydb users.csv

# List databases
mysql -u root -p -e 'SHOW DATABASES'

# List tables in a database
mysql -u root -p -e 'SHOW TABLES' mydb

# Describe a table
mysql -u root -p -e 'DESCRIBE users' mydb

# Show a table's create statement
mysql -u root -p -e 'SHOW CREATE TABLE users' mydb

# Show indexes
mysql -u root -p -e 'SHOW INDEX FROM users' mydb

# Table sizes, largest first
mysql -u root -p -e "SELECT table_name, ROUND(data_length/1024/1024) AS data_mb, ROUND(index_length/1024/1024) AS idx_mb FROM information_schema.tables WHERE table_schema='mydb' ORDER BY data_length DESC LIMIT 10"

# Database sizes
mysql -u root -p -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY 1 ORDER BY mb DESC"

# Current connections and what they are running
mysql -u root -p -e 'SHOW FULL PROCESSLIST'

# Only the queries that are actually running
mysql -u root -p -e "SELECT id, user, time, LEFT(info,60) FROM information_schema.processlist WHERE command <> 'Sleep'"

# Kill a query
mysql -u root -p -e 'KILL 4821'

# Server status variables
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads%'"

# Configuration variables
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections'"

# Explain a query plan
mysql -u root -p -e 'EXPLAIN SELECT * FROM users WHERE email = "a@b.c"' mydb

# Create a database
mysql -u root -p -e 'CREATE DATABASE myapp CHARACTER SET utf8mb4'

# Create a user and grant rights
mysql -u root -p -e "CREATE USER 'myapp'@'%' IDENTIFIED BY 'secret'; GRANT ALL ON myapp.* TO 'myapp'@'%'; FLUSH PRIVILEGES"

# Show a user's grants
mysql -u root -p -e "SHOW GRANTS FOR 'myapp'@'%'"

# Back up one database
mysqldump -u root -p --single-transaction mydb > mydb.sql

# Back up compressed
mysqldump -u root -p --single-transaction mydb | gzip > mydb.sql.gz

# Back up schema only
mysqldump -u root -p --no-data mydb > schema.sql

# Back up every database
mysqldump -u root -p --all-databases --single-transaction | gzip > all.sql.gz

# Restore from a dump
mysql -u root -p mydb < mydb.sql

# Restore from a compressed dump
zcat mydb.sql.gz | mysql -u root -p mydb

# Check and repair tables
mysqlcheck -u root -p --auto-repair --check mydb

# Avoid the password on the command line: use a config file
printf '[client]\nuser=merab\npassword=secret\n' > ~/.my.cnf && chmod 600 ~/.my.cnf
