You can query
InnoDB tables where the MySQL
data directory is on read-only media by enabling the
option at server startup.
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
do a slow shutdown.
To enable read-only mode for an entire MySQL instance, specify the following configuration options at server startup:
--innodb-temp-data-file-path. This option specifies the path, file name, and file size for
InnoDBtemporary tablespace data files. The default setting is
ibtmp1:12M:autoextend, which creates the
ibtmp1temporary tablespace data file in the data directory. To prepare an instance for read-only operation, set
innodb_temp_data_file_pathto a location outside of the data directory. The path must be relative to the data directory. For example:
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
innodb_read_only is enabled.
The same restriction applies to any operation that modifies data
dictionary tables, such as
In addition, other tables in the
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,
INSTALL PLUGIN operations are not
permitted in read-only mode.
This mode of operation is appropriate in situations such as:
Distributing a MySQL application, or a set of MySQL data, on a read-only storage medium such as a DVD or CD.
Multiple MySQL instances querying the same data directory simultaneously, typically in a data warehousing configuration. You might use this technique to avoid bottlenecks that can occur with a heavily loaded MySQL instance, or you might use different configuration options for the various instances to tune each one for particular kinds of queries.
Querying data that has been put into a read-only state for security or data integrity reasons, such as archived backup data.
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.
When the server is run in read-only mode through the
InnoDB features and components are
reduced or turned off entirely:
No change buffering is done, in particular no merges from the change buffer. To make sure the change buffer is empty when you prepare the instance for read-only operation, disable change buffering (
innodb_change_buffering=0) and do a slow shutdown first.
Most background threads are turned off. I/O read threads remain, as well as I/O write threads and a page flush coordinator thread for writes to temporary files, which are permitted in read-only mode. A buffer pool resize thread also remains active to enable online resizing of the buffer pool.
Information about deadlocks, monitor output, and so on is not written to temporary files. As a consequence,
SHOW ENGINE INNODB STATUSdoes not produce any output.
Changes to configuration option settings that would normally change the behavior of write operations, have no effect when the server is in read-only mode.