mysqlvariablesansiblemysql-variables

Is there a way for Ansible mysql_variables module to set variable persistent?

I have an Ansible playbook with this task..

---
- name: Set mysql_variable
  mysql_variables:
    variable: mysql_variable
    value: 2000
- name: Reload MySQL
  service:
    name: mysql
    state: restarted
    enabled: yes

the 1st task sets the variable to desired state, I can verify.. but it's not persistent. When 2nd task reloads mysql service, the variable gets set to default value.

How to set this variable persistent with Ansible on the target node??

Solution

  • The community.mysql.mysql_variables module supports this with mode: persist, assuming you have MySQL 8.0+ and are setting a dynamic variable:

    - name: Configure a MySQL dynamic setting
  community.mysql.mysql_variables:
    variable: "binlog_expire_logs_seconds"
    value: "{{ '1w' | community.general.to_seconds | int }}"
    mode: persist
  become: true

    The equivalent SET PERSIST statement:

    $ sudo mysql --execute "SET persist binlog_expire_logs_seconds = 604800;"

    Show any variables persisted to mysqld-auto.cnf (reference):

    $ sudo mysql --execute "SELECT * FROM performance_schema.persisted_variables;"
+---------------------------------+----------------+
| VARIABLE_NAME                   | VARIABLE_VALUE |
+---------------------------------+----------------+
| binlog_expire_logs_seconds      | 604800         |
| max_allowed_packet              | 268435456      |
+---------------------------------+----------------+

    Caveats