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.
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
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: Starting MySQL Community Server... Aug 16 12:14:36 undol mysql-systemd-start: ERROR: Unable to start MySQL server: Aug 16 12:14:36 undol mysql-systemd-start: 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: 2023-08-16T17:14:36.573098Z 0 [ERROR] [MY-010119] [Server] Aborting Aug 16 12:14:36 undol mysql-systemd-start: 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: Once the problem is resolved, restart the service. Aug 16 12:14:36 undol systemd: mysql.service: Control process exited, code=exited, status=1/FAILURE Aug 16 12:14:36 undol systemd: mysql.service: Failed with result 'exit-code'. Aug 16 12:14:36 undol systemd: 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.