def create_root_cnf(cnf_parser, override_dir): """ Create a .my.cnf file to setup defaults for username/password Args: cnf_parser - A ConfigParser object of mysqld settings override_dir - Write to this directory rather than default """ admin_user, admin_password = mysql_lib.get_mysql_user_for_role('admin') dump_user, dump_password = mysql_lib.get_mysql_user_for_role('mysqldump') parser = ConfigParser.RawConfigParser(allow_no_value=True) parser.add_section('mysql') parser.set('mysql', 'user', admin_user) parser.set('mysql', 'password', admin_password) parser.set('mysql', 'socket', cnf_parser.get(MYSQLD_SECTION, 'socket')) parser.add_section('mysqladmin') parser.set('mysqladmin', 'user', admin_user) parser.set('mysqladmin', 'password', admin_password) parser.set('mysqladmin', 'socket', cnf_parser.get(MYSQLD_SECTION, 'socket')) parser.add_section('mysqldump') parser.set('mysqldump', 'user', dump_user) parser.set('mysqldump', 'password', dump_password) parser.set('mysqldump', 'socket', cnf_parser.get(MYSQLD_SECTION, 'socket')) if override_dir: root_cnf_path = os.path.join(override_dir, os.path.basename(ROOT_CNF)) else: root_cnf_path = ROOT_CNF log.info('Writing file {root_cnf_path}' ''.format(root_cnf_path=root_cnf_path)) with open(root_cnf_path, "w") as root_cnf_handle: parser.write(root_cnf_handle)
def csv_backups_running(instance): """ Check to see if csv dumps are running Args: instance - we will use this to determine the replica set Returns: True if backups are running, False otherwise """ (dump_user, _) = mysql_lib.get_mysql_user_for_role(backup.USER_ROLE_MYSQLDUMP) replica_set = instance.get_zk_replica_set()[0] zk = host_utils.MysqlZookeeper() for slave_role in [ host_utils.REPLICA_ROLE_DR_SLAVE, host_utils.REPLICA_ROLE_SLAVE ]: slave_instance = zk.get_mysql_instance_from_replica_set( replica_set, slave_role) if not slave_instance: continue if dump_user in mysql_lib.get_connected_users(slave_instance): return True return False
def checksum_tbl(instance, db, tbl): """ Args: instance: the master instance to run against db: the database to checksum tbl: the table within the database to checksum Returns: cmd: the command line(s) executed out: any output written to STDOUT err: any output written to STDERR ret: the return code of the checksum process """ username, password = mysql_lib.get_mysql_user_for_role('ptchecksum') cmd = (' '.join(('/usr/bin/pt-table-checksum', CHECKSUM_DEFAULTS, '--tables={db}.{tbl}', '--user={username}', '--password={password}', '--host={host}', '--port={port}')).format(tbl=tbl, db=db, username=username, password=password, host=instance.hostname, port=instance.port)) out, err, ret = host_utils.shell_exec(cmd) return cmd.replace(password, 'REDACTED'), out, err, ret
def create_pt_kill_conf(override_dir): """ Create the config file for pt-kill Args: override_dir - Write to this directory rather than default """ template_path = os.path.join(RELATIVE_DIR, PT_KILL_TEMPLATE) with open(template_path, 'r') as f: template = f.read() kill_user, kill_password = mysql_lib.get_mysql_user_for_role('ptkill') if override_dir: kill_cnf_path = os.path.join(override_dir, os.path.basename(PT_KILL_CONF_FILE)) else: kill_cnf_path = PT_KILL_CONF_FILE log.info('Writing file {kill_cnf_path}' ''.format(kill_cnf_path=kill_cnf_path)) with open(kill_cnf_path, "w") as kill_cnf_handle: kill_cnf_handle.write( template.format(username=kill_user, password=kill_password, busy_time=PT_KILL_BUSY_TIME, ignore_users='|'.join(PT_KILL_IGNORE_USERS)))
def create_pt_heartbeat_conf(override_dir): """ Create the config file for pt-hearbeat Args: override_dir - Write to this directory rather than default """ template_path = os.path.join(RELATIVE_DIR, PT_HEARTBEAT_TEMPLATE) with open(template_path, 'r') as f: template = f.read() heartbeat_user, heartbeat_password = mysql_lib.get_mysql_user_for_role( 'ptheartbeat') if override_dir: heartbeat_cnf_path = os.path.join( override_dir, os.path.basename(PT_HEARTBEAT_CONF_FILE)) else: heartbeat_cnf_path = PT_HEARTBEAT_CONF_FILE log.info('Writing file {heartbeat_cnf_path}' ''.format(heartbeat_cnf_path=heartbeat_cnf_path)) with open(heartbeat_cnf_path, "w") as heartbeat_cnf_handle: heartbeat_cnf_handle.write( template.format(defaults_file=host_utils.MYSQL_CNF_FILE, username=heartbeat_user, password=heartbeat_password, metadata_db=mysql_lib.METADATA_DB))
def check_for_user_activity(instance): zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') # check mysql activity log.info('Checking activity on {instance}'.format(instance=instance['hostname'])) with timeout.timeout(3): conn = MySQLdb.connect(host=instance['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('Could not connect to {ip}' ''.format(ip=instance['internal_ip'])) activity = mysql_lib.get_user_activity(host_utils.HostAddr(instance['hostname'])) unexpected = set(activity.keys()).difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected activity on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True log.info('Checking current connections on ' '{instance}'.format(instance=instance['hostname'])) connected_users = mysql_lib.get_connected_users(host_utils.HostAddr(instance['hostname'])) unexpected = connected_users.difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected connection on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True return False
def manage_pt_heartbeat(instance): """ Restarts ptheartbeat if it isn't currently running and the replica role type is master, or stop it if it is running on a non-master. Args: instance (host_utils.HostAddr): host to check for ptheartbeat Returns: None """ connected_users = mysql_lib.get_connected_users(instance) zk = host_utils.MysqlZookeeper() try: replica_type = zk.get_replica_type_from_instance(instance) except: replica_type = None pthb_user, pthb_pass = mysql_lib.get_mysql_user_for_role('ptheartbeat') if replica_type == host_utils.REPLICA_ROLE_MASTER and \ pthb_user not in connected_users: host_utils.manage_pt_heartbeat(instance.port) log.info('Started process pt-heartbeat') elif replica_type != host_utils.REPLICA_ROLE_MASTER and \ pthb_user in connected_users: host_utils.manage_pt_heartbeat(instance.port, action='stop') log.info('Stopped pt-heartbeat on non-master replica')
def terminate_instances(hostname=None, dry_run=False): zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') terminate_instances = get_retirement_queue_servers(TERMINATE_INSTANCE) botoconn = boto.ec2.connect_to_region('us-east-1') if hostname: if hostname in terminate_instances: log.info('Only acting on {hostname}'.format(hostname=hostname)) terminate_instances = {hostname: terminate_instances[hostname]} else: log.info('Supplied host {hostname} is not ready ' 'for termination'.format(hostname=hostname)) return for hostname in terminate_instances: if hostname in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for instance in zk.get_all_mysql_instances(): if instance.hostname == hostname: log.warning("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) remove_from_retirement_queue(hostname) continue log.info('Confirming mysql is down on ' '{hostname}'.format(hostname=hostname)) try: with timeout.timeout(3): conn = MySQLdb.connect( host=terminate_instances[hostname]['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) log.error('Did not get MYSQL_ERROR_CONN_HOST_ERROR, removing {} ' 'from queue'.format(hostname)) conn.close() remove_from_retirement_queue(hostname) continue except MySQLdb.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.info('MySQL is down') log.info('Terminating instance ' '{instance}'.format( instance=terminate_instances[hostname]['instance_id'])) if dry_run: log.info('In dry_run mode, not changing state') else: botoconn.terminate_instances( instance_ids=[terminate_instances[hostname]['instance_id']]) log_to_retirement_queue( hostname, terminate_instances[hostname]['instance_id'], TERMINATE_INSTANCE)
def kill_mysql_backup(instance): """ Kill sql, csv and xtrabackup backups Args: instance - Instance to kill backups, does not apply to csv or sql """ (username, _) = mysql_lib.get_mysql_user_for_role(backup.USER_ROLE_MYSQLDUMP) mysql_lib.kill_user_queries(instance, username) kill_xtrabackup()
def terminate_instances(hostname=None, dry_run=False): zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') terminate_instances = get_retirement_queue_servers(TERMINATE_INSTANCE) botoconn = boto.ec2.connect_to_region('us-east-1') if hostname: if hostname in terminate_instances: log.info('Only acting on {hostname}'.format(hostname=hostname)) terminate_instances = {hostname: terminate_instances[hostname]} else: log.info('Supplied host {hostname} is not ready ' 'for termination'.format(hostname=hostname)) return for hostname in terminate_instances: if hostname in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for instance in zk.get_all_mysql_instances(): if instance.hostname == hostname: log.warning("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) remove_from_retirement_queue(hostname) continue log.info('Confirming mysql is down on ' '{hostname}'.format(hostname=hostname)) try: with timeout.timeout(3): pymysql.connect(host=terminate_instances[hostname]['internal_ip'], user=username, passwd=password, cursorclass=pymysql.cursors.DictCursor) log.error('Did not get MYSQL_ERROR_CONN_HOST_ERROR') continue except pymysql.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.info('MySQL is down') log.info('Terminating instance ' '{instance}'.format(instance=terminate_instances[hostname]['instance_id'])) if dry_run: log.info('In dry_run mode, not changing state') else: botoconn.terminate_instances( instance_ids=[terminate_instances[hostname]['instance_id']]) log_to_retirement_queue(hostname, terminate_instances[ hostname]['instance_id'], TERMINATE_INSTANCE)
def create_maxwell_config(client_id, instance, exclude_dbs=None, target='kafka', gtid_mode='true'): """ Create the maxwell config file. Args: client_id = The server_uuid instance = What instance is this? exclude_dbs = Exclude these databases (in addition to mysql and test) target = Output to kafka or a file (which will be /dev/null) gtid_mode = True if this is a GTID cluster, false otherwise Returns: Nothing """ template_path = os.path.join(RELATIVE_DIR, MAXWELL_TEMPLATE) with open(template_path, 'r') as f: template = f.read() (username, password) = mysql_lib.get_mysql_user_for_role('maxwell') zk = host_utils.MysqlZookeeper() replica_set = zk.get_replica_set_from_instance(instance) hostname_prefix = instance.hostname_prefix if hostname_prefix in environment_specific.FLEXSHARD_DBS or hostname_prefix in environment_specific.SHARDED_DBS_PREFIX: namespace = hostname_prefix else: namespace = replica_set master = zk.get_mysql_instance_from_replica_set( replica_set, host_utils.REPLICA_ROLE_MASTER) log.info('Writing file {}'.format(MAXWELL_CONF_FILE)) excluded = ','.join(['mysql', 'test', exclude_dbs]) if exclude_dbs \ else 'mysql,test' target_map = environment_specific.MAXWELL_TARGET_MAP[ master.hostname_prefix] with open(MAXWELL_CONF_FILE, "w") as f: f.write( template.format(master_host=master.hostname, master_port=master.port, instance_host=instance.hostname, instance_port=instance.port, username=username, password=password, kafka_topic=target_map['kafka_topic'], kafka_servers=target_map['kafka_servers'], generator=target_map['generator'], zen_service=target_map['zen_service'], client_id=client_id, output=target, excludes=excluded, gtid_mode=gtid_mode, namespace=namespace))
def restart_maxwell_if_not_exists(instance): """ Start Maxwell if it isn't currently running. Args: instance: (host_utils.HostAddr): host to check Returns: none """ zk = host_utils.MysqlZookeeper() replica_type = zk.get_replica_type_from_instance(instance) gvars = mysql_lib.get_global_variables(instance) client_id = gvars['server_uuid'] gtid_mode = True if gvars.get('gtid_mode') == 'ON' else False (username, _) = mysql_lib.get_mysql_user_for_role('maxwell') output_target = 'file' # master writes to kafka, everything else writes to /dev/null, # at least for now. if instance.hostname_prefix in environment_specific.MAXWELL_TARGET_MAP \ and replica_type == host_utils.REPLICA_ROLE_MASTER: output_target = 'kafka' # we need to rewrite the config each time, because something may # have changed - i.e., a failover. this is just a stopgap solution # pending resolution of LP-809 mysql_cnf_builder.create_maxwell_config(client_id, instance, None, output_target, gtid_mode) # Check for the Maxwell PID file and then see if it belongs to Maxwell. maxwell_running = False try: with open(environment_specific.MAXWELL_PID, "r") as f: pid = f.read() proc = psutil.Process(int(pid)) cmdline = proc.cmdline() if 'java' in cmdline and 'com.zendesk.maxwell.Maxwell' in cmdline: maxwell_running = True except (IOError, psutil.NoSuchProcess, psutil.ZombieProcess): # No PID file or no process matching said PID, so maxwell is definitely # not running. If maxwell is a zombie then it's not running either. pass if maxwell_running: log.debug('Maxwell is already running') return if instance.hostname_prefix in environment_specific.MAXWELL_TARGET_MAP: host_utils.manage_maxwell(instance.port) log.info('Started Maxwell process')
def process_mysql_shutdown(hostname=None, dry_run=False): """ Check stats, and shutdown MySQL instances""" zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') shutdown_instances = get_retirement_queue_servers(SHUTDOWN_MYSQL) if hostname: if hostname in shutdown_instances: log.info('Only acting on {}'.format(hostname)) shutdown_instances = {hostname: shutdown_instances[hostname]} else: log.info('Supplied host {} is not ready ' 'for shutdown'.format(hostname)) return for instance in shutdown_instances: if instance in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for active_instance in zk.get_all_mysql_instances(): if active_instance.hostname == instance: log.warning("It appears {instance} is in zk. This is " "very dangerous! If you got to here, you may be " "trying to turn down a replica set. Please remove " "it from zk and try again" "".format(instance=instance)) continue if dry_run: log.info('In dry_run mode, not changing state') continue try: if check_for_user_activity(shutdown_instances[instance]): log.info('Activity detected on {}, removing from queue' ''.format(instance)) remove_from_retirement_queue(hostname) continue else: log.info('Shutting down mysql on {}'.format(instance)) mysql_lib.shutdown_mysql(host_utils.HostAddr(instance)) except MySQLdb.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.warning("Can't connect to MySQL on {}".format(instance)) log_to_retirement_queue(instance, shutdown_instances[instance]['instance_id'], SHUTDOWN_MYSQL)
def find_gtid_for_timestamp(instance, timestamp): """ Find the GTID for the supplied timestamp on the specified instance. Args: instance: a HostAddr object timestamp: the timestamp to search for Returns: If the instance doesn't support GTID, return None. If no GTID was found in the binlogs for the supplied timestamp, return a blank string. Otherwise, return a GTID. """ vars = mysql_lib.get_global_variables(instance) # we are not generating GTIDs / no GTID support if vars['gtid_mode'] == 'OFF' or vars['gtid_deployment_step'] == 'ON': log.warning('This replica set does not currently support GTID') return None # go in reverse order, because odds are that the log we want # is closer to the end than the beginning. master_logs = list(reversed(mysql_lib.get_master_logs(instance))) (username, password) = mysql_lib.get_mysql_user_for_role('replication') for binlog in master_logs: # if the timestamp we want is prior to the first entry in the # binlog, it can't possibly be in there. log_start = get_binlog_start(binlog['Log_name'], instance, username, password) if timestamp < log_start: log.debug('Skipping binlog {bl} because desired {ts} < ' '{ls}'.format(bl=binlog['Log_name'], ts=timestamp, ls=log_start)) continue # The binlog that we end up checking, if we check one at all, # is the first one that could possibly contain our GTID, so # if it isn't in this one, we're not going to find anything. log.debug('Checking for matching GTID in {}'.format( binlog['Log_name'])) gtid = check_one_binlog(timestamp, binlog['Log_name'], instance, username, password) if gtid: return gtid else: break log.warning("No matching GTID was found for that timestamp.") return ''
def add_to_queue(hostname, dry_run): """ Add an instance to the retirement queue Args: hostname - The hostname of the instance to add to the retirement queue """ log.info('Adding server {hostname} to retirement ' 'queue'.format(hostname=hostname)) if hostname in get_protected_hosts('set'): raise Exception('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) # basic sanity check zk = host_utils.MysqlZookeeper() for instance in zk.get_all_mysql_instances(): if instance.hostname == hostname: raise Exception("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) all_servers = environment_specific.get_all_server_metadata() if not hostname in all_servers: raise Exception('Host {hostname} is not cmdb'.format(hostname=hostname)) instance_metadata = all_servers[hostname] log.info(instance_metadata) username, password = mysql_lib.get_mysql_user_for_role('admin') try: log.info('Trying to reset user_statistics on ip ' '{ip}'.format(ip=instance_metadata['internal_ip'])) with timeout.timeout(3): conn = MySQLdb.connect(host=instance_metadata['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('timeout') mysql_lib.enable_and_flush_activity_statistics(conn) activity = RESET_STATS except MySQLdb.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.info('Could not connect to ' '{ip}'.format(ip=instance_metadata['internal_ip'])) activity = SHUTDOWN_MYSQL log_to_retirement_queue(hostname, instance_metadata['instance_id'], activity)
def restart_pt_kill_if_not_exists(instance): """ Restarts ptkill if it isn't currently running Args: instance (host_utils.HostAddr): host to check for ptkill Returns: None """ connected_users = mysql_lib.get_connected_users(instance) ptkill_user, ptkill_pass = mysql_lib.get_mysql_user_for_role('ptkill') if ptkill_user not in connected_users: host_utils.manage_pt_kill(instance.port) log.info('Started Processes ptkill')
def restart_pt_kill_if_not_exists(instance): """ Restarts ptkill if it isn't currently running Args: instance (host_utils.HostAddr): host to check for ptkill Returns: None """ connected_users = mysql_lib.get_connected_users(instance) ptkill_user, ptkill_pass = mysql_lib.get_mysql_user_for_role('ptkill') if ptkill_user not in connected_users: host_utils.restart_pt_kill(instance.port) log.info('Started Processes ptkill')
def process_mysql_shutdown(hostname=None, dry_run=False): """ Check stats, and shutdown MySQL instances""" zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') shutdown_instances = get_retirement_queue_servers(SHUTDOWN_MYSQL) if hostname: if hostname in shutdown_instances: log.info('Only acting on {hostname}'.format(hostname=hostname)) shutdown_instances = {hostname: shutdown_instances[hostname]} else: log.info('Supplied host {hostname} is not ready ' 'for shutdown'.format(hostname=hostname)) return for instance in shutdown_instances: if instance in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for active_instance in zk.get_all_mysql_instances(): if active_instance.hostname == instance: log.warning("It appears {instance} is in zk. This is " "very dangerous! If you got to here, you may be " "trying to turn down a replica set. Please remove " "it from zk and try again" "".format(instance=instance)) continue # check mysql activity if check_for_user_activity(shutdown_instances[instance]): continue # joining on a blank string as password must not have a space between # the flag and the arg if dry_run: log.info('In dry_run mode, not changing state') else: log.info('Shuting down mysql on {instance}'.format( instance=instance)) mysql_lib.shutdown_mysql(host_utils.HostAddr(instance)) log_to_retirement_queue(instance, shutdown_instances[ instance]['instance_id'], SHUTDOWN_MYSQL)
def process_mysql_shutdown(hostname=None, dry_run=False): """ Check stats, and shutdown MySQL instances""" zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') shutdown_instances = get_retirement_queue_servers(SHUTDOWN_MYSQL) if hostname: if hostname in shutdown_instances: log.info('Only acting on {hostname}'.format(hostname=hostname)) shutdown_instances = {hostname: shutdown_instances[hostname]} else: log.info('Supplied host {hostname} is not ready ' 'for shutdown'.format(hostname=hostname)) return for instance in shutdown_instances: if instance in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for active_instance in zk.get_all_mysql_instances(): if active_instance.hostname == instance: log.warning("It appears {instance} is in zk. This is " "very dangerous! If you got to here, you may be " "trying to turn down a replica set. Please remove " "it from zk and try again" "".format(instance=instance)) continue # check mysql activity if check_for_user_activity(shutdown_instances[instance]): continue # joining on a blank string as password must not have a space between # the flag and the arg if dry_run: log.info('In dry_run mode, not changing state') else: log.info( 'Shuting down mysql on {instance}'.format(instance=instance)) mysql_lib.shutdown_mysql(host_utils.HostAddr(instance)) log_to_retirement_queue( instance, shutdown_instances[instance]['instance_id'], SHUTDOWN_MYSQL)
def checksum_tbl_via_sync(instance, db, tbl): username, password = mysql_lib.get_mysql_user_for_role('ptchecksum') cmd = (' '.join( ('/usr/bin/pt-table-sync', CHECKSUM_SYNC_DEFAULTS, '--tables={db}.{tbl}', '--user={username}', '--password={password}', 'h={host},P={port}')).format(db=db, tbl=tbl, username=username, password=password, host=instance.hostname, port=instance.port)) out, err, ret = host_utils.shell_exec(cmd) diff_count = 0 for line in out.split("\n"): diff_count += parse_sync_row(line) # strip out the password in case we are storing it in the DB. return cmd.replace(password, 'REDACTED'), out, err, ret, diff_count
def logical_restore(dump, destination): """ Restore a compressed mysqldump file from s3 to localhost, port 3306 Args: dump - a mysqldump file in s3 destination - a hostaddr object for where the data should be loaded on localhost """ (user, password) = mysql_lib.get_mysql_user_for_role('admin') if dump.name.startswith(backup.BACKUP_TYPE_PARTIAL_LOGICAL): # TODO: check if db is empty before applying rate limit rate_limit = backup.MAX_TRANSFER_RATE else: log.info('Restarting MySQL to turn off enforce_storage_engine') host_utils.stop_mysql(destination.port) host_utils.start_mysql(destination.port, host_utils.DEFAULTS_FILE_ARG.format( defaults_file=host_utils.MYSQL_UPGRADE_CNF_FILE)) rate_limit = None log.info('Downloading, decompressing and importing backup') procs = dict() procs['s3_download'] = backup.create_s3_download_proc(dump) procs['pv'] = backup.create_pv_proc(procs['s3_download'].stdout, size=dump.size, rate_limit=rate_limit) log.info('zcat |') procs['zcat'] = subprocess.Popen(['zcat'], stdin=procs['pv'].stdout, stdout=subprocess.PIPE) mysql_cmd = ['mysql', '--port={}'.format(str(destination.port)), '--host={}'.format(destination.hostname), '--user={}'.format(user), '--password={}'.format(password)] log.info(' '.join(mysql_cmd)) procs['mysql'] = subprocess.Popen(mysql_cmd, stdin=procs['zcat'].stdout) while(not host_utils.check_dict_of_procs(procs)): time.sleep(.5)
def restart_pt_heartbeat_if_not_exists(instance): """ Restarts ptheartbeat if it isn't currently running and the replica role type is master Args: instance (host_utils.HostAddr): host to check for ptheartbeat Returns: None """ connected_users = mysql_lib.get_connected_users(instance) zk = host_utils.MysqlZookeeper() try: (_, replica_type) = zk.get_replica_set_from_instance(instance) except: replica_type = None pthb_user, pthb_pass = mysql_lib.get_mysql_user_for_role('ptheartbeat') if replica_type in (host_utils.REPLICA_ROLE_MASTER, None) and \ pthb_user not in connected_users: host_utils.restart_pt_heartbeat(instance.port) log.info('Started Processes ptheartbeat')
def csv_backups_running(instance): """ Check to see if csv dumps are running Args: instance - we will use this to determine the replica set Returns: True if backups are running, False otherwise """ (dump_user, _) = mysql_lib.get_mysql_user_for_role(backup.USER_ROLE_MYSQLDUMP) replica_set = instance.get_zk_replica_set()[0] zk = host_utils.MysqlZookeeper() for slave_role in [host_utils.REPLICA_ROLE_DR_SLAVE, host_utils.REPLICA_ROLE_SLAVE]: slave_instance = zk.get_mysql_instance_from_replica_set(replica_set, slave_role) if not slave_instance: continue if dump_user in mysql_lib.get_connected_users(slave_instance): return True return False
def checksum_tbl_via_sync(instance, db, tbl): username, password = mysql_lib.get_mysql_user_for_role('ptchecksum') cmd = (' '.join(('/usr/bin/pt-table-sync', CHECKSUM_SYNC_DEFAULTS, '--tables={db}.{tbl}', '--user={username}', '--password={password}', 'h={host},P={port}')).format(db=db, tbl=tbl, username=username, password=password, host=instance.hostname, port=instance.port)) out, err, ret = host_utils.shell_exec(cmd) diff_count = 0 for line in out.split("\n"): diff_count += parse_sync_row(line) # strip out the password in case we are storing it in the DB. return cmd.replace(password, 'REDACTED'), out, err, ret, diff_count
def check_for_user_activity(instance): username, password = mysql_lib.get_mysql_user_for_role('admin') # check mysql activity log.info('Checking activity on {}'.format(instance['hostname'])) activity = mysql_lib.get_user_activity( host_utils.HostAddr(instance['hostname'])) unexpected = set(activity.keys()).difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected activity on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True log.info('Checking current connections on ' '{instance}'.format(instance=instance['hostname'])) # try catch here due to the query creates the temp file will break our # code if disk space is full try: connected_users = mysql_lib.get_connected_users( host_utils.HostAddr(instance['hostname'])) except MySQLdb.InternalError as detail: (err_code, msg) = detail.args if err_code == mysql_lib.MYSQL_ERROR_CANT_CREATE_WRITE_TO_FILE: log.info('No space left on device') return False except: log.info('Something else is not correct here') return False unexpected = connected_users.difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected connection on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True return False
def create_pt_kill_conf(override_dir): """ Create the config file for pt-kill Args: override_dir - Write to this directory rather than default """ template_path = os.path.join(RELATIVE_DIR, PT_KILL_TEMPLATE) with open(template_path, 'r') as f: template = f.read() kill_user, kill_password = mysql_lib.get_mysql_user_for_role('ptkill') if override_dir: kill_cnf_path = os.path.join(override_dir, os.path.basename(PT_KILL_CONF_FILE)) else: kill_cnf_path = PT_KILL_CONF_FILE log.info('Writing file {kill_cnf_path}' ''.format(kill_cnf_path=kill_cnf_path)) with open(kill_cnf_path, "w") as kill_cnf_handle: kill_cnf_handle.write(template.format(username=kill_user, password=kill_password, busy_time=PT_KILL_BUSY_TIME, ignore_users='|'.join(PT_KILL_IGNORE_USERS)))
def create_pt_heartbeat_conf(override_dir): """ Create the config file for pt-hearbeat Args: override_dir - Write to this directory rather than default """ template_path = os.path.join(RELATIVE_DIR, PT_HEARTBEAT_TEMPLATE) with open(template_path, 'r') as f: template = f.read() heartbeat_user, heartbeat_password = mysql_lib.get_mysql_user_for_role('ptheartbeat') if override_dir: heartbeat_cnf_path = os.path.join(override_dir, os.path.basename(PT_HEARTBEAT_CONF_FILE)) else: heartbeat_cnf_path = PT_HEARTBEAT_CONF_FILE log.info('Writing file {heartbeat_cnf_path}' ''.format(heartbeat_cnf_path=heartbeat_cnf_path)) with open(heartbeat_cnf_path, "w") as heartbeat_cnf_handle: heartbeat_cnf_handle.write(template.format(defaults_file=host_utils.MYSQL_CNF_FILE, username=heartbeat_user, password=heartbeat_password, metadata_db=mysql_lib.METADATA_DB))
def check_for_user_activity(instance): zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') # check mysql activity log.info('Checking activity on {instance}'.format( instance=instance['hostname'])) with timeout.timeout(3): conn = MySQLdb.connect(host=instance['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('Could not connect to {ip}' ''.format(ip=instance['internal_ip'])) activity = mysql_lib.get_user_activity( host_utils.HostAddr(instance['hostname'])) unexpected = set(activity.keys()).difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected activity on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True log.info('Checking current connections on ' '{instance}'.format(instance=instance['hostname'])) connected_users = mysql_lib.get_connected_users( host_utils.HostAddr(instance['hostname'])) unexpected = connected_users.difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected connection on {instance} by user(s):' '{unexpected}'.format(instance=instance['hostname'], unexpected=','.join(unexpected))) return True return False
def mysql_connect(sockfile): """ Connects to the MySQL server using the specified socket file. """ user, passwd = mysql_lib.get_mysql_user_for_role(MYSQL_USER_ROLE) return MySQLdb.connect(unix_socket=sockfile, connect_timeout=CONNECT_TIMEOUT, user=user, passwd=passwd)
def main(): parser = argparse.ArgumentParser() parser.add_argument('db', help='What server, shard or replica set to connect to ' '(e.g., sharddb-21-2[:3306], db00003, pbdata03862, ' 'zenfollowermysql_zendata001002, ' 'zenshared_video_zendata000002, etc.)') parser.add_argument('-p', '--privileges', help=''.join(('Default is ', DEFAULT_ROLE)), default=DEFAULT_ROLE, choices=environment_specific.CLI_ROLES.keys()) parser.add_argument('-l', '--longquery', default=False, action='store_true', help='For standard read or write access, use this ' 'flag if you expect the query to take more than ' '10 seconds.') parser.add_argument('--trust_me_im_a_doctor', default=False, action='store_true', help='If this is set, we bypass any paranoid replica ' 'set checks. User assumes all risk.') parser.add_argument('-e', '--execute', help='An optional SQL command to run.', default=False) args = parser.parse_args() zk = host_utils.MysqlZookeeper() host = None db = '' role_modifier = 'default' long_query = '' if args.longquery: role_modifier = 'long' long_query = '(long queries enabled)' # check if db exists in dns, if so the supplied argument will be considered # a hostname, otherwise a replica set. try: socket.gethostbyname(args.db) host = host_utils.HostAddr(args.db) log.info('{} appears to be a hostname'.format(args.db)) except: log.info('{} appears not to be a hostname'.format(args.db)) # Maybe it is a replica set if not host: try: host = zk.get_mysql_instance_from_replica_set(args.db) log.info('{} appears to be a replica set'.format(args.db)) except: log.info('{} appears not to be a replica set'.format(args.db)) # Perhaps a shard? if not host: try: (replica_set, db) = zk.map_shard_to_replica_and_db(args.db) host = zk.get_mysql_instance_from_replica_set(replica_set) log.info('{} appears to be a shard'.format(args.db)) except: log.info('{} appears not to be a shard'.format(args.db)) raise if not host: raise Exception('Could not determine what host to connect to') log.info('Will connect to {host} with {privileges} ' 'privileges {lq}'.format(host=host, privileges=args.privileges, lq=long_query)) (username, password) = mysql_lib.get_mysql_user_for_role( environment_specific.CLI_ROLES[args.privileges][role_modifier]) # we may or may not know what replica set we're connecting to at # this point. sql_safe = '' try: replica_set = zk.get_replica_set_from_instance(host) except Exception as e: if 'is not in zk' in e.message: log.warning('SERVER IS NOT IN ZK!!!') replica_set = None else: raise if not args.trust_me_im_a_doctor: try: # do we need a prompt? if replica_set in environment_specific.EXTRA_PARANOID_REPLICA_SETS: warn = environment_specific.EXTRA_PARANOID_ALERTS.get( replica_set) if args.privileges in ['read-write', 'admin']: resp = raw_input( "You've asked for {priv} access to replica " "set {rs}. Are you sure? (Y/N): ".format( priv=args.privileges, rs=replica_set)) if not resp or resp[0] not in ['Y', 'y']: raise Exception('Connection aborted by user!') else: print warn # should we enable safe-updates? if replica_set in environment_specific.PARANOID_REPLICA_SETS: if args.privileges in ['read-write', 'admin']: sql_safe = '--init-command="SET SESSION SQL_SAFE_UPDATES=ON"' except Exception as e: log.error("Unable to continue: {}".format(e)) return else: log.warning("OK, we trust you know what you're doing, but " "don't say we didn't warn you.") if args.execute: execute_escaped = string.replace(args.execute, '"', '\\"') cmd = MYSQL_CLI_EX.format(host=host.hostname, port=host.port, db=db, user=username, password=password, sql_safe=sql_safe, execute=execute_escaped) else: cmd = MYSQL_CLI.format(host=host.hostname, port=host.port, db=db, user=username, password=password, sql_safe=sql_safe) log.info(cmd) proc = subprocess.Popen(cmd, shell=True) proc.wait()
def main(): parser = argparse.ArgumentParser() parser.add_argument('db', help='What server, shard or replica set to connect to ' '(ie sharddb021b[:3306], db00003, pbdata03862, ' 'follower_zendata001002)') parser.add_argument('-p', '--privileges', help=''.join(('Default is ', DEFAULT_ROLE)), default=DEFAULT_ROLE, choices=environment_specific.CLI_ROLES.keys()) parser.add_argument('-l', '--longquery', default=False, action='store_true', help='For standard read or write access, use this ' 'flag if you expect the query to take more than ' '10 seconds.') parser.add_argument('-e', '--execute', help='An optional SQL command to run.', default=False) args = parser.parse_args() zk = host_utils.MysqlZookeeper() host = None db = '' role_modifier = 'default' long_query = '' if args.longquery: role_modifier = 'long' long_query = '(long queries enabled)' # check if db exists in dns, if so the supplied argument will be considered # a hostname, otherwise a replica set. try: socket.gethostbyname(args.db) host = host_utils.HostAddr(args.db) log.info('{db} appears to be a hostname'.format(db=args.db)) except: log.info('{db} appears not to be a hostname'.format(db=args.db)) # Maybe it is a replica set if not host: config = zk.get_all_mysql_config() if args.db in config: master = config[args.db]['master'] log.info('{db} appears to be a replica set'.format(db=args.db)) host = host_utils.HostAddr(''.join((master['host'], ':', str(master['port'])))) else: log.info('{db} appears not to be a replica set'.format(db=args.db)) # Perhaps a shard? if not host: shard_map = zk.get_host_shard_map() for master in shard_map: if args.db in shard_map[master]: log.info('{db} appears to be a shard'.format(db=args.db)) host = host_utils.HostAddr(master) db = environment_specific.convert_shard_to_db(args.db) break if not host: log.info('{db} appears not to be a shard'.format(db=args.db)) if not host: raise Exception('Could not determine what host to connect to') log.info('Will connect to {host} with {privileges} ' 'privileges {lq}'.format(host=host, privileges=args.privileges, lq=long_query)) (username, password) = mysql_lib.get_mysql_user_for_role( environment_specific.CLI_ROLES[args.privileges][role_modifier]) # we may or may not know what replica set we're connecting to at # this point. sql_safe = '' try: replica_set, _ = zk.get_replica_set_from_instance(host) except Exception as e: if 'is not in zk' in e.message: log.warning('SERVER IS NOT IN ZK!!!') replica_set = None else: raise try: # do we need a prompt? if replica_set in environment_specific.EXTRA_PARANOID_REPLICA_SETS: if args.privileges in ['read-write', 'admin']: resp = raw_input("You've asked for {priv} access to replica " "set {rs}. Are you sure? (Y/N): ".format( priv=args.privileges, rs=replica_set)) if not resp or resp[0] not in ['Y', 'y']: raise Exception('Connection aborted by user!') # should we enable safe-updates? if replica_set in environment_specific.PARANOID_REPLICA_SETS: if args.privileges in ['read-write', 'admin']: sql_safe = '--init-command="SET SESSION SQL_SAFE_UPDATES=ON"' except Exception as e: log.error("Unable to continue: {}".format(e)) return if args.execute: execute_escaped = string.replace(args.execute, '"', '\\"') cmd = MYSQL_CLI_EX.format(host=host.hostname, port=host.port, db=db, user=username, password=password, sql_safe=sql_safe, execute=execute_escaped) else: cmd = MYSQL_CLI.format(host=host.hostname, port=host.port, db=db, user=username, password=password, sql_safe=sql_safe) log.info(cmd) proc = subprocess.Popen(cmd, shell=True) proc.wait()
def mysql_connect(sockfile): """ Connects to the MySQL server using the specified socket file. """ user, passwd = mysql_lib.get_mysql_user_for_role(MYSQL_USER_ROLE) return pymysql.connect(unix_socket=sockfile, connect_timeout=CONNECT_TIMEOUT, user=user, passwd=passwd)
def main(): parser = argparse.ArgumentParser() parser.add_argument('db', help='What server, shard or replica set to connect to ' '(ie sharddb021b[:3306], db00003, pbdata03862)') parser.add_argument('-p', '--privileges', help=''.join(('Default is ', DEFAULT_ROLE)), default=DEFAULT_ROLE, choices=ROLES.keys()) parser.add_argument('-e', '--execute', help='An optional SQL command to run.', default=False) args = parser.parse_args() zk = host_utils.MysqlZookeeper() host = None db = '' # check if db exists in dns, if so the supplied argument will be considered # a hostname, otherwise a replica set. try: socket.gethostbyname(args.db) host = host_utils.HostAddr(args.db) log.info('{db} appears to be a hostname'.format(db=args.db)) except: log.info('{db} appears not to be a hostname'.format(db=args.db)) # Maybe it is a replica set if not host: config = zk.get_all_mysql_config() if args.db in config: master = config[args.db]['master'] log.info('{db} appears to be a replica set'.format(db=args.db)) host = host_utils.HostAddr(''.join((master['host'], ':', str(master['port'])))) else: log.info('{db} appears not to be a replica set'.format(db=args.db)) # Perhaps a shard? if not host: shard_map = zk.get_host_shard_map() for master in shard_map: if args.db in shard_map[master]: log.info('{db} appears to be a shard'.format(db=args.db)) host = host_utils.HostAddr(master) db = args.db break if not host: log.info('{db} appears not to be a shard'.format(db=args.db)) if not host: raise Exception('Could not determine what host to connect to') log.info('Will connect to {host} with {privileges} ' 'privileges'.format(host=host, privileges=args.privileges)) (username, password) = mysql_lib.get_mysql_user_for_role(ROLES[args.privileges]) if args.execute: cmd = MYSQL_CLI_EX.format(host=host.hostname, port=host.port, db=db, user=username, password=password, execute=args.execute) else: cmd = MYSQL_CLI.format(host=host.hostname, port=host.port, db=db, user=username, password=password) log.info(cmd) proc = subprocess.Popen(cmd, shell=True) proc.wait()
def add_to_queue(hostname, dry_run, skip_production_check=False): """ Add an instance to the retirement queue Args: hostname - The hostname of the instance to add to the retirement queue """ log.info('Adding server {hostname} to retirement ' 'queue'.format(hostname=hostname)) if hostname in get_protected_hosts('set'): raise Exception('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) # basic sanity check zk = host_utils.MysqlZookeeper() for instance in zk.get_all_mysql_instances(): if instance.hostname == hostname: if skip_production_check: log.warning("It appears {instance} is in zk but " "skip_production_check is set so continuing." "".format(instance=instance)) else: raise Exception("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) all_servers = environment_specific.get_all_server_metadata() if hostname not in all_servers: raise Exception('Host {hostname} is not cmdb'.format(hostname=hostname)) instance_metadata = all_servers[hostname] log.info(instance_metadata) username, password = mysql_lib.get_mysql_user_for_role('admin') try: if check_for_user_activity(instance_metadata): log.info('Trying to reset user_statistics on ip ' '{ip}'.format(ip=instance_metadata['internal_ip'])) with timeout.timeout(3): conn = MySQLdb.connect(host=instance_metadata['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('timeout') if dry_run: log.info('In dry_run mode, not changing anything') else: mysql_lib.enable_and_flush_activity_statistics(host_utils.HostAddr(hostname)) else: log.info("No recent user activity, skipping stats reset") # We still need to add it to the queue the first time. # Check if it was added recently and exit if it was if is_host_in_retirement_queue(hostname): return activity = RESET_STATS except MySQLdb.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.info('Could not connect to ' '{ip}'.format(ip=instance_metadata['internal_ip'])) activity = SHUTDOWN_MYSQL # We only want to add the host if it wasn't already in the queue if is_host_in_retirement_queue(hostname): return if dry_run: log.info('In dry_run mode, not changing anything') else: log_to_retirement_queue(hostname, instance_metadata['instance_id'], activity)
def main(): parser = argparse.ArgumentParser() parser.add_argument('db', help='What server, shard or replica set to connect to ' '(ie sharddb021b[:3306], db00003, pbdata03862, ' 'follower_zendata001002)') parser.add_argument('-p', '--privileges', help=''.join(('Default is ', DEFAULT_ROLE)), default=DEFAULT_ROLE, choices=environment_specific.CLI_ROLES.keys()) parser.add_argument('-l', '--longquery', default=False, action='store_true', help='For standard read or write access, use this ' 'flag if you expect the query to take more than ' '10 seconds.') parser.add_argument('-e', '--execute', help='An optional SQL command to run.', default=False) args = parser.parse_args() zk = host_utils.MysqlZookeeper() host = None db = '' role_modifier = 'default' long_query = '' if args.longquery: role_modifier = 'long' long_query = '(long queries enabled)' # check if db exists in dns, if so the supplied argument will be considered # a hostname, otherwise a replica set. try: socket.gethostbyname(args.db) host = host_utils.HostAddr(args.db) log.info('{db} appears to be a hostname'.format(db=args.db)) except: log.info('{db} appears not to be a hostname'.format(db=args.db)) # Maybe it is a replica set if not host: config = zk.get_all_mysql_config() if args.db in config: master = config[args.db]['master'] log.info('{db} appears to be a replica set'.format(db=args.db)) host = host_utils.HostAddr(''.join((master['host'], ':', str(master['port'])))) else: log.info('{db} appears not to be a replica set'.format(db=args.db)) # Perhaps a shard? if not host: shard_map = zk.get_host_shard_map() for master in shard_map: if args.db in shard_map[master]: log.info('{db} appears to be a shard'.format(db=args.db)) host = host_utils.HostAddr(master) db = environment_specific.convert_shard_to_db(args.db) break if not host: log.info('{db} appears not to be a shard'.format(db=args.db)) if not host: raise Exception('Could not determine what host to connect to') log.info('Will connect to {host} with {privileges} ' 'privileges {lq}'.format(host=host, privileges=args.privileges, lq=long_query)) (username, password) = mysql_lib.get_mysql_user_for_role( environment_specific.CLI_ROLES[args.privileges][role_modifier]) if args.execute: execute_escaped = string.replace(args.execute, '"', '\\"') cmd = MYSQL_CLI_EX.format(host=host.hostname, port=host.port, db=db, user=username, password=password, execute=execute_escaped) else: cmd = MYSQL_CLI.format(host=host.hostname, port=host.port, db=db, user=username, password=password) log.info(cmd) proc = subprocess.Popen(cmd, shell=True) proc.wait()
def add_to_queue(hostname, dry_run, skip_production_check=False): """ Add an instance to the retirement queue Args: hostname - The hostname of the instance to add to the retirement queue """ log.info('Adding server {hostname} to retirement ' 'queue'.format(hostname=hostname)) if hostname in get_protected_hosts('set'): raise Exception('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) # basic sanity check zk = host_utils.MysqlZookeeper() for instance in zk.get_all_mysql_instances(): if instance.hostname == hostname: if skip_production_check: log.warning("It appears {instance} is in zk but " "skip_production_check is set so continuing." "".format(instance=instance)) else: raise Exception("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) all_servers = environment_specific.get_all_server_metadata() if hostname not in all_servers: raise Exception( 'Host {hostname} is not cmdb'.format(hostname=hostname)) instance_metadata = all_servers[hostname] log.info(instance_metadata) username, password = mysql_lib.get_mysql_user_for_role('admin') try: if check_for_user_activity(instance_metadata): log.info('Trying to reset user_statistics on ip ' '{ip}'.format(ip=instance_metadata['internal_ip'])) with timeout.timeout(3): conn = MySQLdb.connect(host=instance_metadata['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('timeout') if dry_run: log.info('In dry_run mode, not changing anything') else: mysql_lib.enable_and_flush_activity_statistics( host_utils.HostAddr(hostname)) else: log.info("No recent user activity, skipping stats reset") # We still need to add it to the queue the first time. # Check if it was added recently and exit if it was if is_host_in_retirement_queue(hostname): return activity = RESET_STATS except MySQLdb.OperationalError as detail: (error_code, msg) = detail.args if error_code != mysql_lib.MYSQL_ERROR_CONN_HOST_ERROR: raise log.info('Could not connect to ' '{ip}'.format(ip=instance_metadata['internal_ip'])) activity = SHUTDOWN_MYSQL # We only want to add the host if it wasn't already in the queue if is_host_in_retirement_queue(hostname): return if dry_run: log.info('In dry_run mode, not changing anything') else: log_to_retirement_queue(hostname, instance_metadata['instance_id'], activity)
def main(): parser = argparse.ArgumentParser() parser.add_argument('db', help='What server, shard or replica set to connect to ' '(ie sharddb021b[:3306], db00003, pbdata03862, ' 'follower_zendata001002)') parser.add_argument('-p', '--privileges', help=''.join(('Default is ', DEFAULT_ROLE)), default=DEFAULT_ROLE, choices=environment_specific.CLI_ROLES.keys()) parser.add_argument('-l', '--longquery', default=False, action='store_true', help='For standard read or write access, use this ' 'flag if you expect the query to take more than ' '10 seconds.') parser.add_argument('-e', '--execute', help='An optional SQL command to run.', default=False) args = parser.parse_args() zk = host_utils.MysqlZookeeper() host = None db = '' role_modifier = 'default' long_query = '' if args.longquery: role_modifier = 'long' long_query = '(long queries enabled)' # check if db exists in dns, if so the supplied argument will be considered # a hostname, otherwise a replica set. try: socket.gethostbyname(args.db) host = host_utils.HostAddr(args.db) log.info('{db} appears to be a hostname'.format(db=args.db)) except: log.info('{db} appears not to be a hostname'.format(db=args.db)) # Maybe it is a replica set if not host: config = zk.get_all_mysql_config() if args.db in config: master = config[args.db]['master'] log.info('{db} appears to be a replica set'.format(db=args.db)) host = host_utils.HostAddr(''.join( (master['host'], ':', str(master['port'])))) else: log.info('{db} appears not to be a replica set'.format(db=args.db)) # Perhaps a shard? if not host: shard_map = zk.get_host_shard_map() for master in shard_map: if args.db in shard_map[master]: log.info('{db} appears to be a shard'.format(db=args.db)) host = host_utils.HostAddr(master) db = environment_specific.convert_shard_to_db(args.db) break if not host: log.info('{db} appears not to be a shard'.format(db=args.db)) if not host: raise Exception('Could not determine what host to connect to') log.info('Will connect to {host} with {privileges} ' 'privileges {lq}'.format(host=host, privileges=args.privileges, lq=long_query)) (username, password) = mysql_lib.get_mysql_user_for_role( environment_specific.CLI_ROLES[args.privileges][role_modifier]) if args.execute: execute_escaped = string.replace(args.execute, '"', '\\"') cmd = MYSQL_CLI_EX.format(host=host.hostname, port=host.port, db=db, user=username, password=password, execute=execute_escaped) else: cmd = MYSQL_CLI.format(host=host.hostname, port=host.port, db=db, user=username, password=password) log.info(cmd) proc = subprocess.Popen(cmd, shell=True) proc.wait()
def process_mysql_shutdown(hostname=None, dry_run=False): """ Check stats, and shutdown MySQL instances""" zk = host_utils.MysqlZookeeper() username, password = mysql_lib.get_mysql_user_for_role('admin') shutdown_instances = get_retirement_queue_servers(SHUTDOWN_MYSQL) if hostname: if hostname in shutdown_instances: log.info('Only acting on {hostname}'.format(hostname=hostname)) shutdown_instances = {hostname: shutdown_instances[hostname]} else: log.info('Supplied host {hostname} is not ready ' 'for shutdown'.format(hostname=hostname)) return for instance in shutdown_instances: if instance in get_protected_hosts('set'): log.warning('Host {hostname} is protected from ' 'retirement'.format(hostname=hostname)) remove_from_retirement_queue(hostname) continue for active_instance in zk.get_all_mysql_instances(): if active_instance.hostname == instance: log.warning("It appears {instance} is in zk. This is " "very dangerous!".format(instance=instance)) remove_from_retirement_queue(instance) continue log.info('Checking activity on {instance}'.format(instance=instance)) # check mysql activity with timeout.timeout(3): conn = MySQLdb.connect(host=shutdown_instances[instance]['internal_ip'], user=username, passwd=password, cursorclass=MySQLdb.cursors.DictCursor) if not conn: raise Exception('Could not connect to {ip}' ''.format(ip=shutdown_instances[instance]['internal_ip'])) activity = mysql_lib.get_user_activity(conn) unexpected = set(activity.keys()).difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected acitivty on {instance} by user(s):' '{unexpected}'.format(instance=instance, unexpected=','.join(unexpected))) continue log.info('Checking current connections on ' '{instance}'.format(instance=instance)) connected_users = mysql_lib.get_connected_users(conn) unexpected = connected_users.difference(IGNORABLE_USERS) if unexpected: log.error('Unexpected connection on {instance} by user(s):' '{unexpected}'.format(instance=instance, unexpected=','.join(unexpected))) continue # joining on a blank string as password must not have a space between # the flag and the arg if dry_run: log.info('In dry_run mode, not changing state') else: log.info('Shuting down mysql on {instance}'.format(instance=instance)) mysql_lib.shutdown_mysql(host_utils.HostAddr(instance)) log_to_retirement_queue(instance, shutdown_instances[instance]['instance_id'], SHUTDOWN_MYSQL)