Keeping a secret: MySQL router "bootstrapping" without a "root" account

Even as an experienced consultant, you don't think about many things until you are faced with them. I have put several InnoDB clusters including MySQL router into operation over the past few years. Up to now, this was always an interactive process where it was no problem to simply enter the passwords of privileged users (at the console). It was clear, at least in theory, that this must also be possible in another way, since the MySQL operator for Kubernetes, for example, is also able to "deploy" routers automatically. Time for a look at the documentation. 

A few basics 

We have already reported about the MySQL InnoDB Cluster and the MySQL Router several times. The Router's job is to know the state of the cluster and the roles of the nodes in the system, and to make this information transparent to the connecting clients. In practice, this means that the client and/or the application server wants to start its connection against the router, but is then forwarded by it to a system in the cluster. In this way, the client does not need to know which node in the cluster is currently able to process the primary role (and thus also accepts write accesses, for example). 

Lace up your boots! 

To put a router into operation, a so-called bootstrapping process is started. The router connects to a node in the corresponding InnoDB cluster and creates a special user account on all nodes with which the cluster status can be checked. This user does not actually need excessive rights: 

GRANT USAGE ON *.* TO `router`@`%` 
GRANT SELECT, EXECUTE ON `mysql_innodb_cluster_metadata`.* TO `router`@`%` 
GRANT INSERT, UPDATE, DELETE ON `mysql_innodb_cluster_metadata`.`routers` TO `router`@`%` 
GRANT INSERT, UPDATE, DELETE ON `mysql_innodb_cluster_metadata`.`v2_routers` TO `router`@`%` 
GRANT SELECT ON `performance_schema`.`global_variables` TO `router`@`%` 
GRANT SELECT ON `performance_schema`.`replication_group_member_stats` TO `router`@`%` 
GRANT SELECT ON `performance_schema`.`replication_group_members` TO `router`@`%`  

According to the example in the documentation, the "root" user should be used for this bootstrapping, as this user has the right to create new users on the nodes involved.

$> mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter --conf-use-sockets --account routerfriend --account-create always

Source: https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-deploying-bootstrapping.html

If no username (--account) is defined, MySQL "makes up" its own name (here "mysql_router12_u36vawan0901"): 

bash> root@2210103d6224:/# mysqlrouter --bootstrap=root:root@node1 -d my_router --user=mysql 
# Bootstrapping MySQL Router instance at '/my_router'... 
... 
bash cat my_router/mysqlrouter.conf | grep user | grep router 
user=mysql_router12_u36vawan0901  

Tighten the boot! 

Now, in certain scenarios (containers, automation solutions, scripting), one does not want to reveal the DBA account "root" incl. password. So can you design the bootstrap process differently? Yes, you can. Instead of letting creating the user during the bootstrapping, we prepare the account in advance. Of course, we could simply create the user manually using simple SQL (see above). But it is easier with the shell. To do this, we take the following steps: 

# Connect to the cluster 
MySQL JS >\c root:root@node1 

# Instantiate cluster object 
MySQL node1:33060+ ssl JS > var clu = dba.getCluster('ordix'); 
 
# create router account 
MySQL node1:33060+ ssl JS > clu.setupRouterAccount('my_router') 

Missing the password for new account my_router@%. Please provide one. 
Password for new account: ****** 
Confirm password: ****** 

Creating user my_router@%. 
Account my_router@% was successfully created.  

In the last step, we use the new, not very highly privileged user account to initialize (bootstrap) the router: 

bash> mysqlrouter --bootstrap=my_router:secret@node1 --account=my_router --directory=safe_router --user=mysql --account-create=never --force 
# Bootstrapping MySQL Router instance at '/safe_router'... 

bash> cat safe_router/mysqlrouter.conf | grep user 
user=my_router  

Conclusion 

As can be seen, the "safe" automated installation of routers can be done quite easily. Of course, this is no secret knowledge and can also be understood (with a certain amount of perseverance) via the documentation. But perhaps this article is nevertheless a small "shortcut" for some of you.

Do you have questions about the highly available operation of MySQL and Co. then please contact us. We are glad to help.