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
