Jenkins pipeline uses mysql database

background

In some cases, we may need Jenkins to connect to the database to do some persistent operations on the data

Environment to prepare

The mysql database

In the past, mysql database is installed on the Internet to find the first installation tutorial, messing around for a long time to install. Later, I found it easy to install mysql through docker, so I installed mysql through docker this time.

The installation

  1. Install the docker

    sudo yum install -y yum-utils device-mapper-persistent-data lvm2 sudo yum-config-manager --add-repo https://mirrors.ustc.edu.cn/docker-ce/linux/centos/docker-ce.repo sudo yum-config-manager --enable docker-ce-test sudo Yum -config-manager --disable docker-ce-edge sudo yum makecache fast sudo yum install docker-ce-18.09.9 Groupadd docker # Add the current user to the docker group sudo Yum remove docker-ce usermod -ag docker $USER # yum remove docker-ceCopy the code

  2. Install the docker compose

    Although it is possible to define a Docker container through Dockerfile, it is not very convenient if we have multiple containers that need to be used together, so docker compose is used here. Docker compose is a tool that can compose a cluster of docker containers in the same way that Dockerfile defines docker containers.

    Docker Compse installation:

    # Install -- binary package. Curl curl curl curl curl curl curl curl curl curl https://github.com/docker/compose/releases curl -L https://github.com/docker/compose/releases/download/1.25.4/docker-compose- ` ` uname - s - ` uname -m ` - o /usr/local/bin/docker-compose /usr/local/bin/docker-compose /usr/local/bin/docker-compose /usr/local/bin/docker-compose -s /usr/local/bin/docker-compose /usr/bin/docker-compose sudo ln -s /usr/local/bin/docker-composeCopy the code

  3. Prepare the docker-comemage. yml configuration file configured with the myslq container

    Version: '3.7' services: mysql: container_name: mydb image: docker. IO /mysql:5.7 command: --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --explicit_defaults_for_timestamp=true --lower_case_table_names=1 --max_allowed_packet=128M --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISI ON_BY_ZERO" ports: - "3306:3306" volumes: - ./data:/var/lib/mysql environment: MYSQL_DATABASE: devops MYSQL_ROOT_PASSWORD: root MYSQL_ROOT_HOST: '%' restart: alwaysCopy the code

  4. Start mysql container

    To start the container, run the following command in the path of the docker-comemage. yml file.

    Ps: the sample we will use the docker – compose. Yml files in the/home/docker/enterprise/compose. Self-modification

    CD/home/docker/enterprise/compose # check if yml file configured properly, if the docker - compose. Yml has a mistake, Docker-compose config # docker-compose up -d docker-compose ps # docker-compose Docker exec-ti mydb docker exec-ti mydb docker exec-ti mydb docker exec-ti mydb docker exec-ti mydb shCopy the code

use

Since we have exposed port 3306 in docker-compse.yml, we can access mysql through host IP + port. Example: JDBC :mysql:// IP :3306/ database name

Jenkins aspects

Although Jenkins pipelined scripts are written in Groovy and groovy can use a database, if you use a database directly from Groovy in a pipelined script, you will face problems with the database JDBC driver not loading. A search revealed that Jenkins already had a database support Plugin: database and MySQL Database Plugin

The installation

  1. Install the database and MySQL Database Plugin on the Jenkins Plugin management page
  2. Configure the database connection on the Jenkins System Settings screen

use

Once the plug-in is installed, you can connect to the database using two methods: getDatabaseConnection and SQL.

Separate SQL statements from parameters to avoid SQL injection. If you don’t mind, write parameters directly to SQL statements

getDatabaseConnection(type: 'GLOBAL') {
            def sqlString="select id from test_table where  job_name= ? and build_number= ?"
            def params=['test',11]
            sql sql:sqlString,parameters:params
        }
Copy the code

The number of connections to the database is set to 8. If there are too many connections to the database, it will wait forever. Then when an error is reported, there will be some problems in connection recovery, which may eventually lead to the situation that the connection cannot be obtained. Later, the solution was changed to directly put the jar package of mysql driver into Jenkins’ lib directory and call the driver to connect to the database.