Deploy MySQL based on StatefulSet in Kubernetes (Part 1)

Hello everyone, my name is Z!

This paper implements the installation and deployment of MySQL database on the K8s cluster based on KubeSphere deployment, and the deployment method adopts the form of graphics. The next article will cover the basic operations of GitOps. All YAML files involved in the deployment process will be version managed using Git and stored in the Git repository, so stay tuned!

The MySQL deployed in this article selects the conservative 5.7 series, and other versions may be different. The operations in this article are only applicable to database usage scenarios with small-scale data volumes and low reliability and performance requirements, such as development and testing environments, such as Nacos services in my production environment. Personally, it is not recommended to put data on K8s in production environments or important databases. The RDS provided by the cloud service provider is preferred, and then the MySQL master-slave or Galera Cluster is built by using virtual machines, and a backup plan must be prepared.

The reliability and availability of the database is the top priority of operation and maintenance, which cannot be ignored. Remember! ! !

Knowledge points of this article

• Rating: entry level
• Installation and configuration of single node MySQL on K8s
• KubeSphere graphically deploys workloads
• Getting Started with GitOps
• Git common operations
• How the configuration code is kept in sync on GitHub and Gitee
• MySQL Performance Testing Basics
• Operation and maintenance ideas, ideas

Demo server configuration

CPU name	operating system	IP	CPU	Memory	system disk	data disk	use
zdeops-master	CentOS-7.9-x86_64	192.168.9.9	2	4	40	200	Ansible operation and maintenance control node
ks-k8s-master-0	CentOS-7.9-x86_64	192.168.9.91	8	32	40	200	KubeSphere/k8s-master/k8s-worker
ks-k8s-master-1	CentOS-7.9-x86_64	192.168.9.92	8	32	40	200	KubeSphere/k8s-master/k8s-worker
ks-k8s-master-2	CentOS-7.9-x86_64	192.168.9.93	8	32	40	200	KubeSphere/k8s-master/k8s-worker
glusterfs-node-0	CentOS-7.9-x86_64	192.168.9.95	4	8	40	200	GlusterFS/Elasticsearch
glusterfs-node-1		192.168.9.96	4	8	40	200	GlusterFS/Elasticsearch
glusterfs-node-2	CentOS-7.9-x86_64	192.168.9.97	4	8	40	200	GlusterFS/Elasticsearch

---

MySQL Installation Tour

Find reference documentation

My personal search path for reference documents is accustomed to

• Official website - precise positioning

  • Sometimes there is no relevant documentation on the official website, or the documentation is not detailed enough
  • English documentation, difficulty reading

• Search Keyword - Finding a Needle in a Haystack

  • CSDN
  • Blog Park
  • someone's blog
  • Q&A site
  • other

Open MySQL official website

Select the Reference Manual for MySQL 5.7 .

In the Installing MySQL on Linux chapter, I searched and found two documents with reference value under the Deploying MySQL on Linux with Docker section. Let's take a look first.

After browsing, you will find that you just learned the basic method of using Docker Image to install MySQL, and the details are not on the picture.

Although the official did not mention how to deploy MySQL on K8s, but I already have the basic knowledge of Docker and K8s, I will not go to search and eat others, and try to deploy a single-node MySQL on K8s.

Trying to deploy single node MySQL

Let’s sort out the ideas first, what resources do we need to prepare to deploy a MySQL

• Get the MySQL image on Docker Hub.
• Check the MySQL image description to determine the installation initialization parameters.
• MySQL is a stateful service, so we need to define a resource of type StatefulSet.
• Write a MySQL resource definition file of type StatefulSet - YAML.

Check the official image description to determine the initialization parameters

If you have experience in deploying MySQL with Docker before, this step is very simple, just move the parameter configuration directly.

Open https://hub.docker.com and search for mysql.

There will be a lot of mysql in the search results, and I focused on two mirrors.

• The mysql repository officially maintained by Docker
• The mysql repository maintained by Oracle's MySQL team

In this experiment, I used the warehouse officially maintained by Docker and entered the MySQL warehouse page.

I browsed around and confirmed several places that must be configured (the determination process requires experience and technical accumulation).

