Technology Sharing | MySQL Shell Customized Deployment of MySQL Instances

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

I have written before using the DBA component of MySQL Shell to build, operate and maintain MySQL InnoDB Cluster. Today we will continue to extend the introduction of the DBA component and discuss how to use it to customize the deployment of MySQL instances.

1. How to deploy multiple MySQL instances of different versions:

When deploying multiple instances using the DBA component by default, use the built-in MySQL version of the system, that is, search for mysqld in the path included in the environment variable $PATH to confirm the specific instance version. For example, on my machine, the default MySQL version is 8.0, so the instance version after direct deployment is also 8.0.

 root@ytt-normal:~# mysqlsh --py 
MySQL Shell 8.0.29
   
...
MySQL  Py > dba.deploy_sandbox_instance(3350);
A new MySQL sandbox instance will be created on this host in 
/root/mysql-sandboxes/3350
   
...
   
Instance localhost:3350 successfully deployed and started.
Use shell.connect('root@localhost:3350') to connect to the instance.

For example, after the above instance with port 3350 is deployed, create a new connection, and the client will receive the specific version after success: Server version: 8.0.29 MySQL Community Server - GPL.

 MySQL  Py > \c root@localhost:3350
...
Your MySQL connection id is 12
Server version: 8.0.29 MySQL Community Server - GPL

If you want to deploy multiple MySQL instances of different versions at the same time, you only need to put the mysqld path of the corresponding version into $PATH. It should be noted that the splicing order of the new path and the old $PATH: the new path comes first, and the old $PATH comes after! After the deployment is complete, remember to restore the $PATH content.

For example, the MySQL 5.7 version installation package directory on my machine is: /root/opt/mysql/5.7.34, then add the subdirectory bin of this directory to the environment variable $PATH:

 root@ytt-normal:~# export PATH=/root/opt/mysql/5.7.34/bin:$PATH

Re-enter the MySQL Shell environment and deploy in the same way as MySQL 8.0 above: you can see that the newly deployed instance version is 5.7.34-log MySQL Community Server (GPL).

 MySQL  Py > dba.deploy_sandbox_instance(3351)
A new MySQL sandbox instance will be created on this host in 
/root/mysql-sandboxes/3351
...
   
Instance localhost:3351 successfully deployed and started.
Use shell.connect('root@localhost:3351') to connect to the instance.
   
MySQL  Py > \c root@localhost:3351
...
Your MySQL connection id is 7
Server version: 5.7.34-log MySQL Community Server (GPL)

2. How to change the base directory of the deployment instance:

The default deployment instance file is under ~/mysql-sandboxes, divided according to the instance port, each port has a subdirectory. For example, the two MySQL instances deployed before correspond to the directories /root/mysql-sandboxes/3350 and /root/mysql-sandboxes/3351 respectively.

There are two ways to change the base directory of the deployment instance:

1. When calling dba.deploy_sandbox_instance, specify the deployment directory explicitly:

 - sandboxDir: path where the new instance will be deployed.

For example, to deploy a new instance 3352, specify the base directory as: /tmp/mysql-sandbox.

 MySQL  Py > dba.deploy_sandbox_instance(3352,{"sandboxDir":"/tmp/mysql-sandbox"})
A new MySQL sandbox instance will be created on this host in 
 /tmp/mysql-sandbox/3352
      
...
Instance localhost:3352 successfully deployed and started.
Use shell.connect('root@localhost:3352') to connect to the instance.

The biggest disadvantage of this method is that the deployment of subsequent new instances is not universal, and the sandboxDir option needs to be specified for each new instance. If not specified explicitly, the default directory continues to be used: ~/mysql-sandboxes. For example, the following deployment instance 3353 still uses the default directory.

 MySQL  Py > dba.deploy_sandbox_instance(3353)
A new MySQL sandbox instance will be created on this host in 
/root/mysql-sandboxes/3353

The next method directly specifies the basic deployment directory in the Shell component of MySQL Shell, which is effective globally.

2. Explicitly set the options dictionary property of the shell component, and modify the value of the KEY named sandboxDir to the specified directory:

 - sandboxDir: default path where the new sandbox instances for InnoDB cluster will be deployed

Set sandboxDir to /tmp/mysql-sandbox: --persist is permanent, similar to the syntax effect of set persist in MySQL.

 MySQL  Py > \option --persist sandboxDir /tmp/mysql-sandbox

Re-enter the MySQL Shell environment and deploy two new instances with corresponding ports 3353 and 3354: both instances are deployed in the directory /tmp/mysql-sandbox.

 MySQL  Py > for i in range(3353,3355):
         ->     dba.deploy_sandbox_instance(i)
         -> 
A new MySQL sandbox instance will be created on this host in 
/tmp/mysql-sandbox/3353
...
A new MySQL sandbox instance will be created on this host in 
/tmp/mysql-sandbox/3354
...

3. How to change the newly deployed instance parameters:

The above-deployed instances did not set specific parameters, and only used the default values. No matter what scenario the deployed instance is used for, some parameters still need to be changed with the scenario. There are two ways to change parameters:

1. Configure the parameters while deploying the instance: suitable for changing a small number of parameters.

For example, to deploy a new instance 3355, specify the following parameters:

server-id=1000

tmp_table_size=64M

read_buffer_size=1M

Just add these parameters to the mysqldOptions array.

 MySQL  Py > dba.deploy_sandbox_instance(3355,{"mysqldOptions":["server_id=1000","tmp_table_size=64M","read_buffer_size=1M"]})
A new MySQL sandbox instance will be created on this host in 
/tmp/mysql-sandbox/3355
  
...
      
Instance localhost:3355 successfully deployed and started.
Use shell.connect('root@localhost:3355') to connect to the instance.

Confirm that my.cnf has been changed successfully:

 root@ytt-normal:/tmp/mysql-sandbox/3355# grep "server_id\|tmp_table_size\|read_buffer_size" my.cnf
      server_id = 1000
      tmp_table_size = 64M
      read_buffer_size = 1M

2. Configure parameters after the instance is deployed: suitable for changing a large number of parameters.

Change the corresponding my.cnf directly, and restart when finished.

Summarize:

Using the DBA component of MySQL Shell can easily deploy MySQL instances, but it is not recommended for production, because after entering the MySQL Shell environment, these instances can be maintained at will, which increases the risk.