Documentation Home
MySQL 9.2 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 259.7Kb
Man Pages (Zip) - 366.9Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.2 Reference Manual  / ...  / The InnoDB Storage Engine  / InnoDB Configuration  /  Configuring InnoDB for Read-Only Operation

17.8.2 Configuring InnoDB for Read-Only Operation

You can queryInnoDB 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 isflushed 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 aslow shutdown.

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

  • --innodb-read-only=1

  • If the instance is on read-only media such as a DVD or CD, or the/var directory is not writeable by all:--pid-file=path_on_writeable_media and--event-scheduler=disabled

  • --innodb-temp-data-file-path. This option specifies the path, file name, and file size forInnoDB temporary tablespace data files. The default setting isibtmp1:12M:autoextend, which creates theibtmp1 temporary tablespace data file in the data directory. To prepare an instance for read-only operation, setinnodb_temp_data_file_path to a location outside of the data directory. The path must be relative to the data directory. For example:

    --innodb-temp-data-file-path=../../../tmp/ibtmp1:12M:autoextend

Enablinginnodb_read_only prevents table creation and drop operations for all storage engines. These operations modify data dictionary tables in themysql system database, but those tables use theInnoDB storage engine and cannot be modified wheninnodb_read_only is enabled. The same restriction applies to any operation that modifies data dictionary tables, such asANALYZE TABLE andALTER TABLEtbl_name ENGINE=engine_name.

In addition, other tables in themysql system database use theInnoDB storage engine in MySQL 9.2. Making those tables read only results in restrictions on operations that modify them. For example,CREATE USER,GRANT,REVOKE, andINSTALL PLUGIN operations are not permitted in read-only mode.

Usage Scenarios

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 avoidbottlenecks 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.

Note

This feature is mainly intended for flexibility in distribution and deployment, rather than raw performance based on the read-only aspect. SeeSection 10.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, certainInnoDB features and components are reduced or turned off entirely:

  • Nochange 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 aslow shutdown first.

  • There is nocrash recovery phase at startup. The instance must have performed aslow shutdown before being put into the read-only state.

  • Because theredo log is not used in read-only operation, you can setinnodb_log_file_size to the smallest size possible (1 MB) before making the instance read-only.

  • 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 STATUS does 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.

  • TheMVCC processing to enforceisolation levels is turned off. All queries read the latest version of a record, because update and deletes are not possible.

  • Theundo log is not used. Disable any settings for theinnodb_undo_tablespaces andinnodb_undo_directory configuration options.