Operating MySQL in production

-- ALWAYS state your requirement. MySQL then FAILS FAST
-- rather than silently choosing a table copy.
ALTER TABLE users ADD COLUMN nickname VARCHAR(64),
  ALGORITHM=INSTANT;
--   error if it cannot be instant → you learn before you deploy

ALTER TABLE users ADD INDEX idx_nick (nickname),
  ALGORITHM=INPLACE, LOCK=NONE;
--   error if it would block writes

-- How many instant row versions has this table used up?
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name LIKE 'yourdb/%'
ORDER BY total_row_versions DESC;

-- And ALWAYS bound the wait (chapter 52):
SET SESSION lock_wait_timeout = 5;

gh-ost \
  --host=primary.db --database=shop --table=orders \
  --alter="ADD COLUMN shipped_at DATETIME NULL, ADD INDEX idx_s (shipped_at)" \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=100" \
  --max-lag-millis=1500 \
  --chunk-size=1000 \
  --throttle-flag-file=/tmp/ghost.throttle \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --allow-on-master \
  --initially-drop-ghost-table \
  --execute

# While it runs:
#   touch /tmp/ghost.throttle     → pause copying immediately
#   rm /tmp/ghost.throttle        → resume
#   rm /tmp/ghost.postpone        → perform the cut-over now
#
# The postpone flag is the important one: the copy finishes
# whenever it finishes, and YOU choose the moment of the
# brief cut-over lock — e.g. 3am, not mid-day.

-- the naive version
pool_size × app_instances  <  max_connections

-- worked example that kills a server
pool = 20, instances = 40 (autoscaled)  →  800 connections
   + background workers, cron, migrations, admin sessions
   + a deploy running double instances briefly
   → 2000+ against a max_connections of 1000

# 1. Restore the most recent full backup.
xtrabackup --copy-back --target-dir=/backup/full-2026-09-21

# 2. Find where that backup ended.
cat /backup/full-2026-09-21/xtrabackup_binlog_info
#   binlog.000042   19384756   3E11FA47-...:1-104721

# 3. Find the exact bad statement in the binlogs.
mysqlbinlog --verbose --base64-output=DECODE-ROWS \
  --start-datetime="2026-09-21 14:25:00" \
  binlog.000042 | grep -n -B5 -A5 "DELETE FROM orders"

# 4. Replay everything UP TO that point, and no further.
#    By GTID (preferred — exact, no off-by-one):
mysqlbinlog --exclude-gtids='3E11FA47-...:104999-105000' \
  binlog.000042 binlog.000043 | mysql

#    Or by position:
mysqlbinlog --start-position=19384756 \
            --stop-position=20481923 \
  binlog.000042 | mysql

# 5. VERIFY before letting traffic back in.

# ── The rule that matters more than any of this ──
# An untested backup is not a backup. Schedule a real
# restore to a scratch host monthly and time it. The
# metric that matters is RESTORE time, not backup time.

-- ═══ WHAT IS SLOW ═══
-- Ranked by total time consumed, which is what actually matters —
-- a 5ms query run 2 million times beats a 3s query run twice.
SELECT LEFT(query, 80) AS q, db, exec_count,
       total_latency, avg_latency, rows_sent_avg, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 15;

-- The single best signal of a missing index:
-- rows examined per row returned.
SELECT LEFT(query,80) AS q, exec_count,
       rows_examined_avg, rows_sent_avg,
       ROUND(rows_examined_avg / NULLIF(rows_sent_avg,0)) AS waste_ratio
FROM sys.statement_analysis
WHERE rows_sent_avg > 0
ORDER BY waste_ratio DESC LIMIT 10;
--   ratio of 1 is perfect. 10000 means scanning for one row.

-- ═══ FULL SCANS ═══
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;

-- ═══ INDEX HYGIENE ═══
SELECT * FROM sys.schema_unused_indexes;      -- check Uptime first!
SELECT * FROM sys.schema_redundant_indexes;

-- ═══ WHAT IS THE SERVER WAITING ON ═══
SELECT * FROM sys.waits_global_by_latency LIMIT 10;
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- ═══ THE SLOW LOG, DIGESTED ═══
-- SET GLOBAL slow_query_log = ON;
-- SET GLOBAL long_query_time = 0.5;
-- SET GLOBAL log_queries_not_using_indexes = ON;  -- noisy; short bursts only
--   then:  pt-query-digest /var/log/mysql/slow.log | head -60

-- Anything still on the 3-byte lie?
SELECT table_schema, table_name, column_name,
       character_set_name, collation_name
FROM information_schema.columns
WHERE character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4'
  AND table_schema NOT IN
      ('mysql','sys','information_schema','performance_schema');

-- More than one collation in the database = latent join problems.
SELECT collation_name, COUNT(*) AS cols
FROM information_schema.columns
WHERE table_schema = DATABASE()
GROUP BY collation_name;

-- The conversion (rebuilds the table — use gh-ost on a big one):
-- ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4
--   COLLATE utf8mb4_0900_ai_ci;

-- 1. a generated column, then index it (works everywhere)
ALTER TABLE events
  ADD COLUMN user_id BIGINT
AS (payload->>'$.user_id') STORED,
  ADD INDEX idx_uid (user_id);

-- 2. a functional index (8.0.13+) — no extra column
CREATE INDEX idx_uid ON events (
  (CAST(payload->>'$.user_id' AS UNSIGNED))
);

-- 3. arrays need a multi-valued index (8.0.17+, ch 36)
CREATE INDEX idx_tags ON events (
  (CAST(payload->'$.tags' AS CHAR(32) ARRAY))
);

-- Who has dangerous privileges?
SELECT grantee, privilege_type
FROM information_schema.user_privileges
WHERE privilege_type IN
  ('SUPER','FILE','PROCESS','SHUTDOWN','GRANT OPTION',
   'SYSTEM_VARIABLES_ADMIN','CONNECTION_ADMIN')
ORDER BY grantee;

-- Accounts reachable from anywhere.
SELECT user, host, plugin, account_locked, password_expired
FROM mysql.user WHERE host = '%';

-- Accounts with NO password. There should be none.
SELECT user, host FROM mysql.user
WHERE authentication_string = '' AND plugin NOT IN ('auth_socket');

-- Roles, the maintainable approach:
CREATE ROLE app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO app_rw;
GRANT app_rw TO 'svc_orders'@'10.0.%';
SET DEFAULT ROLE app_rw TO 'svc_orders'@'10.0.%';

-- Is traffic encrypted?
SHOW STATUS LIKE 'Ssl_cipher';
-- ALTER USER 'svc'@'%' REQUIRE SSL;   -- enforce per account