DB-유

What's New in MySQL 8.0 for Administrators

· mysql, mysql8, upgrade

MySQL 8.0.11 went GA and I've been poking at it on a couple of test boxes since the release candidates. Everyone writes about window functions and CTEs. I care more about what changes when you have to run the server: the data dictionary, persisted config, roles, the new default authentication plugin, and defaults that bite if you aren't expecting them.

The transactional data dictionary

Until 5.7, table metadata lived in .frm files plus a mix of mysql.* MyISAM tables. 8.0 stores all of it in InnoDB tables inside the mysql schema. Consequences you will notice:

  • No more .frm files in the data directory; you cannot recover a table definition by copying one.
  • DDL is atomic: an ALTER TABLE or DROP TABLE that crashes halfway is rolled back cleanly instead of leaving an orphaned .ibd.
  • information_schema queries are much faster because they read dictionary tables instead of opening every table file.
  • mysql_upgrade is still required after an in-place upgrade from 5.7 to migrate the dictionary (it becomes automatic in later releases, but not in 8.0.11).

Check the server and dictionary state after an upgrade:

SHOW VARIABLES LIKE 'version%';
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'mysql' AND engine <> 'InnoDB';

The second query should return no rows apart from the general_log and slow_log CSV tables.

SET PERSIST

You can now change a global variable and have it survive restarts without touching the config file:

SET PERSIST max_connections = 500;
SET PERSIST_ONLY innodb_buffer_pool_size = 8589934592;   -- for read-only variables, applied at next restart
SELECT * FROM performance_schema.persisted_variables;

Values are written to mysqld-auto.cnf in the data directory and read after my.cnf, so they override it. RESET PERSIST max_connections; removes one. Handy for pushing the same change everywhere via SQL. I'm a bit wary of it, though: when a setting doesn't match my.cnf, someone will forget to look in mysqld-auto.cnf.

Roles

Privileges can finally be grouped:

CREATE ROLE 'app_rw', 'app_ro';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw';
GRANT SELECT ON shop.* TO 'app_ro';

CREATE USER 'shopapp'@'10.0.1.%' IDENTIFIED BY 'strong-password';
GRANT 'app_rw' TO 'shopapp'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'shopapp'@'10.0.1.%';

Without SET DEFAULT ROLE, a role is granted but not active until the session runs SET ROLE. That trips up almost everyone the first time. SHOW GRANTS FOR 'shopapp'@'10.0.1.%' USING 'app_rw'; shows the effective privileges.

caching_sha2_password is the new default

New accounts use caching_sha2_password instead of mysql_native_password. It's more secure, but many client libraries don't support it yet, and this will be your first support ticket. If applications fail to connect after the upgrade, put the old plugin back as default until the clients catch up:

[mysqld]
default_authentication_plugin = mysql_native_password

Or per account:

ALTER USER 'shopapp'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'strong-password';

Existing accounts migrated from 5.7 keep their plugin, so this only concerns accounts you create afterwards.

Defaults that changed

Several defaults are different:

  • character_set_server is utf8mb4 with collation utf8mb4_0900_ai_ci. Tables created on 5.7 keep their collation, but joins between old utf8mb4_general_ci columns and new ones can produce Illegal mix of collations errors.
  • The query cache is removed entirely. query_cache_type and query_cache_size in my.cnf make the server refuse to start; delete them.
  • explicit_defaults_for_timestamp is ON.
  • innodb_undo_log_truncate is ON, and undo logs live in separate tablespaces by default.
  • log_error_verbosity defaults to 2 (no notes), and the error log format changed to include a subsystem tag.
  • innodb_dedicated_server = ON makes the server size innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method automatically from the machine's RAM. Only use it on hosts that run nothing else.

Smaller things I like

  • Invisible indexes: ALTER TABLE orders ALTER INDEX idx_status INVISIBLE; hides an index from the optimizer without dropping it, so you can test whether it is needed and turn it back on instantly.
  • Descending indexes are real now (KEY (created_at DESC)), which helps ORDER BY a ASC, b DESC queries.
  • information_schema views are backed by the dictionary, and SHOW CREATE TABLE output is always consistent with what the server actually has.
  • Resource groups let you pin threads to CPUs and set priorities: CREATE RESOURCE GROUP batch TYPE=USER VCPU=4-7 THREAD_PRIORITY=10;.
  • GRANT can no longer create users at all; NO_AUTO_CREATE_USER was removed from sql_mode because it is now the only behavior.

Testing an upgrade

Before an in-place upgrade run the checker in MySQL Shell against the 5.7 instance:

mysqlsh --js root@db01:3306

mysql-js> util.checkForServerUpgrade()

It lists removed variables, reserved-word conflicts (RANK, GROUPS, SYSTEM, and others became reserved), old temporal types and partitioned tables without a native InnoDB partition handler. Fix everything it reports before you start.

Notes

  • In-place upgrade works from 5.7 only. A 5.6 server must go through 5.7 first.
  • There is no downgrade from 8.0 to 5.7. Take a full backup before upgrading and keep the 5.7 binaries around.
  • Older mysqldump clients cannot dump an 8.0 server correctly because of the changed mysql schema; use the 8.0 client tools.
  • mysql.user no longer contains the password_last_changed-era workarounds; scripts that read mysql.user directly should switch to information_schema.USER_PRIVILEGES or SHOW GRANTS.

My view: atomic DDL, persisted variables and roles matter more to me than the SQL features. I'd still wait for a couple of point releases before touching production.