def _create_schema(self, session, database, schema, auths, options,
**kwargs):
"""create new schema intance on database_id"""
req = kwargs.pop('req')
try:
local_ip, port = self._get_entity(req, int(database.reflection_id))
if local_ip == 'unkonwn' or port == 0:
raise exceptions.AcceptableDbError('Database not online')
connection = connformater % dict(user=database.user,
passwd=database.passwd,
host=local_ip,
port=port,
schema=schema)
engine = create_engine(connection,
thread_checkin=False,
poolclass=NullPool)
utils.create_schema(engine,
auths=auths,
character_set=options.get('character_set'),
collation_type=options.get('collation_type'),
connection_timeout=3)
yield local_ip, port
except Exception:
if LOG.isEnabledFor(logging.DEBUG):
LOG.exception('Create schema fail')
raise
def unbond(self, cfgfile, postrun, timeout,
**kwargs):
"""slave unbond master database"""
conf = CONF[common.DB]
master = kwargs.pop('master')
schemas = master.get('schemas')
ready = master.get('ready')
force = kwargs.pop('force', False)
config = self.config_cls.load(cfgfile)
sockfile = config.get('socket')
master_name = 'masterdb-%(database_id)s' % master
with self._lower_conn(sockfile, conf.localroot, conf.localpass,
schema=None, raise_on_warnings=False) as conn:
LOG.info('Login mysql from unix sock success, try stop salve and unbond')
# check schemas
if (set(schemas) - set(self._schemas(conn))) and not force:
raise exceptions.AcceptableDbError('Slave schemas not same as master %(database_id)s' % master)
slaves = self._slave_status(conn)
for slave_status in slaves:
if slave_status.get('Connection_name') == master_name:
if LOG.isEnabledFor(logging.DEBUG):
for key in slave_status.keys():
LOG.debug('UNBOND SLAVE %s STATUS ------ %s : %s'
% (master_name, key, slave_status[key]))
running = False
if slave_status.get('Slave_IO_Running').lower() == 'yes' \
or slave_status.get('Slave_SQL_Running').lower() == 'yes':
running = True
if running:
if ready and not force:
raise exceptions.AcceptableDbError('Slave thread is running')
cursor = conn.cursor()
cursor.execute("STOP SLAVE '%s'" % master_name)
cursor.close()
cursor = conn.cursor()
cursor.execute("RESET SLAVE '%s' ALL" % master_name)
cursor.close()
break
if postrun:
postrun()
def start_database(self, database_id, **kwargs):
session = endpoint_session(readonly=True)
query = model_query(session,
GopDatabase,
filter=GopDatabase.database_id == database_id)
_database = query.one()
if _database.status != common.OK:
raise exceptions.AcceptableDbError('Database is not OK now')
return self._start_database(_database, **kwargs)
def _delete_schema(self, session, database, schema, **kwargs):
"""delete schema intance on reflection_id"""
req = kwargs.pop('req')
local_ip, port = self._get_entity(req, int(database.reflection_id))
if port <= 0:
raise exceptions.AcceptableDbError(
'Can not find Database port, not init finished')
if not local_ip:
raise exceptions.AcceptableDbError('Database agent is offline now')
conn = connformater % dict(user=database.user,
passwd=database.passwd,
schema=schema.schema,
host=local_ip,
port=port)
engine = create_engine(conn, thread_checkin=False, poolclass=NullPool)
dropauths = None
if schema.user != database.user:
dropauths = privilegeutils.mysql_privileges(schema)
utils.drop_schema(engine, dropauths)
yield local_ip, port
def show_schema(self, database_id, schema, **kwargs):
"""show schema info"""
session = endpoint_session()
query = model_query(session,
GopSchema,
filter=and_(GopSchema.database_id == database_id,
GopSchema.schema == schema))
secret = kwargs.pop('secret', False)
show_quotes = kwargs.pop('quotes', False)
if show_quotes:
query = query.options(joinedload(GopSchema.quotes,
innerjoin=False))
_schema = query.one_or_none()
if not _schema:
raise exceptions.AcceptableSchemaError(
'Schema not not be found in %d' % database_id)
_database = _schema.database
if _database.slave:
raise exceptions.AcceptableDbError(
'Database is slave, can not get schema')
_result = dict(database_id=database_id,
impl=_database.impl,
dbtype=_database.dbtype,
dbversion=_database.dbversion,
schema=_schema.schema,
schema_id=_schema.schema_id,
desc=_schema.desc)
if show_quotes:
_result.setdefault('quotes', [
dict(quote_id=_quote.quote_id, desc=_quote.desc)
for _quote in _schema.quotes
])
if secret:
_result.update({
'user': _schema.user,
'passwd': _schema.passwd,
'ro_user': _schema.ro_user,
'ro_passwd': _schema.ro_passwd
})
with self._show_schema(session, _database, _schema,
**kwargs) as address:
host = address[0]
port = address[1]
_result.setdefault('host', host)
_result.setdefault('port', port)
return _result
def bondslave(self, cfgfile, postrun, timeout, dbinfo,
**kwargs):
"""master bond salve"""
conf = CONF[common.DB]
replication = kwargs.pop('replication')
schemas = kwargs.pop('schemas')
cf = self.config_cls.load(cfgfile)
sockfile = cf.get('socket')
if not cf.get('log-bin'):
if schemas:
raise exceptions.AcceptableDbError('Databaes bin log is off in config file')
LOG.warning('Database log-bin not open, try open it')
cf.binlog()
with self._lower_conn(sockfile, conf.localroot, conf.localpass,
schema=None, raise_on_warnings=False) as conn:
if self._binlog_on(conn):
LOG.error('Config file %s value error on log bin' % cfgfile)
raise exceptions.UnAcceptableDbError('Log bin is on in mysql process but off in config')
if self._master_status(conn):
raise exceptions.UnAcceptableDbError('Bin log has been opened but now closed')
cf.save(cfgfile)
LOG.info('log bin opened in config file, try restart mysql')
self.stop(cfgfile, timeout=3)
self.start(cfgfile)
if not os.path.exists(sockfile):
eventlet.sleep(1)
sqls = []
sqls.append("grant %(privileges)s on *.* to '%(user)s'@'%(source)s' IDENTIFIED by '%(passwd)s'"
% replication)
sqls.append("FLUSH PRIVILEGES")
if not schemas:
sqls.append("RESET MASTER")
with self._lower_conn(sockfile, conf.localroot, conf.localpass) as conn:
LOG.info('Login mysql from unix sock %s success, try bond slave' % sockfile)
if not self._binlog_on(conn):
raise exceptions.AcceptableDbError('Database binlog is off')
if set(schemas) != set(self._schemas(conn)):
raise exceptions.UnAcceptableDbError('Master schemas record not the same with schemas in entity')
if schemas:
if not kwargs.get('file') or not kwargs.get('position'):
raise exceptions.AcceptableSchemaError('Database got schemas, need file and position')
LOG.warning('Database add slave with schemas already exist')
for sql in sqls:
LOG.debug(sql)
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
if schemas:
binlog = dict(File=kwargs.get('file'), Position=kwargs.get('position'))
else:
binlog = self._master_status(conn)
LOG.info('Grant privileges for replication user success')
if postrun:
try:
postrun(binlog, schemas)
except Exception as e:
LOG.error('Bond slave fail with exception %s' % e.__class__.__name__)
sqls = []
if not replication.get('schema'):
replication['schema'] = '*'
sqls.append("REVOKE %(privileges)s ON %(schema)s.* FROM '%(user)s'@'%(source)s'" % replication)
sqls.append("DROP USER '%(user)s'@'%(source)s'" % replication)
sqls.append("FLUSH PRIVILEGES")
try:
with self._lower_conn(sockfile, conf.localroot, conf.localpass) as conn:
for sql in sqls:
LOG.debug(sql)
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
# TODO change Exception type
except Exception:
LOG.error('Drop user fail')
LOG.info('Bond fail, revoke user privilege success')
raise e
def bond(self, cfgfile, postrun, timeout, dbinfo,
**kwargs):
"""slave bond to master database"""
conf = CONF[common.DB]
master = kwargs.pop('master')
schemas = set(master.pop('schemas'))
force = kwargs.pop('force', False)
config = self.config_cls.load(cfgfile)
sockfile = config.get('socket')
auth = privilegeutils.mysql_slave_replprivileges(slave_id=dbinfo.get('database_id'), **master)
master_name = 'masterdb-%(database_id)s' % auth
sql = "CHANGE MASTER 'masterdb-%(database_id)s' TO MASTER_HOST='%(host)s', MASTER_PORT=%(port)d," \
"MASTER_USER='******',MASTER_PASSWORD='******'," \
"MASTER_LOG_FILE='%(file)s',MASTER_LOG_POS=%(position)s" % auth
LOG.info('Replication connect sql %s' % sql)
with self._lower_conn(sockfile, conf.localroot, conf.localpass) as conn:
LOG.info('Login mysql from unix sock %s success, try bond master' % sockfile)
if schemas & set(self._schemas(conn)):
raise exceptions.AcceptableSchemaError('Schema with same name exist')
LOG.info('Slave channel name %s' % master_name)
slaves = self._slave_status(conn)
for slave_status in slaves:
channel = slave_status.get('Connection_name')
host = slave_status.get('Master_Host')
port = slave_status.get('Master_Port')
iothread = slave_status.get('Slave_IO_Running').lower()
epos = slave_status.get('Exec_Master_Log_Pos')
rpos = slave_status.get('Read_Master_Log_Pos')
bsecond = slave_status.get('Seconds_Behind_Master')
if channel != master_name and (host == auth.get('host') and port == auth.get('port')):
LOG.info('Bond slave find same host and port with different channel name %s' % channel)
if iothread == 'yes':
raise exceptions.AcceptableDbError('Slave with channel name %s '
'is running in same host:port' % channel)
if epos != 0 or rpos != 0 or bsecond != 0:
if not force:
raise exceptions.AcceptableDbError('Channel %s pos not zero, need force' %
channel)
LOG.warning('Reset slave channel %s' % channel)
if LOG.isEnabledFor(logging.DEBUG):
for key in slave_status.keys():
LOG.debug('BOND FIND OLD SLAVE %s STATUS ------ %s : %s'
% (channel, key, slave_status[key]))
cursor = conn.cursor()
cursor.execute("RESET SLAVE '%s' ALL" % channel)
cursor.close()
break
elif channel == master_name:
LOG.info('Bond slave find same channel')
if host != auth.get('host') or port != auth.get('port'):
if iothread == 'yes':
raise exceptions.AcceptableDbError('Channel %s is running but '
'connection is not the same' % channel)
if epos != 0 or rpos != 0 or bsecond != 0:
if not force:
raise exceptions.AcceptableDbError('Channel %s pos not zero, need force' %
channel)
if LOG.isEnabledFor(logging.DEBUG):
for key in slave_status.keys():
LOG.debug('BOND FIND OLD SLAVE %s STATUS ------ %s : %s'
% (channel, key, slave_status[key]))
if iothread == 'yes':
cursor = conn.cursor()
cursor.execute("STOP SLAVE '%s'" % channel)
cursor.close()
cursor = conn.cursor()
cursor.execute("RESET SLAVE '%s' ALL" % channel)
cursor.close()
break
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
LOG.info('Connect to master success, try start slave')
# master have no schemas auto start slave
if not schemas:
cursor = conn.cursor()
cursor.execute("START SLAVE '%s'" % master_name)
cursor.close()
LOG.info('START SLAVE %s success' % master_name)
if postrun:
postrun()
def delete_schema(self, database_id, schema, **kwargs):
"""delete schema intance on reflection_id"""
unquotes = set(kwargs.get('unquotes', []))
ignores = set(kwargs.get('ignores', []))
force = kwargs.get('force', False)
session = endpoint_session()
query = model_query(session,
GopDatabase,
filter=GopDatabase.database_id == database_id)
query = query.options(joinedload(GopDatabase.schemas, innerjoin=False))
with session.begin():
_database = query.one()
_result = dict(database_id=_database.database_id,
impl=_database.impl,
dbtype=_database.dbtype,
dbversion=_database.dbversion)
if _database.slave:
raise exceptions.AcceptableDbError(
'can not delete schema from slave database')
squery = model_query(session,
GopSchema,
filter=and_(
GopSchema.schema == schema,
GopSchema.database_id == database_id))
squery = squery.options(
joinedload(GopSchema.quotes, innerjoin=False))
_schema = squery.one()
_result.setdefault('schema_id', _schema.schema_id)
_slaves = [_slave.slave_id for _slave in _database.slaves]
_slaves_q_query = model_query(
session,
SchemaQuote,
filter=and_(SchemaQuote.schema_id == _schema.schema_id,
SchemaQuote.qdatabase_id.in_(_slaves)))
quotes = {}
# quote of slave
slave_quotes = _slaves_q_query.all()
for _quotes_list in (slave_quotes, _schema.quotes):
if _quotes_list:
for _quote in _quotes_list:
quotes[_quote.quote_id] = _quote.desc
# check quotes
for quote_id, desc in quotes.items():
if quote_id in unquotes:
quotes.pop(quote_id, None)
if desc in ignores:
quotes.pop(quote_id, None)
if quotes:
if force:
for quote_id, desc in quotes.items():
LOG.warning('Quote %d: [%s] force delete' %
(quote_id, desc))
else:
raise exceptions.AcceptableSchemaError(
'Schema in quote, can not be delete')
with self._delete_schema(session, _database, _schema,
**kwargs) as address:
host = address[0]
port = address[1]
_result.setdefault('host', host)
_result.setdefault('port', port)
_result.setdefault('schema', schema)
squery.delete()
return _result
def copy_schema(self, src_database_id, src_schema, dst_database_id,
dst_schema, auth, **kwargs):
"""create a schema"""
auths = privilegeutils.mysql_privileges(auth)
session = endpoint_session()
query = model_query(session,
GopDatabase,
filter=GopDatabase.database_id.in_(
[src_database_id, dst_database_id]))
query = query.options(joinedload(GopDatabase.schemas, innerjoin=False))
src_database, dst_database = None, None
for _database in query.all():
if _database.database_id == src_database_id:
if _database.slave:
raise exceptions.AcceptableDbError(
'Source database is not master')
if not _database.passwd:
raise exceptions.AcceptableDbError(
'Source database has no passwd, can not copy')
schemas = [_schema.name for _schema in _database.schemas]
if src_schema not in schemas:
raise exceptions.AcceptableSchemaError(
'Source schemas %s not exist' % src_schema)
src_database = _database
elif _database.database_id == dst_database_id:
if _database.slave:
raise exceptions.AcceptableDbError(
'Destination database is not master')
if not _database.passwd:
raise exceptions.AcceptableDbError(
'Destination database has no passwd, can not copy')
schemas = [_schema.name for _schema in _database.schemas]
if dst_schema in schemas:
raise exceptions.AcceptableSchemaError(
'Destination schemas %s alreday exist' % dst_schema)
dst_database = _database
if not src_database or not dst_database:
raise
_result = dict(database_id=dst_database.database_id,
impl=dst_database.impl,
dbtype=dst_database.dbtype,
dbversion=dst_database.dbversion)
with session.begin():
with self._copy_schema(session, src_database, src_schema,
dst_database, dst_schema, auths,
**kwargs) as options:
character_set = options[0] or 'utf8'
collation_type = options[1]
gop_schema = GopSchema(schema=dst_schema,
database_id=dst_database.database_id,
user=auth.get('user'),
passwd=auth.get('passwd'),
ro_user=auth.get('ro_user'),
ro_passwd=auth.get('ro_passwd'),
source=auth.get('source'),
character_set=character_set,
collation=collation_type)
session.add(gop_schema)
session.flush()
_result.setdefault('schema_id', gop_schema.schema_id)
_result.setdefault('schema', gop_schema.schema)
return _result
def create_schema(self, database_id, schema, auth, options, **kwargs):
"""create new schema intance on reflection_id"""
auths = privilegeutils.mysql_privileges(auth)
bond = kwargs.get('bond')
affinity = kwargs.get('affinity', None)
session = endpoint_session()
query = model_query(session,
GopDatabase,
filter=GopDatabase.database_id == database_id)
query = query.options(joinedload(GopDatabase.schemas, innerjoin=False))
quote_id = 0
with session.begin():
_database = query.one()
_result = dict(database_id=database_id,
impl=_database.impl,
dbtype=_database.dbtype,
dbversion=_database.dbversion)
if _database.slave:
raise exceptions.AcceptableDbError(
'Database is slave, can not create schema')
if _database.status != common.OK:
raise exceptions.AcceptableDbError('Database is not OK now')
if affinity is not None and (_database.affinity & affinity) == 0:
raise exceptions.AcceptableDbError(
'Database affinity not match')
schemas = [_schema.schema for _schema in _database.schemas]
if schema in schemas:
raise exceptions.AcceptableDbError('Duplicate schema name %s' %
schema)
options = options or {'character_set': 'utf8'}
with self._create_schema(session, _database, schema, auths,
options, **kwargs) as address:
gop_schema = GopSchema(
schema=schema,
database_id=_database.database_id,
user=auth.get('user'),
passwd=auth.get('passwd'),
ro_user=auth.get('ro_user'),
ro_passwd=auth.get('ro_passwd'),
source=auth.get('source') or '%',
rosource=auth.get('rosource') or '%',
character_set=options.get('character_set'),
collation_type=options.get('collation_type'))
session.add(gop_schema)
session.flush()
if bond:
_quote = SchemaQuote(schema_id=gop_schema.schema_id,
qdatabase_id=_database.database_id,
entity=bond.get('entity'),
endpoint=bond.get('endpoint'),
desc=bond.get('desc'))
session.add(_quote)
session.flush()
quote_id = _quote.quote_id
host = address[0]
port = address[1]
_result.setdefault('host', host)
_result.setdefault('port', port)
_result.setdefault('character_set',
options.get('character_set'))
_result.setdefault('collation_type',
options.get('collation_type'))
_result.setdefault('schema_id', gop_schema.schema_id)
_result.setdefault('schema', gop_schema.schema)
_result.setdefault('quote_id', quote_id)
return _result
def delete_database(self, database_id, master, **kwargs):
"""delete master database intance"""
session = endpoint_session()
query = model_query(session,
GopDatabase,
filter=GopDatabase.database_id == database_id)
if master:
query = query.options(
joinedload(GopDatabase.schemas, innerjoin=False))
else:
query = query.options(
joinedload(GopDatabase.quotes, innerjoin=False))
with session.begin():
_database = query.one()
_result = dict(database_id=_database.database_id,
slave=_database.slave,
impl=_database.impl,
dbtype=_database.dbtype,
dbversion=_database.dbversion)
# 删除主库
if master:
if _database.slave:
raise exceptions.AcceptableDbError(
'Target database is a salve database')
if _database.schemas or _database.slaves:
raise exceptions.AcceptableDbError(
'can not delete master database, slaves or schemas exist'
)
if model_count_with_key(session,
SchemaQuote.qdatabase_id,
filter=SchemaQuote.qdatabase_id ==
_database.database_id):
raise exceptions.AcceptableDbError(
'Database in schema quote list')
with self._delete_database(session, _database,
**kwargs) as address:
host = address[0]
port = address[1]
_result.setdefault('host', host)
_result.setdefault('port', port)
query.delete()
# 删除从库
else:
if not _database.slave:
raise exceptions.AcceptableDbError(
'Target database is not a slave database')
if _database.quotes:
raise exceptions.AcceptableDbError(
'Target slave database in schema quote list')
_masters = [
m[0] for m in model_query(session,
GopSalveRelation.master_id,
filter=GopSalveRelation.slave_id
== database_id).all()
]
if _masters:
masters = model_query(
session,
GopDatabase,
filter=and_(GopDatabase.database_id.in_(_masters),
GopDatabase.slave == 0))
if len(_masters) != len(masters):
raise exceptions.UnAcceptableDbError(
'Target slave database master missed')
raise exceptions.AcceptableDbError(
'Slave is bond to masters, unbond before delete')
with self._delete_database(session, _database,
**kwargs) as address:
query.delete()
host = address[0]
port = address[1]
_result.setdefault('host', host)
_result.setdefault('port', port)
return _result
def bond(self, req, database_id, schema, body=None):
"""schema quote"""
body = body or {}
database_id = int(database_id)
slave = body.get('slave', True)
slave_id = body.get('slave_id')
desc = body.get('desc')
esure = body.get('esure', True)
quote_id = body.get('quote_id')
entity = body.pop('entity', None)
entity = int(entity) if entity is not None else None
endpoint = body.pop(common.ENDPOINTKEY, None)
if esure:
if not endpoint or not entity:
raise InvalidArgument(
'No endpoint info or entity, esure should be flase')
# TODO log entity info
entity_info = entity_controller.show(req=req,
endpoint=endpoint,
entity=entity)['data'][0]
session = endpoint_session()
query = model_query(session,
GopDatabase,
filter=and_(GopDatabase.database_id == database_id,
GopDatabase.slave == 0))
query = query.options(joinedload(GopDatabase.schemas, innerjoin=False))
_database = query.one()
_schema = None
for __schema in _database.schemas:
if __schema.schema == schema:
_schema = __schema
break
if not _schema:
raise exceptions.AcceptableSchemaError('Schema %s not found' %
schema)
quote_database_id = _database.database_id
user = _schema.user
passwd = _schema.passwd
# glock = get_global().lock('entitys')
# with glock(common.DB, [entity, ]):
if slave:
slaves = [
_slave.slave_id for _slave in _database.slaves if _slave.ready
]
if slave_id:
if slave_id not in slaves:
raise exceptions.AcceptableDbError(
'Slave %d not found or not ready' % slave)
quote_database_id = slave_id
else:
if slaves:
quote_database_id = slaves[0]
else:
LOG.warning(
'Not slave database, use master database as slave')
user = _schema.ro_user
passwd = _schema.ro_passwd
address = _address([
quote_database_id,
]).get(quote_database_id)
with session.begin():
schema_quote = SchemaQuote(quote_id=quote_id,
schema_id=_schema.schema_id,
qdatabase_id=quote_database_id,
entity=entity,
endpoint=endpoint,
desc=desc)
session.add(schema_quote)
session.flush()
port = address.get('port')
host = address.get('host')
return resultutils.results(
result='quote to %s.%d success' %
(schema_quote.qdatabase_id, schema_quote.schema_id),
data=[
dict(schema_id=schema_quote.schema_id,
quote_id=schema_quote.quote_id,
qdatabase_id=quote_database_id,
host=host,
port=port,
user=user,
passwd=passwd,
schema=schema)
])