# Connect
mysql -u root -p                         # prompt for password
mysql -u app_user -p mydb                # connect to specific database
mysql -h 127.0.0.1 -P 3306 -u root -p   # explicit host/port
mysql -u root -p --ssl-mode=REQUIRED     # enforce TLS

# One-liners (no interactive shell)
mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -pMySuperSecret mydb < dump.sql   # restore
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users;"

# MySQL shell meta commands (no semicolon needed)
\h           -- help
\s           -- server status, version, connection info
\u mydb      -- use database
\!           -- shell command
\q           -- quit
status       -- alias for \s

-- SQL meta queries
SHOW DATABASES;
SHOW TABLES;
SHOW TABLES FROM mydb;
DESCRIBE users;                        -- column info + types
SHOW CREATE TABLE users\G              -- full CREATE TABLE (with indexes)
SHOW FULL PROCESSLIST;                 -- running queries (like pg_stat_activity)
SHOW GLOBAL VARIABLES LIKE '%timeout%';
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW ENGINE INNODB STATUS\G            -- InnoDB internals (locks, buffer pool)

-- Switch output format (wide rows)
SELECT * FROM users LIMIT 5\G         -- \G = vertical format, one column per line

-- Create user (MySQL 8+)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPassword123!';
CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'pwd';    -- restrict subnet
CREATE USER 'readonly'@'%' IDENTIFIED WITH caching_sha2_password BY 'pwd';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'%';
GRANT SELECT ON mydb.users TO 'readonly'@'%';      -- table-level
GRANT SELECT (id, email) ON mydb.users TO 'ro'@'%';  -- column-level

-- Apply changes (not needed in MySQL 8+ but harmless)
FLUSH PRIVILEGES;

-- View grants
SHOW GRANTS FOR 'appuser'@'%';
SELECT user, host FROM mysql.user;

-- Revoke
REVOKE DELETE ON mydb.* FROM 'appuser'@'%';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'%';

-- Change password
ALTER USER 'appuser'@'%' IDENTIFIED BY 'NewPassword!';

-- Drop user
DROP USER 'appuser'@'%';

-- Reset root password (MySQL 8+)
-- 1. Stop MySQL: systemctl stop mysql
-- 2. Start with skip-grant: mysqld --skip-grant-tables &
-- 3. Connect: mysql -u root
-- 4. FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPwd!';
-- 5. Restart normally

