To enable read-write splitting, the following
router
options must be enabled:
-
access_mode
: must be set toauto
.NoteIt is possible to define
read_write
andread_only
access_mode
values per session, only. See Per-Session Configuration. connection_sharing
: must be set to1
.protocol
: must be set toclassic
.destinations
: must be set to ametadata-cache
URL with the role set toPRIMARY_AND_SECONDARY
.
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
and
values:
optionNames
-
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.
Session variables are reset to their initial values if the
client sends a change_user
or
reset_connection
.
For example:
Press CTRL+C to copySQL> ROUTER SET access_mode='read_write'
Press CTRL+C to copySQL> query_attribute router.access_mode read_write
When MySQL Router is bootstrapped, the default configuration is created with the following values:
Press CTRL+C to copy[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
.