MySQL 5.6: Testing innodb_read_only with multiple instances

There are many good things in the upcoming MySQL 5.6 release. One thing that caught my eye early on was the ability to start the server with InnoDB set to a read only mode.

A few months ago Todd Farmer wrote about this ability from the perspective of setting up an instance on read-only media (InnoDB now works with read-only media). And I encourage you to read that post first.
I decided to test this from a data warehouse perspective (as Sunny Bains points out in a comment to Todd's post). I used machines I have available: 1 OL6 desktop running 5.6.9 and 1 MacBook Pro running 10.8.

First thing to try was adding the innodb_read_only parameter to the config file on an instance I already had running on the Linux box. My setting:

[mysqld]
user = mysql
port = 5602
basedir = /opt/mysql/5.6.9rc
datadir = /data/5.6.ro
socket = /tmp/mysql5602.sock
tmpdir = /tmp
pid-file = /tmp/mysql_5602.pid
log-error = /tmp/mysql_5602.err
#

innodb-read-only
lower_case_table_names = 1
innodb-log-file-size = 256M
innodb_buffer_pool_size = 2G
innodb_change_buffering = none


One thing you may notice is that I have the log file size set to 256M, which is really a waste since there will be no logging taking place. I'll explain why towards the end of this post.

So, nothing unexpected after starting the instance with the settings above. SELECTs work fine but DML operations fail:

craig@localhost[5.6_r02] (employees) > select * from departments where dept_no = 'd001';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d001    | Marketing |
+---------+-----------+
1 row in set (0.00 sec)

craig@localhost[5.6_r02] (employees) > delete from departments where dept_no = 'd001';
ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only)
craig@localhost[5.6_r02] (employees) >

OK, now to set up a separate instance and point it to the same data directory.
The my.cnf file for the second instance:

[mysqld]
user            = mysql
port            = 5601
socket          = /tmp/mysql5601.sock
basedir         = /opt/mysql/5.6.9rc
datadir         = /data/5.6.ro
tmpdir          = /tmp
pid-file        = /tmp/mysql_5601.pid
log-error       = /tmp/mysql_5601.err
#
innodb-read-only
innodb-log-file-size    = 256M
innodb_buffer_pool_size = 2G
innodb_change_buffering = none
lower_case_table_names  = 1

Again, try to DELETE from the employees.departments table:

craig@localhost[5.6_r01] (employees) > select * from departments where dept_no = 'd001';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d001    | Marketing |
+---------+-----------+
1 row in set (0.00 sec)

craig@localhost[5.6_r01] (employees) > delete from departments where dept_no = 'd001';
ERROR 1015 (HY000): Can't lock file (errno: 165 - Table is read only)
craig@localhost[5.6_r01] (employees) >

So, two instances using the same InnoDB data. Now to try setting up an instance on a separate machine accessing the same datadir on my Linux server. After learning enough of NFS to be dangerous and flailing around for half a day, I was finally able the get the exported NFS file system mounted on my laptop. On the Linux side (NFS server), the /etc/exports file has the following entry:

[craig@craighp msb_56_ro2]$ cat /etc/exports
/data  craig-mbp(rw,sync,insecure)

A screenshot from the laptop of mounting this directory:

And here is the my.cnf file I used, starting of the server, and SQL commands showing I can read the data but changes are not allowed (at least to InnoDB data).

So, multiple instances across multiple machines accessing one data directory of InnoDB data. May have some uses in a data warehouse, operational data store, or reporting environment by spreading processing across servers and saving disk space.

Now, back to why I set innodb_log_file_size = 256M. I wouldn't necessarily recommend this but I wanted to see I could restart one of my instances as a RW instance (commenting out the innodb_read_only parameter). And I could. And I could make changes, load new data, delete old data, etc. And after you make your changes, you can restart the instance as read only. While this is going on, the other instances are blissfully unaware and will see none of it until they are restarted. I will reiterate that this worked in my limited testing. Production is an entirely different beast.

The MySQL server and InnoDB has done an amazing job with the 5.6 release. Kudos. And thanks to Giuseppe Maxia for his MySQL Sandbox toolkit. Spinning up test instances is fast and easy.