• Mirror: mysql:5.7.38
• root password: MYSQL_ROOT_PASSWORD
• Data persistent storage directory: /var/lib/mysql

Deploy MySQL with KubeSphere (version V1)

After the initialization parameters are determined, MySQL is deployed.

Write a resource definition YAML file in the usual way of K8s? NO! I'm a novice now, and handwritten profiles are too high-end for me.

Let's take a chance here and use KubeSphere's graphical operation to ensure the success rate of one deployment (there is also a hidden benefit, sell it first).

Log in to the KubeSphere console using an account with enterprise space administrator privileges.

In this step, the admin user is not used, and the multi-tenant account is used to simulate a real production environment.

< , >

Click the project , click the lstack project, and enter the project management page (if there is no special instruction, many of the following interface operations are done on this page).

< , >

Application Load -> Workload -> Stateful Replica Set , click Create .

The Create Stateful Replica Set page pops up, the basic information page, enter mysql for the name .

Container group settings page.

• Number of Container Group Replicas : 1
• Click Add Container , enter mysql:5.7.38 in the image search bar
• Container name : lstack-mysql
• CPU (Core) resources : reserve 0.5, limit 2
• Memory (Mi) : Reserve 500i, limit 4000
• Port settings : protocol TCP, name tcp-mysql, container port: 3306, service port 3306

• Environment variables :

  • Reference to configuration dictionary or privacy dictionary
  • Create secret dictionary, key (MYSQL_ROOT_PASSWORD), value (P@88w0rd)
• Synchronize host time zone : Check it
• Other unspecified configurations take default values

< , , , >

Create a secret dictionary : In the environment variable option, click Create a secret dictionary , and follow the steps shown below.

< , , , >

Click Create to return to the container group settings page.

After completing the configuration according to the above information, click the check button.

After the container group settings are complete, click Next to enter the storage volume settings .

Storage Volume Settings -> Storage Volume Templates -> Add Storage Volume Template .

• Storage volume name: data

  • Don't write too much here, the system will automatically add the name of StatefulSet as the name suffix to generate a storage volume with a naming format similar to data-mysql-0
• Storage type: glusterfs
• Access Mode: ReadWriteOnce
• Storage volume capacity: 5Gi
• Mount path: read and write /var/lib/mysql
  

After completing the configuration according to the above information, click the check button.

After the storage volume setting is complete, click Next to enter the advanced settings , keep the default values, and click the Create button.

After the creation is successful, it will automatically return to the workload page. The first time you create it, you will go to DockerHub to download the image, so the initial display status is Updating .

When the image download is complete and the container is configured correctly, the status changes to Running .

Click mysql to enter the stateful replica set details page.

< , , >

Monitoring , you can see the resource usage at the initial startup, and then you can adjust the configuration of our resources based on the monitoring data.

Environment variables , you can see that our newly added Secret dictionary is in effect, and the password is hidden and displayed.

< , >

Let's take a look at the details of the container group . On the resource status page, click the container group mysql-0.

< , , , , , >

Let's take a look at the service corresponding to StatefulSet, application load -> service .

You can see that a StatefulSet MySQL corresponding state service (Headless), mysql-2v7f (mysql) , is automatically created.

Click mysql-2v7f(mysql) to view service details.

< , , >

Finally, verify whether our MySQL service is normal (here we only look at the service itself, do not test the external connection first).

Application load -> workload -> stateful replica set -> mysql -> container group -> mysql-0 -> terminal.

< , >

At this point, the basic installation of MySQL in K8s has been completed, and other applications in the K8s cluster can access the MySQL service through the svc address (the svc address is mysql-2v7f.lstack ). At this time, the name is still very unfriendly, so we don’t need it for now. it.

Advanced MySQL configuration

The above completes the basic installation and configuration of MySQL. However, in actual use, we usually have the following requirements, which require us to configure MySQL.

Enable external access

Enabling external access is convenient for administrators to operate the MySQL database, and can also meet the needs of services outside the K8s cluster to access the MySQL database.

To enable external access to services in KubeSphere, you need to set the project gateway first.

Log in to the console with the project administrator user.

Workbench- > Project- >Click the specific project- > Project Settings- > Gateway Settings , click to open the gateway .

