How to start mysqld with read-only data directory?

Question

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag to my.conf, I get the following errors:

root@2380610236d1:/# mysqld --innodb-read-only
140807 19:19:38 [Warning] Can't create test file /var/lib/mysql/2380610236d1.lower-test
140807 19:19:38 [ERROR] mysqld: Can't create/write to file '/var/lib/mysql/aria_log_control' (Errcode: 13 "Permission denied")
140807 19:19:38 [ERROR] mysqld: Got error 'Can't create file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
140807 19:19:38 [ERROR] Plugin 'Aria' init function returned error.
140807 19:19:38 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] InnoDB: Started in read only mode
140807 19:19:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
140807 19:19:38 [Note] InnoDB: The InnoDB memory heap is disabled
140807 19:19:38 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140807 19:19:38 [Note] InnoDB: Compressed tables use zlib 1.2.8
140807 19:19:38 [Note] InnoDB: Using Linux native AIO
140807 19:19:38 [Note] InnoDB: Using CPU crc32 instructions
140807 19:19:38 [Note] InnoDB: Disabling background IO write threads.
140807 19:19:38 [Warning] InnoDB: Unable to open "./ib_logfile0" to check native AIO read support.
140807 19:19:38 [Warning] InnoDB: Linux Native AIO disabled.
140807 19:19:38 [Note] InnoDB: Initializing buffer pool, size = 256.0M
140807 19:19:38 [Note] InnoDB: Completed initialization of buffer pool
2014-08-07 19:19:38 7f2bc5a247c0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
140807 19:19:38 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
140807 19:19:38 [ERROR] InnoDB: The system tablespace must be writable!
140807 19:19:38 [ERROR] Plugin 'InnoDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] Plugin 'FEEDBACK' is disabled.
140807 19:19:38 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Couldn't start tokudb because some other tokudb process is using the same directory [/var/lib/mysql/] for [environment]
140807 19:19:38 [ERROR] Plugin 'TokuDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [ERROR] Unknown/unsupported storage engine: InnoDB
140807 19:19:38 [ERROR] Aborting

140807 19:19:38 [Note] mysqld: Shutdown complete

How can I start the database server with a read-only data directory?

Answer 1

Please note the InnoDB Architecture (Pictorial Representation by Percona CTO Vadim Tkachenko)

The system tablespace file ibdata1 has moving parts requiring writes

• Data Dictionary
• Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs

It's good you have --innodb-read-only set to InnoDB handle read-only situations.

You also need to make sure innodb_change_buffering is 0.

You also needed to make sure that crash recovery never happens. Here is how to set it up.

First, with the database on a writeable media, login to MySQL and run this:

mysql> set global innodb_fast_shutdown = 0;

Then, go to the OS and run

service mysql stop

With innodb_fast_shutdown enabled, this cause mysqld to purge everything and its grandmother out of the InnoDB plumbing upon shutdown.

Next, move the datadir to the readonly media.

Then, add this to my.conf

[mysqld]
innodb_change_buffering = 0
innodb_read_only = 1

Finally, go start mysql with service mysql start and InnoDB should start very quickly because there will be no need to perform crash recovery since everything was purged out of the InnoDB plumbing beforehand.