community / community.mysql / 3.9.0 / module / mysql_info Gather information about MySQL servers Authors: Andrew Klychkov (@Andersson007), Sebastian Gumprich (@rndmh3ro), Laurent Indermühle (@laurent-indermuehle)community.mysql.mysql_info (3.9.0) — module
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections: - name: community.mysql version: 3.9.0
Gathers 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