def rename_db_to_drop(instance, dbs, verbose=False, dry_run=False):
""" Create a new empty db and move the contents of the original db there
Args:
instance - a hostaddr object
dbs - a set of database names
verbose - bool, will direct sql to stdout
dry_run - bool, will make no changes to
"""
# confirm db is not in zk and not in use
orphaned, _, _ = find_shard_mismatches.find_shard_mismatches(instance)
if not orphaned:
print "Detected no orphans"
sys.exit(1)
instance_orphans = orphaned[instance.__str__()]
unexpected = dbs.difference(instance_orphans)
if unexpected:
print ''.join(("Cowardly refusing to act on the following dbs: ",
','.join(unexpected)))
sys.exit(1)
# confirm that renames would not be blocked by an existing table
conn = mysql_lib.connect_mysql(instance)
cursor = conn.cursor()
for db in dbs:
renamed_db = ''.join((DB_PREPEND, db))
sql = ''.join(("SELECT CONCAT(t2.TABLE_SCHEMA, \n",
" '.', t2.TABLE_NAME) as tbl \n",
"FROM information_schema.tables t1 \n",
"INNER JOIN information_schema.tables t2 \n",
" USING(TABLE_NAME) \n",
"WHERE t1.TABLE_SCHEMA = %(old_db)s AND \n"
" t2.TABLE_SCHEMA = %(new_db)s;"))
params = {'old_db': db,
'new_db': renamed_db}
cursor = conn.cursor()
cursor.execute(sql, params)
dups = cursor.fetchall()
if dups:
for dup in dups:
print "Table rename blocked by {tbl}".format(tbl=dup['tbl'])
sys.exit(1)
# We should be safe to create the new db and rename
if not dry_run:
mysql_lib.create_db(instance, renamed_db)
mysql_lib.move_db_contents(instance,
old_db=db,
new_db=renamed_db,
verbose=verbose,
dry_run=dry_run)
def rename_db_to_drop(instance, dbs, verbose=False, dry_run=False):
""" Create a new empty db and move the contents of the original db there
Args:
instance - a hostaddr object
dbs - a set of database names
verbose - bool, will direct sql to stdout
dry_run - bool, will make no changes to
"""
# confirm db is not in zk and not in use
orphaned, _, _ = find_shard_mismatches.find_shard_mismatches(instance)
if not orphaned:
print "Detected no orphans"
sys.exit(1)
instance_orphans = orphaned[instance.__str__()]
unexpected = dbs.difference(instance_orphans)
if unexpected:
print ''.join(("Cowardly refusing to act on the following dbs: ",
','.join(unexpected)))
sys.exit(1)
# confirm that renames would not be blocked by an existing table
conn = mysql_lib.connect_mysql(instance)
cursor = conn.cursor()
for db in dbs:
renamed_db = ''.join((DB_PREPEND, db))
sql = ''.join(("SELECT CONCAT(t2.TABLE_SCHEMA, \n",
" '.', t2.TABLE_NAME) as tbl \n",
"FROM information_schema.tables t1 \n",
"INNER JOIN information_schema.tables t2 \n",
" USING(TABLE_NAME) \n",
"WHERE t1.TABLE_SCHEMA = %(old_db)s AND \n"
" t2.TABLE_SCHEMA = %(new_db)s;"))
params = {'old_db': db,
'new_db': renamed_db}
cursor = conn.cursor()
cursor.execute(sql, params)
dups = cursor.fetchall()
if dups:
for dup in dups:
print "Table rename blocked by {tbl}".format(tbl=dup['tbl'])
sys.exit(1)
# We should be safe to create the new db and rename
if not dry_run:
mysql_lib.create_db(conn, renamed_db)
mysql_lib.move_db_contents(conn=conn,
old_db=db,
new_db=renamed_db,
verbose=verbose,
dry_run=dry_run)
def main():
action_desc = """Action description
rename - after checking no recent changes and shard not in zk,
create a db with the old name appended to 'dropme_'. Then
copy all tables to the new db
revert_rename - Copy all tables back from a 'dropme_' to their original table
drop - This should be run a few days after a rename. Drop the empty original
db, and drop the 'dropme_' db.
"""
parser = argparse.ArgumentParser(description='MySQL shard cleanup utility',
epilog=action_desc,
formatter_class=argparse.RawDescriptionHelpFormatter)
parser.add_argument('-i',
'--instance',
help='Instance to act on if other than localhost:3306',
default=''.join((socket.getfqdn(), ':3306')))
parser.add_argument('-a',
'--action',
choices=('rename',
'revert_rename',
'drop',),
required=True)
parser.add_argument('-d',
'--dbs',
help=("Comma seperated list of db's to act upon"),
required=True)
parser.add_argument('-r',
'--dry_run',
help=("Do not change any state"),
default=False,
action='store_true')
parser.add_argument('-v',
'--verbose',
default=False,
action='store_true')
args = parser.parse_args()
dbs = set(args.dbs.split(','))
instance = host_utils.HostAddr(args.instance)
if args.action == 'rename':
rename_db_to_drop(instance, dbs, args.verbose, args.dry_run)
elif args.action == 'revert_rename':
conn = mysql_lib.connect_mysql(instance)
for db in dbs:
mysql_lib.move_db_contents(conn=conn,
old_db=''.join((DB_PREPEND, db)),
new_db=db,
verbose=args.verbose,
dry_run=args.dry_run)
elif args.action == 'drop':
drop_db_after_rename(instance, dbs, args.verbose, args.dry_run)
def main():
action_desc = """Action description
rename - after checking no recent changes and shard not in zk,
create a db with the old name appended to 'dropme_'. Then
copy all tables to the new db
revert_rename - Copy all tables back from a 'dropme_' to their original table
drop - This should be run a few days after a rename. Drop the empty original
db, and drop the 'dropme_' db.
"""
parser = argparse.ArgumentParser(
description="MySQL shard cleanup utility",
epilog=action_desc,
formatter_class=argparse.RawDescriptionHelpFormatter,
)
parser.add_argument(
"-i",
"--instance",
help="Instance to act on if other than localhost:3306",
default="".join((socket.getfqdn(), ":3306")),
)
parser.add_argument("-a", "--action", choices=("rename", "revert_rename", "drop"), required=True)
parser.add_argument("-d", "--dbs", help=("Comma seperated list of db's to act upon"), required=True)
parser.add_argument("-r", "--dry_run", help=("Do not change any state"), default=False, action="store_true")
parser.add_argument("-v", "--verbose", default=False, action="store_true")
args = parser.parse_args()
dbs = set(args.dbs.split(","))
instance = host_utils.HostAddr(args.instance)
if args.action == "rename":
rename_db_to_drop(instance, dbs, args.verbose, args.dry_run)
elif args.action == "revert_rename":
for db in dbs:
mysql_lib.move_db_contents(
instance, old_db="".join((DB_PREPEND, db)), new_db=db, verbose=args.verbose, dry_run=args.dry_run
)
elif args.action == "drop":
drop_db_after_rename(instance, dbs, args.verbose, args.dry_run)
def rename_db_to_drop(instance, dbs=None, dry_run=False, skip_check=False):
""" Create a new empty db and move the contents of the original db
into it
Args:
instance - a hostaddr object
dbs - a set of database names
dry_run - bool, will make no changes to anything
skip_check - Do not verify that db is not in production
"""
orphaned, _, _ = find_shard_mismatches.find_shard_mismatches(instance)
if not dbs:
if instance not in orphaned:
log.info("No orphaned shards, returning now.")
return
dbs = orphaned[instance]
log.info('Detected orphaned shareds: {}'.format(dbs))
if not skip_check:
# confirm db is not in ZK and not in use.
if not orphaned:
log.info("No orphans detected, returning now.")
return
instance_orphans = orphaned[instance]
unexpected = dbs.difference(instance_orphans)
if unexpected:
raise Exception('Cowardly refusing to act on the following'
'dbs: {}'.format(unexpected))
# confirm that renames would not be blocked by an existing table
conn = mysql_lib.connect_mysql(instance)
cursor = conn.cursor()
for db in dbs:
# already dealt with
if db.startswith(DB_PREPEND):
continue
renamed_db = ''.join((DB_PREPEND, db))
sql = ''.join(("SELECT CONCAT(t2.TABLE_SCHEMA, \n",
" '.', t2.TABLE_NAME) as tbl \n",
"FROM information_schema.tables t1 \n",
"INNER JOIN information_schema.tables t2 \n",
" USING(TABLE_NAME) \n",
"WHERE t1.TABLE_SCHEMA = %(old_db)s AND \n"
" t2.TABLE_SCHEMA = %(new_db)s;"))
params = {'old_db': db, 'new_db': renamed_db}
cursor = conn.cursor()
cursor.execute(sql, params)
dups = cursor.fetchall()
if dups:
for dup in dups:
log.error('Table rename blocked by {}'.format(dup['tbl']))
sys.exit(1)
# We should be safe to create the new db and rename
if not dry_run:
mysql_lib.create_db(instance, renamed_db)
mysql_lib.move_db_contents(instance,
old_db=db,
new_db=renamed_db,
dry_run=dry_run)
if dbs and not dry_run:
log.info('To finish cleanup, wait a bit and then run:')
log.info('/usr/local/bin/mysql_utils/fix_orphaned_shards.py -a'
'drop -i {}'.format(instance))
