MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

15.8.2 Configuring InnoDB for Read-Only Operation

You can now query InnoDB tables where the MySQL data directory is on read-only media, by enabling the --innodb-read-only configuration option at server startup.

How to Enable

To prepare an instance for read-only operation, make sure all the necessary information is flushed to the data files before storing it on the read-only medium. Run the server with change buffering disabled (innodb_change_buffering=0) and do a slow shutdown.

To enable read-only mode for an entire MySQL instance, specify the following configuration options at server startup:

As of MySQL 8.0, enabling innodb_read_only prevents table creation and drop operations for all storage engines. These operations modify data dictionary tables in the mysql system database, but those tables use the InnoDB storage engine and cannot be modified when innodb_read_only is enabled. The same restriction applies to any operation that modifies data dictionary tables, such as ANALYZE TABLE and ALTER TABLE tbl_name ENGINE=engine_name.

In addition, other tables in the mysql system database use the InnoDB storage engine in MySQL 8.0. Making those tables read only results in restrictions on operations that modify them. For example, CREATE USER, GRANT, REVOKE, and INSTALL PLUGIN operations are not permitted in read-only mode.

Usage Scenarios

This mode of operation is appropriate in situations such as:

Note

This feature is mainly intended for flexibility in distribution and deployment, rather than raw performance based on the read-only aspect. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for ways to tune the performance of read-only queries, which do not require making the entire server read-only.

How It Works

When the server is run in read-only mode through the --innodb-read-only option, certain InnoDB features and components are reduced or turned off entirely: