mysql_replication – Manage MySQL replication

Synopsis

  • Manages MySQL server replication, slave, master status, get and change master host.

Requirements

The below requirements are needed on the host that executes this module.

  • PyMySQL (Python 2.7 and Python 3.X), or
  • MySQLdb (Python 2.x)

Parameters

Parameter Choices/Defaults Comments
ca_cert
path
The path to a Certificate Authority (CA) certificate. This option, if used, must specify the same certificate as used by the server.

aliases: ssl_ca
channel
string
Name of replication channel.
Multi-source replication is supported from MySQL 5.7.
Mutually exclusive with connection_name.
client_cert
path
The path to a client public key certificate.

aliases: ssl_cert
client_key
path
The path to the client private key.

aliases: ssl_key
config_file
path
Default:
"~/.my.cnf"
Specify a config file from which user and password are to be read.
connect_timeout
integer
Default:
30
The connection timeout when connecting to the MySQL server.
connection_name
string
Name of the master connection.
Supported from MariaDB 10.0.1.
Mutually exclusive with channel.
login_host
string
Default:
"localhost"
Host running the database.
login_password
string
The password used to authenticate with.
login_port
integer
Default:
3306
Port of the MySQL server. Requires login_host be defined as other than localhost if login_port is used.
login_unix_socket
string
The path to a Unix domain socket for local connections.
login_user
string
The username used to authenticate with.
master_auto_position
boolean
    Choices:
  • no
  • yes
Whether the host uses GTID based replication or not.
master_connect_retry
integer
Same as mysql variable.
master_delay
integer
Time lag behind the master's state (in seconds).
Available from MySQL 5.6.
master_host
string
Same as mysql variable.
master_log_file
string
Same as mysql variable.
master_log_pos
integer
Same as mysql variable.
master_password
string
Same as mysql variable.
master_port
integer
Same as mysql variable.
master_ssl
boolean
    Choices:
  • no
  • yes
Same as mysql variable.
master_ssl_ca
string
Same as mysql variable.
master_ssl_capath
string
Same as mysql variable.
master_ssl_cert
string
Same as mysql variable.
master_ssl_cipher
string
Same as mysql variable.
master_ssl_key
string
Same as mysql variable.
master_use_gtid
string
    Choices:
  • current_pos
  • slave_pos
  • disabled
Configures the slave to use the MariaDB Global Transaction ID.
disabled equals MASTER_USE_GTID=no command.
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.
master_user
string
Same as mysql variable.
mode
string
    Choices:
  • changemaster
  • getmaster
  • getslave ←
  • startslave
  • stopslave
  • resetmaster
  • resetslave
  • resetslaveall
Module operating mode. Could be changemaster (CHANGE MASTER TO), getmaster (SHOW MASTER STATUS), getslave (SHOW SLAVE STATUS), startslave (START SLAVE), stopslave (STOP SLAVE), resetmaster (RESET MASTER) - supported from Ansible 2.10, resetslave (RESET SLAVE), resetslaveall (RESET SLAVE ALL).
relay_log_file
string
Same as mysql variable.
relay_log_pos
integer
Same as mysql variable.

Notes

Note

  • Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) package on the remote host. The Python package may be installed with apt-get install python-pymysql (Ubuntu; see apt) or yum install python2-PyMySQL (RHEL/CentOS/Fedora; see yum). You can also use dnf install python2-PyMySQL for newer versions of Fedora; see dnf.
  • Both login_password and login_user are 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.

See Also

See also

mysql_info – Gather information about MySQL servers
The official documentation on the mysql_info module.
MySQL replication reference
Complete reference of the MySQL replication documentation.
MariaDB replication reference
Complete reference of the MariaDB replication documentation.

Examples

- name: Stop mysql slave thread
  mysql_replication:
    mode: stopslave

- name: Get master binlog file name and binlog position
  mysql_replication:
    mode: getmaster

- name: Change master to master server 192.0.2.1 and use binary log 'mysql-bin.000009' with position 4578
  mysql_replication:
    mode: changemaster
    master_host: 192.0.2.1
    master_log_file: mysql-bin.000009
    master_log_pos: 4578

- name: Check slave status using port 3308
  mysql_replication:
    mode: getslave
    login_host: ansible.example.com
    login_port: 3308

- name: On MariaDB change master to use GTID current_pos
  mysql_replication:
    mode: changemaster
    master_use_gtid: current_pos

- name: Change master to use replication delay 3600 seconds
  mysql_replication:
    mode: changemaster
    master_host: 192.0.2.1
    master_delay: 3600

- name: Start MariaDB standby with connection name master-1
  mysql_replication:
    mode: startslave
    connection_name: master-1

- name: Stop replication in channel master-1
  mysql_replication:
    mode: stopslave
    channel: master-1

- name: >
    Run RESET MASTER command which will delete all existing binary log files
    and reset the binary log index file on the master
  mysql_replication:
    mode: resetmaster

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
queries
list
added in 2.10
always
List of executed queries which modified DB's state.

Sample:
["CHANGE MASTER TO MASTER_HOST='master2.example.com',MASTER_PORT=3306"]


Status

Authors

  • Balazs Pocze (@banyek)
  • Andrew Klychkov (@Andersson007)

Hint

If you notice any issues in this documentation, you can edit this document to improve it.