Streamline MySQL Root Password Setup on Debian: Leveraging debconf-set-selections

Encountering issues with MySQL root password configuration during automated installations on Debian-based systems like Ubuntu is a common frustration. Specifically, attempts to set the root password post-installation, especially within automation frameworks like Ansible, can lead to persistent connection errors and access denials. This article delves into a reliable solution using debconf-set-selections to preconfigure your MySQL root password, ensuring a smooth and unattended installation process.

The Pitfalls of Post-Installation Password Configuration

A typical approach to MySQL server setup involves purging any existing installations and then proceeding with a fresh install. Consider this common sequence for removing and installing MySQL using Ansible:

sudo apt-get --yes --purge remove mysql-server mysql-common mysql-client

- name: MySQL | Install server
  action: apt pkg=$item state=installed
  with_items:
    - mysql-server
  register: last_result
  tags: mysql

- name: MySQL | Install client and libs
  action: apt pkg=$item state=installed
  with_items:
    - mysql-client
    - python-mysqldb
    - php5-mysql
  tags: mysql

Following this installation, one might attempt to set the MySQL root password using Ansible’s mysql_user module:

- name: update mysql root password for all root accounts
  mysql_user: name=root host=$item password=$mysql_root_password
  with_items:
    - $ansible_hostname
    - 127.0.0.1
    - ::1
    - localhost

However, this post-installation password setup frequently results in errors such as:

failed: [your_server_ip] => (item=127.0.0.1) => {"failed": true, "item": "127.0.0.1"} msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

Directly attempting to connect to MySQL using the intended password also fails:

mysql -u root -p

Furthermore, using mysqladmin to set the password yields an “Access denied” error, even immediately after installation when, theoretically, no password should be required for the root user:

sudo mysqladmin -u root password password
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

This perplexing situation arises because the unattended installation process on Debian-based distributions, particularly when DEBIAN_FRONTEND=noninteractive is involved, can interact with the debconf system in unexpected ways, potentially setting an incorrect root password during the installation itself.

The Solution: Preconfiguring with debconf-set-selections

To circumvent these issues, the key is to preconfigure the MySQL root password before the mysql-server package is installed. This is effectively achieved using debconf-set-selections. By feeding the desired root password to debconf prior to installation, you ensure that the MySQL server is set up with the correct password from the outset.

Here’s the Ansible code snippet that resolves the problem:

- name: MySQL | Set debconf vars - root password
  action: raw sudo echo "mysql-server mysql-server/root_password password $mysql_root_password" | sudo /usr/bin/debconf-set-selections
  tags: mysql

- name: MySQL | Set debconf vars - repeat root password
  action: raw sudo echo "mysql-server mysql-server/root_password_again password $mysql_root_password" | sudo /usr/bin/debconf-set-selections
  tags: mysql

These tasks utilize the raw module in Ansible to execute shell commands directly. They pipe echo commands into debconf-set-selections. These commands effectively answer the debconf prompts for the MySQL root password and password confirmation before the apt installation process begins.

By incorporating these debconf-set-selections tasks before the installation of mysql-server, you preemptively configure the root password, eliminating the connection and access issues encountered with post-installation password setups. This ensures a reliable and automated MySQL installation on Debian-based systems, perfectly suited for unattended deployments and infrastructure automation.