1#!/usr/bin/python 2# -*- coding: utf-8 -*- 3# Copyright: (c) 2017, Ansible Project 4# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) 5 6from __future__ import absolute_import, division, print_function 7__metaclass__ = type 8 9DOCUMENTATION = ''' 10--- 11module: proxysql_backend_servers 12version_added: "2.3" 13author: "Ben Mildren (@bmildren)" 14short_description: Adds or removes mysql hosts from proxysql admin interface. 15description: 16 - The M(proxysql_backend_servers) module adds or removes mysql hosts using 17 the proxysql admin interface. 18options: 19 hostgroup_id: 20 description: 21 - The hostgroup in which this mysqld instance is included. An instance 22 can be part of one or more hostgroups. 23 default: 0 24 hostname: 25 description: 26 - The ip address at which the mysqld instance can be contacted. 27 required: True 28 port: 29 description: 30 - The port at which the mysqld instance can be contacted. 31 default: 3306 32 status: 33 description: 34 - ONLINE - Backend server is fully operational. 35 OFFLINE_SOFT - When a server is put into C(OFFLINE_SOFT) mode, 36 connections are kept in use until the current 37 transaction is completed. This allows to gracefully 38 detach a backend. 39 OFFLINE_HARD - When a server is put into C(OFFLINE_HARD) mode, the 40 existing connections are dropped, while new incoming 41 connections aren't accepted either. 42 43 If omitted the proxysql database default for I(status) is C(ONLINE). 44 choices: [ "ONLINE", "OFFLINE_SOFT", "OFFLINE_HARD"] 45 weight: 46 description: 47 - The bigger the weight of a server relative to other weights, the higher 48 the probability of the server being chosen from the hostgroup. If 49 omitted the proxysql database default for I(weight) is 1. 50 compression: 51 description: 52 - If the value of I(compression) is greater than 0, new connections to 53 that server will use compression. If omitted the proxysql database 54 default for I(compression) is 0. 55 max_connections: 56 description: 57 - The maximum number of connections ProxySQL will open to this backend 58 server. If omitted the proxysql database default for I(max_connections) 59 is 1000. 60 max_replication_lag: 61 description: 62 - If greater than 0, ProxySQL will regularly monitor replication lag. If 63 replication lag goes above I(max_replication_lag), proxysql will 64 temporarily shun the server until replication catches up. If omitted 65 the proxysql database default for I(max_replication_lag) is 0. 66 use_ssl: 67 description: 68 - If I(use_ssl) is set to C(True), connections to this server will be 69 made using SSL connections. If omitted the proxysql database default 70 for I(use_ssl) is C(False). 71 type: bool 72 max_latency_ms: 73 description: 74 - Ping time is monitored regularly. If a host has a ping time greater 75 than I(max_latency_ms) it is excluded from the connection pool 76 (although the server stays ONLINE). If omitted the proxysql database 77 default for I(max_latency_ms) is 0. 78 comment: 79 description: 80 - Text field that can be used for any purposed defined by the user. 81 Could be a description of what the host stores, a reminder of when the 82 host was added or disabled, or a JSON processed by some checker script. 83 default: '' 84 state: 85 description: 86 - When C(present) - adds the host, when C(absent) - removes the host. 87 choices: [ "present", "absent" ] 88 default: present 89extends_documentation_fragment: 90 - proxysql.managing_config 91 - proxysql.connectivity 92''' 93 94EXAMPLES = ''' 95--- 96# This example adds a server, it saves the mysql server config to disk, but 97# avoids loading the mysql server config to runtime (this might be because 98# several servers are being added and the user wants to push the config to 99# runtime in a single batch using the M(proxysql_manage_config) module). It 100# uses supplied credentials to connect to the proxysql admin interface. 101 102- proxysql_backend_servers: 103 login_user: 'admin' 104 login_password: 'admin' 105 hostname: 'mysql01' 106 state: present 107 load_to_runtime: False 108 109# This example removes a server, saves the mysql server config to disk, and 110# dynamically loads the mysql server config to runtime. It uses credentials 111# in a supplied config file to connect to the proxysql admin interface. 112 113- proxysql_backend_servers: 114 config_file: '~/proxysql.cnf' 115 hostname: 'mysql02' 116 state: absent 117''' 118 119RETURN = ''' 120stdout: 121 description: The mysql host modified or removed from proxysql 122 returned: On create/update will return the newly modified host, on delete 123 it will return the deleted record. 124 type: dict 125 "sample": { 126 "changed": true, 127 "hostname": "192.168.52.1", 128 "msg": "Added server to mysql_hosts", 129 "server": { 130 "comment": "", 131 "compression": "0", 132 "hostgroup_id": "1", 133 "hostname": "192.168.52.1", 134 "max_connections": "1000", 135 "max_latency_ms": "0", 136 "max_replication_lag": "0", 137 "port": "3306", 138 "status": "ONLINE", 139 "use_ssl": "0", 140 "weight": "1" 141 }, 142 "state": "present" 143 } 144''' 145 146ANSIBLE_METADATA = {'metadata_version': '1.1', 147 'status': ['stableinterface'], 148 'supported_by': 'community'} 149 150 151from ansible.module_utils.basic import AnsibleModule 152from ansible.module_utils.mysql import mysql_connect, mysql_driver, mysql_driver_fail_msg 153from ansible.module_utils.six import iteritems 154from ansible.module_utils._text import to_native 155 156# =========================================== 157# proxysql module specific support methods. 158# 159 160 161def perform_checks(module): 162 if module.params["login_port"] < 0 \ 163 or module.params["login_port"] > 65535: 164 module.fail_json( 165 msg="login_port must be a valid unix port number (0-65535)" 166 ) 167 168 if module.params["port"] < 0 \ 169 or module.params["port"] > 65535: 170 module.fail_json( 171 msg="port must be a valid unix port number (0-65535)" 172 ) 173 174 if module.params["compression"]: 175 if module.params["compression"] < 0 \ 176 or module.params["compression"] > 102400: 177 module.fail_json( 178 msg="compression must be set between 0 and 102400" 179 ) 180 181 if module.params["max_replication_lag"]: 182 if module.params["max_replication_lag"] < 0 \ 183 or module.params["max_replication_lag"] > 126144000: 184 module.fail_json( 185 msg="max_replication_lag must be set between 0 and 102400" 186 ) 187 188 if mysql_driver is None: 189 module.fail_json(msg=mysql_driver_fail_msg) 190 191 192def save_config_to_disk(cursor): 193 cursor.execute("SAVE MYSQL SERVERS TO DISK") 194 return True 195 196 197def load_config_to_runtime(cursor): 198 cursor.execute("LOAD MYSQL SERVERS TO RUNTIME") 199 return True 200 201 202class ProxySQLServer(object): 203 204 def __init__(self, module): 205 self.state = module.params["state"] 206 self.save_to_disk = module.params["save_to_disk"] 207 self.load_to_runtime = module.params["load_to_runtime"] 208 209 self.hostgroup_id = module.params["hostgroup_id"] 210 self.hostname = module.params["hostname"] 211 self.port = module.params["port"] 212 213 config_data_keys = ["status", 214 "weight", 215 "compression", 216 "max_connections", 217 "max_replication_lag", 218 "use_ssl", 219 "max_latency_ms", 220 "comment"] 221 222 self.config_data = dict((k, module.params[k]) 223 for k in config_data_keys) 224 225 def check_server_config_exists(self, cursor): 226 query_string = \ 227 """SELECT count(*) AS `host_count` 228 FROM mysql_servers 229 WHERE hostgroup_id = %s 230 AND hostname = %s 231 AND port = %s""" 232 233 query_data = \ 234 [self.hostgroup_id, 235 self.hostname, 236 self.port] 237 238 cursor.execute(query_string, query_data) 239 check_count = cursor.fetchone() 240 return (int(check_count['host_count']) > 0) 241 242 def check_server_config(self, cursor): 243 query_string = \ 244 """SELECT count(*) AS `host_count` 245 FROM mysql_servers 246 WHERE hostgroup_id = %s 247 AND hostname = %s 248 AND port = %s""" 249 250 query_data = \ 251 [self.hostgroup_id, 252 self.hostname, 253 self.port] 254 255 for col, val in iteritems(self.config_data): 256 if val is not None: 257 query_data.append(val) 258 query_string += "\n AND " + col + " = %s" 259 260 cursor.execute(query_string, query_data) 261 check_count = cursor.fetchone() 262 263 if isinstance(check_count, tuple): 264 return int(check_count[0]) > 0 265 266 return (int(check_count['host_count']) > 0) 267 268 def get_server_config(self, cursor): 269 query_string = \ 270 """SELECT * 271 FROM mysql_servers 272 WHERE hostgroup_id = %s 273 AND hostname = %s 274 AND port = %s""" 275 276 query_data = \ 277 [self.hostgroup_id, 278 self.hostname, 279 self.port] 280 281 cursor.execute(query_string, query_data) 282 server = cursor.fetchone() 283 return server 284 285 def create_server_config(self, cursor): 286 query_string = \ 287 """INSERT INTO mysql_servers ( 288 hostgroup_id, 289 hostname, 290 port""" 291 292 cols = 3 293 query_data = \ 294 [self.hostgroup_id, 295 self.hostname, 296 self.port] 297 298 for col, val in iteritems(self.config_data): 299 if val is not None: 300 cols += 1 301 query_data.append(val) 302 query_string += ",\n" + col 303 304 query_string += \ 305 (")\n" + 306 "VALUES (" + 307 "%s ," * cols) 308 309 query_string = query_string[:-2] 310 query_string += ")" 311 312 cursor.execute(query_string, query_data) 313 return True 314 315 def update_server_config(self, cursor): 316 query_string = """UPDATE mysql_servers""" 317 318 cols = 0 319 query_data = [] 320 321 for col, val in iteritems(self.config_data): 322 if val is not None: 323 cols += 1 324 query_data.append(val) 325 if cols == 1: 326 query_string += "\nSET " + col + "= %s," 327 else: 328 query_string += "\n " + col + " = %s," 329 330 query_string = query_string[:-1] 331 query_string += ("\nWHERE hostgroup_id = %s\n AND hostname = %s" + 332 "\n AND port = %s") 333 334 query_data.append(self.hostgroup_id) 335 query_data.append(self.hostname) 336 query_data.append(self.port) 337 338 cursor.execute(query_string, query_data) 339 return True 340 341 def delete_server_config(self, cursor): 342 query_string = \ 343 """DELETE FROM mysql_servers 344 WHERE hostgroup_id = %s 345 AND hostname = %s 346 AND port = %s""" 347 348 query_data = \ 349 [self.hostgroup_id, 350 self.hostname, 351 self.port] 352 353 cursor.execute(query_string, query_data) 354 return True 355 356 def manage_config(self, cursor, state): 357 if state: 358 if self.save_to_disk: 359 save_config_to_disk(cursor) 360 if self.load_to_runtime: 361 load_config_to_runtime(cursor) 362 363 def create_server(self, check_mode, result, cursor): 364 if not check_mode: 365 result['changed'] = \ 366 self.create_server_config(cursor) 367 result['msg'] = "Added server to mysql_hosts" 368 result['server'] = \ 369 self.get_server_config(cursor) 370 self.manage_config(cursor, 371 result['changed']) 372 else: 373 result['changed'] = True 374 result['msg'] = ("Server would have been added to" + 375 " mysql_hosts, however check_mode" + 376 " is enabled.") 377 378 def update_server(self, check_mode, result, cursor): 379 if not check_mode: 380 result['changed'] = \ 381 self.update_server_config(cursor) 382 result['msg'] = "Updated server in mysql_hosts" 383 result['server'] = \ 384 self.get_server_config(cursor) 385 self.manage_config(cursor, 386 result['changed']) 387 else: 388 result['changed'] = True 389 result['msg'] = ("Server would have been updated in" + 390 " mysql_hosts, however check_mode" + 391 " is enabled.") 392 393 def delete_server(self, check_mode, result, cursor): 394 if not check_mode: 395 result['server'] = \ 396 self.get_server_config(cursor) 397 result['changed'] = \ 398 self.delete_server_config(cursor) 399 result['msg'] = "Deleted server from mysql_hosts" 400 self.manage_config(cursor, 401 result['changed']) 402 else: 403 result['changed'] = True 404 result['msg'] = ("Server would have been deleted from" + 405 " mysql_hosts, however check_mode is" + 406 " enabled.") 407 408# =========================================== 409# Module execution. 410# 411 412 413def main(): 414 module = AnsibleModule( 415 argument_spec=dict( 416 login_user=dict(default=None, type='str'), 417 login_password=dict(default=None, no_log=True, type='str'), 418 login_host=dict(default='127.0.0.1'), 419 login_unix_socket=dict(default=None), 420 login_port=dict(default=6032, type='int'), 421 config_file=dict(default='', type='path'), 422 hostgroup_id=dict(default=0, type='int'), 423 hostname=dict(required=True, type='str'), 424 port=dict(default=3306, type='int'), 425 status=dict(choices=['ONLINE', 426 'OFFLINE_SOFT', 427 'OFFLINE_HARD']), 428 weight=dict(type='int'), 429 compression=dict(type='int'), 430 max_connections=dict(type='int'), 431 max_replication_lag=dict(type='int'), 432 use_ssl=dict(type='bool'), 433 max_latency_ms=dict(type='int'), 434 comment=dict(default='', type='str'), 435 state=dict(default='present', choices=['present', 436 'absent']), 437 save_to_disk=dict(default=True, type='bool'), 438 load_to_runtime=dict(default=True, type='bool') 439 ), 440 supports_check_mode=True 441 ) 442 443 perform_checks(module) 444 445 login_user = module.params["login_user"] 446 login_password = module.params["login_password"] 447 config_file = module.params["config_file"] 448 449 cursor = None 450 try: 451 cursor = mysql_connect(module, 452 login_user, 453 login_password, 454 config_file, 455 cursor_class='DictCursor') 456 except mysql_driver.Error as e: 457 module.fail_json( 458 msg="unable to connect to ProxySQL Admin Module.. %s" % to_native(e) 459 ) 460 461 proxysql_server = ProxySQLServer(module) 462 result = {} 463 464 result['state'] = proxysql_server.state 465 if proxysql_server.hostname: 466 result['hostname'] = proxysql_server.hostname 467 468 if proxysql_server.state == "present": 469 try: 470 if not proxysql_server.check_server_config(cursor): 471 if not proxysql_server.check_server_config_exists(cursor): 472 proxysql_server.create_server(module.check_mode, 473 result, 474 cursor) 475 else: 476 proxysql_server.update_server(module.check_mode, 477 result, 478 cursor) 479 else: 480 result['changed'] = False 481 result['msg'] = ("The server already exists in mysql_hosts" + 482 " and doesn't need to be updated.") 483 result['server'] = \ 484 proxysql_server.get_server_config(cursor) 485 except mysql_driver.Error as e: 486 module.fail_json( 487 msg="unable to modify server.. %s" % to_native(e) 488 ) 489 490 elif proxysql_server.state == "absent": 491 try: 492 if proxysql_server.check_server_config_exists(cursor): 493 proxysql_server.delete_server(module.check_mode, 494 result, 495 cursor) 496 else: 497 result['changed'] = False 498 result['msg'] = ("The server is already absent from the" + 499 " mysql_hosts memory configuration") 500 except mysql_driver.Error as e: 501 module.fail_json( 502 msg="unable to remove server.. %s" % to_native(e) 503 ) 504 505 module.exit_json(**result) 506 507 508if __name__ == '__main__': 509 main() 510