Unit
- Architecture overview: MySQL uses a client-server model with a server daemon (
mysqld) that handles connections, query processing, and storage management. The server supports multiple storage engines, with InnoDB being the default transactional engine. Understanding the layered architecture helps in configuring resources and troubleshooting performance. - Editions comparison: MySQL Community Edition is open source and free. Oracle offers Enterprise Edition with additional features like MySQL Enterprise Monitor, Audit Plugin, and Transparent Data Encryption. Standard Edition includes InnoDB Cluster and Group Replication. Choose based on compliance needs and budget constraints.
- InnoDB engine specifics: InnoDB is a transaction-safe (ACID compliant) storage engine with row-level locking and foreign key support. It uses a clustered index and write-ahead logging (redo log) for crash recovery. The buffer pool caches data and indexes to reduce disk I/O. Tuning
innodb_buffer_pool_sizeis critical for performance. - MyISAM and legacy engines: MyISAM supports full-text indexing and table-level locking. It is non-transactional and suited for read-heavy workloads. The
MEMORYengine stores data in RAM, useful for temporary tables or caching. TheARCHIVEengine compresses data for storage. Knowing trade-offs helps in table engine selection. - Connection handling: The server uses a thread pool to manage client connections. Each connection consumes system resources. The
max_connectionsvariable limits concurrent clients. Connection pooling is recommended for web applications to reduce overhead. UseSHOW PROCESSLISTto monitor active connections. - Data directory structure: The MySQL data directory holds database subdirectories, each containing files for tables:
.ibdfor InnoDB tablespaces,.frmfor table definitions (older versions), and.MYD/.MYIfor MyISAM. Understanding this layout aids in manual backups and recovery. - Configuration file organization: MySQL uses configuration files like
my.cnf(Linux) ormy.ini(Windows). Options are organized in sections:[mysqld]for server,[client]for client tools,[mysqldump]for dump utility. Changes require server restart or dynamic setting viaSET GLOBALcommand. - System variables and status: MySQL exposes hundreds of system variables (like
innodb_log_file_size) and status counters (likeInnodb_rows_read). UseSHOW VARIABLESandSHOW GLOBAL STATUSto inspect the current state. Monitoring key metrics guides tuning decisions. - Binary logging: MySQL binary log records all changes to the database. It is essential for replication and point-in-time recovery. The log contains events describing data modifications. Enable it with the
log_binvariable. Monitor binary log size and rotation usingmax_binlog_sizeandexpire_logs_days. Proper management prevents disk space issues and ensures reliable recovery. - User account management: MySQL manages access through user accounts stored in the
mysql.usertable. Create accounts withCREATE USERand grant privileges usingGRANT. UseSHOW GRANTSto review permissions. Themysql_native_passwordplugin is default, butcaching_sha2_passwordoffers stronger authentication. Regularly audit user privileges to enforce the principle of least privilege.
Code Example
SQL
-- Check server version and storage engine support
SELECT VERSION();
-- List storage engine capabilities
SHOW ENGINES;
-- View current InnoDB buffer pool size in bytes
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
-- Change a dynamic variable without restart
SET GLOBAL max_connections = 200;These standard queries help assess the server configuration. Use SHOW VARIABLES with LIKE pattern to filter specific parameters.
Quick Revision
- MySQL follows client-server architecture with pluggable storage engines.
- InnoDB is the default engine, supporting ACID transactions and row-level locking.
- Different editions cater to varying cost and feature requirements.
- Server configuration is managed through
my.cnformy.inifiles. - System variables and status counters provide insights for tuning.