MySQL Administration
MySQL Administration is the practice of running a MySQL database server day to day: installing and configuring it, managing accounts and privileges, securing connections, logging and monitoring activity, backing up and recovering data, and keeping replicas in sync. It's the operational counterpart to writing SQL — the work of keeping the database available, correct, and recoverable.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
MySQL Administration
MySQL is a relational database management system: software that stores data in tables and lets you add, access, and process it through SQL, the Structured Query Language. Administering MySQL is different work from writing queries against it. Administration is keeping the server itself installed correctly, configured sensibly, secured, monitored, backed up, and current — so the queries have something reliable to run against.
MySQL ships as open source software distributed and supported by Oracle. That matters operationally: you get a free Community Edition with an active upstream, a paid Enterprise Edition with extra security and management features, and a documented release process you can plan upgrades around.
The layers you administer
Every MySQL administration task touches one of a small number of layers. Knowing which layer owns a piece of state tells you where to look and where to make a change.
The server and its configuration
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.mysql.com/
Supports
- MySQL as an Oracle-distributed open source RDBMS with Community and Enterprise editions
- Rationale for the MySQL Official Site link
- https://dev.mysql.com/doc/refman/9.7/en/what-is-mysql.html
Supports
- Definition of MySQL as a relational database management system and its relationship to SQL
- Open source licensing (GPL) versus commercial licensing
- 00-novice.md, 01-intro.md opening, glossary, video script, and link rationale
- https://dev.mysql.com/doc/refman/9.7/en/server-administration.html
Supports
- Scope of server administration: the server, data directory, mysql system schema, logs, components, plugins
- Structure used to organize 01-intro.md and 02-slides.md layer sections
- Link rationale for the Server Administration reference entry
- https://dev.mysql.com/doc/refman/9.7/en/option-files.html
Supports
- my.cnf search order on Unix, option group syntax ([client], [mysqld])
- Precedence rules, world-writable file exclusion, !include/!includedir directives
- mysqld-auto.cnf as a server-managed JSON file for SET PERSIST
- Cheatsheet option-file section and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/innodb-introduction.html
Supports
- InnoDB as the default storage engine
- ACID transactions, row-level locking, clustered index on primary key, foreign key enforcement
- Intro/slides/cheatsheet storage-engine sections and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/innodb-index-types.html
Supports
- InnoDB clustered-index selection: primary key, suitable unique index, or a hidden generated index
- Intro and slides clustered-index explanation
- https://dev.mysql.com/doc/refman/9.7/en/security.html
Supports
- Scope of MySQL security administration: account management, encrypted connections, authentication plugins
- Structure of the account/security sections in 01-intro.md and link rationale
- https://dev.mysql.com/doc/refman/9.7/en/creating-accounts.html
Supports
- Account identity as username plus host (e.g. 'admin'@'localhost' vs 'admin'@'%')
- CREATE USER / GRANT / SHOW GRANTS / REVOKE / DROP USER syntax and examples
- Cheatsheet accounts section and related quiz answers
- https://dev.mysql.com/doc/refman/9.7/en/roles.html
Supports
- Roles as named collections of privileges granted to accounts
- CREATE ROLE / GRANT privileges-to-role / GRANT role-to-account pattern
- Roles reducing per-account privilege maintenance as account count grows
- Cheatsheet roles example and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/mysql-secure-installation.html
Supports
- What mysql_secure_installation does: root password, anonymous accounts, remote root, test database
- Cheatsheet "Securing a Fresh Install" section and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/server-logs.html
Supports
- Log types (error, general query, slow query, binary, relay) and their purposes
- Default-disabled state of most logs besides the error log
- Cheatsheet logs table and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/mysqlbinlog.html
Supports
- mysqlbinlog's role in reading and replaying binary log contents
- Point-in-time recovery workflow and example command syntax
- Cheatsheet binary log commands and related quiz answer
- https://dev.mysql.com/doc/refman/9.7/en/mysqladmin.html
Supports
- mysqladmin status, processlist, extended-status, variables, ping, version, shutdown, and flush-logs commands
- Cheatsheet connection, status, log-maintenance, and terminal-monitoring commands
- https://dev.mysql.com/doc/refman/9.7/en/backup-and-recovery.html
Supports
- Logical versus physical backup distinction and tradeoffs
- Point-in-time recovery as full backup plus binary log replay
- Cheatsheet backup section and related quiz answers
- https://dev.mysql.com/doc/refman/9.7/en/replication.html
Supports
- File-position versus GTID-based replication
- Semisynchronous replication and Group Replication behavior
- Replication as change propagation, not a backup substitute
- Cheatsheet replication section and related quiz answers
- https://dev.mysql.com/doc/refman/9.7/en/performance-schema.html
Supports
- Performance Schema as low-overhead runtime instrumentation
- Relationship to the sys schema and INFORMATION_SCHEMA
- Cheatsheet monitoring section and link rationale
- https://dev.mysql.com/doc/refman/9.7/en/mysql-releases.html
Supports
- Innovation versus LTS release model and support windows
- Upgrade path rules (no skipping LTS series, no downgrade)
- Intro/slides/cheatsheet upgrade sections and related quiz answers
- https://dev.mysql.com/doc/refman/9.7/en/upgrading.html
Supports
- Recommendation to test an upgrade on a test system before production
- In-place upgrade procedure references
- Cheatsheet upgrade section and related quiz answer
- Link rationale for the Upgrading MySQL entry
- https://dev.mysql.com/doc/mysql-shell/9.7/en/
Supports
- MySQL Shell's SQL/JavaScript/Python modes and AdminAPI for InnoDB Cluster/ReplicaSet
- Route toward competence pointer toward cluster administration
- Link rationale for the MySQL Shell Documentation entry
- https://github.com/sindresorhus/awesome
Supports
- Discovery path to the awesome-mysql list used for 09-awesome-links.yaml
- https://github.com/shlomi-noach/awesome-mysql
Supports
- Selection and categorization (Analysis & Monitoring, Backup & Recovery, High Availability & Replication, Schema Migration & Development, GUI & Admin Tools) of every 09-awesome-links.yaml entry
- https://www.percona.com/percona-toolkit
Supports
- Percona Toolkit description in 09-awesome-links.yaml
- https://docs.percona.com/percona-xtrabackup/8.0/index.html
Supports
- Percona XtraBackup as an online, non-blocking physical backup tool for InnoDB
- 09-awesome-links.yaml Percona XtraBackup entry
- https://docs.percona.com/percona-monitoring-and-management/3/index.html
Supports
- PMM as an open source monitoring/management platform for MySQL
- 09-awesome-links.yaml PMM entry
- https://dev.mysql.com/doc/workbench/en/
Supports
- MySQL Workbench as Oracle's official GUI for design, development, and administration
- 09-awesome-links.yaml MySQL Workbench entry
- https://www.phpmyadmin.net/
Supports
- phpMyAdmin as a web-based MySQL administration tool
- 09-awesome-links.yaml phpMyAdmin entry
- https://dbeaver.io/
Supports
- DBeaver as a free, cross-platform SQL client and administration tool
- 09-awesome-links.yaml DBeaver entry
- https://www.mycli.net/
Supports
- mycli as a terminal MySQL client with autocompletion and syntax highlighting
- 09-awesome-links.yaml mycli entry
- https://www.skeema.io/
Supports
- Skeema as declarative, version-controlled schema management for MySQL/MariaDB
- 09-awesome-links.yaml Skeema entry
- https://galeracluster.com/
Supports
- Galera Cluster as a synchronous multi-master clustering solution
- 09-awesome-links.yaml Galera Cluster entry
- https://github.com/github/gh-ost
Supports
- gh-ost as a triggerless, binary-log-based online schema migration tool
- 09-awesome-links.yaml gh-ost entry