Here is a brief example to demonstrate how MySQL Router can be
deployed to use an InnoDB Cluster using bootstrapping. For
additional information, see
--bootstrap
and the other
bootstrap
options.
This example creates a standalone MySQL Router instance using the
--directory
option, enables
sockets, uses --account
to customize
Router's MySQL username, and sets
--account-create
to
always
to only bootstrap if the account does
not already exist. This example assumes that an InnoDB Cluster
named myCluster
already exists.
Press CTRL+C to copy$> mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter --conf-use-sockets --account routerfriend --account-create always Please enter MySQL password for root: # Bootstrapping MySQL Router instance at '/tmp/myrouter'... Please enter MySQL password for routerfriend: - Creating account(s) - Verifying account (using it to run SQL queries that would be run by Router) - Storing account in keyring - Adjusting permissions of generated files - Creating configuration /tmp/myrouter/mysqlrouter.conf # MySQL Router configured for the InnoDB Cluster 'myCluster' After this MySQL Router has been started with the generated configuration $ mysqlrouter -c /tmp/myrouter/mysqlrouter.conf the cluster 'myCluster' can be reached by connecting to: ## MySQL Classic protocol - Read/Write Connections: localhost:6446, /tmp/myrouter/mysql.sock - Read/Only Connections: localhost:6447, /tmp/myrouter/mysqlro.sock ## MySQL X protocol - Read/Write Connections: localhost:6448, /tmp/myrouter/mysqlx.sock - Read/Only Connections: localhost:6449, /tmp/myrouter/mysqlxro.sock
At this point the bootstrap process has created a
mysqlrouter.conf
file with the required files
at the directory specified, and the result shows you how to start
this MySQL Router instance. A generated MySQL Router directory looks
similar to:
Press CTRL+C to copy$> ls -l | awk '{print $9}' data/ log/ mysqlrouter.conf mysqlrouter.key run/ start.sh stop.sh
A generated MySQL Router configuration file
(mysqlrouter.conf
) looks similar to:
Press CTRL+C to copy# File automatically generated during MySQL Router bootstrap [DEFAULT] logging_folder=/tmp/myrouter/log runtime_folder=/tmp/myrouter/run data_folder=/tmp/myrouter/data keyring_path=/tmp/myrouter/data/keyring master_key_path=/tmp/myrouter/mysqlrouter.key connect_timeout=15 read_timeout=30 dynamic_state=/tmp/myrouter/data/state.json [logger] level = INFO [metadata_cache:myCluster] cluster_type=gr router_id=1 user=routerfriend metadata_cluster=myCluster ttl=0.5 auth_cache_ttl=-1 auth_cache_refresh_interval=2 use_gr_notifications=0 [routing:myCluster_rw] bind_address=0.0.0.0 bind_port=6446 socket=/tmp/myrouter/mysql.sock destinations=metadata-cache://myCluster/?role=PRIMARY routing_strategy=first-available protocol=classic [routing:myCluster_ro] bind_address=0.0.0.0 bind_port=6447 socket=/tmp/myrouter/mysqlro.sock destinations=metadata-cache://myCluster/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=classic [routing:myCluster_x_rw] bind_address=0.0.0.0 bind_port=6448 socket=/tmp/myrouter/mysqlx.sock destinations=metadata-cache://myCluster/?role=PRIMARY routing_strategy=first-available protocol=x [routing:myCluster_x_ro] bind_address=0.0.0.0 bind_port=6449 socket=/tmp/myrouter/mysqlx.sock destinations=metadata-cache://myCluster/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=x
In this example, MySQL Router configured four ports and four sockets.
Ports are added by default, and sockets were added by passing in
--conf-use-sockets
. The
InnoDB Cluster named "myCluster" is the source of the metadata,
and the destinations
are using
the InnoDB Cluster metadata cache to dynamically configure host
information. The related command line options:
--conf-use-sockets
: Optionally enable UNIX domain sockets for all four connection types, as demonstrated in the example.--conf-skip-tcp
: Optionally disable TCP ports, an option to pass in with--conf-use-sockets
if you only want sockets.--conf-base-port
: Optionally change the range of ports rather than using the default ports. This sets the port for classic read-write (PRIMARY) connections, and defaults to 6446.--conf-bind-address
: Optionally change the bind_address value for each route.
To demonstrate MySQL Router's behavior, the following client (application) connects to port 6446 but is connected to a MySQL instance on port 3310.
Press CTRL+C to copy$> mysql -u root -h 127.0.0.1 -P 6446 -p ... mysql> select @@port; +--------+ | @@port | +--------+ | 3310 | +--------+ 1 row in set (0.00 sec)
For additional examples, see Set Up a MySQL Server Sandbox and Deploying a Production InnoDB Cluster.