Mysql – In MySQL 8, can we make group replication db as readonly

MySQLmysql-8.0replication

I have configured group_replication on 4 db. Out of which I am trying to make 2 db as slave(only read). Even though I make read_only, user can write data in it.

Is possible to make 2 as master and 2 as slave?

Best Answer

The simple answer to this would be ... MAYBE.

Single Primary mode has super_read_only on every node that is not Primary.

When it comes to Multi-Primary mode, one possible workaround would be to add nodes to the cluster that have a higher version of MySQL 8 than the Primary. Such a node should go into read-only mode automatically. Please note the paragraph of the MySQL 8 Multi-Primary Mode Docs under the heading Version Compatibility:

For optimal compatibility and performance, all members of a group should run the same version of MySQL Server and therefore of Group Replication. In multi-primary mode, this is more significant because all members would normally join the group in read-write mode. If a group includes members running more than one MySQL Server version, there is a potential for some members to be incompatible with others, because they support functions others do not, or lack functions others have. To guard against this, when a new member joins (including a former member that has been upgraded and restarted), the member carries out compatibility checks against the rest of the group.

One result of these compatibility checks is particularly important in multi-primary mode. If a joining member is running a higher MySQL Server version than the lowest version that the existing group members are running, it joins the group but remains in read-only mode. (In a group that is running in single-primary mode, newly added members default to being read-only in any case.) Members running MySQL 8.0.17 or higher take into account the patch version of the release when checking their compatibility. Members running MySQL 8.0.16 or lower, or MySQL 5.7, only take into account the major version.

In a group running in multi-primary mode with members that use different MySQL Server versions, Group Replication automatically manages the read-write and read-only status of members running MySQL 8.0.17 or higher. If a member leaves the group, the members running the version that is now the lowest are automatically set to read-write mode. When you change a group that was running in single-primary mode to run in multi-primary mode, using the group_replication_switch_to_multi_primary_mode() UDF, Group Replication automatically sets members to the correct mode. Members are automatically placed in read-only mode if they are running a higher MySQL server version than the lowest version present in the group, and members running the lowest version are placed in read-write mode.

For full information on version compatibility in a group and how this influences the behavior of a group during an upgrade process, see Section 18.7.1, “Combining Different Member Versions in a Group” .

You can also try using super_read_only on the desired node you want as read only, just make sure Group Replication doesn't start acting funny.

Related Question