At work we are doing quite a bit with Debian. To make things easier and automated, we can use the non-interactive feature of the Debian packaging system to deploy services. One of the stacks we are enrolling is MySQL Group Replication, which requires at least 3 servers.

Although the MySQL Documentation show how to install MySQL non-interactively, it is not entirely complete. The missing part is the extra question during the installation of the server whether you want to keep using the new default authentention method, or use the (now) legacy native method.

If you are using a MySQL driver which has no support yet for strong password encryption (for example the go-sql-driver/mysql package driver), or you “need” to switch to MySQL 8.0 and keep previous user accounts intact, you will want to set the default back to the mysql_native_plugin for authentication. If you start new, and you can, stick to the new method.

This post will show how to install MySQL 8.0 on a Debian 9 using the official MSQL APT repositories. It also shows the extra step to keep using the legacy MySQL authentencation method.

Bonus: Ansible playbook included!

Unattended Debian Installation

Debian comes with debconf which can be used to do install the whole distribtion non-interactively without a (virtual) monitor attached. You can also do such unattended installs for each application.

Installing a single MySQL server is easy, but once you have done it, the fun starts to fade after a dozen or so times doing it. Especially with Group Replication, where you need a minimal of 3 servers.

There are currently two interactive questions being asked:

Password for the root MySQL user
Re-entry of password for root MySQL
Selection of authentication method

The first two screens are not new; the 3rd is, and it is not documented. Whether that is a documetation bug is open for discussion.

If you are using Go, the new default authentication of MySQL 8.0 does not work yet. You need the “legacy” plugin which can be set globally in the configuration or, more preferably, when creating the database user.

First, Set Up Official MySQL APT Repository

The following is inspired by the MySQL Documentation under A Quick Guide to Using the MySQL APT Repository and Adding and Configuring the MySQL APT Repository Manually.

In the following steps, we do not download the mysql-apt-repo package, instead we do whatever it does, manually:

sudo apt install -y dirmngr
sudo apt-key adv --keyserver pool.sks-keyservers.net --recv-keys 5072E1F5
# Note: key 5072E1F5 is MySQL official's (always double check!)

Next, we add the actualy APT repository. You could use add-apt-repository, but it would mean installing yet again more dependencies. The other way is to simple create the file /etc/apt/sources.list.d/mysql8.list:

echo "deb http://repo.mysql.com/apt/debian $(lsb_release -sc) mysql-8.0" | \
    sudo tee /etc/apt/sources.list.d/mysql80.list
sudo apt update

The above commands can be easily scripted, and no interaction is needed. At the end of the post you have the full playbook in which we do it all using Ansible.

Non-Interactive Installation of MySQL Server 8.0

Interaction is required when you install the MySQL server.

sudo apt update
sudo apt install mysql-server

The above commands will install the server, but require you to:

enter the MySQL root user’s password,
re-enter that same password,
and new: choose the authentication method.

This last step is not documemented (at the time of writing) in the MySQL Documentation. Possible reason, and also a good reason, is that it is good for new installations to use the newer, more secure method.

If you however have systems, applications, or MySQL drivers, which do not support the new method, you can either:

choose the legacy method,
or recreate the users with the ... IDENTIFIED BY 'mysql_native_password' ....

The latter is preferred, but it can be tedious.

When doing an non-interative install, and you want the legacy method, you have to do the following before doing the apt install mysql-server:

sudo debconf-set-selections <<< "mysql-community-server mysql-server/default-auth-override select Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)"

It’s quite a long string, and you have to be careful with the format, your you will have lots of fun debugging:

single space (or tab) between mysql-community-server and mysql-server/...
a single space (or tab) around the word select.

To recap, the following will install the MySQL Server non-interactively, setting the root password to ‘tiger’, and enabling the legacy authentication method:

sudo apt update

sudo debconf-set-selections <<< \
  "mysql-community-server mysql-community-server/root-pass password tiger"

sudo debconf-set-selections <<< \
  "mysql-community-server mysql-community-server/re-root-pass password tiger"

sudo debconf-set-selections <<< \
  "mysql-community-server mysql-server/default-auth-override select Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)"

sudo DEBIAN_FRONTEND=noninteractive apt install mysql-server

You can verify checking the configuration:

cat /etc/mysql/mysql.conf.d/default-auth-override.cnf

If you made a mistake, and you want to start over, don’t forget to purge the debconf database for the MySQL server:

echo PURGE | sudo debconf-communicate mysql-community-server
sudo apt purge mysql-client mysql-server

Full Ansible Playbook

The following playbook can be used with Ansible to automate the installation of MySQL 8.0 on Debian 9:

---
- hosts: mysql_servers
  become: true
  tasks:
  - name: Install APT extras
    apt:
      name: "{{ item }}"
      update_cache: yes
      state: latest
    with_items:
      - dirmngr
      - debconf
    
  - name: Add official MySQL package authentication key
    apt_key:
      keyserver: pool.sks-keyservers.net
      id: 5072E1F5
      state: present

  - name: Add MySQL repository
    apt_repository:
      repo: deb http://repo.mysql.com/apt/debian/ stretch mysql-8.0
      filename: mysql80
      state: present

  - name: Set MySQL root password
    debconf:
      name: mysql-community-server
      question: mysql-community-server/root-pass
      value: "{{ root_password }}"
      vtype: password

  - name: Confirm MySQL root password
    debconf:
      name: mysql-community-server
      question: mysql-community-server/re-root-pass
      value: "{{ root_password }}"
      vtype: password

  - name: Enable Legacy Authentication Method
    debconf:
      name: mysql-community-server
      question: mysql-server/default-auth-override
      value: Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)
      vtype: select

  - name: Install/Update MySQL 8.0 Community Server
    apt:
      name: "{{ item }}"
      update_cache: yes
      state: latest
    with_items:
      - mysql-server
    environment:
      DEBIAN_FRONTEND: noninteractive

A possible inventory file:

[mysql_servers]
192.168.99.100 root_password=tiger