Deploy Database to Automatically Initialize Schema

We use the container method to deploy database components, especially when the company has a large number of project development services and there are many development and test database components deployed. The following problems are often encountered:

  1. The business needs to use a database, but after deploying the database, you need to perform the operation of creating a schema or the creation of some initial data in the database.
  2. To develop and test multiple deployment environments, step 1 needs to be repeated multiple times.
  3. There are many projects, and the database schema required by the project is not clear after a long time.
  4. The database schema management is chaotic when the project is delivered.

Now if it is a business system developed using languages ​​such as Go, all have the ability to automatically initialize and update the Schema at the ORM layer. If this is the case, this article is useless for you. But currently most other development language businesses do not have this ability.

If we manage the database as a service module in a business, we hope that the initialization of the schema can be completed directly after the service is started, and the data service capabilities can be directly provided.

So how to achieve this effect in Kato?

Schema initialization generally has two schemes in traditional mode:

  • Manual import after the database is started; manual operation through the client, there is no degree of automation at all;
  • Connect to the database for initialization when the business service starts, depending on the capabilities of the business server.

It can be seen that these two methods have their own drawbacks, so is there a way to automatically initialize the specified data when the database is started? The answer is yes!

Let’s take MySQL as an example. The official has good support for Docker. First, let’s look at the description of Mysql official mirror on Dockerhub:

When the database container is started for the first time, a new database with the specified name will be created and initialized with the provided environment variables. In addition, it will execute the files with extensions .sh, .sql and .sql.gz found in /docker-entrypoint-initdb.d. The files will be executed in alphabetical order. By default, the SQL file will be imported into the database specified by the MYSQL_DATABASE variable. Therefore, we only need to maintain the SQL required for database initialization on the basis of the Mysql mirroring mechanism. As we mentioned above, the database is also used as an independent service module. We can also manage Sql and other steps through code to divide version branches.

Kato supports a variety of component creation methods. Here we use the method of creating components from source code, write a Dockerfile and upload it to a client that supports the Git/Svn protocol, and then you can build directly on the platform; this method is transparent and reproducible It is used and can be automatically constructed.

Directory Structure

./
└── Dockerfile
    └── config
        ├── my.cnf
        ├── conf.d
             ├── docker.cnf
    └── sql
        ├── init_database
    └── README.md

Dockerfile

#Basic Mirror
FROM mysql:latest
MAINTAINER Aaron <Aaron_ops@163.com>

#Copy the sql file to the /docker-entrypoint-initdb.d/ directory to automatically execute this sql at startup
COPY ./sql/*.sql /docker-entrypoint-initdb.d
#Copy mysql configuration file
COPY ./config/ /etc/mysql/
#MysqlPassword
ENV MYSQL_ROOT_PASSWORD kato
#Data persistence directory
VOLUME ["/var/lib/mysql"]
#port
EXPOSE 3306

Project address: https://github.com/gridworkz/Initialize-db

Using this project to build on the platform, Kato will automatically detect the environment variables, storage, ports and other information defined in the Dockerfile, automatically configure these configuration items, and automatically start the database after the Dockerfile is built.

It should be noted that MySQL is a stateful service, so you need to modify the component type to a stateful single instance before building. For the component status, please refer to Component Deployment Type.

Startup is complete and enter the running state

Enter the database through the Web terminal to check that the relevant data has been initialized

In this way, the database is initialized, no need to modify the program code, no need to use external tools, and quickly deploy the database and complete the initialization through the capabilities of the platform. In the subsequent use process, the service operation can be completed through functions such as performance analysis, resource monitoring, and instance scaling. Timely full life cycle management to improve development efficiency and enhance business stability.

In addition, we can also publish this component to the Kato internal component library. The subsequent development, testing and delivery process can directly install this component with one click to obtain the database service of the initial data completion.

The above MySQL database is only a reference example, and MongoDB, PostgreSQL/Yugabyte and other databases all support the same type of data initialization method.