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: 

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"): 

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: 

In the last step, we use the new, not very highly privileged user account to initialize (bootstrap) the 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.