The current access modes are NodePort and LoadBalancer, but LoadBalancer only supports load balancers on the public cloud provider cloud, so we can only select NodePort and click OK.

In NodePort mode, a container group using kubesphere-router with nginx-ingress will be created, and we will discuss the details in a future article.

< , >

The details of gateway settings are not discussed in depth in this article, and there will be special articles to discuss them later. Now, this step is OK.

Next, create a MySQL service to provide external services.

Apply Workload ->- Services > Create- >Select Custom Service- > Specify Workload .

There is an option for an external service, that is based on Ingress using a domain name to access, not the way we currently want.

Specifies the workload creation service - basic information .

• Name: mysql-external

Specify the workload to create a service - service settings , click on the specified workload , select the stateful replica set -> mysql , and click OK .

Specify the workload creation service - service settings , port configuration.

• Protocol: TCP
• Name: tcp-mysql-external
• container port: 3306
• Service port: 3306

Specify Workload Creation Service - Advanced Settings .

• External access: access mode select NodePort

After completing all settings, click Create , and the creation will automatically return to the service list. In the service list, you can see our newly created service mysql-external and the automatically assigned external access port number.

First, use the telnet command to test the connectivity of the MySQL service. If you can see the following results, it means that MySQL can be accessed outside the K8s cluster.

 [root@ks-k8s-master-0 ~]# telnet 192.168.9.91 32529
Trying 192.168.9.91...
Connected to 192.168.9.91.
Escape character is '^]'.
EHost '10.233.117.0' is not allowed to connect to this MySQL serverConnection closed by foreign host.

# 细节！上面的EHost地址是192.168.9.91这个节点在K8s集群内部分配的IP
[root@ks-k8s-master-0 ~]# ip add | grep 117 -B 2 -A 1
7: tunl0@NONE: <NOARP,UP,LOWER_UP> mtu 1440 qdisc noqueue state UNKNOWN group default qlen 1000
    link/ipip 0.0.0.0 brd 0.0.0.0
    inet 10.233.117.0/32 scope global tunl0
       valid_lft forever preferred_lft forever
[root@ks-k8s-master-0 ~]# ip add | grep 91
    inet 192.168.9.91/24 brd 192.168.9.255 scope global noprefixroute ens160
    link/ether c6:d3:91:95:f1:0f brd ff:ff:ff:ff:ff:ff

Custom MySQL configuration file

The my.cnf configuration file used by the default installed MySQL has limited adaptation scenarios, so customizing the mysql configuration file is an inevitable configuration.

Here I randomly found a configuration file, just to realize the function of custom configuration, please use the appropriate custom configuration file according to your own usage scenarios.

Before using custom configuration, we first need to understand the configuration file structure of the current mysql container.

Use the terminal tool provided by KubeSphere, enter the mysql container, execute the following command, and analyze the execution result (refer to the previous screenshot for the terminal login method).

 # bash
root@mysql-0:/# ls /etc/mysql/ -l
total 8
drwxr-xr-x 2 root root   62 Apr 28 06:20 conf.d
lrwxrwxrwx 1 root root   24 Apr 28 06:20 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root  839 Aug  3  2016 my.cnf.fallback
-rw-r--r-- 1 root root 1200 Mar 22 01:44 mysql.cnf
drwxr-xr-x 2 root root   24 Apr 28 06:20 mysql.conf.d

root@mysql-0:/# ls /etc/mysql/conf.d/ -l
total 12
-rw-r--r-- 1 root root 43 Apr 28 06:20 docker.cnf
-rw-r--r-- 1 root root  8 Aug  3  2016 mysql.cnf
-rw-r--r-- 1 root root 55 Aug  3  2016 mysqldump.cnf

root@mysql-0:/# ls /etc/mysql/mysql.conf.d/ -l
total 4
-rw-r--r-- 1 root root 1589 Apr 28 06:20 mysqld.cnf

root@mysql-0:/# ls -l /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 20 Apr 28 06:20 /etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf

root@mysql-0:/# cat /etc/mysql/mysql.cnf
# Copyright (c) 2016, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

## 挑两个配置文件看看
root@mysql-0:~# cat /etc/mysql/conf.d/docker.cnf
[mysqld]
skip-host-cache
skip-name-resolve

root@mysql-0:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Analyzing the above output we get the following conclusions.

