Try SpotterGather information about MySQL servers
Authors: Andrew Klychkov (@Andersson007), Sebastian Gumprich (@rndmh3ro), Laurent Indermühle (@laurent-indermuehle)
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections:
- name: community.mysql
version: 3.9.0Gathers information about MySQL servers.
# Display info from mysql-hosts group (using creds from ~/.my.cnf to connect):
# ansible mysql-hosts -m mysql_info
# Display only databases and users info:
# ansible mysql-hosts -m mysql_info -a 'filter=databases,users'
# Display all users privileges:
# ansible mysql-hosts -m mysql_info -a 'filter=users_info'
# Display only slave status:
# ansible standby -m mysql_info -a 'filter=slave_status'
# Display all info from databases group except settings:
# ansible databases -m mysql_info -a 'filter=!settings'
# If you encounter the "Please explicitly state intended protocol" error,
# use the login_unix_socket argument
- name: Collect all possible information using passwordless root access
community.mysql.mysql_info:
login_user: root
login_unix_socket: /run/mysqld/mysqld.sock
- name: Get MySQL version with non-default credentials
community.mysql.mysql_info:
login_user: mysuperuser
login_password: mysuperpass
filter: version
- name: Collect all info except settings and users by root
community.mysql.mysql_info:
login_user: root
login_password: rootpass
filter: "!settings,!users"
- name: Collect info about databases and version using ~/.my.cnf as a credential file
become: true
community.mysql.mysql_info:
filter:
- databases
- version
- name: Collect info about databases and version using ~alice/.my.cnf as a credential file
become: true
community.mysql.mysql_info:
config_file: /home/alice/.my.cnf
filter:
- databases
- version
- name: Collect info about databases including empty and excluding their sizes
become: true
community.mysql.mysql_info:
config_file: /home/alice/.my.cnf
filter:
- databases
exclude_fields: db_size
return_empty_dbs: true
- name: Clone users from one server to another
block:
# Step 1
- name: Fetch information from a source server
delegate_to: server_source
community.mysql.mysql_info:
filter:
- users_info
register: result
# Step 2
# Don't work with sha256_password and cache_sha2_password
- name: Clone users fetched in a previous task to a target server
community.mysql.mysql_user:
name: "{{ item.name }}"
host: "{{ item.host }}"
plugin: "{{ item.plugin | default(omit) }}"
plugin_auth_string: "{{ item.plugin_auth_string | default(omit) }}"
plugin_hash_string: "{{ item.plugin_hash_string | default(omit) }}"
tls_require: "{{ item.tls_require | default(omit) }}"
priv: "{{ item.priv | default(omit) }}"
resource_limits: "{{ item.resource_limits | default(omit) }}"
column_case_sensitive: true
state: present
loop: "{{ result.users_info }}"
loop_control:
label: "{{ item.name }}@{{ item.host }}"
when:
- item.name != 'root' # In case you don't want to import admin accounts
- item.name != 'mariadb.sys'
- item.name != 'mysql'
filter:
description:
- Limit the collected information by comma separated string or YAML list.
- Allowable values are C(version), C(databases), C(settings), C(global_status), C(users),
C(users_info), C(engines), C(master_status), C(slave_status), C(slave_hosts).
- By default, collects all subsets.
- You can use '!' before value (for example, C(!settings)) to exclude it from the
information.
- If you pass including and excluding values to the filter, for example, I(filter=!settings,version),
the excluding values, C(!settings) in this case, will be ignored.
elements: str
type: list
ca_cert:
aliases:
- ssl_ca
description:
- The path to a Certificate Authority (CA) certificate. This option, if used, must
specify the same certificate as used by the server.
type: path
login_db:
description:
- Database name to connect to.
- It makes sense if I(login_user) is allowed to connect to a specific database only.
type: str
client_key:
aliases:
- ssl_key
description:
- The path to the client private key.
type: path
login_host:
default: localhost
description:
- Host running the database.
- In some cases for local connections the I(login_unix_socket=/path/to/mysqld/socket),
that is usually C(/var/run/mysqld/mysqld.sock), needs to be used instead of I(login_host=localhost).
type: str
login_port:
default: 3306
description:
- Port of the MySQL server. Requires I(login_host) be defined as other than localhost
if login_port is used.
type: int
login_user:
description:
- The username used to authenticate with.
type: str
client_cert:
aliases:
- ssl_cert
description:
- The path to a client public key certificate.
type: path
config_file:
default: ~/.my.cnf
description:
- Specify a config file from which user and password are to be read.
- The default config file, C(~/.my.cnf), if it exists, will be read, even if I(config_file)
is not specified.
- The default config file, C(~/.my.cnf), if it exists, must contain a C([client])
section as a MySQL connector requirement.
- To prevent the default config file from being read, set I(config_file) to be an
empty string.
type: path
check_hostname:
description:
- Whether to validate the server host name when an SSL connection is required. Corresponds
to MySQL CLIs C(--ssl) switch.
- Setting this to C(false) disables hostname verification. Use with caution.
- Requires pymysql >= 0.7.11.
- This option has no effect on MySQLdb.
type: bool
version_added: 1.1.0
version_added_collection: community.mysql
exclude_fields:
description:
- List of fields which are not needed to collect.
- 'Supports elements: C(db_size). Unsupported elements will be ignored.'
elements: str
type: list
version_added: 0.1.0
version_added_collection: community.mysql
login_password:
description:
- The password used to authenticate with.
type: str
connect_timeout:
default: 30
description:
- The connection timeout when connecting to the MySQL server.
type: int
return_empty_dbs:
default: false
description:
- Includes names of empty databases to returned dictionary.
type: bool
login_unix_socket:
description:
- The path to a Unix domain socket for local connections.
- Use this parameter to avoid the C(Please explicitly state intended protocol) error.
type: str
connector_name:
description: Name of the python connector used by the module. When the connector
is not identified, returns C(Unknown).
returned: always
sample:
- pymysql
- MySQLdb
type: str
version_added: 3.6.0
version_added_collection: community.mysql
connector_version:
description: Version of the python connector used by the module. When the connector
is not identified, returns C(Unknown).
returned: always
sample:
- 1.0.2
type: str
version_added: 3.6.0
version_added_collection: community.mysql
databases:
contains:
size:
description: Database size in bytes.
returned: if not excluded by filter
sample:
size: 656594
type: dict
description: Information about databases.
returned: if not excluded by filter
sample:
- information_schema:
size: 73728
mysql:
size: 656594
type: dict
engines:
description: Information about the server's storage engines.
returned: if not excluded by filter
sample:
- CSV:
Comment: CSV storage engine
Savepoints: 'NO'
Support: 'YES'
Transactions: 'NO'
XA: 'NO'
type: dict
global_status:
description: Global status information.
returned: if not excluded by filter
sample:
- Innodb_buffer_pool_read_requests: 123
Innodb_buffer_pool_reads: 32
type: dict
master_status:
description: Master status information.
returned: if master
sample:
- Binlog_Do_DB: ''
Binlog_Ignore_DB: mysql
File: mysql-bin.000001
Position: 769
type: dict
settings:
description: Global settings (variables) information.
returned: if not excluded by filter
sample:
- innodb_open_files: 300
innodb_page_size": 16384
type: dict
slave_hosts:
description: Slave status information.
returned: if master
sample:
- '2':
Host: ''
Master_id: 1
Port: 3306
type: dict
slave_status:
description: Slave status information.
returned: if standby
sample:
- 192.168.1.101:
'3306':
replication_user:
Connect_Retry: 60
Exec_Master_Log_Pos: 769
Last_Errno: 0
type: dict
users:
description: Return a dictionnary of users grouped by host and with global privileges
only.
returned: if not excluded by filter
sample:
- localhost:
root:
Alter_priv: Y
Alter_routine_priv: Y
type: dict
users_info:
description:
- Information about users accounts.
- The output can be used as an input of the M(community.mysql.mysql_user) plugin.
- Useful when migrating accounts to another server or to create an inventory.
- Does not support proxy privileges. If an account has proxy privileges, they won't
appear in the output.
- Causes issues with authentications plugins C(sha256_password) and C(caching_sha2_password).
If the output is fed to M(community.mysql.mysql_user), the ``plugin_auth_string``
will most likely be unreadable due to non-binary characters.
returned: if not excluded by filter
sample:
- host: host.com
name: user1
plugin: mysql_native_password
plugin_auth_string: '*1234567'
priv: db1.*:SELECT/db2.*:SELECT
resource_limits:
MAX_USER_CONNECTIONS: 100
type: dict
version_added: 3.8.0
version_added_collection: community.mysql
version:
contains:
full:
description: Full server version.
returned: if not excluded by filter
sample: 5.5.60-MariaDB
type: str
major:
description: Major server version.
returned: if not excluded by filter
sample: 5
type: int
minor:
description: Minor server version.
returned: if not excluded by filter
sample: 5
type: int
release:
description: Release server version.
returned: if not excluded by filter
sample: 60
type: int
suffix:
description: Server suffix, for example MySQL, MariaDB, other or none.
returned: if not excluded by filter
sample: MariaDB
type: str
description: Database server version.
returned: if not excluded by filter
sample:
version:
full: 5.5.60-MariaDB
major: 5
minor: 5
release: 60
suffix: MariaDB
type: dict