def test_query_charset(self): sql_create_table = ( 'CREATE TABLE `test`.`q` ( `name` varchar(128) NOT NULL)' ' ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin') pool = mysqlconnpool.make({ 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, 'charset': 'utf8', }) rst = pool.query('CREATE DATABASE `test`') dd('create database rst: ', rst) rst = pool.query(sql_create_table) dd('create table rst: ', rst) rst = pool.query('INSERT INTO `test`.`q` (`name`) VALUES ("我")') dd('insert rst: ', rst) rst = pool.query('SELECT * FROM `test`.`q`') dd('select rst: ', rst) self.assertEqual(({'name': u'\u6211'}, ), rst)
def test_query_charset(self): sql_create_table = ('CREATE TABLE `test`.`q` ( `name` varchar(128) NOT NULL)' ' ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin') pool = mysqlconnpool.make({ 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, 'charset': 'utf8', }) rst = pool.query('CREATE DATABASE `test`') dd('create database rst: ', rst) rst = pool.query(sql_create_table) dd('create table rst: ', rst) rst = pool.query('INSERT INTO `test`.`q` (`name`) VALUES ("我")') dd('insert rst: ', rst) rst = pool.query('SELECT * FROM `test`.`q`') dd('select rst: ', rst) self.assertEqual(({'name': u'\u6211'},), rst)
def group_replication_setup_channel(self): # replication: # user: "******" # password: "******" # source: [...] rpl = self.bkp_conf['replication'] alive = self.is_instance_alive() proc = None if not alive: proc = self.start_tmp_mysql() try: pool = mysqlconnpool.make(self.mysql_addr) sql = ('CHANGE MASTER TO' ' MASTER_USER="******"' ' , MASTER_PASSWORD="******"' ' FOR CHANNEL "group_replication_recovery"').format(**rpl) self.mysql_pool_query(pool, sql) finally: if not alive: self.stop_tmp_mysql(proc)
def make_sharding(db, table, conn, shard_fields, start, number_per_shard, tolerance_of_shard, shard_maker=list): scan_args = [(db, table), shard_fields, shard_fields, start] scan_kwargs = {"left_open": False, "use_dict": False, "retry": 3} connpool = mysqlconnpool.make(conn) record_iter = scan_index(connpool, *scan_args, **scan_kwargs) shards = strutil.sharding( record_iter, number_per_shard, accuracy=tolerance_of_shard, joiner=list) _, count = shards[0] result = { "shard": [shard_maker(start)], "number": [count], "total": count, } for shard, count in shards[1:]: result['shard'].append(shard_maker(shard)) result['number'].append(count) result['total'] += count return result
def setup_replication(self): # replication: # user: "******" # password: "******" # source: # - host: # port: # id: # - host: # port: # id: alive = self.is_instance_alive() proc = None if not alive: proc = self.start_tmp_mysql() try: pool = mysqlconnpool.make(self.mysql_addr) if self.bkp_conf['replication'].get('group_replication') == 1: self._group_replication_reset_relay(pool) if alive: self.mysql_pool_query(pool, 'START group_replication') else: self._slave_reset(pool) if alive: self.mysql_pool_query(pool, 'START SLAVE') finally: if not alive: self.stop_tmp_mysql(proc)
def diff_replication(self): self.assert_instance_is_alive() rpl = self.bkp_conf['replication'] sql = 'show slave status' mine = self.mysql_query(sql)[0] rst = {} for src in rpl['source']: pool = mysqlconnpool.make({ 'host': src['host'], 'port': int(src['port']), 'user': rpl['user'], 'passwd': rpl['password'], }) k = '{host}:{port}-[{id}]'.format(**src) try: r = pool.query(sql)[0] except MySQLdb.OperationalError as e: rst[k] = 'Unreachable: ' + repr(e) else: diff = self.diff_slave_status_gtidset(mine, r) rst[k] = diff return rst
def table_sizes(self, db=None, sortby=None): if db is None: db = self.get_dbs()[0] if sortby is None: sortby = 'total' mp = { 'free': lambda x: x['Data_free'], 'used': lambda x: x['Data_length'], 'total': lambda x: x['Data_free'] + x['Data_length'], } sort_key = mp[sortby] addr = {} addr.update(self.mysql_addr) addr.update({ 'db': db, }) pool = mysqlconnpool.make(addr) rsts = pool.query('show table status') for rst in rsts: self._rst_to_number(rst) rsts = list(rsts) rsts.sort(key=sort_key, reverse=True) rsts = [('{Data_length:>6} free-able: {Data_free:>6} {Name}'.format( **humannum.humannum(x)), x) for x in rsts] return rsts
def make_sharding(db, table, conn, shard_fields, start, number_per_shard, tolerance_of_shard, shard_maker=list): scan_args = [(db, table), shard_fields, shard_fields, start] scan_kwargs = {"left_open": False, "use_dict": False, "retry": 3} connpool = mysqlconnpool.make(conn) record_iter = scan_index(connpool, *scan_args, **scan_kwargs) shards = strutil.sharding(record_iter, number_per_shard, accuracy=tolerance_of_shard, joiner=list) _, count = shards[0] result = { "shard": [shard_maker(start)], "number": [count], "total": count, } for shard, count in shards[1:]: result['shard'].append(shard_maker(shard)) result['number'].append(count) result['total'] += count return result
def setUp(self): utdocker.start_container( mysql_test_name, mysql_test_tag, ip=mysql_test_ip, env={ 'MYSQL_ROOT_PASSWORD': mysql_test_password, }, port_bindings={mysql_test_port: mysql_test_port}) addr = (mysql_test_ip, mysql_test_port) # some time it takes several seconds to start listening for ii in range(40): try: sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1) sock.connect(addr) break except socket.error: dd('trying to connect to {0} failed'.format(str(addr))) sock.close() time.sleep(.4) else: raise self.pool = mysqlconnpool.make({ 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, })
def setUp(self): self.mysql_ip = start_mysql_server() addr = (self.mysql_ip, mysql_test_port) # some time it takes several seconds to start listening for ii in range(40): try: sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1) sock.connect(addr) break except socket.error: dd('trying to connect to {0} failed'.format(str(addr))) sock.close() time.sleep(.4) else: raise self.pool = mysqlconnpool.make({ 'host': self.mysql_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, })
def clean_binlog(self, before_days=5): ts = timeutil.ts() ts = ts - 86400 * before_days dt = timeutil.format_ts(ts, 'daily') pool = mysqlconnpool.make(self.mysql_addr) self.mysql_pool_query( pool, 'PURGE BINARY LOGS BEFORE "{dt} 00:00:00"'.format(dt=dt))
def scan_index(connpool, table, result_fields, index_fields, index_values, left_open=False, limit=None, index_name=None, use_dict=True, retry=0): if type(connpool) == dict: # an address pool = mysqlconnpool.make(connpool) elif isinstance(connpool, mysqlconnpool.MysqlConnectionPool): pool = connpool else: raise ConnectionTypeError if len(index_values) != len(index_fields): raise InvalidLength( 'number of index fields and values are not equal: ' 'index fields: {fld}; ' 'index values: {val}'.format(fld=index_fields, val=index_values)) req_fields = list(index_fields) req_values = list(index_values) strict = True if limit is None: strict = False limit = 1024 while True: sql = make_index_scan_sql(table, None, req_fields, req_values, left_open=left_open, limit=limit, index_name=index_name) rst = pool.query(sql, retry=retry) for rr in rst: if use_dict: yield dict([(k, rr[k]) for k in result_fields]) else: yield [rr[k] for k in result_fields] if strict: break if len(rst) > 0: last_row = rst[-1] req_fields = list(index_fields) req_values = [str(last_row[x]) for x in req_fields] left_open = True continue req_fields = req_fields[:-1] req_values = req_values[:-1] if len(req_fields) > 0: continue break
def setUp(self): super(TestMysql, self).setUp() self.addr = { 'host': base.mysql_test_ip, 'port': base.mysql_test_port, 'user': mysql_test_user, 'passwd': base.mysql_test_password, } self.conn_pool = mysqlconnpool.make(self.addr) self.conn = self.conn_pool.get_conn()
def group_replication_bootstrap(self): pool = mysqlconnpool.make(self.mysql_addr) sqls = ( "SET GLOBAL group_replication_bootstrap_group=ON", "START GROUP_REPLICATION", "SET GLOBAL group_replication_bootstrap_group=OFF", ) for sql in sqls: self.mysql_pool_query(pool, sql)
def get_slave_status(port): pool = mysqlconnpool.make( {'unix_socket': '/var/run/mysqld/mysqld-{p}.sock'.format(p=port)}) try: slave_status = pool.query('show slave status') except (MySQLdb.OperationalError, MySQLdb.InternalError): return None return slave_status
def __init__(self, bkp_conf): self.bkp_conf = self.extend_backup_conf(bkp_conf) self.mysql_addr = { 'unix_socket': self.bkp_conf['mysql_socket'], 'user': '******' } if 'root_password' in self.bkp_conf: self.mysql_addr.update({ 'passwd': self.bkp_conf['root_password'], }) self.mysql_conn_pool = mysqlconnpool.make(self.mysql_addr)
def optimize_tables(self, db=None): if db is None: db = self.get_dbs()[0] addr = {} addr.update(self.mysql_addr) addr.update({ 'db': db, }) pool = mysqlconnpool.make(addr) rsts = pool.query('show table status') for rst in rsts: self._rst_to_number(rst) msg = self._table_status_str(rst) need_optimize = False if rst['Data_free'] > 10 * (1024**3): self.info(msg + ' Need optimize, Data_free > 10G') need_optimize = True if float(rst['Data_free']) / float(rst['Data_length']) > 0.5: self.info(msg + ' Need optimize, Data_free/Data_length > 0.5') need_optimize = True if need_optimize: _rsts = pool.query( 'optimize local table `{Name}`'.format(**rst)) self.info('optimize done') for _r in _rsts: self.info(str(_r)) else: self.info(msg + ' Do not need optimize') rsts = pool.query('show table status') self.info('After optimize:') for rst in rsts: self._rst_to_number(rst) msg = self._table_status_str(rst) self.info(msg)
def get_dbs(self, exclude=None): addr = {} addr.update(self.mysql_addr) if exclude is None: exclude = ( 'information_schema', 'mysql', 'performance_schema', 'sys', ) pool = mysqlconnpool.make(addr) rsts = pool.query('show databases') dbs = [x['Database'] for x in rsts if x['Database'] not in exclude] return dbs
def setUp(self): utdocker.create_network() utdocker.start_container(mysql_test_name, mysql_test_tag, ip=mysql_test_ip, env={ 'MYSQL_ROOT_PASSWORD': mysql_test_password, }) ututil.wait_listening(mysql_test_ip, mysql_test_port) self.pool = mysqlconnpool.make({ 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, })
def setUp(self): utdocker.create_network() utdocker.start_container( mysql_test_name, mysql_test_tag, ip=mysql_test_ip, env={ 'MYSQL_ROOT_PASSWORD': mysql_test_password, } ) ututil.wait_listening(mysql_test_ip, mysql_test_port) self.pool = mysqlconnpool.make({ 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, })
def test_scan_index(self): addr = { 'host': mysql_test_ip, 'port': mysql_test_port, 'user': mysql_test_user, 'passwd': mysql_test_password, } conns = ( addr, mysqlconnpool.make(addr), ) table = ('test', 'errlog') result_fields = ['_id'] cases = ( ( [['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], {}, ('8', '12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'), 'test common', ), ( [['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], { 'left_open': True }, ('12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'), 'test left_open', ), ( [['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], { 'limit': 3 }, ( '8', '12', '18', ), 'test limit', ), ( [['autolvl', 'service', 'ip', '_id'], ['stable', 'common0', '127.0.0.1', '8']], { 'index_name': 'idx_service_ip__id' }, ('12', '32', '2', '13', '19', '30', '22', '28', '6', '15', '21', '7'), 'test index_name', ), ( [['service', 'ip', '_id'], ['common0', '127.0.0.1', 8]], { 'left_open': True, 'limit': 3, 'index_name': 'idx_time__id' }, ( '12', '18', '20', ), 'test all kwargs', ), ) for conn in conns: dd('conn: ', conn) for args, kwargs, rst_expect, msg in cases: args = [conn, table, result_fields] + args kwargs['use_dict'] = False dd('msg: ', msg) rst = mysqlutil.scan_index(*args, **kwargs) for i, rr in enumerate(rst): dd('rst:', rr) dd('except: ', rst_expect[i]) self.assertEqual(rr[0], long(rst_expect[i])) self.assertEqual(len(rst_expect), i + 1) error_cases = ( ( [ addr, table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.2'] ], {}, mysqlutil.InvalidLength, 'test index_fields amount greater than index_values', ), ( [ addr, table, result_fields, ['service', 'ip'], ['common0', '127.0.0.2', '13'] ], {}, mysqlutil.InvalidLength, 'test index_fields amount less than index_values', ), ( [ 'addr', table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.1', '8'] ], {}, mysqlutil.ConnectionTypeError, 'test conn type error', ), ) for args, kwargs, error, msg in error_cases: dd('msg: ', msg) try: rst = mysqlutil.scan_index(*args, **kwargs) except error as e: self.assertEqual(type(e), error)
def mysql_query(self, sql): pool = mysqlconnpool.make(self.mysql_addr) return self.mysql_pool_query(pool, sql)
def create_user(self, username, password, host='%', privileges=None, binlog=True): # To setup replication, a replicator user should be created on each # instance, before binlog can be sync-ed. # But after setting up binlog, the row in mysql.user of the `replicator` # will be sync-ed and results in conflicting user row. # # Thus we might need to disable binlog when creating replication user. # See: https://dev.mysql.com/doc/refman/5.7/en/group-replication-user-credentials.html self.info('create or change user:'******' {username}@{host},' ' password: {password}' ' privileges: {privileges}' ' binlog: {binlog}'.format(username=username, password=password, host=host, privileges=privileges, binlog=binlog)) username = MySQLdb.escape_string(username) password = MySQLdb.escape_string(password) host = MySQLdb.escape_string(host) # privileges is a list of tuple (<table>, <privilege>) alive = self.is_instance_alive() proc = None if not alive: proc = self.start_tmp_mysql() try: pool = mysqlconnpool.make(self.mysql_addr) if not binlog: self.mysql_pool_query(pool, 'SET SQL_LOG_BIN=0') rst = self.mysql_pool_query( pool, 'SELECT `Host`, `User` FROM `mysql`.`user`' ' WHERE `Host`="{host}" AND `User`="{username}"'.format( host=host, username=username)) if len(rst) == 0: self.mysql_pool_query( pool, 'CREATE USER "{username}"@"{host}" IDENTIFIED BY "{password}"' .format(host=host, username=username, password=password)) else: # user exists, just set password self.mysql_pool_query( pool, 'SET PASSWORD FOR "{username}"@"{host}" = "{password}"'. format(host=host, username=username, password=password)) for tbl, prv in privileges: # `prv` is a key of privilege set defined in # mysqlutil.privileges, or a tuple of mysql privileges. # Such as: "monitor", "readwrite", or just ("REPLICATION SLAVE") # # Convert it to tuple. if isinstance(prv, basestring): prv = mysqlutil.privileges[prv] prv = ','.join(prv) self.mysql_pool_query( pool, 'GRANT {prv} ON {tbl} TO "{username}"@"{host}"'.format( host=host, username=username, prv=prv, tbl=tbl)) self.mysql_pool_query(pool, 'FLUSH PRIVILEGES') if not binlog: self.mysql_pool_query(pool, 'SET SQL_LOG_BIN=1') finally: if not alive: self.stop_tmp_mysql(proc)
def scan_index(connpool, table, result_fields, index_fields, index_values, left_open=False, limit=None, index_name=None, use_dict=True, retry=0): if type(connpool) == dict: # an address pool = mysqlconnpool.make(connpool) elif isinstance(connpool, mysqlconnpool.MysqlConnectionPool): pool = connpool else: raise ConnectionTypeError if len(index_values) != len(index_fields): raise InvalidLength('number of index fields and values are not equal: ' 'index fields: {fld}; ' 'index values: {val}'.format(fld=index_fields, val=index_values)) req_fields = list(index_fields) req_values = list(index_values) strict = True if limit is None: strict = False limit = 1024 while True: sql = make_index_scan_sql(table, None, req_fields, req_values, left_open=left_open, limit=limit, index_name=index_name) rst = pool.query(sql, retry=retry) for rr in rst: if use_dict: yield dict([(k, rr[k]) for k in result_fields]) else: yield [rr[k] for k in result_fields] if strict: break if len(rst) > 0: last_row = rst[-1] req_fields = list(index_fields) req_values = [str(last_row[x]) for x in req_fields] left_open = True continue req_fields = req_fields[:-1] req_values = req_values[:-1] if len(req_fields) > 0: continue break
def test_scan_index(self): addr = { 'host': base.mysql_test_ip, 'port': base.mysql_test_port, 'user': mysql_test_user, 'passwd': base.mysql_test_password, } conns = (addr, mysqlconnpool.make(addr), ) table = ('test', 'errlog') result_fields = ['_id'] cases = ( ([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], {}, ('8', '12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'), 'test common', ), ([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], {'left_open': True}, ('12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'), 'test left_open', ), ([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], {'limit': 3}, ('8', '12', '18', ), 'test limit', ), ([['autolvl', 'service', 'ip', '_id'], ['stable', 'common0', '127.0.0.1', '8']], {'index_name': 'idx_service_ip__id'}, ('12', '32', '2', '13', '19', '30', '22', '28', '6', '15', '21', '7'), 'test index_name', ), ([['service', 'ip', '_id'], ['common0', '127.0.0.1', 8]], {'left_open': True, 'limit': 3, 'index_name': 'idx_time__id'}, ('12', '18', '20', ), 'test all kwargs', ), ) for conn in conns: dd('conn: ', conn) for args, kwargs, rst_expect, msg in cases: args = [conn, table, result_fields] + args kwargs['use_dict'] = False dd('msg: ', msg) rst = mysqlutil.scan_index(*args, **kwargs) for i, rr in enumerate(rst): dd('rst:', rr) dd('except: ', rst_expect[i]) self.assertEqual(rr[0], long(rst_expect[i])) self.assertEqual(len(rst_expect), i+1) error_cases = ( ([addr, table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.2']], {}, mysqlutil.InvalidLength, 'test index_fields amount greater than index_values', ), ([addr, table, result_fields, ['service', 'ip'], ['common0', '127.0.0.2', '13']], {}, mysqlutil.InvalidLength, 'test index_fields amount less than index_values', ), (['addr', table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']], {}, mysqlutil.ConnectionTypeError, 'test conn type error', ), ) for args, kwargs, error, msg in error_cases: dd('msg: ', msg) try: rst = mysqlutil.scan_index(*args, **kwargs) except error as e: self.assertEqual(type(e), error)
def test_setup_user(self): # test grant cases = ( ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT", "state": "present"}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT,UPDATE"}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT']}}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT', "SELECT"]}}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT SELECT, INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT"}, {"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:UPDATE"}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT', "SELECT"]}}, {"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['UPDATE']}}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT SELECT, INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT,UPDATE"}, {"name": 'foo', "host": "127.0.0.1", "priv": "*.*:SELECT"}], ({'Grants for [email protected]': "GRANT SELECT ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ( "foo", [{"name": 'foo', "host": "%", "priv": "test.errlog:INSERT", "state": "present"}], ({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"}, {'Grants for foo@%': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'%'"}), ), ( "foo", [{"name": 'foo', "priv": "test.errlog:INSERT"}], ({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"}, {'Grants for foo@%': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'%'"}), ), ( "'foo'@'127.0.0.1'", [{"name": 'foo', "host": "127.0.0.1", "priv": {('test', 'errlog'): ['INSERT', "SELECT"]}}], ({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"}, {'Grants for [email protected]': "GRANT SELECT, INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}), ), ) for name, c, expected in cases: mysqlutil.setup_user(self.addr, c) pri = mysqlconnpool.conn_query(self.conn, 'SHOW GRANTS FOR ' + name) self.assertEqual(expected, pri) mysqlconnpool.conn_query(self.conn, 'DROP USER ' + name) # test revoke cases = ( [{"name": 'foo', "priv": "test.errlog:INSERT", 'state': 'absent'}], [{"name": 'foo', "priv": {"test.errlog": ["INSERT"]}, 'state': 'absent'}], [{"name": 'foo', "priv": {("test", "errlog"): ["INSERT"]}, 'state': 'absent'}], ) pris = [{"name": 'foo', "priv": "test.errlog:SELECT,INSERT,UPDATE"}] for c in cases: mysqlutil.setup_user(self.conn, pris) mysqlutil.setup_user(self.conn, c) res = mysqlconnpool.conn_query(self.conn, 'SHOW GRANTS FOR foo') expected = ({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"}, {'Grants for foo@%': "GRANT SELECT, UPDATE ON `test`.`errlog` TO 'foo'@'%'"}) self.assertEqual(expected, res) mysqlconnpool.conn_query(self.conn, 'DROP USER foo') # test password pris = [{"name": 'foo', 'host': '192.168.52.254', 'password': '******', "priv": "test.errlog:SELECT"}] mysqlutil.setup_user(self.conn_pool, pris) addr = { 'host': base.mysql_test_ip, 'port': base.mysql_test_port, 'user': '******', 'passwd': 'abcdef', } pool = mysqlconnpool.make(addr) rst = pool.query('SELECT * FROM `test`.`errlog`') self.assertGreater(len(rst), 0) # wrong password addr['passwd'] = '123456' pool = mysqlconnpool.make(addr) self.assertRaises(MySQLdb.OperationalError, pool.query, 'SELECT * FROM `test`.`errlog`') # use another host connect mysql pris = [{"name": 'bar', 'host': '10.10.10.10', 'password': '******', "priv": "test.errlog:SELECT"}] mysqlutil.setup_user(self.conn_pool, pris) addr['passwd'] = 'abcdef' addr['user'] = '******' pool = mysqlconnpool.make(addr) self.assertRaises(MySQLdb.OperationalError, pool.query, 'SELECT * FROM `test`.`errlog`') # test Exception self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'state': 'xx'}]) self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 123}]) self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 'db.tb'}]) self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 'db.tb:FOO'}])