Ansible role. FreeBSD. Install and configure MySQL.

Feel free to share your feedback and report issues.

Contributions are welcome.

• vbotka.ansible_lib

• ansible.posix
• community.general
• community.mysql

See defaults/main.yml

• See defaults/main.yml and examples in vars/main.yml
• MySQL version less then 5.7 needs the file bsd_mysql_secret_local_file with the root password
• By default the server is disabled bsd_mysql_enable: False
• freebsd_flavors_enable (default: False). This variable enables the flavors stored in freebsd_flavors. By default the flavors are disabled. This means that the default flavors from /etc/make.conf will be installed. Enable this variable only if freebsd_install_method=packages. Ports won't recognize the flavors and the installation will crash. See the variables bsd_mysql_packages in defaults/main.yml.

1. Change shell on the remote host to /bin/sh if necessary

shell> ansible dbserver -e 'ansible_shell_type=csh ansible_shell_executable=/bin/csh' \
       -a 'sudo pw usermod freebsd -s /bin/sh'

2. Install the role and collections

shell> ansible-galaxy role install vbotka.freebsd_mysql

Install the collections if necessary

shell> ansible-galaxy collection install community.general
shell> ansible-galaxy collection install community.mysql
shell> ansible-galaxy collection install ansible.posix

3. Fit variables to your needs.

4. Create playbook and inventory

shell> cat mysql.yml
- hosts: dbserver
  roles:
    - vbotka.freebsd_mysql

shell> cat hosts
[dbserver]
<SERVER1-IP-OR-FQDN>
<SERVER2-IP-OR-FQDN>

[dbserver:vars]
ansible_connection=ssh
ansible_user=freebsd
ansible_become=true
ansible_become_user=root
ansible_become_method=sudo
ansible_python_interpreter=/usr/local/bin/python3.9
ansible_perl_interpreter=/usr/local/bin/perl

5. Install and configure MySQL

In development, test the role step by step

• Create directories and files

By default, the lists bsd_mysql_directories and bsd_mysql_files are empty. As a result the below tasks will be skipped. You might want to create bsd_mysql_mysql_user first if you want to manage any directories or files before the packages are installed

shell> ansible-playbook mysql.yml -t bsd_mysql_directories
shell> ansible-playbook mysql.yml -t bsd_mysql_files

• Test sanity

shell> ansible-playbook mysql.yml -t bsd_mysql_sanity

• Review variables

shell> ansible-playbook mysql.yml -t bsd_mysql_debug -e bsd_mysql_debug=true

• Install packages

shell> ansible-playbook mysql.yml -t bsd_mysql_packages -e bsd_mysql_install=true -e freebsd_flavors_enable=true

• Apply patches

Review patches in the file directory and fit the variable bsd_mysql_patches to your needs. Default v80 is secure initialization (--initialize) and output of mysql_create_auth_tables to console. v80 works also for v81.

shell>  ansible-playbook mysql.yml -t bsd_mysql_patches -e bsd_mysql_patch_backup=true

• Configure mycnf.yml

shell> ansible-playbook freebsd-mysql.yml -t bsd_mysql_mycnf -e bsd_mysql_conf_backup=true

• Configure rc.conf

Review the list of options bsd_mysql_rcconf. Default for version 80 is mysql_args: "--skip-new --log-error". MySQL should be enabled bsd_mysql_enable: true. The service should start, initialize databases and create temporary root password. Optionally see the log /var/db/mysql/${hostname}.err.

shell> ansible-playbook freebsd-mysql.yml -t bsd_mysql_rcconf -e bsd_mysql_conf_backup=true

• Test existence of files that contain temporary root password

This task is tagged never and shall be run on demand only to test the temporary root password

shell> ansible-playbook mysql.yml -t bsd_mysql_assert

If the test fails find the HOWTO sections in the message.

• Change root password

WARNING: Tasks secret.yml are not idempotent !

Run the tasks '-t bsd_mysql_secret' only once to change temporary password for bsd_mysql_login_user (by default root). Run '-t bsd_mysql_assert' first to see the status of the passwords.

Store temporary root password in the local file. Change the root password to bsd_mysql_secret. This task is tagged never and shall be run on demand only to change the temporary root password

shell> ansible-playbook mysql.yml -t bsd_mysql_secret

• Run check mode and show the differences

shell> ansible-playbook mysql.yml -CD

• Execute the playbook

shell> ansible-playbook mysql.yml

• Execute the playbook again. Test the playbook is idempotent

shell> ansible-playbook mysql.yml

When the server is running it's sufficient to select particular tasks by tags to reconfigure any parameter. The role should be idempotent. It's possible to repeatedly run the whole role if necessary.

Enable bsd_mysql_debug_classified. Warning: the passwords will be displayed!

Use the configuration file .ansible-lint.local when running ansible-lint. Some rules might be disabled and some warnings might be ignored. See the notes in the configuration file.

shell> ansible-lint -c .ansible-lint.local

• MySQL Reference Manual
• Installing MySQL on FreeBSD

Vladimir Botka