With a handful of exceptions, few people deliberately choose to deploy MySQL on read-only media – but there are cases where being able to access InnoDB data that way comes in handy. As it happened, I had exactly this need a few months back, and the excellent InnoDB development team at Oracle has recently implemented this feature in MySQL 5.6.
First, some background. We had a need to migrate legacy systems from a 3rd party data center into new corporate data centers. These systems were redundant, but we wanted to retain access to the data for archival purposes. All went well, except one small detail: Because the machines were originally housed outside the new data center, we weren’t allowed to stand up the servers inside the new data center – except in read-only mode.
Therein was the problem – we had many GB worth of data stored in MySQL using InnoDB, and we could access the data files – but mysqld would not start without read/write access to InnoDB-related files. That meant a long process of transferring the data files from the original read-only mount to somewhere else. While an inconvenience for us, it’s easy to see similar situations causing business pain as the data is transferred.
This enhancement also allows you to more quickly access backups you’ve stored on CD or DVD. This helps validate the integrity of a file system backup on read-only media (so you can be assured it won’t later be overwritten). In addition, it makes it possible to distribute read-only database content via CD or DVD.
You can enable this with the new –innodb-read-only configuration option, starting in MySQL 5.6.7. Make sure you read the excellent documentation on running with InnoDB in read-only mode (also linked from the configuration option description). Here’s an example:
C:\mysql-5.6.8-rc-winx64>bin\mysqladmin -uroot --port=3307 shutdown C:\mysql-5.6.8-rc-winx64>attrib +R data\test\tt.ibd C:\mysql-5.6.8-rc-winx64>attrib data\test\tt.ibd A R C:\mysql-5.6.8-rc-winx64\data\test\tt.ibd C:\mysql-5.6.8-rc-winx64>start bin\mysqld --no-defaults --console --port=3307 --innodb-read-only C:\mysql-5.6.8-rc-winx64>bin\mysql -uroot --port=3307 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.8-rc MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELET * FROM tt; +------+ | i | +------+ | 1 | | 2 | +------+ 2 rows in set (0.03 sec) mysql> INSERT INTO tt VALUES (3); ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only) mysql> UPDATE tt SET i = 4; ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only) mysql> CREATE TABLE mt (i INT) ENGINE = MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO mt SELECT * FROM tt; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM mt; +------+ | i | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
Please note that it’s currently essential that you enable this option *before* starting MySQL with read-only media. When started without this option, InnoDB will choke on read-only media and crash. As noted in the documentation, crash recovery cannot happen with –innodb-read-only, so the only way forward is to start without read-only media.
With the added support for transportable tablespaces, read-only media, fulltext indexes and performance optimizations for read-only transactions, the number of use cases that prefer MyISAM over InnoDB are significantly reduced with the release of MySQL 5.6. Good work InnoDB team!