Using MySQL

DETAILS: Tier: Free, Premium, Ultimate Offering: GitLab.com, Self-managed, GitLab Dedicated

Many applications depend on MySQL as their database, and you may need it for your tests to run.

Use MySQL with the Docker executor

If you want to use a MySQL container, you can use GitLab Runner with the Docker executor.

This example shows you how to set a username and password that GitLab uses to access the MySQL container. If you do not set a username and password, you must use root.

NOTE: Variables set in the GitLab UI are not passed down to the service containers. For more information, see GitLab CI/CD variables.

To specify a MySQL image, add the following to your .gitlab-ci.yml file:
```
services:
  - mysql:latest
```
- You can use any Docker image available on Docker Hub. For example, to use MySQL 5.5, use mysql:5.5.
- The mysql image can accept environment variables. For more information, view the Docker Hub documentation.
To include the database name and password, add the following to your .gitlab-ci.yml file:
```
variables:
  # Configure mysql environment variables (https://hub.docker.com/_/mysql/)
  MYSQL_DATABASE: $MYSQL_DATABASE
  MYSQL_ROOT_PASSWORD: $MYSQL_ROOT_PASSWORD
```
The MySQL container uses MYSQL_DATABASE and MYSQL_ROOT_PASSWORD to connect to the database. Pass these values by using variables ($MYSQL_DB and $MYSQL_PASS), rather than calling them directly.
Configure your application to use the database, for example:
```
Host: mysql
User: runner
Password: <your_mysql_password>
Database: <your_mysql_database>
```
In this example, the user is runner. You should use a user that has permission to access your database.

Use MySQL with the Shell executor

You can also use MySQL on manually-configured servers that use GitLab Runner with the Shell executor.

Install the MySQL server:

sudo apt-get install -y mysql-server mysql-client libmysqlclient-dev

Choose a MySQL root password and type it twice when asked.

NOTE: As a security measure, you can run mysql_secure_installation to remove anonymous users, drop the test database, and disable remote logins by the root user.
Create a user by logging in to MySQL as root:
```
mysql -u root -p
```
Create a user (in this case, runner) that is used by your application. Change $password in the command to a strong password.

At the mysql> prompt, type:
```
CREATE USER 'runner'@'localhost' IDENTIFIED BY '$password';
```

Create the database:

CREATE DATABASE IF NOT EXISTS `<your_mysql_database>` DEFAULT CHARACTER SET `utf8` \
COLLATE `utf8_unicode_ci`;

Grant the necessary permissions on the database:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, DROP, INDEX, ALTER, LOCK TABLES ON `<your_mysql_database>`.* TO 'runner'@'localhost';

If all went well, you can quit the database session:
```
\q
```
Connect to the newly-created database to check that everything is in place:
```
mysql -u runner -p -D <your_mysql_database>
```

Configure your application to use the database, for example:

Host: localhost
User: runner
Password: $password
Database: <your_mysql_database>

Example project

To view a MySQL example, create a fork of this sample project. This project uses publicly-available instance runners on GitLab.com. Update the README.md file, commit your changes, and view the CI/CD pipeline to see it in action.