• Root configuration file: /etc/mysql/mysql.cnf
• Customized configuration files can be stored in the /etc/mysql/conf.d/ or /etc/mysql/mysql.conf.d/ directory

• Through the above conclusions, it is found that there are two ways to implement custom configuration files.

  • Directly replace /etc/mysql/mysql.cnf

    It is suitable for more complex scenarios with more personalized configuration, such as 50+ configuration items.

  • Put the customized configuration in the /etc/mysql/conf.d/ or /etc/mysql/mysql.conf.d/ directory. According to the official configuration usage, it is recommended to select /etc/mysql/conf.d/

    Applicable to scenarios with less custom configuration, such as just to enable individual functions, or individual default parameters that do not meet usage requirements

This article adopts the second method, using a separate custom.cnf file to configure the following parameters.

 [mysqld]
#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

Realize ideas.

• In k8s, we can mount files to containers by configuring ConfigMap
• Define a custom mysql configuration file as a ConfigMap
• Mount the ConfigMap to the mysql container

Create a ConfigMap configuration file, Configure -> Configuration Dictionary , click Create .

Create a configuration dictionary - basic information .

• Name: mysql-cnf

Create a configuration dictionary - data settings .

• Click to add data
• Key: custom.cnf
• Value: Paste the configuration parameter above

< , >

After filling in the key-value information, click the check mark to confirm, and finally click Create . After the creation is completed, the configuration dictionary page will be returned.

< , >

Next, mount the custom configuration file to the mysql container.

Application load -> workload -> stateful replica set -> click mysql -> enter detailed configuration page -> more operations - click edit settings .

Edit Settings -> Storage Volume -> Mount Configuration Dictionary or Privacy Dictionary .

< , >

storage volume .

• Select the configuration dictionary: mysql-cnf
• read only
• Mount path: /etc/mysql/conf.d/custom.cnf

• Specify the subpath: custom.cnf

  • This must be written here, otherwise all existing files in the specified directory will be overwritten
  • The bottom layer is subPath
  • For specific operation, please refer to the figure below and pay attention to the details.

• Select a specific key:

  • Key: custom.cnf
  • Path: custom.cnf

< , , >

After entering, click the check mark .

Click the check mark again, click OK , and the mysql container will automatically start rebuilding.

< , >

After the reconstruction is successful, we verify whether the configuration file is successfully mounted.

First look at the configuration of the container group and find that a new storage volume volume-xxxx has been added.

Terminal -> Go inside the container to view.

View configuration file mounts and file contents.

 # bash
root@mysql-0:/# ls /etc/mysql/conf.d/
custom.cnf  docker.cnf  mysql.cnf  mysqldump.cnf

root@mysql-0:/# ls -l /etc/mysql/conf.d/
total 16
-rw-r--r-- 1 root root 463 May 11 11:07 custom.cnf
-rw-r--r-- 1 root root  43 Apr 28 06:20 docker.cnf
-rw-r--r-- 1 root root   8 Aug  3  2016 mysql.cnf
-rw-r--r-- 1 root root  55 Aug  3  2016 mysqldump.cnf

root@mysql-0:/# cat /etc/mysql/conf.d/custom.cnf
[mysqld]
#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

Check whether the configuration parameters take effect.

 root@mysql-0:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connect%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_connect_errors | 1000000 |
| max_connections    | 512     |
+--------------------+---------+
2 rows in set (0.02 sec)

mysql>

The execution result is consistent with our configuration, indicating that the configuration is successful.

Import database data

Mount the database file (SQL) to /docker-entrypoint-initdb.d in the specified directory of the container, and it will be automatically imported when the container is created ( not necessary and not recommended ).

Manage the database remotely with a database management tool ( recommended ).

Summarize

This article introduces in detail the installation and configuration process of KubeSphere graphical deployment on a single node MySQL, how to use KubeSphere's graphical function to create a resource configuration list YAML file, the idea and specific operation process, and then deploy others that cannot find detailed configuration guides on the official website. Services can learn from this approach.

The next article will introduce the basic concepts of GitOps and demonstrate how to use the GitOps concept to deploy MySQL services on native K8S.

This article is published by OpenWrite , a multi-post blog platform!