Using MySQL

DETAILS: Tier: Free, Premium, Ultimate Offering: SaaS, self-managed

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.