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 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 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 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 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)
