How to make MySQL work in your GitHub Actions

GitHub Actions: MySQL service is unhealthy - error while starting

I spent too much time trying to make MySQL service work on GitHub Actions and after hours of googling and configuration tests I’d like to share with you my findings.

If you come to this post from a Google search, you may have already read a ton of tutorials that have this code running MySQL inside your workflow:

services:
  mysql:
    image: mysql:5.7
    env:
      MYSQL_DATABASE: test_db
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: rootpassword
    ports:
      - 33306:3306
    options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3

Some may have included additional information on why do you need env inside the MySQL service, some may not. But basically, services have stopped automatically receiving all env values from the job-level scope, and you need to copy-paste them manually.

There might be several variations of the ports value.

  1. Here you will need to use 3306 later in your workflow, but depending on your setup you may have port conflicts.
ports:
  - 3306:3306
  1. Here you redefine the local port to 33306. But in this case, you need to be able to redefine the port everywhere in your code.
ports:
  - 33306:3306
  1. Or even a bit more complicated solution, where later in your workflow steps you need to retrieve the port manually using ${{ job.services.mysql.ports['3306'] }}:
ports:
  - 3306

But in my experience, none of those ways worked and I was always receiving this error:

Failed to initialize, mysql service is unhealthy.

It looks like this:

GitHub Actions: "mysql service is unhealthy" error
Ubuntu can’t connect to the MySQL service

It most likely means that a host (ubuntu-20.04) can’t connect to a separate MySQL container that is created using your services:mysql details due to ports issues.

The easiest way to work with MySQL in GitHub Actions

Ever wondered why there are official PostgreSQL and Redis services guides available for you on docs.github.com, but not for MySQL?

That’s because MySQL 5.7 is pre-installed on Ubuntu 18.04 containers (and MySQL 8 on 20 and latest), but not running by default. Here is a related announcement by GitHub. That means you don’t need services:mysql at all, you just need to start the built-in mysql process.

I haven’t found this information in any official docs, but the default MySQL login and password are both root. Update: they have this information in their repository. Here is an example for ubuntu-20.04.

Below is the configuration code I crafted to make MySQL work for me:

env:
  DB_DATABASE: test_db
  DB_USER: root
  DB_PASSWORD: root
steps:
  - name: Set up MySQL
    run: |
      sudo /etc/init.d/mysql start
      mysql -e 'CREATE DATABASE ${{ env.DB_DATABASE }};' -u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}

The use of env variables is not mandatory, but I re-use those values in one of the workflow steps later so that’s why I put them separately.

Here is what you will see in GitHub interface:

GitHub Actions: MySQL warning when passing root password via cli
Successfully started MySQL

That warning:

Using a password on the command line interface can be insecure.

is valid when you operate with a live/staging database. In my case, I was using MySQL to run tests so it’s not really relevant.

I hope this post and the approach above will help you save hours of googling and debugging various MySQL errors while using Ubuntu environment to run tests or do whatever you need.

By Slava Abakumov

// Be good, have fun, create things.

