3.5.1 Configuration

To enable read-write splitting, the following router options must be enabled:

• access_mode: must be set to auto.

  Note

  It is possible to define read_write and read_only access_mode values per session, only. See Per-Session Configuration.

• connection_sharing: must be set to 1.

• protocol: must be set to classic.

• destinations: must be set to a metadata-cache URL with the role set to PRIMARY_AND_SECONDARY.

Per-Session Configuration

Read-write splitting configuration can be defined per session, using one of the following:

• ROUTER SET optionName='value'

• query_attributes router.optionName value

The following are the possible optionNames and values:

• access_mode set to one of the following values:

  • read_write: all session traffic is sent to a read_write server.

  • read_only: all session traffic is sent to a read_only server.

  • auto: the server is selected based on the type of transaction, reads are targetted to read_only servers, writes to read_write servers.

• wait_for_my_writes [ 0 | 1 ]: If enabled, 1, read-only queries wait for the last written transaction of the session.

• wait_for_my_writes_timeout [ 0 | 4294967295 ]: Maximum time in seconds to wait for a read_only destination to apply the written transaction, before falling back to a read_write destination. Default is 1.

Note

Session variables are reset to their initial values if the client sends a change_user or reset_connection.

For example:

        SQL> ROUTER SET access_mode='read_write'

        SQL> query_attribute router.access_mode read_write

Bootstrapping

When MySQL Router is bootstrapped, the default configuration is created with the following values:

[DEFAULT]
max_idle_server_connections=64

[routing:read_write_split]
bind_port=6450
destinations=metadata-cache://mycluster/?role=PRIMARY_AND_SECONDARY
routing_strategy=round-robin
access_mode=auto
protocol=classic
connection_sharing=1

To disable this configuration, you must bootstrap with --disable-rw-split.