15.8.2 Configuring InnoDB for Read-Only Operation

Inhaltsverzeichnis

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:

--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 for InnoDB temporary tablespace data files. The default setting is ibtmp1:12M:autoextend, which creates the ibtmp1 temporary tablespace data file in the data directory. To prepare an instance for read-only operation, set innodb_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
```

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.

Inhaltsverzeichnis Haut

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

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.

Inhaltsverzeichnis Haut

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:

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.
There is no crash recovery phase at startup. The instance must have performed a slow shutdown before being put into the read-only state.
Because the redo log is not used in read-only operation, you can set innodb_log_file_size to the smallest size possible (1 MB) before making the instance read-only.
All background threads other than I/O read threads are turned off. As a consequence, a read-only instance cannot encounter any deadlock.
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.
The MVCC processing to enforce isolation levels is turned off. All queries read the latest version of a record, because update and deletes are not possible.
The undo log is not used. Disable any settings for the innodb_undo_tablespaces and innodb_undo_directory configuration options.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-read-only-instance.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut