MENU
Administration
Administration covers the tools, statements, and files a database administrator uses to configure, monitor, secure, and maintain a running MySQL server, as distinct from the schema design (DDL) and query (DML) topics covered elsewhere in this reference.Many server behaviors are controlled through the MySQL configuration file, which lives in a different location depending on the host platform:
| Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini |
| Linux: /etc/my.cnf |
| Mac: /etc/mysql/my.cnf |
- port – the port that MySQL listens on.
- socket – the path to the MySQL socket file.
- datadir – the directory where MySQL stores its data files.
- log-error – the path to the MySQL error log file.
- bind-address – the IP address that MySQL listens on.
- max_connections – the maximum number of simultaneous connections.
- innodb_buffer_pool_size – the size of the InnoDB buffer pool.
- innodb_redo_log_capacity – the total size of the InnoDB redo log; since MySQL 8.0.30 this is the standard, dynamically resizable way to size it, superseding the older innodb_log_file_size/innodb_log_files_in_group pair (see Server Commands).
- innodb_flush_log_at_trx_commit – when InnoDB flushes log files to disk.
| SET GLOBAL autocommit = 1; SET @@global.autocommit = 1; SELECT @@autocommit; |
Three equivalent ways to read or set a global system variable.
SET GLOBAL autocommit = 1;
SELECT @@autocommit;Query OK, 0 rows affected
| @@autocommit |
|---|
| 1 |
- Information Databases – the built-in INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, SYS, and MYSQL databases used to introspect server and database state.
- Information Commands – HELP, EXPLAIN/DESCRIBE/DESC, and the many forms of SHOW.
- Table Maintenance Commands – ANALYZE, CHECK, CHECKSUM, OPTIMIZE, and REPAIR TABLE.
- Server Commands – resource allocation and connection compression control, character set/collation session settings, ALTER INSTANCE, RESOURCE GROUP, CLONE, FLUSH, RESET, RESET PERSIST, KILL, RESTART, and SHUTDOWN.
- Logging – the Workbench, error, general query, slow query, binary, relay, redo, undo, ISAM, and audit logs, plus LOGFILE GROUP.
- Backup and Recovery – MySQL Workbench exports, mysqldump/mysqlimport, MySQL Shell's dump/load utilities, SELECT ... INTO, LOAD DATA/LOAD XML, IMPORT TABLE, LOCK INSTANCE FOR BACKUP, raw file copying, and MySQL Enterprise Backup.
- Replication – CHANGE REPLICATION SOURCE TO, setting up source/replica pairs, SSL, GTID-based replication, statement-based vs. row-based logging, replication administration, and Group Replication.
- Partitioning – creating RANGE, LIST, HASH, and KEY partitions and subpartitions, managing them with ALTER TABLE, and selecting specific partitions in DML.
- Utilities – the command-line programs that ship with MySQL for running the server, checking and repairing data files, backup/recovery, security, and routing.
- Plugins – security, replication, enterprise, query rewrite, version tokens, clone, X, and test-framework plugins.
- Storage Engines – the pluggable engines and how they differ in transactions, locking, and indexing support.