class MySQL_OPS(MySQL): def __init__(self, clusterobj, dbname=''): self.ip = clusterobj.foreign_ip self.user = clusterobj.plat_user self.passwd = clusterobj.plat_user_pass self.db = dbname self.port = int(clusterobj.foreign_port) self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd) # self._conn = self.connect(self.ip, self.port, self.db, self.user, self.passwd) # self._cursor = self._conn.cursor() def getBinaryLog(self): result, col = self._sql.getBinaryLog() return result, col def getTables(self): if self.db: sql = 'show tables;' rc, rs = self._sql.queryAll(sql=sql) return rc, rs else: rc = 0 rs = None return rc, rs
class MySQL_update(object): def __init__(self, clusterid): self._cluster = MySQLCluster.objects.get(id=clusterid) self.cluster_id = clusterid self.ip = self._cluster.foreign_ip self.port = self._cluster.foreign_port self.user = self._cluster.plat_user self.arch = self._cluster.arch self.passwd = self._cluster.plat_user_pass self._conn = MySQL(ip=self.ip, port=self.port, user=self.user, passwd=self.passwd) def mysql_update_db(self): #取出数据库信息 sql = ''' select table_schema as 'databases', sum(table_rows) as 'allrows', sum(truncate(data_length / 1024 / 1024, 2)) as 'dbsize(MB)', sum(truncate(index_length / 1024 / 1024, 2)) as 'indsize(MB)' from information_schema.tables where TABLE_SCHEMA not in ( 'mysql','sys','information_schema','performance_schema') group by table_schema order by sum(data_length) desc, sum(index_length) desc; ''' rc, rs = self._conn.queryAll(sql=sql) mysqldb = self._cluster.mysql_db for ii in rs: #获取用户,更新用户 aa = mysqldb.get_or_create(dbname=ii[0], dbcluster_id=self.cluster_id) # aa[0].objects.update(db_rows=ii[1],db_size=ii[2],index_size=ii[3]) aa[0].db_rows = ii[1] aa[0].db_size = ii[2] aa[0].index_size = ii[3] aa[0].save() return True def mysql_update_user(self): #取出用户信息,这里过滤DBA使用的用户 sql = ''' select user, host from mysql.user where user not in('root','dba','backup','monitor','slave') and user not like 'mysql%' and user not like 'repli%'; ''' rc, rs = self._conn.queryAll(sql=sql) mysql_user = self._cluster.mysql_user for ii in rs: sql2 = "show grants for %s@`%s`;" % (ii[0], ii[1]) rc2, rs2 = self._conn.queryAll(sql=sql2) priv = '' for jj in rs2: priv = priv + jj[0] + ' ; ' aa = mysql_user.get_or_create(db_user=ii[0], db_host=ii[1], dbcluster_id=self.cluster_id) aa[0].privlige = priv aa[0].save() return True # def mysql_update_instance(self): if self.arch == '主从': rs = self._conn.getSlaveIP() conn_slave = MySQL(ip=rs[1], port=self.port, user=self.user, passwd=self.passwd) rs2 = conn_slave.getReplStatus() myinstance = self._cluster.mysql_instance if rs2[3] == self.port: #代表主从是一致的 slave = myinstance.get_or_create(dbcluster_id=self.cluster_id, m_ip=rs[1], port=self.port) slave[0].master_ip = rs2[1] slave[0].master_port = rs2[3] slave[0].role = u'从库' # slave[0].db_status=u'仅从库' slave[0].save() master = myinstance.get_or_create(dbcluster_id=self.cluster_id, m_ip=rs2[1], port=self.port) master[0].vist_ip = self.ip master[0].role = u'主库' master[0].db_status = u'服务中' master[0].save() return True