MySQL Fundamentals
MySQL is a relational database server. Applications send SQL statements to store related records in tables, enforce rules between them, and retrieve the rows they need.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
MySQL Fundamentals
MySQL is a client-server relational database management system. A MySQL server stores data in databases, while applications and interactive clients send Structured Query Language, or SQL, statements over a connection. The server parses each statement, checks permissions, chooses an execution plan, and asks a storage engine to read or change rows.
The default storage engine is InnoDB. It organizes each table around a clustered primary-key index and maintains secondary indexes for other access paths. InnoDB also supplies transactions, row-level locking, multi-version concurrency control, foreign keys, redo logging, undo data, and crash recovery. MySQL supports other storage engines, but their transaction and indexing behavior can differ.
From a request to rows
A connection authenticates as a MySQL account. The account's privileges determine which schemas and objects the session can use. After authorization, the SQL layer resolves names and validates the statement. The optimizer compares possible access paths and join orders. The executor then requests rows through the table's storage engine and returns a result or a row count to the client.
SQL describes the result you need rather than the physical steps that produce it. A SELECT can filter with WHERE, combine related tables with JOIN, group rows with GROUP BY, and sort with ORDER BY. The optimizer may scan a table or use an index. EXPLAIN displays the selected plan so you can inspect access types, chosen keys, join order, and row estimates.
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://dev.mysql.com/doc/refman/8.4/en/
Supports
- Current MySQL 8.4 terminology
- feature map
- SQL statements
- administration
- security
- backup
- optimization
- and replication
- https://dev.mysql.com/doc/refman/8.4/en/what-is-mysql.html
Supports
- Client-server relational database definition and architecture
- https://dev.mysql.com/doc/refman/8.4/en/features.html
Supports
- Server
- SQL
- storage-engine
- connector
- security
- and replication capabilities
- https://dev.mysql.com/doc/refman/8.4/en/tutorial.html
Supports
- Connection
- database and table creation
- loading
- retrieval
- filtering
- grouping
- and common query progression
- https://dev.mysql.com/doc/refman/8.4/en/innodb-introduction.html
Supports
- InnoDB default status
- ACID transactions
- row locking
- clustered indexes
- foreign keys
- and crash recovery
- https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html
Supports
- Index read benefits and write and storage costs
- https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html
Supports
- Composite index ordering and leftmost-prefix use
- https://dev.mysql.com/doc/refman/8.4/en/using-explain.html
Supports
- Execution-plan inspection
- indexes
- join order
- and optimizer estimates
- https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html
Supports
- Parent and child relationships
- referential consistency
- indexes
- and actions
- https://dev.mysql.com/doc/refman/8.4/en/commit.html
Supports
- Autocommit
- start transaction
- commit
- and rollback behavior
- https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html
Supports
- Consistent reads
- undo records
- older row versions
- and purge retention
- https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks-handling.html
Supports
- Deadlock causes
- victim rollback
- transaction retry
- short transactions
- and consistent access order
- https://dev.mysql.com/doc/refman/8.4/en/backup-and-recovery.html
Supports
- Logical and physical backups
- binary logs
- point-in-time recovery
- and restore planning
- https://dev.mysql.com/doc/refman/8.4/en/security.html
Supports
- Accounts
- privileges
- authentication
- encrypted connections
- and deployment security
- https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html
Supports
- Global
- database
- and object privilege scopes
- https://dev.mysql.com/doc/refman/8.4/en/history.html
Supports
- First MySQL release date and project origin
- https://dev.mysql.com/doc/refman/8.0/en/faqs-general.html
Supports
- General-availability dates for MySQL 5.0 through 8.4 and production-series history
- https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
Supports
- Native JSON type and JSON functions in MySQL 5.7
- https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
Supports
- Transactional data dictionary
- common table expressions
- and window functions in MySQL 8.0
- https://dev.mysql.com/doc/refman/8.4/en/mysql-releases.html
Supports
- Innovation and LTS release tracks and their start
- https://github.com/sindresorhus/awesome
Supports
- Required discovery starting point and link to the MySQL list
- https://github.com/shlomi-noach/awesome-mysql
Supports
- Discovery of DBeaver
- mycli
- phpMyAdmin
- MySQLTuner
- Percona Toolkit
- and gh-ost
- https://dbeaver.com/docs/dbeaver/Database-driver-MySQL/
Supports
- DBeaver MySQL connections
- objects
- features
- driver settings
- and secure connections
- https://www.mycli.net/
Supports
- Interactive MySQL terminal completion and syntax highlighting
- https://docs.phpmyadmin.net/en/latest/
Supports
- Browser-based MySQL administration
- import
- export
- accounts
- configuration
- and security
- https://github.com/major/MySQLTuner-perl
Supports
- MySQL configuration and status review tool identity and usage
- https://docs.percona.com/percona-toolkit/
Supports
- Command-line tools for query analysis
- schema changes
- replication
- and consistency
- https://github.com/github/gh-ost
Supports
- Binary-log-driven online schema migration and ghost-table cutover
- https://www.mysql.com/products/community/
Supports
- MySQL Community Server Landscape identity and destination
- https://www.mysql.com/products/enterprise/
Supports
- MySQL Enterprise Edition Landscape identity and commercial operational tooling
- https://mariadb.com/docs/release-notes/compatibility-and-differences/mariadb-vs-mysql-compatibility
Supports
- MariaDB and MySQL compatibility boundaries and independent behavior
- https://docs.percona.com/percona-server/8.4/
Supports
- Percona Server for MySQL compatibility
- observability
- and administration
- https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.html
Supports
- Aurora MySQL compatibility
- managed architecture
- replicas
- backups
- and failover
- https://cloud.google.com/sql/docs/mysql
Supports
- Cloud SQL for MySQL provisioning
- connections
- backups
- maintenance
- and availability
- https://learn.microsoft.com/en-us/azure/mysql/
Supports
- Azure Database for MySQL use of Community Edition and managed-service operations
- https://docs.pingcap.com/tidb/stable/mysql-compatibility/
Supports
- TiDB MySQL protocol
- SQL compatibility
- and documented differences
- https://planetscale.com/docs/vitess
Supports
- PlanetScale use of Vitess concepts and MySQL-compatible distributed operation