def main():
db1 = Connection(options.database_host, options.database_user,
options.database_password)
#grab all shared files in order
sfs = db1.query("""SELECT id FROM sharedfile ORDER BY created_at""")
#for each, get counts
for sf in sfs:
likes = 0
saves = 0
like_counts = db1.query(
"SELECT count(id) as like_count from favorite where sharedfile_id = %s and deleted=0",
(sf.id))
if like_counts:
likes = like_counts[0]['like_count']
save_counts = db1.query(
"SELECT count(id) AS save_count FROM sharedfile WHERE original_id = %s and deleted = 0",
sf.id)
if save_counts[0]['save_count'] > 0:
saves = save_counts[0]['save_count']
else:
save_counts = db1.query(
"SELECT count(id) AS save_count FROM sharedfile WHERE parent_id = %s and deleted = 0",
sf.id)
saves = save_counts[0]['save_count']
if likes > 0 or saves > 0:
print "UPDATE sharedfile SET like_count = %s, save_count = %s WHERE id = %s" % (
likes, saves, sf.id)
print db1.execute(
"UPDATE sharedfile SET like_count = %s, save_count = %s WHERE id = %s",
likes, saves, sf.id)
def get(self):
category = self.get_argument("category", "all")
cur_page = self.get_argument("page", "0")
query = self.get_argument("query", "")
num_per_page = 5
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
condition = "WHERE category='{0}'".format(category)
if category == "all":
condition = ""
if query:
condition = """WHERE UPPER(title) LIKE '%%{0}%%'
OR UPPER(profile) LIKE '%%{0}%%'
OR UPPER(author) LIKE '%%{0}%%'
OR UPPER(content) LIKE '%%{0}%%'
""".format(query.upper())
sql = "SELECT COUNT(*) FROM articles {0}".format(condition)
count = db.query(sql)[0]["COUNT(*)"]
max_page = int(math.ceil((count + 0.0) / num_per_page))
sql = """SELECT articles.id AS id, title, profile, author, url, picUrl,
articles.time AS time,
(SELECT COUNT(*) FROM article_reads
WHERE article_reads.article_id=articles.id) AS read_count,
(SELECT COUNT(*) FROM article_comment
WHERE article_comment.article_id=articles.id) AS comment_count
FROM articles
{0}
ORDER BY articles.time DESC
LIMIT {1}, {2};
""".format(condition, int(cur_page) * num_per_page, num_per_page)
articles = db.query(sql)
for art in articles:
# art["read_count"], art["comment_count"] = \
# get_article_statistics(db, art["id"])
art["time"] = art["time"].strftime("%Y-%m-%d")
art["picUrl"] = "/image-proxy/?url={0}".format(
urllib.quote(art["picUrl"])
)
kwargs = dict(articles=articles,
category=category,
cur_page=int(cur_page),
max_page=max_page)
self.render("", kwargs=kwargs)
def read_db(info):
"""Get info from Racktables DB"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if object_id already exists
object_id = ""
for item in db.query("select * from Object where name='{0}'".format(
info['hostname'])):
object_id = item.id
if not object_id:
print "Object:{0} does not exist".format(info['hostname'])
return False
# get the location info
rack_id_list = []
unit_no_list = []
for item in db.query(
"select rack_id,unit_no from RackSpace where object_id=(select id from Object where name='{0}')"
.format(info['hostname'])):
rack_id_list.append(int(item.rack_id))
unit_no_list.append(int(item.unit_no))
if not item:
print "Object:{0} does not have location info".format(info['hostname'])
return False
rack_id = ','.join(str(i) for i in list(set(rack_id_list)))
unit_no = ','.join(str(i) for i in list(set(unit_no_list)))
location_name = ""
row_name = ""
rack_name = ""
for item in db.query(
"select location_name,row_name,name from Rack where id='{0}'".
format(rack_id)):
location_name = item.location_name
row_name = item.row_name
rack_name = item.name
if not location_name or not row_name or not rack_name:
print "Object:{0} does not have location info".format(info['hostname'])
return False
print "RACKSPACE: {0}:{1}:{2}:{3}".format(location_name, row_name,
rack_name, unit_no)
# close db
db.close()
return {
'location_name': location_name,
'row_name': row_name,
'rack_name': rack_name,
'unit_no': unit_no
}
def list_object(info):
"""List the objects of the given rackspace"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if rackspace is correct
rs_info = opts['hostname'].split(':')
colo = "".join(rs_info[0:1])
row = "".join(rs_info[1:2])
rack = "".join(rs_info[2:3])
if not rack:
print "The rackspace is not correct"
return False
# get rack_id
for item in db.query(
"select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'"
.format(rack, colo, row)):
rack_id = item.id
if not rack_id:
print "Failed to get rack_id"
return False
# get object_id
object_id_list = []
for item in db.query(
"select * from RackSpace where rack_id={0}".format(rack_id)):
object_id_list.append(item.object_id)
if len(object_id_list) == 0:
print "Failed to get object_id"
return False
# get rid of the duplicated items then sort and read one by one
for object_id in sorted(list(set(object_id_list))):
for item in db.query(
"select * from Object where id={0}".format(object_id)):
object_name = item.name
object_type_id = item.objtype_id
for item in db.query(
"select * from Dictionary where dict_key={0}".format(
object_type_id)):
object_type_name = item.dict_value
print "{0}: {1}".format(object_type_name, object_name)
# close db
db.close()
return True
def delete_object(info):
"""Delete object from DB"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if object_id already exists, then create object if not
object_id = ""
for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
object_id = item.id
# delete object if already exists
if object_id:
url = """http://{0}/racktables/index.php?module=redirect&op=deleteObject&page=depot&tab=addmore&object_id={1}""".format(
rt_server, object_id
)
req = requests.get(url, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to delete the existing object: {0}".format(info["hostname"])
return False
else:
print "OK - Deleted the existing object: {0}".format(info["hostname"])
# close db
db.close()
return True
def get(self):
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
condition = """WHERE target_user_id='1' and consumed='0'
and article_comment.user_id=user.id
and article_comment.article_id=articles.id"""
sql = """SELECT user_id, name AS user_name, email, article_id,
article_comment.time AS time, title as article_title,
article_comment.content AS content
FROM article_comment,user,articles {0}
ORDER BY article_comment.time DESC;""".format(condition)
comments = db.query(sql)
for comment in comments:
comment["time"] = convert_to_time_zone(comment["time"],
"Asia/Shanghai")
comment["time"] = comment["time"].strftime("%Y-%m-%d %H:%M:%S")
kwargs = dict(comments=comments,
)
self.render("", kwargs=kwargs)
def do_query(database=None):
"""Perform generic query on database."""
# Pick up the database credentials
creds = get_db_creds(database)
# If we couldn't find corresponding credentials, throw a 404
if not creds:
msg = "Unable to find credentials matching {0}."
return {"ERROR": msg.format(database)}, 404
# Prepare the database connection
app.logger.debug("Connecting to %s database (%s)" % (
database, request.remote_addr))
db = Connection(**creds)
# See if we received a query
sql = request.form.get('sql')
if not sql:
sql = request.args.get('sql')
if not sql:
return {"ERROR": "SQL query missing from request."}, 400
# If the query has a percent sign, we need to excape it
if '%' in sql:
sql = sql.replace('%', '%%')
# Attempt to run the query
try:
app.logger.info("%s attempting to run \" %s \" against %s database" % (
request.remote_addr, sql, database))
results = db.query(sql)
app.logger.info(results)
except Exception, e:
return {"ERROR": ": ".join(str(i) for i in e.args)}, 422
def get_project_keys_from_mysql(self):
from torndb import Connection
db = Connection(
"%s:%s" % (self.config.MYSQL_HOST, self.config.MYSQL_PORT),
self.config.MYSQL_DB,
user=self.config.MYSQL_USER,
password=self.config.MYSQL_PASS
)
query = "select project_id, public_key, secret_key from sentry_projectkey"
logging.info("Executing query %s in MySQL", query)
project_keys = {}
try:
db_projects = db.query(query)
if db_projects is None:
return None
for project in db_projects:
logging.info("Updating information for project with id %s...", project.project_id)
self.add_project(project_keys, project.project_id, project.public_key, project.secret_key)
finally:
db.close()
return project_keys
def do_query(database=None):
# Pick up the database credentials
# app.logger.warning("%s requesting access to %s database" % (
# request.remote_addr, database))
creds = get_db_creds(database)
# If we couldn't find corresponding credentials, throw a 404
if not creds:
return {"ERROR": "Unable to find credentials matching %s." % database}
abort(404)
# Prepare the database connection
app.logger.debug("Connecting to %s database (%s)" % (
database, request.remote_addr))
db = Connection(**creds)
# See if we received a query
sql = request.form.get('sql')
if not sql:
sql = request.args.get('sql')
if not sql:
return {"ERROR": "SQL query missing from request."}
# If the query has a percent sign, we need to excape it
if '%' in sql:
sql = sql.replace('%', '%%')
# Attempt to run the query
try:
app.logger.info("%s attempting to run \" %s \" against %s database" % (
request.remote_addr, sql, database))
results = db.query(sql)
app.logger.info(results)
except Exception, e:
return {"ERROR": ": ".join(str(i) for i in e.args)}
def delete_object(info):
"""Delete object from DB"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if object_id already exists, then create object if not
object_id = ""
for item in db.query("select * from Object where name='{0}'".format(
info['hostname'])):
object_id = item.id
# delete object if already exists
if object_id:
url = """http://{0}/racktables/index.php?module=redirect&op=deleteObject&page=depot&tab=addmore&object_id={1}"""\
.format(rt_server,object_id)
req = requests.get(url, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to delete the existing object: {0}".format(
info['hostname'])
return False
else:
print "OK - Deleted the existing object: {0}".format(
info['hostname'])
# close db
db.close()
return True
def get(self):
# template_name = "article_list.html"
template_name = "mobile/article_list.html"
category = self.get_argument("category", "all")
cur_page = self.get_argument("page", "0")
query = self.get_argument("query", "")
num_per_page = 5
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
condition = "WHERE category='{0}'".format(category)
if category == "all":
condition = ""
if query:
condition = """WHERE UPPER(title) LIKE '%%{0}%%'
OR UPPER(profile) LIKE '%%{0}%%'
OR UPPER(author) LIKE '%%{0}%%'
OR UPPER(content) LIKE '%%{0}%%'
""".format(query.upper())
sql = "SELECT COUNT(*) FROM articles {0}".format(condition)
count = db.query(sql)[0]["COUNT(*)"]
max_page = int(math.ceil((count + 0.0) / num_per_page))
kwargs = dict(
category=category,
query=query,
cur_page=int(cur_page),
max_page=max_page)
self.render(template_name, **kwargs)
def update_rack(info, object_id):
"""Automate server audit for rack info into Racktables"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# update the rackspace
if opts['rackspace']:
rs_info = opts['rackspace'].split(':')
colo = "".join(rs_info[0:1])
row = "".join(rs_info[1:2])
rack = "".join(rs_info[2:3])
atom = "".join(rs_info[3:4])
if not atom:
print "The rackspace is not correct"
return False
# get rack_id
for item in db.query(
"select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'"
.format(rack, colo, row)):
rack_id = item.id
if not rack_id:
print "Failed to get rack_id"
return False
atom_list = atom.split(',')
atom_data = []
for i in atom_list:
if opts['rackposition']:
if opts['rackposition'] in ['left', 'front']:
atom_data.append("&atom_{0}_{1}_0=on".format(rack_id, i))
if opts['rackposition'] in ['right', 'back']:
atom_data.append("&atom_{0}_{1}_2=on".format(rack_id, i))
if opts['rackposition'] in ['interior']:
atom_data.append("&atom_{0}_{1}_1=on".format(rack_id, i))
else:
atom_data.append(
"&atom_{0}_{1}_0=on&atom_{0}_{1}_1=on&atom_{0}_{1}_2=on".
format(rack_id, i))
atom_url = "".join(atom_data)
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=rackspace&op=updateObjectAllocation""".format(
rt_server)
payload = """object_id={0}&rackmulti%5B%5D={1}&comment=&got_atoms=Save{2}"""\
.format(object_id,rack_id,atom_url)
req = requests.post(url,
data=payload,
headers=rt_headers,
auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update rackspace"
return False
print "OK - Updated rackspace"
# close db
db.close()
# end
return True
def get(self, article_id):
# template_name = "article_details.html"
template_name = "mobile/article_details.html"
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
sql = "SELECT * FROM articles WHERE id='{0}'".format(
article_id)
article = db.query(sql)[0]
# article["read_count"], article["comment_count"] = \
# get_article_statistics(db, article_id)
article["url"] = urllib.quote(article["url"])
# Update article read count
now = datetime.datetime.now()
sql = """INSERT INTO article_reads (`article_id`, `user_id`, `time`)
VALUES ('{0}', '{1}', '{2}')
""".format(article_id, 0, now)
db.execute(sql)
kwargs = dict(article=article,
day=article["day"])
super(ArticleDetailsHandler, self).render(
template_name,
**kwargs
)
def update_rack(info, object_id):
"""Automate server audit for rack info into Racktables"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# update the rackspace
if opts["rackspace"]:
rs_info = opts["rackspace"].split(":")
colo = "".join(rs_info[0:1])
row = "".join(rs_info[1:2])
rack = "".join(rs_info[2:3])
atom = "".join(rs_info[3:4])
if not atom:
print "The rackspace is not correct"
return False
# get rack_id
for item in db.query(
"select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'".format(
rack, colo, row
)
):
rack_id = item.id
if not rack_id:
print "Failed to get rack_id"
return False
atom_list = atom.split(",")
atom_data = []
for i in atom_list:
if opts["rackposition"]:
if opts["rackposition"] in ["left", "front"]:
atom_data.append("&atom_{0}_{1}_0=on".format(rack_id, i))
if opts["rackposition"] in ["right", "back"]:
atom_data.append("&atom_{0}_{1}_2=on".format(rack_id, i))
if opts["rackposition"] in ["interior"]:
atom_data.append("&atom_{0}_{1}_1=on".format(rack_id, i))
else:
atom_data.append("&atom_{0}_{1}_0=on&atom_{0}_{1}_1=on&atom_{0}_{1}_2=on".format(rack_id, i))
atom_url = "".join(atom_data)
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=rackspace&op=updateObjectAllocation""".format(
rt_server
)
payload = """object_id={0}&rackmulti%5B%5D={1}&comment=&got_atoms=Save{2}""".format(
object_id, rack_id, atom_url
)
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update rackspace"
return False
print "OK - Updated rackspace"
# close db
db.close()
# end
return True
def read_db(info):
"""Get info from Racktables DB"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if object_id already exists
object_id = ""
for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
object_id = item.id
if not object_id:
print "Object:{0} does not exist".format(info["hostname"])
return False
# get the location info
rack_id_list = []
unit_no_list = []
for item in db.query(
"select rack_id,unit_no from RackSpace where object_id=(select id from Object where name='{0}')".format(
info["hostname"]
)
):
rack_id_list.append(int(item.rack_id))
unit_no_list.append(int(item.unit_no))
if not item:
print "Object:{0} does not have location info".format(info["hostname"])
return False
rack_id = ",".join(str(i) for i in list(set(rack_id_list)))
unit_no = ",".join(str(i) for i in list(set(unit_no_list)))
location_name = ""
row_name = ""
rack_name = ""
for item in db.query("select location_name,row_name,name from Rack where id='{0}'".format(rack_id)):
location_name = item.location_name
row_name = item.row_name
rack_name = item.name
if not location_name or not row_name or not rack_name:
print "Object:{0} does not have location info".format(info["hostname"])
return False
print "RACKSPACE: {0}:{1}:{2}:{3}".format(location_name, row_name, rack_name, unit_no)
# close db
db.close()
return {"location_name": location_name, "row_name": row_name, "rack_name": rack_name, "unit_no": unit_no}
def add_posts(shake_id=0,
sharedfile_id=0,
sourcefile_id=0,
deleted=0,
created_at=None,
**kwargs):
"""
This task will get the subscribers for a shake and insert
a post for every user. If the sourcefile_id exists in posts
it will set "seen" equal to 1.
"""
db = Connection(options.database_host, options.database_name,
options.database_user, options.database_password)
#get subscribers for shake_id
results = db.query(
"""SELECT user_id as id FROM subscription WHERE shake_id = %s and deleted = 0""",
shake_id)
for user in results:
seen = 0
#does this sourcefile exist in post for this user?
existing = db.query(
"""SELECT id FROM post WHERE user_id = %s and sourcefile_id = %s and deleted=0 ORDER BY created_at desc LIMIT 1""",
user['id'], sourcefile_id)
#if so, set seen = 1
if existing:
seen = 1
#if not, insert a new row
if created_at:
db.execute(
"""INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s)""",
user['id'], sourcefile_id, sharedfile_id, seen, deleted,
shake_id, created_at)
else:
db.execute(
"""INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, NOW())""",
user['id'], sourcefile_id, sharedfile_id, seen, deleted,
shake_id)
#now insert a post for the user who originally posted it.
sharedfile = db.get("""SELECT user_id from sharedfile WHERE id = %s""",
sharedfile_id)
db.execute(
"""INSERT INTO post (user_id, sourcefile_id, sharedfile_id, seen, deleted, shake_id, created_at) VALUES (%s, %s, %s, %s, %s, %s, NOW())""",
sharedfile['user_id'], sourcefile_id, sharedfile_id, 1, 0, shake_id)
db.close()
def list_object(info):
"""List the objects of the given rackspace"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# check if rackspace is correct
rs_info = opts["hostname"].split(":")
colo = "".join(rs_info[0:1])
row = "".join(rs_info[1:2])
rack = "".join(rs_info[2:3])
if not rack:
print "The rackspace is not correct"
return False
# get rack_id
for item in db.query(
"select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'".format(rack, colo, row)
):
rack_id = item.id
if not rack_id:
print "Failed to get rack_id"
return False
# get object_id
object_id_list = []
for item in db.query("select * from RackSpace where rack_id={0}".format(rack_id)):
object_id_list.append(item.object_id)
if len(object_id_list) == 0:
print "Failed to get object_id"
return False
# get rid of the duplicated items then sort and read one by one
for object_id in sorted(list(set(object_id_list))):
for item in db.query("select * from Object where id={0}".format(object_id)):
object_name = item.name
object_type_id = item.objtype_id
for item in db.query("select * from Dictionary where dict_key={0}".format(object_type_id)):
object_type_name = item.dict_value
print "{0}: {1}".format(object_type_name, object_name)
# close db
db.close()
return True
def main():
db1 = Connection(options.database_host, options.database_user, options.database_password)
db1.execute("DELETE FROM post WHERE 1")
ssfs = db1.query("""SELECT shake_id, sharedfile_id from shakesharedfile order by created_at""")
for shakesharedfile in ssfs:
sf = db1.get("""SELECT id, source_id, name, deleted, created_at FROM sharedfile WHERE id = %s""", shakesharedfile['sharedfile_id'])
print "%s. Adding posts for sharedfile: %s created at %s." % (sf.id, sf.name, sf.created_at)
add_posts(shake_id=shakesharedfile['shake_id'], sharedfile_id=sf['id'], sourcefile_id=sf['source_id'], deleted=sf['deleted'], created_at=sf['created_at'])
def get_query_result(query, **kw_dict):
"""get query result
"""
conn = Connection(**MYSQL_SETTINGS)
if kw_dict is None:
try:
result = conn.query(query)
conn.close()
return result
except Exception, e:
print e
def update_blank_switch_security_pdu_offline(info):
"""Automate server autodir for PatchPanel/NetworkSwitch/NetworkSecurity/PDU into Racktables or as offline mode"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# delete object if already exists
delete_object(info)
# create object
url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(rt_server)
if opts["blank"]:
payload = """0_object_type_id=9&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
info["hostname"]
)
if opts["switch"]:
payload = """0_object_type_id=8&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
info["hostname"]
)
if opts["security"]:
payload = """0_object_type_id=798&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
info["hostname"]
)
if opts["pdu"]:
payload = """0_object_type_id=2&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
info["hostname"]
)
if opts["offline"]:
payload = """0_object_type_id=4&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
info["hostname"]
)
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to create object: {0}".format(info["hostname"])
return False
else:
print "OK - Created object: {0}".format(info["hostname"])
# get object_id
for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
object_id = item.id
if not object_id:
print "Failed to get object_id"
return False
# update rack info
update_rack(info, object_id)
# close db
db.close()
# end
return True
def main():
db1 = Connection(options.database_host, options.database_user,
options.database_password)
#grab all group shakes
shakes = db1.query("""SELECT id, user_id FROM shake WHERE type=%s""",
"group")
for shake in shakes:
db1.execute(
"""INSERT IGNORE INTO subscription (user_id, shake_id, deleted, created_at, updated_at)
VALUES (%s, %s, 0, NOW(), NOW())""", shake['user_id'],
shake['id'])
def update_blank_switch_security_pdu_offline(info):
"""Automate server autodir for PatchPanel/NetworkSwitch/NetworkSecurity/PDU into Racktables or as offline mode"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# delete object if already exists
delete_object(info)
# create object
url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(
rt_server)
if opts['blank']:
payload = """0_object_type_id=9&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\
.format(info['hostname'])
if opts['switch']:
payload = """0_object_type_id=8&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\
.format(info['hostname'])
if opts['security']:
payload = """0_object_type_id=798&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\
.format(info['hostname'])
if opts['pdu']:
payload = """0_object_type_id=2&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\
.format(info['hostname'])
if opts['offline']:
payload = """0_object_type_id=4&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21"""\
.format(info['hostname'])
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to create object: {0}".format(info['hostname'])
return False
else:
print "OK - Created object: {0}".format(info['hostname'])
# get object_id
for item in db.query("select * from Object where name='{0}'".format(
info['hostname'])):
object_id = item.id
if not object_id:
print "Failed to get object_id"
return False
# update rack info
update_rack(info, object_id)
# close db
db.close()
# end
return True
def get_job_status(v_job_id):
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_get_sql = '''SELECT cur_cum_prog_desc,cur_prog_com_rate,cur_prog_shell_cmd from dba_job_progress where id=(select max(id) from dba_job_progress where job_id=%d)''' % (
v_job_id)
job_list = db.query(v_get_sql)
db.close()
return job_list
def get_domain_list_from_env(v_role_id,belong_env):
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
str_sql = '''select b.id,b.name from resources_role_app a,resources_app b where
a.app_id = b.id and a.role_id = %d and b.app_type= %d ''' % (v_role_id,belong_env)
app_list = db.query(str_sql)
db.close()
return app_list
def get_latest_article(self, from_user, to_user, timestamp):
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
sql = "SELECT * FROM articles ORDER BY time DESC LIMIT 1"
article = db.query(sql)[0]
return self.make_single_pic_response(
from_user,
to_user,
timestamp,
article["title"],
article["profile"],
article["picUrl"],
"{0}/m#/article_details/{1}".format(
settings.SITE_HTTP_URL,
article["id"]
)
)
def search_for_articles(self, from_user, to_user, timestamp, query):
db = Connection(settings.DATABASE_SERVER,
settings.DATABASE_NAME,
settings.DATABASE_USER,
settings.DATABASE_PASSWORD,
)
# Simple way to avoid SQL insertion attack
if query.strip() and ";" not in query:
condition = u"""WHERE UPPER(title) LIKE '%%{0}%%'
OR UPPER(profile) LIKE '%%{0}%%'
OR UPPER(author) LIKE '%%{0}%%'
OR UPPER(content) LIKE '%%{0}%%'
""".format(query.strip().upper())
else:
condition = ""
sql = u"SELECT * FROM articles {0} ORDER BY time DESC LIMIT 10".format(
condition)
articles = db.query(sql)
if len(articles) > 0:
return self.make_multi_pic_response(
from_user,
to_user,
timestamp,
[article["title"] for article in articles],
[article["profile"] for article in articles],
[article["picUrl"] for article in articles],
["{0}/m#/article_details/{1}".format(
settings.SITE_HTTP_URL,
article["id"]
) for article in articles])
else:
return self.make_text_response(
from_user,
to_user,
timestamp,
u"还没有关于“%s”的内容哦！你有什么想法呢？告诉微君吧！" % query)
def update(self):
config = self.application.config
db = Connection(
"%s:%s" % (config.MYSQL_HOST, config.MYSQL_PORT),
config.MYSQL_DB,
user=config.MYSQL_USER,
password=config.MYSQL_PASS
)
query = "select project_id, public_key, secret_key from sentry_projectkey"
logging.info("Executing query %s in MySQL" % query)
projects = {}
try:
db_projects = db.query(query)
if db_projects is None:
logging.warn("Could not retrieve nformation from sentry's database because MySQL Server was unavailable")
return
for project in db_projects:
logging.info("Updating information for project with id %s..." % project.project_id)
if not project.project_id in projects.keys():
projects[project.project_id] = {
"public_key": [],
"secret_key": []
}
projects[project.project_id]['public_key'].append(project.public_key)
projects[project.project_id]['secret_key'].append(project.secret_key)
self.application.project_keys = projects
finally:
db.close()
def upload_user_stats():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
db.execute('truncate table meta_table_statistics') # 先truncate 再插入
db.execute('truncate table meta_index_statistics') # 先truncate 再插入
# 由于id为64的E4S 服务器不在平台管辖，先手工剔除 b.id !=64
v_sql = r"""SELECT b.ip,b.port,b.id as instance_id,a.id as schema_id,a.name as db_name from
resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id"""
#print v_sql
upload_tables_list = db.query(v_sql)
if upload_tables_list: # 对实例表进行循环
i=0
for upload_table in upload_tables_list:
instance_id = upload_table['instance_id']
schema_id = upload_table['schema_id']
db_name = upload_table['db_name']
host_ip = upload_table['ip']
mysql_port = upload_table['port']
v_host =host_ip + ':' + str(mysql_port)
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
# 取表的元信息然后插入
v_pl = r"""SELECT table_schema,table_name,rows_read,rows_changed,rows_changed_x_indexes
from table_statistics where table_schema='%s' """ % (db_name)
#print v_pl
table_list = db_remote.query(v_pl)
for table_row in table_list:
table_schema = table_row['table_schema']
table_name = table_row['table_name']
rows_read = table_row['rows_read']
rows_changed = table_row['rows_changed']
rows_changed_x_indexes = table_row['rows_changed_x_indexes']
#try:
v_insert_sql='''insert into meta_table_statistics(instance_id,schema_id,table_schema,
table_name,rows_read,rows_changed,rows_changed_x_indexes)
values(%d,%d,'%s','%s',%d,%d,%d)''' % (
instance_id,schema_id,table_schema,
table_name,rows_read,rows_changed,rows_changed_x_indexes
)
#print v_insert_sql
db.execute(v_insert_sql.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
# 取索引的元信息，然后插入
v_pl2 = r"""SELECT table_schema,table_name,index_name,rows_read from index_statistics
where TABLE_SCHEMA='%s' """ % (db_name)
table_list2 = db_remote.query(v_pl2)
for table_row2 in table_list2:
table_schema = table_row2['table_schema']
table_name = table_row2['table_name']
index_name = table_row2['index_name']
rows_read = table_row2['rows_read']
#try:
v_insert_sql2='''insert into meta_index_statistics(instance_id,schema_id,table_schema,
table_name,index_name,rows_read)
values(%d,%d,'%s','%s','%s',%d)''' % (
instance_id,schema_id,table_schema,
table_name,index_name,rows_read
)
#print v_insert_sql
db.execute(v_insert_sql2.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
db_remote.close()
# 开始生成log_hot_tables 统计表数据
# statistic_time 为统计数据的实际生成时间
v_insert_sql3='''insert into log_hot_tables(instance_id,schema_id,table_schema,
table_name,TABLE_COMMENT,TABLE_CREATE_TIME,rows_read,rows_changed,
rows_changed_x_indexes) select a.instance_id,a.schema_id,a.TABLE_SCHEMA,
a.TABLE_NAME,b.TABLE_COMMENT,b.CREATE_TIME,a.ROWS_READ,a.ROWS_CHANGED,
a.ROWS_CHANGED_X_INDEXES from meta_table_statistics a,meta_tables b where
a.instance_id=%d and a.schema_id=%d and
a.TABLE_SCHEMA='%s' and a.instance_id=b.instance_id and a.TABLE_SCHEMA=b.TABLE_SCHEMA
and a.TABLE_NAME=b.TABLE_NAME''' % (
instance_id,schema_id,table_schema
)
print v_insert_sql3
db.execute(v_insert_sql3.replace('%','%%'))
i=i+1
db.close()
# 开始远程flush 统计信息表
# for remote execute
os_user = '******'
OS_APPS_PASSWD = config.OS_APPS_PASSWD
DB_USER = config.DB_USER
DB_PASSWD = config.DB_PASSWD
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
# 由于id为64的E4S 服务器不在平台管辖，先手工剔除 b.id !=64
v_sql = r"""SELECT distinct b.ip,b.port from
resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id"""
#print v_sql
server_list = db.query(v_sql)
if server_list: # 对实例表进行循环
i=0
for single_server in server_list:
host_ip = single_server['ip']
mysql_port = single_server['port']
# 远程paramiko调用 在本机执行sql
exe_sql = 'flush table_statistics;flush index_statistics;'
result = func.remote_exe_sql(host_ip,os_user,OS_APPS_PASSWD,mysql_port,'information_schema',exe_sql,DB_USER,DB_PASSWD)
if result == '':
result = '执行成功!'
print result
class MySQLHelper:
def __init__(self, host, user, password, database):
self._db = Connection(host, database, user=user, password=password, max_idle_time=10)
if not self._db._db:
raise Exception('%s' % self._db.error_msg)
def query(self, sql, *parameters):
return self._db.query(sql, *parameters)
def query_one(self, sql, *parameters):
res = self._db.query(sql, *parameters)
return res.pop() if res else {}
def write(self, sql, *parameters):
return self._db.execute(sql, *parameters)
def gen_insert(self, tablename, rowdict, replace=False):
return self._db.gen_insert_sql(tablename, rowdict, replace)
def insert_dict(self, tablename, rowdict):
key_strs = ", ".join(["""`%s`""" % key for key in rowdict.keys()])
value_strs = ", ".join(["""'%s'""" % rowdict.get(key) for key in rowdict.keys()])
sql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, key_strs, value_strs)
return self._db.execute(sql)
def insert_batch(self, tablename, batch_params):
value_batch = []
for param in batch_params:
keys = param.keys()
key_strs = ", ".join(["""`%s`""" % key for key in keys])
value_strs = "(%s)" % ", ".join(
["""'%s'""" % "%s" % param.get(key) for key in keys])
value_batch.append(value_strs)
sql = """INSERT INTO %s (%s) VALUES %s""" % (tablename, key_strs, ",".join(value_batch))
return self._db.execute(sql)
def update_dict(self, tablename, rowdict, where):
sql = """UPDATE %s SET %s WHERE %s""" % (
tablename, self._formatter(rowdict, ', '), self._formatter(where, " AND "))
return self._db.execute(sql)
def transaction(self, query, parameters):
return self._db.transaction(query, parameters)
def get(self, tablename, conds, cols='', extra_conds={}):
if not tablename:
return False
cols = "%s" % ','.join(cols) if cols else '*'
wheres = []
values = []
if conds and isinstance(conds, dict):
for key, value in conds.items():
if isinstance(value, (list, tuple)):
wheres.append("`%s` IN (%s)" % (key, "'%s'" % "','".join([str(v) for v in value])))
else:
wheres.append("`%s`=%%s" % key)
values.append("%s" % value)
where_str = ' AND '.join(wheres)
sql = """ SELECT %s FROM `%s` """ % (cols, tablename)
if where_str:
sql += """ WHERE %s """ % (where_str)
if extra_conds.get('group_by'):
sql += """ GROUP by %s """ % ','.join(extra_conds['group_by'])
if extra_conds.get('order_by'):
sql += """ ORDER by %s """ % ','.join(extra_conds['order_by'])
if extra_conds.get('limit'):
sql += """ LIMIT %s """ % ','.join(map(str, extra_conds['limit']))
return self._db.query(sql, *values)
def _serialize(self, value):
if isinstance(value, (dict, list, set)):
value = json.dumps(value)
else:
value = "%s" % value
return value
def _formatter(self, pairs, delimiter):
values = []
for key, value in pairs.items():
if not isinstance(value, list):
value = self._serialize(value)
values.append("""`%s`='%s'""" % (key, value))
else:
values.append("""`%s` in ("%s")""" % (key, '","'.join([self._serialize(val) for val in value])))
return delimiter.join(values)
def __del__(self):
self._db.close()
def get(self):
db = Connection('127.0.0.1', 'zfz', 'zfz', 'zfz...891', 25200)
q = self.get_argument(name="query", default="")
p = self.get_argument(name="price", default="999999999")
ma = re.search(r'\d+', p)
if ma is None:
p = "999999999"
else:
p = ma.group(0)
q = q.lstrip().rstrip().replace("'", "").replace('"', '').replace(
'#', '').replace('%', '')
qs = q.split(' ')
if len(q) > 0:
if len(qs) == 1:
m = '%' + q + '%'
items = db.query(
"select title, url, price, area, arch, address, district "
"from pages where price <= %s and (address like %s or district like %s or title like %s) "
"order by date desc limit 20", p, m, m, m)
else:
l = qs[0]
r = qs[-1]
m1 = ''
m2 = ''
for i in range(1, len(qs) - 1):
m1 += '%' + qs[i]
m2 += '%' + qs[len(qs) - 1 - i]
m1 += '%'
m2 += '%'
items = db.query(
"select title, url, price, area, arch, address, district "
"from pages where price <= %s and ((address like %s and district like %s) or "
"(address like %s and district like %s) or "
"(title like %s and address like %s) or "
"(title like %s and address like %s and district like %s) or "
# "title like %s or "
"address like %s) "
"order by date desc limit 20",
p,
'%' + l + m1,
'%' + r + '%',
'%' + r + m2,
'%' + l + '%',
'%' + l + '%',
m1 + r + '%',
'%' + l + '%',
m1,
'%' + r + '%',
# '%' + l + m1 + r + '%',
'%' + l + m1 + r + '%')
else:
items = []
if len(items) < 1:
hit = False
else:
hit = True
if p == "999999999":
p = ""
self.render("search.html", query=q, price=p, items=items, hit=hit)
# to learn how to use it.
# Installation: pip install torndb
# Official doc: http://torndb.readthedocs.org/en/latest/
# Source: https://github.com/bdarnell/torndb/blob/master/torndb.py
from torndb import Connection
# Connect by IP address
db = Connection('127.0.0.1', 'database_name', user='******', password='******')
# Connect by IP address with port
db = Connection('127.0.0.1:1234', 'database_name', user='******', password='******')
# Connect by socket
db = Connection('/tmp/mysql.sock', 'database_name', user='******', password='******')
# Connection over SSH, open a SSH tunnel with
# ssh -L 1234:localhost:3306 [email protected]
# then connect to 127.0.0.1:1234
# Retreive one object
post = db.get("SELECT * FROM posts LIMIT 1")
# Retreive several objects
for post in db.query("SELECT * FROM posts"):
print post.title
# Insert one entry
db.execute("INSERT INTO posts (user_id, content) VALUES (%s, %s)", 12, "hello world !")
# Insert multiple entries
values = [(12, "hello"), (17, "world"), (22, "blah")]
db.executemany("INSERT INTO posts (user_id, content) VALUES (%s, %s)", values)
class TelegramBot:
telegram_url = "https://api.telegram.org/bot{token}/{method}"
vk_url = "https://api.vk.com/method/{method}?{params}&access_token={access_token}&v=5.67"
chat_id = CHAT_ID
@staticmethod
def split_message_by_chunks(iterable, step=4096):
for chunk in range(0, len(iterable), step):
yield iterable[chunk:chunk + step]
def __init__(self, token, db_creds, vk_token):
self.access_token = token
self.db = Connection(**db_creds)
self.vk_token = vk_token
def send_telegram_message(self, text):
if len(text) > 4096:
chunk_try = []
log.info('Sending big message by chunks..')
for chunk in TelegramBot.split_message_by_chunks(text):
response = requests.post(self.telegram_url.format(token=self.access_token, method='sendmessage'), json={
'text': chunk,
'chat_id': self.chat_id
})
chunk_try.append(True if response.status_code == 200 else False)
if all(chunk_try):
log.info('Good response when sending big message from Telegram..successfully sent message')
else:
log.error('Something went wrong when sending big message..')
else:
log.info('Sending simple message..')
response = requests.post(self.telegram_url.format(token=self.access_token, method='sendmessage'), json={
'text': text,
'chat_id': self.chat_id
})
if response.status_code == 200:
log.info('Good response from Telegram..successfully sent message')
def send_request(self, url):
log.info('Checking VK data ..')
response = requests.get(url=url)
res = response.json()
if response.status_code != 200 or 'error' in res:
log.error('Something went wrong with response from VK...%s' % (res['error'],))
return
res = {x['id']: x['text'] for x in filter(lambda x: '#знакомства_парни' not in x['text'] and # no gays obv
('#ищупарня' in x['text'] or
'#знакомства' in x['text'] or
'#ищутебя' in x['text']),
[x for x in res['response']['items']])}
ids = set(res.keys())
last = self.db.query("SELECT * FROM messages LIMIT 10")
new = ids - set(x['id'] for x in last) if last else ids
if new:
log.info('Got %s new updates from Odessa Search, querying DB...' % (len(new),))
self.db.insertmany('INSERT INTO messages set id = %s, message = %s',
[(key, str(res[key].encode('utf-8'), 'utf8')) for key in new])
for i, msg in enumerate(new):
log.info('Sending messages to Telegram... %s of %s' % (i, len(new)))
self.send_telegram_message(res[msg])
else:
log.warning('No updates from VK...Retrying again in 10 mins...')
def get_update(self):
self.send_request(self.vk_url.format(method='wall.get',
params='&'.join(
[k+'='+v for k, v in
{'domain': VK_GROUP_DOMAIN,
'count': '10'}.items()]),
access_token=self.vk_token))
def add_schedule_backup_job():
# if __name__ == '__main__':
os_user = config.OS_USER
os_password = config.OS_APPS_PASSWD
scheduler = BackgroundScheduler() # 默认内存的jobstore
url = "sqlite:////home/apps/dbajob.sqlite"
scheduler.add_jobstore("sqlalchemy", url=url, alias="sqlite_js")
scheduler.print_jobs()
print "a"
scheduler.remove_all_jobs(jobstore="sqlite_js")
scheduler.print_jobs()
print "remove"
# v_current_jobs = scheduler.get_jobs()
# print v_current_jobs
# if v_current_jobs: # 如果job存在的话,先请客
# scheduler.remove_job('backup')
# 连接配置中心库，获取数据库备份周期等信息
db = Connection("/tmp/mysql3306.sock", config.DB_NAME, config.DB_USER, config.DB_PASSWD, time_zone="+8:00")
v_sql = r"""SELECT a.instance_id,b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where
a.instance_id=b.id """
print v_sql
bak_server_list = db.query(v_sql)
if bak_server_list: # 有server需要配置
i = 0
# 把还没有开始的调度任务，置为手工结束 backup_result_type=4
v_manual_end_sql = "update mysql_ins_bak_log set backup_result_type=4 where backup_result_type=0"
db.execute(v_manual_end_sql)
for bak_server in bak_server_list:
instance_id = bak_server["instance_id"]
from_host = bak_server["ip"]
# print from_host
mysql_port = bak_server["port"]
backup_interval_type = bak_server["backup_interval_type"]
backup_start_time = bak_server["backup_start_time"]
str_start_date = time.strftime("%Y-%m-%d") + " " + backup_start_time
print str_start_date
v_job_id = "backup_%s_%s" % (from_host, str(mysql_port))
if backup_interval_type == 1: # every day
# scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], jobstore='file')
scheduler.add_job(
backup,
"interval",
id=v_job_id,
days=1,
start_date=str_start_date,
args=[from_host, mysql_port, os_user, os_password],
replace_existing=True,
jobstore="sqlite_js",
)
elif backup_interval_type == 2: # every week weeks=1
scheduler.add_job(
backup,
"interval",
id=v_job_id,
weeks=1,
start_date=str_start_date,
args=[from_host, mysql_port, os_user, os_password],
replace_existing=True,
jobstore="sqlite_js",
)
elif backup_interval_type == 3: # every hour hours=1
scheduler.add_job(
backup,
"interval",
id=v_job_id,
hours=1,
start_date=str_start_date,
args=[from_host, mysql_port, os_user, os_password],
replace_existing=True,
jobstore="sqlite_js",
)
else:
pass
# 开始在数据库记录备份的调度任务状态 0:调度任务已启动，实际备份还没有开始
v_sche_start_sql = """insert into mysql_ins_bak_log(instance_id,backup_result_type)
values(%d,0)""" % (
instance_id
)
db.execute(v_sche_start_sql)
i = i + 1
scheduler.print_jobs()
print "b"
scheduler.start()
scheduler.print_jobs()
print "c"
db.close()
def update_db(info):
"""Automate server audit into Racktables"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# get object_type_id
for item in db.query(
"select * from Dictionary where dict_value='{0}'".format(
info['server_type'])):
object_type_id = item.dict_key
# delete object if already exists
delete_object(info)
# create object
url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(
rt_server)
if info['server_type'] in ["Server", "XenServer"]:
payload = """0_object_type_id={0}&0_object_name={1}&0_object_label=&0_object_asset_no={1}&got_fast_data=Go%21"""\
.format(object_type_id,info['hostname'])
if info['server_type'] in ["VM", "EC2"]:
payload = """virtual_objects=&0_object_type_id={0}&0_object_name={1}&got_fast_data=Go%21"""\
.format(object_type_id,info['hostname'])
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to create object: {0}".format(info['hostname'])
return False
else:
print "OK - Created object: {0}".format(info['hostname'])
object_id = ""
# get object_id
for item in db.query("select * from Object where name='{0}'".format(
info['hostname'])):
object_id = item.id
if not object_id:
print "Failed to get object_id"
return False
# get os_release_id
os_release_key = ""
for item in db.query(
"select * from Dictionary where dict_value='{0}'".format(
info['os_release'])):
os_release_key = item.dict_key
if not os_release_key:
print "Failed to get object_type_id, please add '{0}' to 'Configuration - Dictionary - Server OS type'.".format(
info['os_release'])
return False
# update the informations of object, all post data formats were got by firebug on firefox
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=edit&op=update""".format(
rt_server)
if info['server_type'] == "Server":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=2&4_value=0&5_attr_id=26&5_value=0&6_attr_id=10006&6_value={6}\
&7_attr_id=10003&7_value={7}&8_attr_id=1&8_value=&9_attr_id=28&9_value=&10_attr_id=21&10_value=&11_attr_id=4&11_value={8}\
&12_attr_id=24&12_value=&13_attr_id=10005&13_value={9}&14_attr_id=25&14_value=&num_attrs=15&object_comment=&submit.x=15&submit.y=13"""\
.format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'],
info['memory'],quote_plus(info['network']),os_release_key,info['swap'])
if info['server_type'] == "XenServer":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=1&7_value=&8_attr_id=28&8_value=&9_attr_id=4&9_value={8}&10_attr_id=24&10_value=&11_attr_id=10005&11_value={9}\
&12_attr_id=25&12_value=&13_attr_id=10008&13_value={10}&num_attrs=14&object_comment=&submit.x=18&submit.y=21"""\
.format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'],
info['memory'],quote_plus(info['network']),os_release_key,info['swap'],quote_plus(info['vm_list']))
if info['server_type'] == "EC2":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=10010&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=19&submit.y=27"""\
.format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'],
info['memory'],quote_plus(info['network']),info['ec2_pubname'],os_release_key,info['swap'])
if info['server_type'] == "VM":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=10007&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=25&submit.y=14'"""\
.format(object_id,object_type_id,info['hostname'],info['cpu_cores'],quote_plus(info['disk']),info['fqdn'],
info['memory'],quote_plus(info['network']),info['resident_on'],os_release_key,info['swap'])
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update attributes"
return False
print "OK - Updated attributes"
# ec2 servers don't need to update the ip pool
if info['server_type'] not in ["EC2"]:
# update the ip pool
nics = ("".join(info['network'].split())).split(',')
for i in nics:
nic_info = i.split(':')
nic_name = "".join(nic_info[0:1])
nic_addr = "".join(nic_info[1:2])
# check if nic_name is not correct
if nic_name.isalnum():
# create nic
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=ip&op=add""".format(
rt_server)
payload = """object_id={0}&bond_name={1}&ip={2}&bond_type=regular&submit.x=11&submit.y=6"""\
.format(object_id,nic_name,nic_addr)
req = requests.post(url,
data=payload,
headers=rt_headers,
auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update ip pool for {0}:{1}".format(
nic_name, nic_addr)
return False
print "OK - Updated ip pool for {0}:{1}".format(
nic_name, nic_addr)
# virtual servers don't need to update the rackspace
if info['server_type'] not in ["EC2", "VM"]:
# update rack info
update_rack(info, object_id)
# close db
db.close()
# end
return True
def upload_meta_tables():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
db.execute('truncate table meta_tables') # 先truncate 再插入
db.execute('truncate table meta_statistics') # 先truncate 再插入
db.execute('truncate table meta_redundant_keys') # 先truncate 再插入
# 由于id为64的E4S 服务器不在平台管辖，先手工剔除 b.id !=64
v_sql = r"""SELECT b.ip,b.port,b.id as instance_id,a.id as schema_id,a.name as db_name from
resources_schema a,tag b where b.online_flag=1 and a.owner=b.id and b.id !=64 order by a.id,b.id"""
#print v_sql
upload_tables_list = db.query(v_sql)
if upload_tables_list: # 对实例表进行循环
i=0
for upload_table in upload_tables_list:
instance_id = upload_table['instance_id']
schema_id = upload_table['schema_id']
db_name = upload_table['db_name']
host_ip = upload_table['ip']
mysql_port = upload_table['port']
v_host =host_ip + ':' + str(mysql_port)
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
# 取表的元信息然后插入
v_pl = r"""SELECT table_catalog,table_schema,table_name,table_type,engine,version,row_format,
table_rows,avg_row_length,data_length,max_data_length,index_length,data_free,auto_increment,create_time,
update_time,check_time,table_collation,checksum,
create_options,table_comment from tables where table_type='BASE TABLE' and TABLE_SCHEMA='%s' """ % (db_name)
#print v_pl
table_list = db_remote.query(v_pl)
for table_row in table_list:
table_catalog = table_row['table_catalog']
table_schema = table_row['table_schema']
table_name = table_row['table_name']
table_type = table_row['table_type']
engine = table_row['engine']
version = table_row['version']
row_format = table_row['row_format']
table_rows = table_row['table_rows']
avg_row_length = table_row['avg_row_length']
data_length = table_row['data_length']
max_data_length = table_row['max_data_length']
index_length = table_row['index_length']
data_free = table_row['data_free']
auto_increment = table_row['auto_increment']
# 本来Int类型，但为了处理Null值插入，以字符串类型%s 进行插入
if auto_increment is None:
auto_increment = 'NULL'
else:
auto_increment = "'"+str(auto_increment)+"'"
# 日期型插入，由于要处理null值插入，经过处理后，以%s 进行插入
create_time = table_row['create_time']
if create_time is None:
create_time = 'NULL'
else:
create_time = "'"+str(create_time)+"'"
# 日期型插入，由于要处理null值插入，经过处理后，以%s 进行插入
update_time = table_row['update_time']
if update_time is None:
update_time = 'NULL'
else:
update_time = "'"+str(update_time)+"'"
check_time = table_row['check_time']
if check_time is None:
check_time = 'NULL'
else:
check_time = "'"+str(check_time)+"'"
table_collation = table_row['table_collation']
# 本来Int类型，但为了处理Null值插入，以字符串类型%s 进行插入
checksum = table_row['checksum']
if checksum is None:
checksum = 'NULL'
else:
checksum = "'"+str(checksum)+"'"
create_options = table_row['create_options']
table_comment = table_row['table_comment']
#try:
v_insert_sql='''insert into meta_tables(instance_id,schema_id,table_catalog,table_schema,table_name,
table_type,engine,version,row_format,table_rows,avg_row_length,data_length,max_data_length,
index_length,data_free,auto_increment,create_time,update_time,check_time,table_collation,
checksum,create_options,table_comment)
values(%d,%d,'%s','%s','%s',
'%s','%s',%d,'%s',%d,%d,%d,%d,
%d,%d,%s,%s,%s,%s,'%s',
%s,'%s','%s')''' % (
instance_id,schema_id,table_catalog,table_schema,table_name,
table_type,engine,version,row_format,table_rows,avg_row_length,data_length,max_data_length,
index_length,data_free,auto_increment,create_time,update_time,check_time,table_collation,
checksum,create_options,table_comment
)
#print v_insert_sql
db.execute(v_insert_sql.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
# 取索引的元信息，然后插入
v_pl2 = r"""SELECT table_catalog,table_schema,table_name,non_unique,index_schema,
index_name,seq_in_index,column_name,collation,cardinality,sub_part,packed,nullable,
index_type,comment,index_comment from statistics where TABLE_SCHEMA='%s' """ % (db_name)
table_list2 = db_remote.query(v_pl2)
for table_row2 in table_list2:
table_catalog = table_row2['table_catalog']
table_schema = table_row2['table_schema']
table_name = table_row2['table_name']
non_unique = table_row2['non_unique']
index_schema = table_row2['index_schema']
index_name = table_row2['index_name']
seq_in_index = table_row2['seq_in_index']
column_name = table_row2['column_name']
collation = table_row2['collation']
cardinality = table_row2['cardinality']
# 本来Int类型，但为了处理Null值插入，以字符串类型%s 进行插入
if cardinality is None:
cardinality = 'NULL'
else:
cardinality = "'"+str(cardinality)+"'"
sub_part = table_row2['sub_part']
# 本来Int类型，但为了处理Null值插入，以字符串类型%s 进行插入
if sub_part is None:
sub_part = 'NULL'
else:
sub_part = "'"+str(sub_part)+"'"
packed = table_row2['packed']
if packed is None:
packed = 'NULL'
else:
packed = '"'+packed+'"' # 防止字符里面本身包含单引号
nullable = table_row2['nullable']
index_type = table_row2['index_type']
comment = table_row2['comment']
index_comment = table_row2['index_comment']
#try:
v_insert_sql2='''insert into meta_statistics(instance_id,schema_id,table_catalog,
table_schema,table_name,non_unique,index_schema,index_name,seq_in_index,
column_name,collation,cardinality,sub_part,packed,nullable,index_type,comment,index_comment)
values(%d,%d,'%s',
'%s','%s',%d,'%s','%s',%d,
'%s','%s',%s,%s,%s,'%s','%s','%s','%s')''' % (
instance_id,schema_id,table_catalog,
table_schema,table_name,non_unique,index_schema,index_name,seq_in_index,
column_name,collation,cardinality,sub_part,packed,nullable,index_type,comment,index_comment
)
#print v_insert_sql
db.execute(v_insert_sql2.replace('%','%%'))
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert meta tables error!," + e.message + ',' + v_insert_sql
# log_w(text)
db_remote.close()
#连接远程实例,提取冗余索引信息
db_remote2 = Connection(v_host,
'common_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
# 取common_schema.redundant_keys 视图信息然后插入
v_pl3 = r"""SELECT table_schema,table_name,redundant_index_name,redundant_index_columns,
redundant_index_non_unique,dominant_index_name,dominant_index_columns,
dominant_index_non_unique,subpart_exists,sql_drop_index
from redundant_keys where table_schema='%s' """ % (db_name)
#print v_pl
table_list3 = db_remote2.query(v_pl3)
for table_row3 in table_list3:
table_schema = table_row3['table_schema']
table_name = table_row3['table_name']
redundant_index_name = table_row3['redundant_index_name']
redundant_index_columns = table_row3['redundant_index_columns']
redundant_index_non_unique = table_row3['redundant_index_non_unique']
dominant_index_name = table_row3['dominant_index_name']
dominant_index_columns = table_row3['dominant_index_columns']
dominant_index_non_unique = table_row3['dominant_index_non_unique']
subpart_exists = table_row3['subpart_exists']
sql_drop_index = table_row3['sql_drop_index']
#try:
v_insert_sql3='''insert into meta_redundant_keys(instance_id,schema_id,table_schema,
table_name,redundant_index_name,redundant_index_columns,redundant_index_non_unique,
dominant_index_name,dominant_index_columns,
dominant_index_non_unique,subpart_exists,sql_drop_index)
values(%d,%d,'%s',
'%s','%s','%s',%d,
'%s','%s',
%d,%d,'%s')''' % (
instance_id,schema_id,table_schema,
table_name,redundant_index_name,redundant_index_columns,redundant_index_non_unique,
dominant_index_name,dominant_index_columns,
dominant_index_non_unique,subpart_exists,sql_drop_index
)
#print v_insert_sql
db.execute(v_insert_sql3.replace('%','%%'))
db_remote2.close()
i=i+1
db.close()
def generate_mysql_config(tlp_file, schema_file, force=False):
"""
Args:
tlp_file(unicode): config template to be written in to config center db
schema_file(unicode): scheme file
"""
if not os.path.exists(tlp_file):
raise ImproperlyConfigured(u"invalid config template file `%s`" %
tlp_file)
if not os.path.exists(schema_file):
raise ImproperlyConfigured(u"invalid config scheme file path `%s`" %
schema_file)
with open(tlp_file) as fi:
config = json.load(fi)
with open(schema_file) as fi:
schema = json.load(fi)
Draft4Validator(schema).validate(config)
config_center = settings.CONFIG_CENTER
env = settings.ENV
logging.warn("init config center with env `%s`" % env)
from torndb import Connection
db = None
try:
db_name = config_center["database"]
db = Connection(host=config_center["host"],
database=db_name,
user=config_center["user"],
password=config_center["password"])
table_name = "t_s_config_%s" % env
if force:
db.execute("DROP TABLE if EXISTS %s" % table_name)
if db.query("show tables where Tables_in_%s = '%s'" %
(db_name, table_name)):
raise RuntimeError(
u"config center of env `%s` has already been initialized !")
create_table_sql = u"""
CREATE TABLE IF NOT EXISTS %s (
`id` int(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
`node` varchar(40) COLLATE utf8_bin NOT NULL COMMENT '模块节点名',
`key` varchar(40) COLLATE utf8_bin NOT NULL COMMENT '模块下键名',
`value` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '模块键值',
`key_type` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT 'string' COMMENT '配置键对应的类型',
`key_note` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT '键名备注说明',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_config` (`node`,`key`) USING BTREE COMMENT '配置唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='%s环境 系统运行配置表'"""\
% (table_name, env)
db.execute(create_table_sql)
sql = "INSERT INTO %s" % table_name
sql += "(`node`, `key`, `value`, `key_type`) VALUES (%s, %s, %s, %s)"
datas = []
for key, value in config.items():
for k, v in value.items():
key_type = "string"
if isinstance(v, list):
v = ", ".join(v)
elif isinstance(v, bool):
key_type = "bool"
elif isinstance(v, int):
key_type = "int"
data = (key, k, v, key_type)
datas.append(data)
db.executemany_rowcount(sql, datas)
except KeyError as e:
raise ImproperlyConfigured(u"need config key `%s` of config_center" %
(e.args[0]))
finally:
if db:
db.close()
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_sql = r"""SELECT b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where
a.instance_id=b.id """
print v_sql
os_user = config.OS_USER
os_password = config.OS_APPS_PASSWD
bak_server_list = db.query(v_sql)
if bak_server_list: # 有server需要配置
i=0
for bak_server in bak_server_list:
from_host = bak_server['ip']
print from_host
mysql_port = bak_server['port']
backup_interval_type = bak_server['backup_interval_type']
def update_db(info):
"""Automate server audit into Racktables"""
# connect to racktables db
db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)
# get object_type_id
for item in db.query("select * from Dictionary where dict_value='{0}'".format(info["server_type"])):
object_type_id = item.dict_key
# delete object if already exists
delete_object(info)
# create object
url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(rt_server)
if info["server_type"] in ["Server", "XenServer"]:
payload = """0_object_type_id={0}&0_object_name={1}&0_object_label=&0_object_asset_no={1}&got_fast_data=Go%21""".format(
object_type_id, info["hostname"]
)
if info["server_type"] in ["VM", "EC2"]:
payload = """virtual_objects=&0_object_type_id={0}&0_object_name={1}&got_fast_data=Go%21""".format(
object_type_id, info["hostname"]
)
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to create object: {0}".format(info["hostname"])
return False
else:
print "OK - Created object: {0}".format(info["hostname"])
object_id = ""
# get object_id
for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
object_id = item.id
if not object_id:
print "Failed to get object_id"
return False
# get os_release_id
os_release_key = ""
for item in db.query("select * from Dictionary where dict_value='{0}'".format(info["os_release"])):
os_release_key = item.dict_key
if not os_release_key:
print "Failed to get object_type_id, please add '{0}' to 'Configuration - Dictionary - Server OS type'.".format(
info["os_release"]
)
return False
# update the informations of object, all post data formats were got by firebug on firefox
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=edit&op=update""".format(rt_server)
if info["server_type"] == "Server":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=2&4_value=0&5_attr_id=26&5_value=0&6_attr_id=10006&6_value={6}\
&7_attr_id=10003&7_value={7}&8_attr_id=1&8_value=&9_attr_id=28&9_value=&10_attr_id=21&10_value=&11_attr_id=4&11_value={8}\
&12_attr_id=24&12_value=&13_attr_id=10005&13_value={9}&14_attr_id=25&14_value=&num_attrs=15&object_comment=&submit.x=15&submit.y=13""".format(
object_id,
object_type_id,
info["hostname"],
info["cpu_cores"],
quote_plus(info["disk"]),
info["fqdn"],
info["memory"],
quote_plus(info["network"]),
os_release_key,
info["swap"],
)
if info["server_type"] == "XenServer":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no={2}&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=1&7_value=&8_attr_id=28&8_value=&9_attr_id=4&9_value={8}&10_attr_id=24&10_value=&11_attr_id=10005&11_value={9}\
&12_attr_id=25&12_value=&13_attr_id=10008&13_value={10}&num_attrs=14&object_comment=&submit.x=18&submit.y=21""".format(
object_id,
object_type_id,
info["hostname"],
info["cpu_cores"],
quote_plus(info["disk"]),
info["fqdn"],
info["memory"],
quote_plus(info["network"]),
os_release_key,
info["swap"],
quote_plus(info["vm_list"]),
)
if info["server_type"] == "EC2":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=10010&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=19&submit.y=27""".format(
object_id,
object_type_id,
info["hostname"],
info["cpu_cores"],
quote_plus(info["disk"]),
info["fqdn"],
info["memory"],
quote_plus(info["network"]),
info["ec2_pubname"],
os_release_key,
info["swap"],
)
if info["server_type"] == "VM":
payload = """object_id={0}&object_type_id={1}&object_name={2}&object_label=&object_asset_no=&0_attr_id=14&0_value=&1_attr_id=10000&1_value={3}\
&2_attr_id=10004&2_value={4}&3_attr_id=3&3_value={5}&4_attr_id=26&4_value=0&5_attr_id=10006&5_value={6}&6_attr_id=10003&6_value={7}\
&7_attr_id=10007&7_value={8}&8_attr_id=4&8_value={9}&9_attr_id=24&9_value=&10_attr_id=10005&10_value={10}&num_attrs=11&object_comment=&submit.x=25&submit.y=14'""".format(
object_id,
object_type_id,
info["hostname"],
info["cpu_cores"],
quote_plus(info["disk"]),
info["fqdn"],
info["memory"],
quote_plus(info["network"]),
info["resident_on"],
os_release_key,
info["swap"],
)
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update attributes"
return False
print "OK - Updated attributes"
# ec2 servers don't need to update the ip pool
if info["server_type"] not in ["EC2"]:
# update the ip pool
nics = ("".join(info["network"].split())).split(",")
for i in nics:
nic_info = i.split(":")
nic_name = "".join(nic_info[0:1])
nic_addr = "".join(nic_info[1:2])
# check if nic_name is not correct
if nic_name.isalnum():
# create nic
url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=ip&op=add""".format(rt_server)
payload = """object_id={0}&bond_name={1}&ip={2}&bond_type=regular&submit.x=11&submit.y=6""".format(
object_id, nic_name, nic_addr
)
req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
if req.status_code != requests.codes.ok:
print "Failed to update ip pool for {0}:{1}".format(nic_name, nic_addr)
return False
print "OK - Updated ip pool for {0}:{1}".format(nic_name, nic_addr)
# virtual servers don't need to update the rackspace
if info["server_type"] not in ["EC2", "VM"]:
# update rack info
update_rack(info, object_id)
# close db
db.close()
# end
return True
def start_schedule():
#if __name__ == '__main__':
os_user = config.OS_USER
os_password = config.OS_APPS_PASSWD
scheduler = Scheduler(daemonic = False)
scheduler.print_jobs()
#scheduler.remove_jobstore('file',close=True)
#scheduler.shutdown(wait=False)
scheduler.shutdown()
#scheduler.unschedule_func(backup)
scheduler.add_jobstore(ShelveJobStore('/tmp/db_schedule'), 'file')
v_current_jobs = scheduler.get_jobs()
print v_current_jobs
if v_current_jobs: # 如果job存在的话,先请客
scheduler.unschedule_func(backup)
#scheduler = Scheduler(standalone=True)
#scheduler = Scheduler(daemon=True)
#连接配置中心库，获取数据库备份周期等信息
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_sql = r"""SELECT a.instance_id,b.ip,b.port,a.backup_interval_type,a.backup_start_time from mysql_ins_bak_setup a,tag b where
a.instance_id=b.id """
print v_sql
bak_server_list = db.query(v_sql)
if bak_server_list: # 有server需要配置
i=0
# 把还没有开始的调度任务，置为手工结束 backup_result_type=4
v_manual_end_sql = 'update mysql_ins_bak_log set backup_result_type=4 where backup_result_type=0'
db.execute(v_manual_end_sql)
for bak_server in bak_server_list:
instance_id = bak_server['instance_id']
from_host = bak_server['ip']
#print from_host
mysql_port = bak_server['port']
backup_interval_type = bak_server['backup_interval_type']
backup_start_time = bak_server['backup_start_time']
str_start_date= time.strftime("%Y-%m-%d") + ' ' + backup_start_time
print str_start_date
if backup_interval_type == 1: # every day
#内存jobstore
#scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password])
#文件jobstore jobstore='file'
scheduler.add_interval_job(backup, days=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password], jobstore='file')
#scheduler.add_interval_job(backup, days=1, start_date='2014-07-18 18:17:01', args=[from_host, mysql_port, os_user, os_password])
elif backup_interval_type == 2: # every week
scheduler.add_interval_job(backup, weeks=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password])
elif backup_interval_type == 3: # every hour
scheduler.add_interval_job(backup, hours=1, start_date=str_start_date, args=[from_host, mysql_port, os_user, os_password])
# 开始在数据库记录备份的调度任务状态 0:调度任务已启动，实际备份还没有开始
v_sche_start_sql = """insert into mysql_ins_bak_log(instance_id,backup_result_type)
values(%d,0)""" % (instance_id)
db.execute(v_sche_start_sql)
i=i+1
db.close()
if bak_server_list: # 有server需要配置
scheduler.start()
print 'success!'
scheduler.print_jobs()
'''
#!/usr/bin/env python
# coding:utf-8
import redis
from torndb import Connection
from models import Domain, Flow, Rule
import settings
rd = redis.Redis(settings.REDIS_HOST, settings.REDIS_PORT)
db = Connection("127.0.0.1", "spider", user="******", password="******")
domains = db.query("select * from domain")
for d in domains:
domain = Domain(rd, d["domain"])
domain.update(d)
if "scheduled" not in domain:
domain["scheduled"] = 0
rule_datas = db.query("select * from rule where domain = %s", d["domain"])
for rule_data in rule_datas:
rule = Rule(rd, d["domain"])
rule.update(rule_data)
flows = db.query("select * from flow where domain = %s", d["domain"])
for f in flows:
flow = Flow(rd, f["flow"])
flow.update(f)
if "scheduled" not in flow:
flow["scheduled"] = 0
def upload_processlist():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1"""
#print v_sql
upload_server_list = db.query(v_sql)
if upload_server_list: # 对实例表进行循环
i=0
for upload_server in upload_server_list:
instance_id = upload_server['id']
host_ip = upload_server['ip']
mysql_port = upload_server['port']
v_host =host_ip + ':' + str(mysql_port)
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
v_pl = r"""SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED from PROCESSLIST"""
#print v_pl
process_list = db_remote.query(v_pl)
for process_row in process_list:
vp_id = process_row['ID']
vp_user = process_row['USER']
vp_host = process_row['HOST']
vp_db = process_row['DB']
# 若是空，变成mysql的null，否则加上引号再传递进去，格式为%s ,而不是'%s'
if vp_db is None:
vp_db = 'NULL'
else:
vp_db = "'"+vp_db+"'"
#print vp_db
vp_command = process_row['COMMAND']
vp_time = process_row['TIME']
vp_state = process_row['STATE']
vp_info = process_row['INFO']
if vp_info is None:
vp_info = 'NULL'
else:
#vp_info = "'"+vp_info+"'"
vp_info = vp_info.replace('"',"'") # 双引号替换为单引号
vp_info = '"'+vp_info+'"' # 防止字符里面本身包含单引号
vp_time_ms = process_row['TIME_MS']
vp_rows_sent = process_row['ROWS_SENT']
vp_rows_examined = process_row['ROWS_EXAMINED']
# v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,
# TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % (
# instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined)
#try:
v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,
TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s',%s,'%s',%d,'%s',%s,%d,%d,%d)''' % (
instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined)
#print v_insert_sql
db.execute(v_insert_sql.replace('%','%%'))
#db.execute(v_insert_sql)
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert process_list error!," + e.message + ',' + v_insert_sql
# log_w(text)
db_remote.close()
i=i+1
db.close()
def upload_auto_increment():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1"""
print v_sql
upload_server_list = db.query(v_sql)
if upload_server_list: # 对实例表进行循环
i=0
for upload_server in upload_server_list:
instance_id = upload_server['id']
host_ip = upload_server['ip']
mysql_port = upload_server['port']
v_host =host_ip + ':' + str(mysql_port)
print v_host
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
v_pl = r"""select information_schema . columns . TABLE_SCHEMA AS TABLE_SCHEMA,
information_schema . columns . TABLE_NAME AS TABLE_NAME,
information_schema . columns . COLUMN_NAME AS COLUMN_NAME,
information_schema . columns . DATA_TYPE AS DATA_TYPE,
information_schema . columns . COLUMN_TYPE AS COLUMN_TYPE,
((case information_schema . columns . DATA_TYPE
when 'tinyint' then
255
when 'smallint' then
65535
when 'mediumint' then
16777215
when 'int' then
4294967295
when 'bigint' then
18446744073709551615
end) >>
if((locate('unsigned', information_schema . columns . COLUMN_TYPE) > 0),
0,
1)) AS MAX_VALUE,
information_schema . tables . AUTO_INCREMENT AS AUTO_INCREMENT,
(information_schema . tables .
AUTO_INCREMENT /
((case information_schema . columns . DATA_TYPE
when 'tinyint' then
255
when 'smallint' then
65535
when 'mediumint' then
16777215
when 'int' then
4294967295
when 'bigint' then
18446744073709551615
end) >>
if((locate('unsigned', information_schema . columns . COLUMN_TYPE) > 0),
0,
1))) AS AUTO_INCREMENT_RATIO
from (information_schema . columns join information_schema . tables
on(((information_schema . columns . TABLE_SCHEMA = information_schema .
tables . TABLE_SCHEMA) and
(information_schema . columns . TABLE_NAME = information_schema .
tables . TABLE_NAME))))
where ((information_schema . columns .TABLE_SCHEMA not in
('mysql', 'INFORMATION_SCHEMA', 'performance_schema','common_schema')) and
(information_schema . tables . TABLE_TYPE = 'BASE TABLE') and
(information_schema . columns . EXTRA = 'auto_increment'))"""
print v_pl
process_list = db_remote.query(v_pl)
for process_row in process_list:
vp_server_ip = v_host
vp_instance_id = instance_id
#vp_id = process_row['ID']
vp_table_schema = process_row['TABLE_SCHEMA']
vp_table_name = process_row['TABLE_NAME']
vp_column_name = process_row['COLUMN_NAME']
vp_data_type = process_row['DATA_TYPE']
# 若是空，变成mysql的null，否则加上引号再传递进去，格式为%s ,而不是'%s'
#if vp_db is None:
# vp_db = 'NULL'
#else:
# vp_db = "'"+vp_db+"'"
#print vp_db
vp_column_type = process_row['COLUMN_TYPE']
vp_max_value = process_row['MAX_VALUE']
vp_auto_increment = process_row['AUTO_INCREMENT']
vp_auto_increment_ratio = process_row['AUTO_INCREMENT_RATIO']
#vp_info = process_row['INFO']
#if vp_info is None:
# vp_info = 'NULL'
#else:
# #vp_info = "'"+vp_info+"'"
# vp_info = vp_info.replace('"',"'") # 双引号替换为单引号
# vp_info = '"'+vp_info+'"' # 防止字符里面本身包含单引号
# v_insert_sql='''insert into log_processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,
# TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % (
# instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined)
#try:
v_delete_sql='''delete from auto_increment_list where INSTANCE_ID = '%s' and TABLE_SCHEMA = '%s' and TABLE_NAME = '%s' and COLUMN_NAME = '%s' ''' % (
vp_instance_id,vp_table_schema,vp_table_name,vp_column_name)
print v_delete_sql
db.execute(v_delete_sql)
v_insert_sql='''insert into auto_increment_list(instance_id,server_ip,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,MAX_VALUE,AUTO_INCREMENT,AUTO_INCREMENT_RATIO)
values(%d,'%s','%s','%s','%s','%s','%s',%d,%d,%d)''' % (
vp_instance_id,vp_server_ip,vp_table_schema,vp_table_name,vp_column_name,vp_data_type,vp_column_type,int(vp_max_value),int(vp_auto_increment),int(vp_auto_increment_ratio))
print v_insert_sql
db.execute(v_insert_sql)
#db.execute(v_insert_sql)
# except Exception, e:
# print e.message
# print v_insert_sql
# text = "insert process_list error!," + e.message + ',' + v_insert_sql
# log_w(text)
db_remote.close()
i=i+1
v_sendmail_sql=r'''select count(*) count
from auto_increment_list b where AUTO_INCREMENT_RATIO >= 80 '''
if_list = db.query(v_sendmail_sql)
for if_row in if_list:
v_if = if_row['count']
print v_if
if v_if >= 1 :
print '11'
v_warn_sql=r'''select SERVER_IP,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,MAX_VALUE,AUTO_INCREMENT,AUTO_INCREMENT_RATIO
from auto_increment_list b where AUTO_INCREMENT_RATIO >= 80 '''
print v_warn_sql
warn_list = db.query(v_warn_sql)
v_server_ip = '\r\n'
for warn_row in warn_list:
v_server_ip = v_server_ip + warn_row['SERVER_IP'] +'|对象名:' + warn_row['TABLE_SCHEMA'] +'|表名:'+ warn_row['TABLE_NAME'] + '|字段名:' + warn_row['COLUMN_NAME']+'\r\n'
print v_server_ip
v_msg_text = v_server_ip
v_receiver = '[email protected],[email protected]'
v_subject = '有快溢出的自增ID，细节请去监控页面查看'
v_return = func.send_mail_to_devs(v_receiver,v_subject,v_msg_text)
db.close()
def get(self):
db = Connection("127.0.0.1", "zfz", "zfz", "zfz...891", 25200)
q = self.get_argument(name="query", default="")
p = self.get_argument(name="price", default="999999999")
ma = re.search(r"\d+", p)
if ma is None:
p = "999999999"
else:
p = ma.group(0)
q = q.lstrip().rstrip().replace("'", "").replace('"', "").replace("#", "").replace("%", "")
qs = q.split(" ")
if len(q) > 0:
if len(qs) == 1:
m = "%" + q + "%"
items = db.query(
"select title, url, price, area, arch, address, district "
"from pages where price <= %s and (address like %s or district like %s or title like %s) "
"order by date desc limit 20",
p,
m,
m,
m,
)
else:
l = qs[0]
r = qs[-1]
m1 = ""
m2 = ""
for i in range(1, len(qs) - 1):
m1 += "%" + qs[i]
m2 += "%" + qs[len(qs) - 1 - i]
m1 += "%"
m2 += "%"
items = db.query(
"select title, url, price, area, arch, address, district "
"from pages where price <= %s and ((address like %s and district like %s) or "
"(address like %s and district like %s) or "
"(title like %s and address like %s) or "
"(title like %s and address like %s and district like %s) or "
# "title like %s or "
"address like %s) " "order by date desc limit 20",
p,
"%" + l + m1,
"%" + r + "%",
"%" + r + m2,
"%" + l + "%",
"%" + l + "%",
m1 + r + "%",
"%" + l + "%",
m1,
"%" + r + "%",
# '%' + l + m1 + r + '%',
"%" + l + m1 + r + "%",
)
else:
items = []
if len(items) < 1:
hit = False
else:
hit = True
if p == "999999999":
p = ""
self.render("search.html", query=q, price=p, items=items, hit=hit)
def upload_processlist():
#连接配置中心库
db = Connection('/tmp/mysql3306.sock',
config.DB_NAME,
config.DB_USER,
config.DB_PASSWD,
time_zone='+8:00')
v_sql = r"""SELECT id,ip,port from tag b where online_flag=1 and is_showprocesslist=1"""
#print v_sql
upload_server_list = db.query(v_sql)
if upload_server_list: # 对实例表进行循环
i=0
for upload_server in upload_server_list:
instance_id = upload_server['id']
host_ip = upload_server['ip']
mysql_port = upload_server['port']
v_host =host_ip + ':' + str(mysql_port)
#连接远程实例
db_remote = Connection(v_host,
'information_schema',
config.DBA_QUERY_USER,
config.DBA_QUERY_PASSWD,
time_zone='+8:00')
v_pl = r"""SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED from PROCESSLIST"""
#print v_pl
process_list = db_remote.query(v_pl)
for process_row in process_list:
vp_id = process_row['ID']
vp_user = process_row['USER']
vp_host = process_row['HOST']
vp_db = process_row['DB']
vp_command = process_row['COMMAND']
vp_time = process_row['TIME']
vp_state = process_row['STATE']
vp_info = process_row['INFO']
vp_time_ms = process_row['TIME_MS']
vp_rows_sent = process_row['ROWS_SENT']
vp_rows_examined = process_row['ROWS_EXAMINED']
v_insert_sql='''insert into processlist(instance_id,TID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,
TIME_MS,ROWS_SENT,ROWS_EXAMINED) values(%d,%d,'%s','%s','%s','%s',%d,'%s','%s',%d,%d,%d)''' % (
instance_id,vp_id,vp_user,vp_host,vp_db,vp_command,vp_time,vp_state,vp_info,vp_time_ms,vp_rows_sent,vp_rows_examined)
db.execute(v_insert_sql)
db_remote.close()
i=i+1
db.close()
