InnoDB now works with read-only media

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!

 

5 thoughts on “InnoDB now works with read-only media

    1. I haven’t done (or seen) performance analysis of InnoDB running with –innodb-read-only enabled. I know Dimitri has tested read-only transaction performance here, which is likely to benefit more use cases.

    1. It’s not possible to set –innodb-read-only and start the server when crash recovery is required. I haven’t tried setting various innodb_crash_recovery parameters to see if that restriction can be bypassed yet.

  1. Todd, Nice write up :-). Another potential benefit of this change is the ability to run multiple InnoDB instances (all in read-only mode) against the same shared data set. e.g., on an NFS mounted drive.

Leave a Reply to Todd Farmer Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.