Read Only Instance

MySQL & MariaDB
1

I am trying to figure out a way to use read-only instances of MySQL (8) and none of the documentation I can find is of any help.

I’ve been trying to follow the MySQL docs and the Oracle docs but there seems to be a disconnect as to actually getting it to work that I appear to be missing.

For my tests, I’m using Ubuntu’s distributed MySQL, which should effectively be the same as Percona’s and MySQL’s distributed variants of MySQL, but willing to test that out if it means that it actually works.

ubuntu@undol:~$ cat /etc/mysql/mysql.conf.d/ro.cnf
[mysqld]
innodb_change_buffering=0
innodb_read_only=1
pid_file=/var/lib/mysqlrw/undol.pid
event_scheduler=disabled
innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend
innodb_temp_tablespaces_dir=/tmp/
tmpdir=/tmp/
skip_log_bin
#lower_case_table_names=0

These are the settings that I am attempting to use to create a read only instance.
First I run the instance in RW with all commented out except for innodb_change_buffering.

Then I do the slow shutdown, and even tried to be more verbose with the slow shutdown.

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
SET GLOBAL innodb_fast_shutdown=0;

I then stop mysql, unmount the directory, remount the directory using -o ro, and when I try to start the instance, I end up with:

2023-08-16T17:18:17.095300Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2023-08-16T17:18:17.095483Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34-0ubuntu0.20.04.1) starting as process 6409
2023-08-16T17:18:17.098164Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2023-08-16T17:18:17.098211Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2023-08-16T17:18:17.106146Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-08-16T17:18:17.396829Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-16T17:18:17.404904Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('2') and data dictionary ('0').
2023-08-16T17:18:17.406336Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-08-16T17:18:17.407136Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-08-16T17:18:17.938524Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34-0ubuntu0.20.04.1)  (Ubuntu).

My assumption is that when it can’t test the case sensitivity, it defaults to 2, but the issue is that it is and should be 0.

mysql> SELECT @@global.lower_case_table_names;
+---------------------------------+
| @@global.lower_case_table_names |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.01 sec)

But when I explicitly try to set it, it won’t even try to start mysql.

Aug 16 12:14:36 undol systemd[1]: Starting MySQL Community Server...
Aug 16 12:14:36 undol mysql-systemd-start[6152]: ERROR: Unable to start MySQL server:
Aug 16 12:14:36 undol mysql-systemd-start[6152]: 2023-08-16T17:14:36.567594Z 0 [ERROR] [MY-010158] [Server] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.
Aug 16 12:14:36 undol mysql-systemd-start[6152]: 2023-08-16T17:14:36.573098Z 0 [ERROR] [MY-010119] [Server] Aborting
Aug 16 12:14:36 undol mysql-systemd-start[6152]: Please take a look at https://wiki.debian.org/Teams/MySQL/FAQ for tips on fixing common upgrade issues.
Aug 16 12:14:36 undol mysql-systemd-start[6152]: Once the problem is resolved, restart the service.
Aug 16 12:14:36 undol systemd[1]: mysql.service: Control process exited, code=exited, status=1/FAILURE
Aug 16 12:14:36 undol systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 16 12:14:36 undol systemd[1]: Failed to start MySQL Community Server.

So I’m really unsure what I could attempt to try, short of re-initializing with a case-insensitive data structure, which defeats the purpose I’m attempting to solve.

Hopefully someone in this community can point me in the right direction, as I have spun my wheels on this and have no idea what I could try next.

Also, for what its worth, apparmor is disabled to rule that out.
And also, I can run MySQL in read-only mode, if the underlying filesystem is read-write.
Again, I’m trying to run MySQL in read-only mode from a read-only filesystem, think a DVD just like the documentation states.

I appreciate any pointers regarding this.

2

Is this a legit path? You don’t have a parameter for datadir in your conf so assuming it’s /var/lib/mysql, going up 3 directories and into tmp should work, provided you have that. When you burn to disk, what’s the path going to be? You should explicitly set a datadir so that your temp path is correct when mounted on another machine.

You are telling MySQL to be case-sensitive, but you’re using a case-insensitive file system. As it says, that is unsupported. MySQL can only operate in case-insensitive mode in your setup. This is probably related to the -o ro mount option.

3

Appreciate the response Matthew.

The path is legit, and the default datadir is /var/lib/mysql and can be assumed as such on the distributed copies.

It appears that innodb_temp_data_file_path can only be specified as a relative path, rather than an explicit path, thus the madness.

Defines the relative path, name, size, and attributes of global temporary tablespace data files.

And also

Before running InnoDB in read-only mode, set innodb_temp_data_file_path to a location outside of the data directory. The path must be relative to the data directory. For example:

--innodb-temp-data-file-path=../../../tmp/ibtmp1:12M:autoextend

Is it case insensitive because it can’t test the case insensitivity? Or is it actually case insensitive?

mount | grep foo #run again after mounting read-write
/home/ubuntu/foo.img on /var/lib/mysql type ext4 (ro,relatime)
/home/ubuntu/foo.img on /var/lib/mysql type ext4 (rw,relatime)

~$ dumpe2fs -h foo.img | egrep '(features|mounted|state)'
dumpe2fs 1.45.5 (07-Jan-2020)
Last mounted on:          /var/lib/mysql
Filesystem features:      has_journal ext_attr resize_inode dir_index filetype extent 64bit flex_bg sparse_super large_file huge_file dir_nlink extra_isize metadata_csum
Filesystem state:         clean
Journal features:         journal_64bit journal_checksum_v3

There is no casefold feature in my ext4 filesystem, which should imply that it can not be case-insensitive.

And as shown,

this would seem to imply that the filesystem (when mounted read-write) is case-sensitive.
And only when it can’t do the test file at bootstrap, it then falls back to case insensitive.

I was hoping to have a more clear why it can only operate in case-insensitive mode when set to read-only. Since neither the mysql or oracle docs mention anything regarding case-sensitivity in their documentation (and I failed to find any corresponding percona documentation to corroborate or contradict) it seems that if that were a critical requisite, it would be clearly spelled out.

Also, when the device is mounted RW, and I supply lower_case_table_names=0 with all of the other RO settings in ro.cnf, it starts the server no problem. So the file system is clearly case sensitive, and doesn’t change other than it ability to be written to, MySQL just fails the test of “can I write? no, failsafe to insensitive.”

2023-08-16T19:31:29.576291Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34-0ubuntu0.20.04.1) starting as process 1511301
2023-08-16T19:31:29.583354Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-08-16T19:31:30.073505Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-16T19:31:30.337782Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2023-08-16T19:31:30.337883Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2023-08-16T19:31:30.365182Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2023-08-16T19:31:30.366258Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-08-16T19:31:30.366298Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2023-08-16T19:31:30.400703Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-08-16T19:31:30.400769Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-08-16T19:31:30.466013Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-08-16T19:31:30.466237Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.34-0ubuntu0.20.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

My use case here is to take a block device from a SAN, clone a snapshot of the block device N-times, and mount read-only on N-instances as development copies with a dataset that will absolutely not change (because it can’t be written to).

FWIW, I’ve also tried with XFS instead of ext4 with no success, so I don’t believe it to be limited to ext4.

4

Yep. That looks exactly correct:

Looks like you were able to get it working from RO mount?

5

@reedacus25 were you able to run mysql in readonly mode?

6

I was never able to get readonly working, though I didn’t try too much more after this point, so things may have changed in the interim.

1 Like