Can we run InnoDB from read only media like DVD/CD ? Yes, it’s possible from MySQL-5.6.7 release candidate. This makes users to explicitly setup the instance on read-only mode, It makes entire server instance as read only, You can’t selectively make certain table as read only. The “–innodb-read-only” boolean configuration parameter is used to start and operate MySQL server in read only mode. MySQL server will not start successfully in read only mode if it was not gracefully shutdown before.
The ideal use case of “innodb-read-only” mode is on an typical MySQL Master-Slave replication topology, If you have a slave definitely use the “–innodb-read-only” option to guarantee the slave consistency. Yet another appropriate scenario for usage of read-only innodb operation is for data warehousing systems, You don’t need to too much worry about custom configuration of MySQL / InnoDB for optimal performance of selected queries. To prepare MySQL infrastructure for read-only operations confirm all data is flushed to data files. Disable change buffering (innodb_change_buffering=0) and perform a slow shutdown. The slow shutdown is also known as clean shutdown which does additional InnoDB flushing operations before shutting down the MySQL infrastructure, You can enable slow shutdown by configuring parameter innodb_fast_shutdown=0 or the command SET GLOBAL innodb_fast_shutdown=0;
Things to remember while enabling –innodb-read-only option
- No change buffering done when innodb-read-only option is enabled, Disable change buffering (innodb-change-buffering=0) and do a slow shutdown. For those who don’t know about change buffering, The modifications on secondary indexes / primary key causes random I/O so instead of performing these random I/O operations necessary to read secondary index pages are cached in a special data structure named the change buffer.
- When you have MySQL infrastructure in configured with –innodb-read-only option there will be no crash recovery phase at startup so MySQL instance must have performed a slow shutdown before started with read-only state
- The redo log is not used in InnoDB read-only operations so you can set innodb_log_file_size to the smallest size possible (1MB) before making the instance read-only
- There will be no deadlocks in read-only instance because all background operations threads other than I/O read threads are turned off.
- SHOW ENGINE INNODB STATUS will not produce any output because information about deadlocks, monitor output and others are not written to temporary files
- If the data directory is still on writeable media even after starting MySQL with –innodb-read-only then root user can still perform DCL operations like GRANT and REVOKE
- The updates and deletes are not possible so all queries will read the latest version of a record and this will turn off isolation levels completely
