SQL

https://dev.mysql.com/doc/refman/8.4/en/getting-information.html
https://dev.mysql.com/doc/refman/8.4/en/creating-database.html
https://dev.mysql.com/doc/refman/8.4/en/create-role.html
https://dev.mysql.com/doc/refman/8.4/en/create-user.html
https://dev.mysql.com/doc/refman/8.4/en/grant.html
https://dev.mysql.com/doc/refman/8.4/en/show-grants.html
https://dev.mysql.com/doc/refman/8.4/en/show.html
https://dev.mysql.com/doc/refman/8.4/en/information-schema.html
https://dev.mysql.com/doc/refman/8.4/en/information-schema-tables-table.html
https://dev.mysql.com/doc/refman/8.4/en/information-schema-statistics-table.html
https://dev.mysql.com/doc/refman/8.4/en/explain.html
https://dev.mysql.com/doc/refman/8.4/en/metadata-locking.html
https://dev.mysql.com/doc/refman/8.4/en/performance-schema-metadata-locks-table.html

scope:
    this page is SQL-heavy and leans DBA / SRE / migration / incident work
    examples use database shop and tables shop.orders / shop.order_items
    schema and database are the same concept in MySQL daily usage

safe habit:
    qualify production objects with database name:
        shop.orders

    inspect before destructive change:
        SELECT ... WHERE ...
        then UPDATE / DELETE with the same predicate

    prefer explicit transactions:
        START TRANSACTION;
        ...
        COMMIT;

    avoid in production without review:
        DROP DATABASE
        DROP TABLE ... CASCADE equivalent thinking
        TRUNCATE TABLE
        UPDATE without WHERE
        DELETE without WHERE
        long-running transaction

MySQL concept reminders:
    account names are usually 'user'@'host'
    role names also use MySQL account syntax rules
    SHOW statements are often the fastest way to inspect state
    INFORMATION_SCHEMA is better for composable SQL and filtering
    performance_schema is best for live engine diagnostics

SELECT DATABASE();
SELECT USER(), CURRENT_USER();
SELECT @@hostname, @@port, @@version, @@version_comment;
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'default_storage_engine';

note:
    USER() shows the account used for login
    CURRENT_USER() shows the account actually used for privilege checking

SELECT
  schema_name,
  default_character_set_name,
  default_collation_name
FROM information_schema.schemata
ORDER BY schema_name;

SHOW DATABASES;
SHOW CREATE DATABASE shop;

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

ALTER DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

note:
    ALTER DATABASE changes default database properties
    it does not rewrite existing tables automatically

USE shop;

DROP DATABASE IF EXISTS shop_old;

warning:
    DROP DATABASE removes the whole database
    do not run it unless the target name is verified

CREATE USER IF NOT EXISTS 'shop_admin'@'10.0.0.%'
  IDENTIFIED BY 'change-me'
  REQUIRE SSL
  PASSWORD EXPIRE INTERVAL 90 DAY;

CREATE USER IF NOT EXISTS 'shop_app'@'%'
  IDENTIFIED BY 'change-me'
  REQUIRE SSL;

account notes:
    MySQL uses 'user'@'host'
    host controls where the account can connect from
    REQUIRE SSL is useful for production server-side TLS enforcement

ALTER USER 'shop_app'@'%'
  IDENTIFIED BY 'new-password';

ALTER USER 'shop_app'@'%'
  ACCOUNT LOCK;

ALTER USER 'shop_app'@'%'
  ACCOUNT UNLOCK;

CREATE ROLE IF NOT EXISTS 'shop_dba', 'shop_migrator', 'shop_ro';

GRANT 'shop_dba' TO 'alice'@'10.0.0.%';
GRANT 'shop_migrator' TO 'ci'@'10.0.1.%';
GRANT 'shop_ro' TO 'bi'@'10.0.2.%';

SET DEFAULT ROLE 'shop_dba' TO 'alice'@'10.0.0.%';
SET DEFAULT ROLE 'shop_migrator' TO 'ci'@'10.0.1.%';
SET DEFAULT ROLE 'shop_ro' TO 'bi'@'10.0.2.%';

SET ROLE 'shop_dba';
SET ROLE DEFAULT;
SELECT CURRENT_ROLE();

GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.*
TO 'shop_app'@'%';

GRANT SELECT
ON shop.*
TO 'shop_ro'@'%';

common DBA privileges:
    CREATE, ALTER, DROP, INDEX, REFERENCES, TRIGGER, EXECUTE
    PROCESS, RELOAD, SHOW DATABASES, LOCK TABLES
    CREATE USER, ROLE_ADMIN, SESSION_VARIABLES_ADMIN, CONNECTION_ADMIN when needed

rule:
    do not give app account more than its runtime data access needs

REVOKE INSERT, UPDATE
ON shop.*
FROM 'shop_app'@'%';

DROP USER IF EXISTS 'temp_user'@'%';

SHOW GRANTS FOR 'shop_app'@'%';
SHOW GRANTS FOR CURRENT_USER();
SHOW PRIVILEGES;

SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'shop';
SELECT * FROM information_schema.table_privileges
WHERE table_schema = 'shop';
SELECT * FROM information_schema.column_privileges
WHERE table_schema = 'shop';

SHOW TABLES FROM shop;
SHOW FULL TABLES FROM shop;

SHOW CREATE TABLE shop.orders\G
DESC shop.orders;
SHOW COLUMNS FROM shop.orders;
SHOW FULL COLUMNS FROM shop.orders;

SELECT
  table_schema,
  table_name,
  table_type,
  engine,
  table_rows,
  avg_row_length,
  data_length,
  index_length,
  data_free,
  create_time,
  update_time
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY table_name;

important:
    for InnoDB, table_rows is approximate
    data_length and index_length are approximate storage metrics
    stats cache may affect freshness

CREATE TABLE IF NOT EXISTS shop.orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  customer_id BIGINT NOT NULL,
  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_orders_order_no (order_no),
  KEY idx_orders_status_created_at (status, created_at)
) ENGINE=InnoDB;

ALTER TABLE shop.orders
  ADD COLUMN note VARCHAR(255) NULL,
  ALGORITHM=INSTANT,
  LOCK=NONE;

ALTER TABLE shop.orders
  MODIFY COLUMN status VARCHAR(64) NOT NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

ALTER TABLE shop.orders
  DROP COLUMN note;

ALTER TABLE shop.orders
  RENAME INDEX idx_orders_status_created_at TO idx_orders_status_time;

RENAME TABLE shop.orders TO shop.orders_archive;

TRUNCATE TABLE shop.orders_archive;

DROP TABLE IF EXISTS shop.orders_archive;

DDL notes:
    always verify the supported algorithm for the exact statement
    large-table DDL should be tested on a clone first
    LOCK=NONE is desirable but not always possible

SHOW FULL TABLES FROM shop WHERE Table_type = 'VIEW';
SHOW CREATE VIEW shop.order_summary\G
SHOW TRIGGERS FROM shop;
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW FUNCTION STATUS WHERE Db = 'shop';
SHOW EVENTS FROM shop;

SELECT id, order_no, status, amount, created_at
FROM shop.orders
WHERE status = 'created'
ORDER BY created_at DESC
LIMIT 20;

INSERT INTO shop.orders (order_no, status, amount, customer_id)
VALUES
  ('A10001', 'created', 99.90, 1001),
  ('A10002', 'created', 49.50, 1002);

INSERT INTO shop.orders (order_no, status, amount, customer_id)
VALUES ('A10001', 'paid', 99.90, 1001)
ON DUPLICATE KEY UPDATE
  status = VALUES(status),
  amount = VALUES(amount),
  updated_at = CURRENT_TIMESTAMP;

warning:
    REPLACE INTO deletes and reinserts under the hood
    use it only when that behavior is really intended

UPDATE shop.orders
SET status = 'paid',
    updated_at = CURRENT_TIMESTAMP
WHERE order_no = 'A10001';

DELETE FROM shop.orders
WHERE order_no = 'A10002';

UPDATE shop.orders o
JOIN shop.order_items oi ON oi.order_id = o.id
SET o.status = 'has_items'
WHERE oi.quantity > 0;

DELETE o
FROM shop.orders o
LEFT JOIN shop.order_items oi ON oi.order_id = o.id
WHERE oi.order_id IS NULL
  AND o.created_at < '2024-01-01';

START TRANSACTION;

UPDATE shop.orders
SET status = 'processing'
WHERE order_no = 'A10001';

SAVEPOINT before_second_change;

UPDATE shop.orders
SET amount = amount + 10
WHERE order_no = 'A10001';

ROLLBACK TO SAVEPOINT before_second_change;
COMMIT;

START TRANSACTION;

SELECT id, status, amount
FROM shop.orders
WHERE id = 1
FOR UPDATE;

COMMIT;

START TRANSACTION;

SELECT id, status
FROM shop.orders
WHERE id = 1
FOR SHARE;

COMMIT;

pattern:
    use FOR UPDATE when you intend to modify the row after reading it
    use FOR SHARE when you need consistent read access and want to block conflicting writes

SHOW INDEX FROM shop.orders;

SELECT
  table_schema,
  table_name,
  index_name,
  seq_in_index,
  column_name,
  non_unique,
  cardinality,
  index_type
FROM information_schema.statistics
WHERE table_schema = 'shop'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

EXPLAIN SELECT
  id, order_no, status
FROM shop.orders
WHERE status = 'created'
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN FORMAT=JSON
SELECT
  id, order_no, status
FROM shop.orders
WHERE status = 'created'
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN ANALYZE
SELECT
  id, order_no, status
FROM shop.orders
WHERE status = 'created'
ORDER BY created_at DESC
LIMIT 10;

ANALYZE TABLE shop.orders;

note:
    ANALYZE TABLE refreshes statistics used by the optimizer
    it is a maintenance action, not a query rewrite

SELECT
  table_schema,
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb,
  ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
  ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb,
  ROUND(SUM(data_free) / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

SELECT
  table_schema,
  table_name,
  engine,
  table_rows,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  ROUND(data_free / 1024 / 1024, 2) AS free_mb,
  create_time,
  update_time
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

SHOW TABLE STATUS FROM shop LIKE 'orders'\G

what to look at:
    Rows
    Data_length
    Index_length
    Data_free
    Engine
    Collation

SELECT
  table_schema,
  table_name,
  COALESCE(partition_name, '(no partition)') AS partition_name,
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb,
  ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
  ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb
FROM information_schema.partitions
WHERE table_schema = 'shop'
GROUP BY table_schema, table_name, partition_name
ORDER BY table_name, partition_name;

SELECT
  table_schema,
  table_name,
  engine,
  row_format,
  create_options
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY table_name;

space note:
    data_length + index_length is the usual first answer for size questions
    for InnoDB, values are approximate and can lag cached statistics
    data_free may reveal fragmentation or reserved free space

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

KILL 12345;
KILL QUERY 12345;

SELECT
  OBJECT_TYPE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_DURATION,
  LOCK_STATUS,
  OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

SELECT * FROM information_schema.innodb_trx\G
SHOW ENGINE INNODB STATUS\G

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_connects';

incident note:
    use processlist + metadata_locks + InnoDB status together
    one view alone is usually not enough

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

SELECT COUNT(*) FROM shop.orders;
SELECT COUNT(*) FROM shop.order_items;

COMMIT;

FLUSH TABLES WITH READ LOCK;

-- take file-level snapshot / logical dump here

UNLOCK TABLES;

warning:
    FLUSH TABLES WITH READ LOCK blocks writes
    keep the lock window as short as possible

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

note:
    this is an operational control, not a backup mechanism
    verify your account has the privileges needed to change global variables

SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'log_output';

CHECK TABLE shop.orders;

practical use:
    use CHECK TABLE for sanity checks and incident triage
    use ANALYZE TABLE for optimizer statistics
    use OPTIMIZE TABLE only when you understand the engine-specific cost

SELECT
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

SELECT
  table_schema,
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
GROUP BY table_schema;

SHOW INDEX FROM shop.orders;

SELECT
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY table_type, table_name;

CREATE DATABASE IF NOT EXISTS shop;
CREATE ROLE IF NOT EXISTS 'shop_app'@'%';
CREATE ROLE IF NOT EXISTS 'shop_ro'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'%';
GRANT SELECT ON shop.* TO 'shop_ro'@'%';
SHOW GRANTS FOR 'shop_app'@'%';
SHOW GRANTS FOR 'shop_ro'@'%';

SHOW FULL PROCESSLIST;

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_STATUS,
  OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop'
ORDER BY OBJECT_NAME;