VhkddlmZmZmZeZdZdZdZddl m Z ddl m Z ddl mZddlmZmZmZmZmZmZmZmZdd lmZmZmZmZmZdd lmZdd l m!Z!Gd d e"Z#dZ$e%dk(re$yy))absolute_importdivisionprint_functionu --- module: mysql_info short_description: Gather information about MySQL or MariaDB servers description: - Gathers information about MySQL or MariaDB servers. options: 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. type: list elements: str 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 exclude_fields: description: - List of fields which are not needed to collect. - "Supports elements: C(db_size), C(db_table_count). Unsupported elements will be ignored." type: list elements: str version_added: '0.1.0' return_empty_dbs: description: - Includes names of empty databases to returned dictionary. type: bool default: false notes: - Compatible with MariaDB or MySQL. - Calculating the size of a database might be slow, depending on the number and size of tables in it. To avoid this, use I(exclude_fields=db_size). - filters C(users_info) doesn't support MariaDB roles. attributes: check_mode: support: full seealso: - module: community.mysql.mysql_variables - module: community.mysql.mysql_db - module: community.mysql.mysql_user - module: community.mysql.mysql_replication author: - Andrew Klychkov (@Andersson007) - Sebastian Gumprich (@rndmh3ro) - Laurent Indermühle (@laurent-indermuehle) extends_documentation_fragment: - community.mysql.mysql a # 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_requires: "{{ item.tls_requires | default(omit) }}" priv: "{{ item.priv | default(omit) }}" resource_limits: "{{ item.resource_limits | default(omit) }}" locked: "{{ item.locked | 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' - item.name != 'PUBLIC' # MariaDB roles are not supported a server_engine: description: Database server engine. returned: if not excluded by filter type: str sample: 'MariaDB' version_added: '3.10.0' version: description: Database server version. returned: if not excluded by filter type: dict sample: { "version": { "major": 5, "minor": 5, "release": 60, "suffix": "MariaDB", "full": "5.5.60-MariaDB" } } contains: major: description: Major server version. returned: if not excluded by filter type: int sample: 5 minor: description: Minor server version. returned: if not excluded by filter type: int sample: 5 release: description: Release server version. returned: if not excluded by filter type: int sample: 60 suffix: description: Server suffix, for example MySQL, MariaDB, other or none. returned: if not excluded by filter type: str sample: "MariaDB" full: description: Full server version. returned: if not excluded by filter type: str sample: "5.5.60-MariaDB" databases: description: Information about databases. returned: if not excluded by filter type: dict sample: - { "mysql": { "size": 656594, "tables": 31 }, "information_schema": { "size": 73728, "tables": 79 } } contains: size: description: Database size in bytes. returned: if not excluded by filter type: dict sample: { 'size': 656594 } tables: description: Count of tables and views in that database. returned: if not excluded by filter type: dict sample: { 'tables': 12 } version_added: '3.11.0' settings: description: Global settings (variables) information. returned: if not excluded by filter type: dict sample: - { "innodb_open_files": 300, innodb_page_size": 16384 } global_status: description: Global status information. returned: if not excluded by filter type: dict sample: - { "Innodb_buffer_pool_read_requests": 123, "Innodb_buffer_pool_reads": 32 } users: description: Return a dictionnary of users grouped by host and with global privileges only. returned: if not excluded by filter type: dict sample: - { "localhost": { "root": { "Alter_priv": "Y", "Alter_routine_priv": "Y" } } } 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. - The "locked" field was aded in ``community.mysql`` 3.13. returned: if not excluded by filter type: dict sample: - { "plugin_auth_string": '*1234567', "name": "user1", "host": "host.com", "plugin": "mysql_native_password", "priv": "db1.*:SELECT/db2.*:SELECT", "resource_limits": { "MAX_USER_CONNECTIONS": 100 }, "tls_requires": { "SSL": null }, "locked": false } version_added: '3.8.0' engines: description: Information about the server's storage engines. returned: if not excluded by filter type: dict sample: - { "CSV": { "Comment": "CSV storage engine", "Savepoints": "NO", "Support": "YES", "Transactions": "NO", "XA": "NO" } } master_status: description: Master status information. returned: if master type: dict sample: - { "Binlog_Do_DB": "", "Binlog_Ignore_DB": "mysql", "File": "mysql-bin.000001", "Position": 769 } slave_status: description: Slave status information. returned: if standby type: dict sample: - { "192.168.1.101": { "3306": { "replication_user": { "Connect_Retry": 60, "Exec_Master_Log_Pos": 769, "Last_Errno": 0 } } } } slave_hosts: description: Slave status information. returned: if master type: dict sample: - { "2": { "Host": "", "Master_id": 1, "Port": 3306 } } connector_name: description: Name of the python connector used by the module. When the connector is not identified, returns C(Unknown). returned: always type: str sample: - "pymysql" version_added: '3.6.0' connector_version: description: Version of the python connector used by the module. When the connector is not identified, returns C(Unknown). returned: always type: str sample: - "1.0.2" version_added: '3.6.0' )Decimal) AnsibleModule)CommandResolver) mysql_connectmysql_common_argument_spec mysql_drivermysql_driver_fail_msgget_connector_nameget_connector_versionget_server_implementationget_server_version)privileges_getget_resource_limitsget_existing_authenticationget_user_implementationuser_is_locked) iteritems) to_nativecfeZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Zd ZdZddZy) MySQL_InfoatClass for collection MySQL instance information. Arguments: module (AnsibleModule): Object of AnsibleModule class. cursor (pymysql/mysql-python): Cursor class for interaction with the database. Note: If you need to add a new subset: 1. add a new key with the same name to self.info attr in self.__init__() 2. add a new private method to get the information 3. add invocation of the new method to self.__collect() 4. add info about the new subset to the DOCUMENTATION block 5. add info about the new subset with an example to RETURN block c ||_||_||_||_||_t |j|j|_iiiiiiiiiid |_y)N) version databasessettings global_statusenginesusers users_info master_status slave_hosts slave_status)modulecursorserver_implementationserver_versionuser_implementationrcommand_resolverinfo)selfr%r&r'r(r)s n/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/mysql/plugins/modules/mysql_info.py__init__zMySQL_Info.__init__csj  %:",#6 /0J0JDL_L_ `   cg}g}|r7i}|D]x}|jd|jvr|jjd|z?|ddk(r!|j |jdh|j |z|rF|j ||t ||jD]}||vs|j|||<|Stt |jt |z } |j ||t | |jD]}||vs|j|||<|S|j ||t |j|jS)zGet MySQL instance information based on filter_. Arguments: filter_ (list): List of collected subsets (e.g., databases, users, etc.), when it is empty, return all available information. !z,filter element: %s is not allowable, ignoredr)lstripr+r%warnappend_MySQL_Info__collectsetlist) r,filter_exclude_fieldsreturn_empty_dbsinc_listexc_list partial_infofiinot_in_exc_lists r-get_infozMySQL_Info.get_infows[ L (99S>2KK$$%SVX%XYa5C<OOBIIcN3OOB' (~/?XO7AH}*.))A, Q7 #'s499~H 'E"F~/?_AUV7A(*.))A, Q7  NN>+;S^ L99 r/czd|vsd|vr|jd|vr|j||d|vr|jd|vr|jd|vr|j d|vr|j d|vr|j d |vr|jd |vr|jy y ) zCollect all possible subsets.rrrrrr r!r"r$r#N) !_MySQL_Info__get_global_variables_MySQL_Info__get_databases_MySQL_Info__get_global_status_MySQL_Info__get_engines_MySQL_Info__get_users_MySQL_Info__get_users_info_MySQL_Info__get_master_status_MySQL_Info__get_slave_status_MySQL_Info__get_slaves)r,r9r:wanteds r- __collectzMySQL_Info.__collects  *"6  ' ' ) &  1A B f $  $ $ &      f      6 !  ! ! # f $  $ $ & V #  # # % F "     #r/c|jd}|rM|D]G}|d}i|jd|<t|D] \}}|dk7s ||jd||<"Iyy)zGet storage engines info.z SHOW ENGINESEnginerN)_MySQL_Info__exec_sqlr+r)r,reslineenginevnamevals r- __get_engineszMySQL_Info.__get_enginess~oon-  Bh/1 )$V,"+D/BJE3(>A ),V4U;B  B r/c t|tr t|}|St|} |S#t$rY|St $rY|SwxYw)zConvert unserializable data.) isinstancerfloatint ValueError TypeError)r,rUs r- __convertzMySQL_Info.__converts` #w'Cj #h         s- - AAAc 4|jd}|r|D])}|j|d|jd|d<+|jddjd}|jdd}|djdd }t |djdd kDr|djdd d }nd }t t |d t |d t |t|t| |jd<y y )z)Get global variables (instance settings).zSHOW GLOBAL VARIABLESValuer Variable_namer.-r)majorminorreleasesuffixfullN)rP_MySQL_Info__convertr+splitlendictrZstr)r,rQvarrrjrhris r-__get_global_variablesz!MySQL_Info.__get_global_variablessoo56  [>BnnSQX\>Z *%c/&:; [ii +I6<>RUV]R^C_ /*3+?@ ` r/c|jjd}|j|}|r<|D]6}t|D]&\}}|j ||j d|<(8yy)z.Get master status if the instance is a master.zSHOW MASTER STATUSr"N)r*resolve_commandrPrrkr+)r,queryrQrRrTrUs r-__get_master_statuszMySQL_Info.__get_master_status su%%556JKooe$  L"+D/LJE38<s8KDIIo.u5L L r/c|jjd}|j|}|r|D]}|d}||jdvri|jd|<|d}||jd|vri|jd||<|d}||jd||vri|jd|||<t |D]4\}}|dvs |j ||jd||||<6yy)z,Get slave status if the instance is a slave.zSHOW SLAVE STATUS Master_Hostr$ Master_Port Master_User)ryrzr{Nr*rurPr+rrk) r,rvrQrRhostportuserrTrUs r-__get_slave_statuszMySQL_Info.__get_slave_statuss6%%556IJooe$  aM*tyy8868DIIn-d3M*tyy8>><>DIIn-d3D9M*tyy8>tDDBDDIIn-d3D9$?"+D/aJE3$QQMQ^^\_M` .1$7=dCEJa a r/c:|jjd}|j|}|rm|D]g}|d}||jdvri|jd|<t |D]/\}}|dk7s |j ||jd||<1iyy)z1Get slave hosts info if the instance is a master.zSHOW SLAVE HOSTS Server_idr#Nr|)r,rvrQrRsrv_idrTrUs r- __get_slaveszMySQL_Info.__get_slaves(s%%556HIooe$  Vk*=!9979DIIm,V4"+D/VJE3 +BF..QTBU -08?V  V r/c<|jd}|r|D]}|d}||jdvri|jd|<|d}i|jd||<t|D]1\}}|dvs |j||jd|||<3yy)zGet user info.SELECT * FROM mysql.userHostr User)rrN)rPr+rrk)r,rQrRr}rrTrUs r- __get_userszMySQL_Info.__get_users6soo89  TF|tyy11/1DIIg&t,F|13 '"4(."+D/TJE3$44@Ds@S '*406u=T T r/c |jd}|syt}|D]$}|d}|d}|jdk(xr| }t|j|||}|s"|j j d|d|_t}|jD]l\} } t| d d hk(st| d hk(r&| jd d jd d } |j| ddj| nt|dkDrd|vr|jdt|j||} tj!| } |j"j%|j||}||dj|| |d}| r8| jD]\}}|dk(s |d|=t|ddk(r|d=|s|d=t'|j||}|r|j)|d|j+dr"|ddk(rt-|j|||d<|j|'||j.d<y)aMGet user privileges, passwords, resources_limits, ... Query the server to get all the users and return a string of privileges that can be used by the mysql_user plugin. For instance: "users_info": [ { "host": "users_info.com", "priv": "*.*: ALL,GRANT", "name": "users_info_adm" }, { "host": "users_info.com", "priv": "`mysql`.*: SELECT/`users_info_db`.*: SELECT", "name": "users_info_multi" } ] rNrrmariadb) maria_rolezNo privileges found for z on host PROXYGRANT`re':,rdz *.*:USAGE/)namer}privresource_limits tls_requiresrrris_roleNlockedr!)rPr7r'rr&r%r3itemsr6replacer4joinrmremoverrncopyr)get_tls_requiresrupdategetrr+)r,rQoutputrRrr}r user_priv priv_stringdb_tablerunquote_db_tablercopy_ressource_limitsr output_dictkeyvalueauthenticationss r-__get_users_infozMySQL_Info.__get_users_infoFsn(oo89? 'D#OP Q;!# {(B"";/1$++tTJO$(IIo$> !33DD T4)L-#8 , K"1"7"7"9@JCz'(9:3?@{#456!;#$56 /9$++tTRO""?1#56xx "tI#'=(6t{{D$(O H% MM+ &? 'B#) ,r/c fd fd}dg} dr|jd dr|jddjd j|}|j|xsg}|D]}|||jd |d <|rL|jd xsg}|D]1}|d } | |jd vs|i|jd | <3yy)zGet info about databases.c4 xsdj|vS)Nzdb_{})format) field_namer9s r-is_field_includedz5MySQL_Info.__get_databases..is_field_includeds%%Y )C>)Y Yr/ci}dr"t|jddxsd|d<dr"t|jddxsd|d<|S)Nsizer table_counttables)rZr)db_datar+rs r-create_db_infoz2MySQL_Info.__get_databases..create_db_infosYD ("7;;vq#9#>Q?V  /!$W[[1%=%B!CXKr/zSELECT table_schema AS "name"rz)SUM(data_length + index_length) AS "size"rzCOUNT(table_name) as "tables"z7{} FROM information_schema.TABLES GROUP BY table_schemaz, rrzSHOW DATABASESDatabaseN)r4rrrPr+) r,r9r:r query_partsrvrdbempty_databasesdb_namers ` @r-__get_databaseszMySQL_Info.__get_databasess Z 77 V $   J K ] +   > ?IPPQUQZQZ[fQghOOE*0b  DB1?1CDIIk "2f: . D "oo.>?E2O% IZ.$))K"886DR6HDIIk*73 I r/c  |jj||s|jj}|Sy#t$r5}|jj d|dt |Yd}~yd}~wwxYw)zExecute SQL. Arguments: ddl (bool): If True, return True or False. Used for queries that don't return any rows (mainly for DDL queries) (default False). TzCannot execute SQL 'z': msgNF)r&executefetchall Exceptionr% fail_jsonr)r,rvddlrQes r- __exec_sqlzMySQL_Info.__exec_sqlsn ] KK   &kk**,  ] KK ! !uiXYl&[ ! \ \ ]s8< A:+A55A:N)F)__name__ __module__ __qualname____doc__r.rAr5rFrkrCrErIrJrKrGrHrDrPr/r-rrQsY  (,\ : B D`La, VT Z)x"IHr/rc t}|jtdtddtddtddt|d }|jd }|jd }|jd }|jd}|jd}|jd}|jd}|jd} |jd} |jd} |jd} |jd} | r| Dcgc]}|j } }| r't | Dcgc]}|j c}} t|jttt}tt} t|||| ||||| |d \}}t}t!|}t#|}t%|||||}|j&d"d|dk(rdnd ||d!|j)| | | ycc}wcc}w#t$r2}d|d|d| dt|}|j|Yd}~d}~wwxYw)#Nro)typer7)relementsboolF)rdefault)login_dbfilterr9r:T) argument_specsupports_check_moderconnect_timeout login_userlogin_password client_cert client_keyca_certcheck_hostname config_filerr9r:r DictCursor)rr cursor_classz$unable to connect to database using  z5, check login_user and login_password are correct or z) has the credentials. Exception message: rMariaDBMySQL)changed server_engineconnector_nameconnector_versionr)r rrnrparamsstripr6r rr r rr rrrrrr exit_jsonrA)rr%rrrrssl_certssl_keyssl_carrr8r9r:frrr&db_connrrr'r(r)mysqls r-mainrsc.0M5!%0%8659 # F z "Bmm$56O|,J]]#34N}}]+HmmL)G ]]9 %F]]#34N-- .KmmH%G]]#34N}}%78&-.1779..@Aaggi@A23' 5N-l; ' N(3XwPR7E8GVbd6f='/N1&9 vv'ansible_collections.community.mysql.plugins.module_utils.mysqlr r r r r rrr=ansible_collections.community.mysql.plugins.module_utils.userrrrrransible.module_utils.sixransible.module_utils._textrobjectrrrrr/r-rsA@ < |\ |G R4   /0GG^ ?RD zFr/