ReadOnlyMedia上的MySQL 5.7

Question

我必须设置一个在只读介质上运行的MySQL。根据MySql https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html的文档，这似乎并不那么困难。

但是按照所描述的配置，不可能从ISO文件启动服务器。

启动服务器的参数：

mysql-5.7.17-win32\bin\mysqld.exe --no-defaults --console --port=3307 --innodb-read-only=1 --event-scheduler=disabled --innodb_change_buffering=none --innodb_flush_method=normal --pid-file=d:\mysql.pid

不幸的是，这不起作用。我将获取日志：

2017-02-24T18:23:33.913117Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-02-24T18:23:33.913117Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-02-24T18:23:33.914120Z 0 [Note] mysql-5.7.17-win32\bin\mysqld.exe (mysqld 5.7.17) starting as process 9468 ...
2017-02-24T18:23:33.916995Z 0 [Warning] Can't create test file G:\mysql-5.7.17-win32\data\MyComputer.lower-test
2017-02-24T18:23:33.917497Z 0 [Warning] Can't create test file G:\mysql-5.7.17-win32\data\MyComputer.lower-test
2017-02-24T18:23:33.919531Z 0 [Note] InnoDB: Started in read only mode
2017-02-24T18:23:33.919531Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-02-24T18:23:33.920004Z 0 [Note] InnoDB: Uses event mutexes
2017-02-24T18:23:33.920004Z 0 [Note] InnoDB: Memory barrier is not used
2017-02-24T18:23:33.920004Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-24T18:23:33.920504Z 0 [Note] InnoDB: Number of pools: 1
2017-02-24T18:23:33.921007Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-02-24T18:23:33.921007Z 0 [Note] InnoDB: Disabling background log and ibuf IO write threads.
2017-02-24T18:23:33.923041Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-02-24T18:23:33.928551Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-24T18:23:33.951319Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-24T18:23:34.109640Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-02-24T18:23:34.110647Z 0 [ERROR] InnoDB: Operating system error number 5 in a file operation.
2017-02-24T18:23:34.110647Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. It may also be you have created a subdirectory of the same name as a data file.
2017-02-24T18:23:34.110647Z 0 [ERROR] InnoDB: Cannot open datafile '.\ibtmp1'
2017-02-24T18:23:34.110647Z 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
2017-02-24T18:23:34.110647Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file
2017-02-24T18:23:34.414731Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-02-24T18:23:34.414731Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-02-24T18:23:34.415619Z 0 [ERROR] Failed to initialize plugins.
2017-02-24T18:23:34.415619Z 0 [ERROR] Aborting

2017-02-24T18:23:34.416141Z 0 [Note] Binlog end
2017-02-24T18:23:34.416141Z 0 [Note] Shutting down plugin 'CSV'
2017-02-24T18:23:34.416640Z 0 [Note] mysql-5.7.17-win32\bin\mysqld.exe: Shutdown complete

不能创建共享表空间是正确的。我们使用的是只读介质。

但是，如何避免创建共享表空间，或者让它在可读介质上的临时文件夹中创建呢？

Answer 1

• 首先，您需要在可写介质上创建实例，
• 按照参考文档中的说明进行缓慢关闭，
• 将数据库文件复制/刻录或其他任何文件复制/刻录到不可写介质
• 更改配置参数以反映新配置: datadir、innodb_data_home_dir、....
• 重新启动实例

et voilà