community.mysql.mysql_replication module – Manage MySQL or MariaDB replication
Note
This module is part of the community.mysql collection (version 3.11.0).
You might already have this collection installed if you are using the ansible package.
It is not included in ansible-core.
To check whether it is installed, run ansible-galaxy collection list.
To install it, use: ansible-galaxy collection install community.mysql.
You need further requirements to be able to use this module,
see Requirements for details.
To use it in a playbook, specify: community.mysql.mysql_replication.
Synopsis
Manages MySQL or MariaDB server replication, replica, primary status, get and change primary host.
Requirements
The below requirements are needed on the host that executes this module.
PyMySQL (Python 2.7 and Python 3.x)
Parameters
Parameter |
Comments |
|---|---|
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server. |
|
Name of replication channel. Multi-source replication is supported from MySQL 5.7. Mutually exclusive with connection_name. For more information see https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html. |
|
Whether to validate the server host name when an SSL connection is required. Corresponds to MySQL CLIs Setting this to Requires pymysql >= 0.7.11. Choices:
|
|
The path to a client public key certificate. |
|
The path to the client private key. |
|
Specify a config file from which user and password are to be read. The default config file, The default config file, To prevent the default config file from being read, set config_file to be an empty string. Default: |
|
The connection timeout when connecting to the MySQL server. Default: |
|
Name of the primary connection. Supported from MariaDB 10.0.1. Mutually exclusive with channel. For more information see https://mariadb.com/kb/en/library/multi-source-replication/. |
|
Fails on error when calling mysql. Choices:
|
|
Host running the database. In some cases for local connections the login_unix_socket=/path/to/mysqld/socket, that is usually Default: |
|
The password used to authenticate with. |
|
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used. Default: |
|
The path to a Unix domain socket for local connections. Use this parameter to avoid the |
|
The username used to authenticate with. |
|
Module operating mode. Could be Choices:
|
|
Whether the host uses GTID based replication or not. Same as the Choices:
|
|
Same as the |
|
Time lag behind the primary’s state (in seconds). Same as the Available from MySQL 5.6. For more information see https://dev.mysql.com/doc/refman/8.0/en/replication-delayed.html. |
|
Same as the |
|
Same as the |
|
Same as the |
|
Same as the |
|
Same as the |
|
Same as the When setting it to For details, refer to MySQL encrypted replication documentation. The default is Choices:
|
|
Same as the For details, refer to MySQL encrypted replication documentation. |
|
Same as the For details, refer to MySQL encrypted replication documentation. |
|
Same as the For details, refer to MySQL encrypted replication documentation. |
|
Same as the Specifies a colon-separated list of one or more ciphers permitted by the replica for the replication connection. For details, refer to MySQL encrypted replication documentation. |
|
Same as the For details, refer to MySQL encrypted replication documentation. |
|
Same as mysql variable. Choices:
|
|
Configures the replica to use the MariaDB Global Transaction ID.
To find information about available values see https://mariadb.com/kb/en/library/change-master-to/#master_use_gtid. Available since MariaDB 10.0.2. Choices:
|
|
Same as the |
|
Same as mysql variable. |
|
Same as mysql variable. |
Attributes
Attribute |
Support |
Description |
|---|---|---|
Support: none |
Can run in check_mode and return changed status prediction without modifying target. |
Notes
Note
Compatible with MariaDB or MySQL.
If an empty value for the parameter of string type is needed, use an empty string.
Requires the PyMySQL (Python 2.7 and Python 3.X) package installed on the remote host. The Python package may be installed with apt-get install python-pymysql (Ubuntu; see ansible.builtin.apt) or yum install python2-PyMySQL (RHEL/CentOS/Fedora; see ansible.builtin.yum). You can also use dnf install python2-PyMySQL for newer versions of Fedora; see ansible.builtin.dnf.
Be sure you have PyMySQL library installed on the target machine for the Python interpreter Ansible discovers. For example if ansible discovers and uses Python 3, you need to install the Python 3 version of PyMySQL. If ansible discovers and uses Python 2, you need to install the Python 2 version of PyMySQL.
If you have trouble, it may help to force Ansible to use the Python interpreter you need by specifying
ansible_python_interpreter. For more information, see https://docs.ansible.com/ansible/latest/reference_appendices/interpreter_discovery.html.Both
login_passwordandlogin_userare required when you are passing credentials. If none are present, the module will attempt to read the credentials from~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password.If there are problems with local connections, using login_unix_socket=/path/to/mysqld/socket instead of login_host=localhost might help. As an example, the default MariaDB installation of version 10.4 and later uses the unix_socket authentication plugin by default that without using login_unix_socket=/var/run/mysqld/mysqld.sock (the default path) causes the error ``Host ‘127.0.0.1’ is not allowed to connect to this MariaDB server``.
If credentials from the config file (for example,
/root/.my.cnf) are not needed to connect to a database server, but the file exists and does not contain a[client]section, before any other valid directives, it will be read and this will cause the connection to fail, to prevent this set it to an empty string, (for exampleconfig_file: '').To avoid the
Please explicitly state intended protocolerror, use the login_unix_socket argument, for example,login_unix_socket: /run/mysqld/mysqld.sock.Alternatively, to avoid using login_unix_socket argument on each invocation you can specify the socket path using the `socket` option in your MySQL config file (usually
~/.my.cnf) on the destination host, for examplesocket=/var/lib/mysql/mysql.sock.
See Also
See also
- community.mysql.mysql_info
Gather information about MySQL or MariaDB servers.
- MySQL replication reference
Complete reference of the MySQL replication documentation.
- MySQL encrypted replication reference.
Setting up MySQL replication to use encrypted connection.
- MariaDB replication reference
Complete reference of the MariaDB replication documentation.
Examples
# If you encounter the "Please explicitly state intended protocol" error,
# use the login_unix_socket argument
- name: Stop mysql replica thread
community.mysql.mysql_replication:
mode: stopreplica
login_unix_socket: /run/mysqld/mysqld.sock
- name: Get primary binlog file name and binlog position
community.mysql.mysql_replication:
mode: getprimary
- name: Change primary to primary server 192.0.2.1 and use binary log 'mysql-bin.000009' with position 4578
community.mysql.mysql_replication:
mode: changeprimary
primary_host: 192.0.2.1
primary_log_file: mysql-bin.000009
primary_log_pos: 4578
- name: Change replication source to replica server 192.0.2.1 and use binary log 'mysql-bin.000009' with position 4578
community.mysql.mysql_replication:
mode: changereplication
primary_host: 192.0.2.1
primary_log_file: mysql-bin.000009
primary_log_pos: 4578
- name: Check replica status using port 3308
community.mysql.mysql_replication:
mode: getreplica
login_host: ansible.example.com
login_port: 3308
- name: On MariaDB change primary to use GTID current_pos
community.mysql.mysql_replication:
mode: changeprimary
primary_use_gtid: current_pos
- name: Change primary to use replication delay 3600 seconds
community.mysql.mysql_replication:
mode: changeprimary
primary_host: 192.0.2.1
primary_delay: 3600
- name: Start MariaDB replica with connection name primary-1
community.mysql.mysql_replication:
mode: startreplica
connection_name: primary-1
- name: Stop replication in channel primary-1
community.mysql.mysql_replication:
mode: stopreplica
channel: primary-1
- name: >
Run RESET MASTER command which will delete all existing binary log files
and reset the binary log index file on the primary
community.mysql.mysql_replication:
mode: resetprimary
- name: Run start replica and fail the task on errors
community.mysql.mysql_replication:
mode: startreplica
connection_name: primary-1
fail_on_error: true
- name: Change primary and fail on error (like when replica thread is running)
community.mysql.mysql_replication:
mode: changeprimary
fail_on_error: true
Return Values
Common return values are documented here, the following are the fields unique to this module:
Key |
Description |
|---|---|
List of executed queries which modified DB’s state. Returned: always Sample: |