Rechercher dans le manuel MySQL
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:
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=
andpath_on_writeable_media
--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
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:
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.
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
andinnodb_undo_directory
configuration options.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-read-only-instance.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.