Appendices

Appendix C. MySQL Installation and Command Reference

The MySQL side of Chapter 14, as a reference card: install routes, server control, the mysql client, configuration, backup and restore, maintenance, and monitoring — MySQL 8.0 and later.

Installation

Server control and connection

sudo systemctl start | stop | restart | status mysql    # unit mysql or mysqld
mysql -h host -P 3306 -u user -p university             # -p prompts (never inline)
mysql -u root -p -e "SELECT VERSION();"                 # one-shot execution
mysqladmin -u root -p status                            # server status

Credential defaults: [client] sections of ~/.my.cnf (host, user, password) — the credential-file pattern.

mysql client essentials

Users, roles, privileges

CREATE USER 'name'@'host' IDENTIFIED BY '...';
ALTER USER 'name'@'host' IDENTIFIED BY '...'
    [PASSWORD EXPIRE [INTERVAL n DAY | NEVER | DEFAULT]]
    [ACCOUNT LOCK | UNLOCK];
DROP USER 'name'@'host';
CREATE ROLE 'faculty';
GRANT 'faculty' TO 'name'@'host';
SET DEFAULT ROLE 'faculty' TO 'name'@'host';   -- roles activate at login
GRANT SELECT, INSERT ON university.enrollment TO 'name'@'host';
GRANT SELECT ON university.* TO 'report'@'%';
REVOKE INSERT ON university.enrollment FROM 'name'@'host';
SHOW GRANTS FOR 'name'@'host';

Host patterns: localhost, exact IP, % (any), 10.0.0.% (subnet). Auth plugins: caching_sha2_password (default), mysql_native_password (legacy driver bridge).

Configuration (my.cnf / my.ini, section [mysqld])

SET PERSIST name = value; writes to mysqld-auto.cnf without editing files; SET GLOBAL is session-of-server only.

Backup and restore

mysqldump --single-transaction --routines --triggers
          --databases university > university.sql
mysqldump --single-transaction university table1 table2 > partial.sql
mysql -u root -p university_new < university.sql          # restore via client
mysqlpump --parallel-schemas ... university > u.sql        # parallel (older tool)
mysqlsh -- util dumpInstance('/path') | util loadDump(...) # MySQL Shell, parallel

Physical: the clone plugin (CLONE INSTANCE FROM 'user'@'host':3306 IDENTIFIED BY '...') and the XtraBackup family. Binary log: mysqlbinlog --start-datetime --stop-datetime binlog.0000* | mysql (PITR's replay tape; GTIDs with --source-id/auto-position).

Maintenance and monitoring

ANALYZE TABLE t;                      -- statistics
OPTIMIZE TABLE t;                     -- rebuild (InnoDB: recreates clustered index)
CHECK TABLE t;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';    -- hit ratio
SELECT * FROM performance_schema.data_locks;            -- current locks
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;  -- slow queries
SELECT * FROM information_schema.INNODB_METRICS ...;    -- engine internals
SHOW REPLICA STATUS;                  -- replication (old spelling: SHOW SLAVE STATUS)
SELECT event_name, count_star FROM performance_schema.events_statements_summary_by_digest
    ORDER BY count_star DESC LIMIT 10;

Common troubleshooting