-- Create table
CREATE TABLE users (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email      VARCHAR(255)    NOT NULL,
  name       VARCHAR(100)    NOT NULL DEFAULT '',
  status     ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ALTER TABLE (online DDL in MySQL 8 with ALGORITHM=INPLACE, LOCK=NONE)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN old_name TO new_name;   -- MySQL 8+
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users ADD CONSTRAINT fk_orders FOREIGN KEY (user_id) REFERENCES users(id);

-- Online schema change (check lock requirement first)
ALTER TABLE large_table ADD COLUMN new_col INT DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;
-- If ALGORITHM=INPLACE fails: use pt-online-schema-change or gh-ost for zero-downtime

-- Truncate vs Delete
TRUNCATE TABLE sessions;    -- fast, resets AUTO_INCREMENT, can't rollback (DDL)
DELETE FROM sessions;       -- slow for large tables, transactional, preserves structure

-- Check table sizes
SELECT
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;

-- EXPLAIN shows the query execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

-- EXPLAIN ANALYZE (MySQL 8.0.18+) — actually runs the query + shows real timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- What to look for in EXPLAIN output:
-- type column (from best to worst):
--   system    — single row (constant table)
--   const     — primary key or unique index lookup
--   eq_ref    — unique index join
--   ref       — non-unique index lookup (GOOD)
--   range     — index range scan (ACCEPTABLE)
--   index     — full index scan (SLOW for large tables)
--   ALL       — full table scan (BAD — add an index)

-- key column: which index is being used (NULL = no index used)
-- rows column: estimated rows scanned (lower is better)
-- filtered column: % of rows filtered (after reading)
-- Extra column: watch for these:
--   Using index        = covering index (best case)
--   Using where        = filter applied after reading
--   Using filesort     = sort can't use index (consider index)
--   Using temporary    = temp table (GROUP BY/ORDER BY mismatch)
--   Impossible WHERE   = query can never return rows

-- Force an index (for testing)
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 123;

-- Find slow queries via slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- log queries > 1 second
SHOW VARIABLES LIKE 'slow_query_log_file';
-- Parse: mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- B-Tree index (default, most common)
CREATE INDEX idx_email ON users (email);
CREATE INDEX idx_status_date ON orders (status, created_at);    -- composite

-- Composite index column order rule: put equality filters first, range last
-- Good: WHERE status = 'active' AND created_at > '2026-01-01'
-- Index: (status, created_at)

-- Covering index — include all columns needed by query (avoids table lookup)
CREATE INDEX idx_covering ON orders (user_id, status, created_at, total);
-- SELECT user_id, total FROM orders WHERE user_id = 1 AND status = 'paid'
-- → "Using index" — never touches the main table

-- Prefix index (for long VARCHAR/TEXT)
CREATE INDEX idx_slug ON articles (slug(50));    -- index first 50 chars

-- FULLTEXT index (for search)
CREATE FULLTEXT INDEX ft_content ON articles (title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('kubernetes deployment' IN BOOLEAN MODE);

-- Unique index
CREATE UNIQUE INDEX uk_email ON users (email);
-- Or in CREATE TABLE: UNIQUE KEY uk_email (email)

-- INVISIBLE index (MySQL 8+) — test impact before dropping
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;   -- disable for optimizer
-- test queries...
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;     -- re-enable

-- Index stats
SHOW INDEX FROM orders;
SELECT index_name, cardinality FROM information_schema.statistics
WHERE table_schema = 'mydb' AND table_name = 'orders';

-- Unused indexes (expensive to maintain — drop them)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;

-- Transaction basics
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ROLLBACK;   -- undo everything since START TRANSACTION

-- Isolation levels
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- avoids phantom reads in most cases
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;    -- MySQL default

-- REPEATABLE READ: snapshot at first read, may see phantom rows in range queries
-- READ COMMITTED: fresh snapshot per statement (PostgreSQL default behavior)

-- SELECT ... FOR UPDATE — locks rows (pessimistic locking)
START TRANSACTION;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;   -- other transactions block
UPDATE orders SET status = 'processing' WHERE id = 42;
COMMIT;

-- SELECT ... LOCK IN SHARE MODE — shared lock (readers ok, writers block)
SELECT * FROM inventory WHERE product_id = 5 LOCK IN SHARE MODE;

-- Find blocking queries
SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- Kill a blocking query
KILL 1234;   -- thread ID from SHOW FULL PROCESSLIST or above query

# mysqldump — logical backup
mysqldump -u root -p mydb > mydb.sql                  # single database
mysqldump -u root -p mydb users orders > partial.sql  # specific tables
mysqldump -u root -p --all-databases > all.sql         # everything
mysqldump -u root -p --single-transaction mydb > mydb.sql  # InnoDB: consistent snapshot without lock
mysqldump -u root -p --no-data mydb > schema.sql       # schema only
mysqldump -u root -p --no-create-info mydb > data.sql  # data only

# Compress backup
mysqldump -u root -p --single-transaction mydb | gzip > mydb.sql.gz

# Restore
mysql -u root -p mydb < mydb.sql
zcat mydb.sql.gz | mysql -u root -p mydb

# mydumper / myloader — parallel dump/restore (much faster than mysqldump)
mydumper -u root -p pwd -B mydb -o /backup/mydb/ -t 4 --trx-consistency-only
myloader -u root -p pwd -B mydb -d /backup/mydb/ -t 4

# Check binary log position (for replication)
SHOW MASTER STATUS;   -- or SHOW BINARY LOG STATUS (MySQL 8.2+)

# Point-in-time recovery
mysqlbinlog --start-datetime="2026-03-14 09:00:00" \
            --stop-datetime="2026-03-14 10:30:00" \
            /var/lib/mysql/binlog.000042 | mysql -u root -p

-- Top 10 slowest queries
SELECT
  digest_text,
  count_star AS executions,
  ROUND(avg_timer_wait / 1e9, 3) AS avg_ms,
  ROUND(sum_timer_wait / 1e9, 3) AS total_ms,
  ROUND(sum_rows_examined / count_star) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Current running queries (> 5 seconds)
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

-- Table I/O stats — which tables are most accessed?
SELECT object_name, count_read, count_write, count_fetch
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'mydb'
ORDER BY count_read + count_write DESC LIMIT 10;

-- Buffer pool usage (InnoDB)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads
-- = buffer pool hit ratio — should be > 99%

-- Connection stats
SHOW GLOBAL STATUS LIKE 'Connections';             -- total since start
SHOW GLOBAL STATUS LIKE 'Threads_connected';       -- current
SHOW GLOBAL STATUS LIKE 'Max_used_connections';    -- peak

-- Query cache hit rate (if enabled — deprecated MySQL 8)
SHOW GLOBAL STATUS LIKE 'Qcache%';