16 comments

  1. How can i run MYSQL 5.7 ? I tried it with the services:mysql but always MYSQL 8 gets started. If i run without services:mysql it runs but its MYSQL 8 again. I check it via mysql -V.

    1. There are several points here.
      1. You should definitely start using MySQL 8 🙂 This is an opinionated suggestion, I know.
      2. If you need exactly MySQL 5.7 – I’m afraid you need to either use ubuntu-18.04 image specifically, or go the long route: remove MySQL completely, install it again. But this remove-install step will take some time so your test runs or whatever you do via GH Actions will basically start running longer.

      With that said, either consider switching to MySQL 8 or use ubuntu-18.04 which has MySQL 5.7 preinstalled.

      1. OK, that helped me. I work with an CMS that is not 100% fit with MYSQL 8. So i wanted to work with it but i needed to change some modes like NO_ZERO_IN_DATE within MYSQL 8 but didn’t get it done in the workflow. So i decided to do the work on 5.7. Now 5.7 is running and hopefully in future MYSQL 8 will be fully supported. Thanks.

  2. Hi, it is giving error "java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)"

  3. Hi.

    I’m trying to use it like below .

      ## —— Setup MySql 
        
            - name: Set up MySQL
              run: |
                sudo /etc/init.d/mysql start
                mysql -e 'CREATE DATABASE ${{env.DB_DATABASE }};' -u${{env.DB_USER }} -p${{ env.DB_PASSWORD }}
                mysql -e 'SHOW DATABASES;' -uroot -proot
         
     ## run tests
            - name: Run Migration && Load Fixtures
              env:
                APP_ENV: test
                DATABASE_URL : mysql://${DB_USER}:${DB_PASSWORD}@127.0.0.1:3306/${DB_DATABASE}?serverVersion=8
              run: |
                php bin/console doctrine:database:drop --if-exists --force --env=test
                php bin/console doctrine:database:create --if-not-exists --env=test
                php bin/console doctrine:schema:update --env=test --force || echo "No migrations found or schema update failed"
                php bin/console doctrine:migrations:migrate --env=test || echo "No migrations found or migration failed"
                php bin/console doctrine:fixtures:load --no-interaction
    

    It’s not working.
    The db is created .
    but for tests, ‘m not able to touch that db with the URL

  4. I wanna create my database structure with sql queries in single sql file via mysqldump utility. How to put sql file in container with mysql server?

    1. The whole repo is already in the container. Just make sure the dump file is there as well, and use mysqldump tool to import that SQL file directly in the container.

  5. can provide a full example .

    i try to use mysql on github action to run laravel test.

    but always field

    (  SQLSTATE[HY000] [2002] Connection refused (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE') 

    my code :

    name: Build and Test
    on: [push]
    jobs:
      Test:
        runs-on: ubuntu-latest
        services:
          mysql:
            image: mysql:8.0
            env:
              MYSQL_DATABASE: ucan_testing
              MYSQL_USER: user
              MYSQL_PASSWORD: secret
              MYSQL_ROOT_PASSWORD: secretroot
            ports:
              - 3306
            options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
        steps:
          - name: Verify MySQL connection
            run: |
              mysql --version
              sudo apt-get install -y mysql-client
              mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -psecretroot -e "SHOW DATABASES"
          - name: Check out repository code
            uses: actions/checkout@v3
    
          - name: Install Dependencies
            run: composer install -q --no-ansi --no-interaction --no-scripts --no-progress
          - name: Prepare Laravel Application
            run: |
              cp .env.ci .env
              php artisan key:generate
          - name: Execute backend tests (using custom composer script)
            run: |
              composer test
    
  6. I guess the problem lies somewhere in this line:

    mysql --host 127.0.0.1 --port ${{ job.services.mysql.ports['3306'] }} -uroot -psecretroot -e "SHOW DATABASES"
    

    I don’t know why you pass secretroot as a password to check DB connection. In my post I wrote that it should be root.

  7. Hello,
    I’m trying to run a laravel test at github actions using kirschbaumdevelopment/laravel-test-runner:7.3 with mysql:5.7 been trying to execute commands to start mysql but none them works.
    Do I need to change ubuntu-latest to ubuntu-18.04?

    Some of errors:
    Run systemctl start mysql
    /__w/_temp/2f3ea661-bfd4-494b-b309-6853b6626088.sh: 1: /__w/_temp/2f3ea661-bfd4-494b-b309-6853b6626088.sh: systemctl: not found
    Error: Process completed with exit code 127.

    Here are some my .yml lines
    name: Laravel
    on:
    push:
    branches: [master]
    pull_request:
    branches: [master]

    jobs:
    laravel-tests:
    runs-on: ubuntu-latest
    container:
    image: kirschbaumdevelopment/laravel-test-runner:7.3
    services:
    mysql:
    image: mysql:5.7
    env:
    MYSQL_ROOT_PASSWORD: root_password
    MYSQL_DATABASE: mydb_test
    ports:
    – 33306:3306
    options: –health-cmd=”mysqladmin ping” –health-interval=10s –health-timeout=5s –health-retries=3

    steps:
    – uses: actions/checkout@v3
    – name: Start Database
    run: |
    systemctl start mysql

  8. Hi! I am trying to run a workflow for Laravel, changed from SQLITE to MYSQL following your example, and I think I’m almost there but the script which runs the tests does not look for the correct DB username and password I think (or maybe I’m completely off).

    This is the file:

    
    name: Laravel
    on:
      push:
        branches: [ master ]
      pull_request:
        branches: [ master ]
    jobs:
      laravel-tests:
        runs-on: ubuntu-latest
        env:
          DB_DATABASE: test_db
          DB_USER: root
          DB_PASSWORD: root
        steps:
        - uses: shivammathur/setup-php@15c43e89cdef867065b0213be354c2841860869e
          with:
            php-version: '8.0'
        - uses: actions/checkout@v3
        - name: Copy .env
          run: php -r "file_exists('.env') || copy('.env.testing.example', '.env');"
        - name: Install Dependencies
          run: composer install -q --no-ansi --no-interaction --no-scripts --no-progress --prefer-dist
        - name: Generate key
          run: php artisan key:generate
        - name: Directory Permissions
          run: chmod -R 777 storage bootstrap/cache
        - name: Set up MySQL and create database
          run: |
            sudo /etc/init.d/mysql start
            mysql -e 'CREATE DATABASE ${{ env.DB_DATABASE }};' -u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
        - name: Execute tests (Unit and Feature tests) via PHPUnit
          run: vendor/bin/phpunit
    

    And the error I get is:
    Illuminate\Database\QueryException: SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: YES) (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE')

    Do you have any idea how to fix this?

Leave a Reply to valery Cancel reply

Your email address will not be published.