6.3.9. Host-based Routing

Auto Read/Write Splitting No
Master Selection Manually, by hostname/IP address
Slave Selection Manually, by hostname/IP address
QoS Compatibility None
SmartScale Compatibility None

Host-based routing uses specific hostnames to provide the distinction between read and write availability within the connector. Two different hostnames and associated IP addresses need to be created on each connector host. Clients connecting to one host will be routed to the current master for writing, and connections to the other host will be redirected to a current slave using the current load-balancing algorithm.

Once enabled, a client can open a connection directly to a master or slave by connecting to the appropriate IP address or hostname. For example:

shell> mysql -hmaster.localhost

Will connect to the currently active master, while:

shell> mysql -hslave.localhost

Would connect to any currently available slave.

6.3.9.1. Enabling Host-based Routing

To enable host-based routing requires both operating system and Connector based configuration changes:

  1. The following steps must be made to the operating system configuration for each Connector host that will be configured within the dataservice:

    1. Add a second IP address to the host. This can be achieved either by adding or exposing a second physical ethernet device, or by exposing an alias on an existing hardware interface.

      For example, to add a second IP address to the physical eth0 interface:

      shell> sudo ifconfig eth0:1 192.168.2.24

      To ensure this is retained during a restart, update your network configuration with the additional physical interface and IP address.

    2. Update the /etc/hosts file to reflect both addresses and appropriate hostnames. For example:

      192.168.2.20 host1 master.host1
      192.168.2.21 slave.host1
    3. When using DNS to resolve addresses, the DNS should also be updated with hostnames to match those configured for each IP interface.

  2. Update the user.map file on every host running a connector to reflect the the desired QoS for each hostname. The connector will automatically read the changes after the file is saved.

    @hostoption master.host1 qos=RW_STRICT
    @hostoption master.host2 qos=RW_STRICT
    @hostoption master.host3 qos=RW_STRICT
    @hostoption slave.host1 qos=RO_RELAXED
    @hostoption slave.host2 qos=RO_RELAXED
    @hostoption slave.host3 qos=RO_RELAXED

Once configured, client applications must be configured to select the appropriate host based on the operation they are performing.

6.3.9.2. Limitations of Host-based Routing

  • Prepared statements must be executed on the master.

  • Smartscale cannot be enabled at the same time as host-based routing.

  • QoS selection will not be honored.