def _get_compare_objects(index_cols, table1): """Build the compare table and identify the primary index This method creates the compare table for use in forming the MD5 hash of rows and a hash of the primary key. It also forms the primary key list of columns. index_cols[in] a list of columns that form the primary key in the form (column_name, type) table1[in] a Table instance of the original table Returns tuple (table create statement, concatenated string of the primary index columns) """ table = None # build primary key col definition index_str = ''.join("{0}, ".format(quote_with_backticks(col[0])) \ for col in index_cols) index_defn = ''.join("{0} {1}, ". format(quote_with_backticks(col[0]), col[1]) \ for col in index_cols) if index_defn == "": raise UtilError("Cannot generate index definition") else: # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table1.tbl_name)) table = _COMPARE_TABLE.format(db=table1.q_db_name, compare_tbl=q_tbl_name, pkdef=index_defn) return (table, index_str)
def database_diff(server1_val, server2_val, db1, db2, options): """Find differences among objects from two databases. This method compares the object definitions among two databases. If any differences are found, the differences are printed in the format chosen and the method returns False. A True result is returned only when all object definitions match. The method will stop and return False on the first difference found unless the option force is set to True (default = False). server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, force) Returns bool True if all object match, False if partial match """ from mysql.utilities.common.dbcompare import get_common_objects from mysql.utilities.common.dbcompare import server_connect force = options.get("force", False) server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2, True, options) in_both.sort() if (len(in_db1) > 0 or len(in_db2) > 0) and not force: return False # Do the diff for the databases themselves result = object_diff(server1, server2, db1, db2, options) if result is not None: success = False if not force: return False # For each that match, do object diff success = True for item in in_both: obj_name1 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db1) else item[1][0] obj_name2 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db2) else item[1][0] object1 = "%s.%s" % (db1, obj_name1) object2 = "%s.%s" % (db2, obj_name2) result = object_diff(server1, server2, object1, object2, options) if result is not None: success = False if not force: return False return success
def get_create_statement(self, db, name, obj_type): """Return the create statement for the object db[in] Database name name[in] Name of the object obj_type[in] Object type (string) e.g. DATABASE Note: this is used to form the correct SHOW command Returns create statement """ row = None q_name = name if is_quoted_with_backticks(name) else quote_with_backticks(name) if obj_type == _DATABASE: name_str = q_name else: q_db = db if is_quoted_with_backticks(db) else quote_with_backticks(db) name_str = q_db + "." + q_name row = self.source.exec_query("SHOW CREATE %s %s" % (obj_type, name_str)) create_statement = None if row: if obj_type == _TABLE or obj_type == _VIEW or obj_type == _DATABASE: create_statement = row[0][1] elif obj_type == _EVENT: create_statement = row[0][3] else: create_statement = row[0][2] return create_statement
def database_diff(server1_val, server2_val, db1, db2, options): """Find differences among objects from two databases. This method compares the object definitions among two databases. If any differences are found, the differences are printed in the format chosen and the method returns False. A True result is returned only when all object definitions match. The method will stop and return False on the first difference found unless the option force is set to True (default = False). server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, force) Returns bool True if all object match, False if partial match """ from mysql.utilities.common.dbcompare import get_common_objects from mysql.utilities.common.dbcompare import server_connect force = options.get("force", False) server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2, True, options) in_both.sort() if (len(in_db1) > 0 or len(in_db2) > 0) and not force: return False # Do the diff for the databases themselves result = object_diff(server1, server2, db1, db2, options) if result is not None: success = False if not force: return False # For each that match, do object diff success = True for item in in_both: obj_name1 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db1) else item[1][0] obj_name2 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db2) else item[1][0] object1 = "%s.%s" % (db1, obj_name1) object2 = "%s.%s" % (db2, obj_name2) result = object_diff(server1, server2, object1, object2, options) if result is not None: success = False if not force: return False return success
def _get_compare_objects(index_cols, table1): """Build the compare table and identify the primary index This method creates the compare table for use in forming the MD5 hash of rows and a hash of the primary key. It also forms the primary key list of columns. index_cols[in] a list of columns that form the primary key in the form (column_name, type) table1[in] a Table instance of the original table Returns tuple (table create statement, concatenated string of the primary index columns) """ table = None # build primary key col definition index_str = ''.join("{0}, ".format(quote_with_backticks(col[0])) \ for col in index_cols) index_defn = ''.join("{0} {1}, ". format(quote_with_backticks(col[0]), col[1]) \ for col in index_cols) if index_defn == "": raise UtilError("Cannot generate index definition") else: # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table1.tbl_name)) table = _COMPARE_TABLE.format(db=table1.q_db_name, compare_tbl=q_tbl_name, pkdef=index_defn) return (table, index_str)
def get_create_statement(self, db, name, obj_type): """Return the create statement for the object db[in] Database name name[in] Name of the object obj_type[in] Object type (string) e.g. DATABASE Note: this is used to form the correct SHOW command Returns create statement """ row = None q_name = name if is_quoted_with_backticks(name) \ else quote_with_backticks(name) if obj_type == _DATABASE: name_str = q_name else: q_db = db if is_quoted_with_backticks(db) \ else quote_with_backticks(db) name_str = q_db + "." + q_name row = self.source.exec_query("SHOW CREATE %s %s" % (obj_type, name_str)) create_statement = None if row: if obj_type == _TABLE or obj_type == _VIEW or \ obj_type == _DATABASE: create_statement = row[0][1] elif obj_type == _EVENT: create_statement = row[0][3] else: create_statement = row[0][2] return create_statement
def __init__(self, db, index_tuple, verbose=False): """Constructor db[in] Name of database index_tuple[in] A tuple from the get_tbl_indexes() result set verbose[in] print extra data during operations (optional) default value = False """ # Initialize and save values self.db = db self.q_db = quote_with_backticks(db) self.verbose = verbose self.columns = [] self.table = index_tuple[0] self.q_table = quote_with_backticks(index_tuple[0]) self.unique = not index_tuple[1] self.name = index_tuple[2] self.q_name = quote_with_backticks(index_tuple[2]) col = (index_tuple[4], index_tuple[7]) self.columns.append(col) self.type = index_tuple[10] self.compared = False # mark as compared for speed self.duplicate_of = None # saves duplicate index if index_tuple[7] > 0: self.column_subparts = True # check subparts e.g. a(20) else: self.column_subparts = False
def has_privilege(self, db, obj, access, allow_skip_grant_tables=True): """Check to see user has a specific access to a db.object. db[in] Name of database obj[in] Name of object access[in] MySQL privilege to check (e.g. SELECT, SUPER, DROP) allow_skip_grant_tables[in] If True, allow silent failure for cases where the server is started with --skip-grant-tables. Default=True Returns True if user has access, False if not """ grants_enabled = self.server1.grant_tables_enabled() # If grants are disabled and it is Ok to allow skipped grant tables, # return True - privileges disabled so user can do anything. if allow_skip_grant_tables and not grants_enabled: return True # Convert privilege to upper cases. access = access.upper() # Get grant dictionary grant_dict = self.get_grants(globals_privs=True, as_dict=True) # If self has all privileges for all databases, no need to check, # simply return True if ("ALL PRIVILEGES" in grant_dict['*']['*'] and "GRANT OPTION" in grant_dict['*']['*']): return True # Quote db and obj with backticks if necessary if not is_quoted_with_backticks(db) and db != '*': db = quote_with_backticks(db) if not is_quoted_with_backticks(obj) and obj != '*': obj = quote_with_backticks(obj) # USAGE privilege is the same as no privileges, # so everyone has it. if access == "USAGE": return True # Even if we have ALL PRIVILEGES grant, we might not have WITH GRANT # OPTION privilege. # Check server wide grants. elif (access in grant_dict['*']['*'] or "ALL PRIVILEGES" in grant_dict['*']['*'] and access != "GRANT OPTION"): return True # Check database level grants. elif (access in grant_dict[db]['*'] or "ALL PRIVILEGES" in grant_dict[db]['*'] and access != "GRANT OPTION"): return True # Check object level grants. elif (access in grant_dict[db][obj] or "ALL PRIVILEGES" in grant_dict[db][obj] and access != "GRANT OPTION"): return True else: return False
def _parse_grant_statement(statement, sql_mode=''): """ Returns a namedtuple with the parsed GRANT information. statement[in] Grant string in the sql format returned by the server. Returns named tuple with GRANT information or None. """ grant_parse_re = re.compile( r""" GRANT\s(.+)?\sON\s # grant or list of grants (?:(?:PROCEDURE\s)|(?:FUNCTION\s))? # optional for routines only (?:(?:(\*|`?[^']+`?)\.(\*|`?[^']+`?)) # object where grant applies | ('[^']*'@'[^']*')) # For proxy grants user/host \sTO\s([^@]+@[\S]+) # grantee (?:\sIDENTIFIED\sBY\sPASSWORD (?:(?:\s<secret>)|(?:\s\'[^\']+\')?))? # optional pwd (?:\sREQUIRE\sSSL)? # optional SSL (\sWITH\sGRANT\sOPTION)? # optional grant option $ # End of grant statement """, re.VERBOSE) grant_tpl_factory = namedtuple( "grant_info", "privileges proxy_user " "db object user") match = re.match(grant_parse_re, statement) if match: # quote database name and object name with backticks if match.group(1).upper() != 'PROXY': db = match.group(2) if not is_quoted_with_backticks(db, sql_mode) and db != '*': db = quote_with_backticks(db, sql_mode) obj = match.group(3) if not is_quoted_with_backticks(obj, sql_mode) and obj != '*': obj = quote_with_backticks(obj, sql_mode) else: # if it is not a proxy grant db = obj = None grants = grant_tpl_factory( # privileges set([priv.strip() for priv in match.group(1).split(",")]), match.group(4), # proxied user db, # database obj, # object match.group(5), # user ) # If user has grant option, add it to the list of privileges if match.group(6) is not None: grants.privileges.add("GRANT OPTION") else: raise UtilError("Unable to parse grant statement " "{0}".format(statement)) return grants
def _parse_grant_statement(statement): """ Returns a namedtuple with the parsed GRANT information. statement[in] Grant string in the sql format returned by the server. Returns named tuple with GRANT information or None. """ grant_parse_re = re.compile(r""" GRANT\s(.+)?\sON\s # grant or list of grants (?:(?:PROCEDURE\s)|(?:FUNCTION\s))? # optional for routines only (?:(?:(\*|`?[^']+`?)\.(\*|`?[^']+`?)) # object where grant applies | ('[^']*'@'[^']*')) # For proxy grants user/host \sTO\s([^@]+@[\S]+) # grantee (?:\sIDENTIFIED\sBY\sPASSWORD(?:\s\'[^\']+\')?)? # optional pwd (?:\sREQUIRE\sSSL)? # optional SSL (\sWITH\sGRANT\sOPTION)? # optional grant option $ # End of grant statement """, re.VERBOSE) grant_tpl_factory = namedtuple("grant_info", "privileges proxy_user " "db object user") match = re.match(grant_parse_re, statement) if match: # quote database name and object name with backticks if match.group(1).upper() != 'PROXY': db = match.group(2) if not is_quoted_with_backticks(db) and db != '*': db = quote_with_backticks(db) obj = match.group(3) if not is_quoted_with_backticks(obj) and obj != '*': obj = quote_with_backticks(obj) else: # if it is not a proxy grant db = obj = None grants = grant_tpl_factory( # privileges set([priv.strip() for priv in match.group(1).split(",")]), match.group(4), # proxied user db, # database obj, # object match.group(5), # user ) # If user has grant option, add it to the list of privileges if match.group(6) is not None: grants.privileges.add("GRANT OPTION") else: raise UtilError("Unable to parse grant statement " "{0}".format(statement)) return grants
def drop(self, server, quiet, db_name=None): """Drop the database server[in] A Server object quiet[in] ignore error on drop db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully dropped, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) \ else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False if quiet: try: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True except: pass else: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
def drop(self, server, quiet, db_name=None): """Drop the database server[in] A Server object quiet[in] ignore error on drop db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully dropped, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False if quiet: try: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True except: pass else: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
def copy_data(self, new_db, options, new_server=None, connections=1): """Copy the data for the tables. This method will copy the data for all of the tables to another, new database. The method will process an input file with INSERT statements if the option was selected by the caller. new_db[in] Name of the new database options[in] Options for copy e.g. force, etc. new_server[in] Connection to another server for copying the db Default is None (copy to same server - clone) connections[in] Number of threads(connections) to use for insert """ from mysql.utilities.common.table import Table # Must call init() first! # Guard for init() prerequisite assert self.init_called, "You must call db.init() before "+ \ "db.copy_data()." if self.skip_data: return self.destination = new_server # We know we're cloning if there is no new connection. self.cloning = (new_server == self.source) if self.cloning: self.destination = self.source quiet = options.get("quiet", False) tbl_options = { 'verbose' : self.verbose, 'get_cols' : True, 'quiet' : quiet } table_names = [obj[0] for obj in self.get_db_objects(_TABLE)] for tblname in table_names: if not quiet: print "# Copying data for TABLE %s.%s" % (self.db_name, tblname) tbl = Table(self.source, "%s.%s" % (self.q_db_name, quote_with_backticks(tblname)), tbl_options) if tbl is None: raise UtilDBError("Cannot create table object before copy.", -1, self.db_name) tbl.copy_data(self.destination, self.cloning, new_db, connections)
def _drop_compare_object(server, db_name, tbl_name): """Drop the compare object table server[in] Server instance db_name[in] database name tbl_name[in] table name """ # Quote compare table appropriately with backticks q_db_name = db_name if is_quoted_with_backticks(db_name) \ else quote_with_backticks(db_name) if is_quoted_with_backticks(tbl_name): q_tbl_name = remove_backtick_quoting(tbl_name) else: q_tbl_name = tbl_name q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=q_tbl_name)) try: server.exec_query(_COMPARE_TABLE_DROP.format(db=q_db_name, compare_tbl=q_tbl_name)) except: pass
def _get_rows_span(table, span): """Get the rows corresponding to a list of span values This method returns the rows from the original table that match the span value presented. TODO: This may need refactoring to make it more efficient. For example, use a WHERE clause such as: WHERE some_col IN ('a','b') table[in] Table instance span[in] span value Returns rows from original table """ server = table.server rows = [] # build WHERE clause for row in span: # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table.tbl_name)) res1 = server.exec_query( _COMPARE_DIFF.format(db=table.q_db_name, compare_tbl=q_tbl_name, span=row)) pk = res1[0][2:len(res1[0]) - 1] pkeys = [quote_with_backticks(col[0]) for col in table.get_primary_index()] where_clause = ' AND '.join("{0} = '{1}'". format(key, col) for key, col in zip(pkeys, pk)) res2 = server.exec_query( _COMPARE_SPAN_QUERY.format(db=table.q_db_name, table=table.q_tbl_name, where=where_clause)) rows.append(res2[0]) return rows
def _drop_compare_object(server, db_name, tbl_name): """Drop the compare object table server[in] Server instance db_name[in] database name tbl_name[in] table name """ # Quote compare table appropriately with backticks q_db_name = db_name if is_quoted_with_backticks(db_name) \ else quote_with_backticks(db_name) if is_quoted_with_backticks(tbl_name): q_tbl_name = remove_backtick_quoting(tbl_name) else: q_tbl_name = tbl_name q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=q_tbl_name)) try: server.exec_query( _COMPARE_TABLE_DROP.format(db=q_db_name, compare_tbl=q_tbl_name)) except: pass
def get_col_names(self, quote_backticks=False): """Get column names for the export operation. quote_backticks[in] If True the column names will be quoted with backticks. Default is False. Return (list) column names """ if self.column_format is None: self.column_names = [] rows = self.server.exec_query("explain %s" % self.q_table) for row in rows: self.column_names.append(row[0]) self.q_column_names.append(quote_with_backticks(row[0])) return self.q_column_names if quote_backticks else self.column_names
def get_col_names(self, quote_backticks=False): """Get column names for the export operation. quote_backticks[in] If True the column names will be quoted with backticks. Default is False. Return (list) column names """ if self.column_format is None: self.column_names = [] self.q_column_names = [] rows = self.server.exec_query("explain %s" % self.q_table) for row in rows: self.column_names.append(row[0]) self.q_column_names.append(quote_with_backticks(row[0])) return self.q_column_names if quote_backticks else self.column_names
def get_column_metadata(self, columns=None): """Get information about the table for the bulk insert operation. This method builds lists that describe the metadata of the table. This includes lists for: column names column format for building VALUES clause blob fields - for use in generating INSERT/UPDATE for blobs text fields - for use in checking for single quotes columns[in] if None, use EXPLAIN else use column list. """ if columns is None: columns = self.server.exec_query("explain %s" % self.q_table) stop = len(columns) self.column_names = [] self.q_column_names = [] col_format_values = [''] * stop if columns is not None: for col in range(0, stop): if is_quoted_with_backticks(columns[col][0]): self.column_names.append( remove_backtick_quoting(columns[col][0])) self.q_column_names.append(columns[col][0]) else: self.column_names.append(columns[col][0]) self.q_column_names.append( quote_with_backticks(columns[col][0])) col_type_prefix = columns[col][1][0:4].lower() if col_type_prefix in ('varc', 'char', 'enum', 'set('): self.text_columns.append(col) col_format_values[col] = "'%s'" elif col_type_prefix in ("blob", "text"): self.blob_columns.append(col) col_format_values[col] = "%s" elif col_type_prefix in ("date", "time"): col_format_values[col] = "'%s'" else: col_format_values[col] = "%s" self.column_format = "%s%s%s" % \ (" (", ', '.join(col_format_values), ")")
def copy_data(self, new_db, options, new_server=None, connections=1): """Copy the data for the tables. This method will copy the data for all of the tables to another, new database. The method will process an input file with INSERT statements if the option was selected by the caller. new_db[in] Name of the new database options[in] Options for copy e.g. force, etc. new_server[in] Connection to another server for copying the db Default is None (copy to same server - clone) connections[in] Number of threads(connections) to use for insert """ from mysql.utilities.common.table import Table # Must call init() first! # Guard for init() prerequisite assert self.init_called, "You must call db.init() before " + "db.copy_data()." if self.skip_data: return self.destination = new_server # We know we're cloning if there is no new connection. self.cloning = new_server == self.source if self.cloning: self.destination = self.source quiet = options.get("quiet", False) tbl_options = {"verbose": self.verbose, "get_cols": True, "quiet": quiet} table_names = [obj[0] for obj in self.get_db_objects(_TABLE)] for tblname in table_names: if not quiet: print "# Copying data for TABLE %s.%s" % (self.db_name, tblname) tbl = Table(self.source, "%s.%s" % (self.q_db_name, quote_with_backticks(tblname)), tbl_options) if tbl is None: raise UtilDBError("Cannot create table object before copy.", -1, self.db_name) tbl.copy_data(self.destination, self.cloning, new_db, connections)
def create(self, server, db_name=None): """Create the database server[in] A Server object db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully created, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False res = server.exec_query("CREATE DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
def copy_data(self, destination, cloning=False, new_db=None, connections=1): """Retrieve data from a table and copy to another server and database. Reads data from a table and inserts the correct INSERT statements into the file provided. Note: if connections < 1 - retrieve the data one row at-a-time destination[in] Destination server cloning[in] If True, we are copying on the same server new_db[in] Rename the db to this name connections[in] Number of threads(connections) to use for insert """ if new_db is None: new_db = self.q_db_name else: # If need quote new_db identifier with backticks if not is_quoted_with_backticks(new_db): new_db = quote_with_backticks(new_db) num_conn = int(connections) if cloning: self._clone_data(new_db) else: # Read and copy the data pthreads = [] for rows in self.retrieve_rows(num_conn): p = self.insert_rows(rows, new_db, destination, num_conn > 1) if p is not None: p.start() pthreads.append(p) if num_conn > 1: # Wait for all to finish num_complete = 0 while num_complete < len(pthreads): for p in pthreads: if not p.is_alive(): num_complete += 1
def __init__(self, source, name, options={}): """Constructor source[in] A Server object name[in] Name of database verbose[in] print extra data during operations (optional) default value = False options[in] Array of options for controlling what is included and how operations perform (e.g., verbose) """ self.source = source # Keep database identifier considering backtick quotes if is_quoted_with_backticks(name): self.q_db_name = name self.db_name = remove_backtick_quoting(self.q_db_name) else: self.db_name = name self.q_db_name = quote_with_backticks(self.db_name) self.verbose = options.get("verbose", False) self.skip_tables = options.get("skip_tables", False) self.skip_views = options.get("skip_views", False) self.skip_triggers = options.get("skip_triggers", False) self.skip_procs = options.get("skip_procs", False) self.skip_funcs = options.get("skip_funcs", False) self.skip_events = options.get("skip_events", False) self.skip_grants = options.get("skip_grants", False) self.skip_create = options.get("skip_create", False) self.skip_data = options.get("skip_data", False) self.exclude_patterns = options.get("exclude_patterns", None) self.use_regexp = options.get("use_regexp", False) self.new_db = None self.q_new_db = None self.init_called = False self.destination = None # Used for copy mode self.cloning = False # Used for clone mode self.query_options = { # Used for skipping fetch of rows 'fetch' : False } self.objects = [] self.new_objects = []
def _make_sum_rows(table, idx_str): """Populate the summary table This method inserts rows into the compare table from the original table then forms the summary table by combining a prefix of the primary key hash (group by). table[in] Table instance idx_str[in] string representation of primary key columns Returns result from """ from mysql.utilities.common.lock import Lock col_str = ", ".join(table.get_col_names(True)) # Lock table first tbl_lock_list = [(table.table, 'READ'), ("%s.compare_%s" % (table.db_name, table.tbl_name), 'WRITE')] my_lock = Lock(table.server, tbl_lock_list) # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table.tbl_name)) table.server.exec_query( _COMPARE_INSERT.format(db=table.q_db_name, compare_tbl=q_tbl_name, colstr=col_str.strip(", "), pkstr=idx_str.strip(", "), table=table.q_tbl_name)) res = table.server.exec_query( _COMPARE_SUM.format(db=table.q_db_name, compare_tbl=q_tbl_name)) # Unlock table my_lock.unlock() return res
def _make_sum_rows(table, idx_str): """Populate the summary table This method inserts rows into the compare table from the original table then forms the summary table by combining a prefix of the primary key hash (group by). table[in] Table instance idx_str[in] string representation of primary key columns Returns result from """ from mysql.utilities.common.lock import Lock col_str = ", ".join(table.get_col_names(True)) # Lock table first tbl_lock_list = [ (table.table, 'READ'), ("%s.compare_%s" % (table.db_name, table.tbl_name), 'WRITE') ] my_lock = Lock(table.server, tbl_lock_list) # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table.tbl_name)) table.server.exec_query( _COMPARE_INSERT.format(db=table.q_db_name, compare_tbl=q_tbl_name, colstr=col_str.strip(", "), pkstr=idx_str.strip(", "), table=table.q_tbl_name)) res = table.server.exec_query( _COMPARE_SUM.format(db=table.q_db_name, compare_tbl=q_tbl_name)) # Unlock table my_lock.unlock() return res
def create(self, server, db_name=None): """Create the database server[in] A Server object db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully created, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) \ else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False res = server.exec_query("CREATE DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
def __get_column_list(self, backtick_quoting=True): """Get the column list for an index This method is used to print the CREATE and DROP statements. backtick_quoting[in] Indicates if the columns names are to be quoted with backticks or not. By default: True. Returns a string representing the list of columns for a column list. e.g. 'a, b(10), c' """ col_list = [] for col in self.columns: name, sub_part = (col[0], col[1]) if backtick_quoting: name = quote_with_backticks(name) if sub_part > 0: col_str = "{0}({1})".format(name, sub_part) else: col_str = name col_list.append(col_str) return ', '.join(col_list)
def copy_data(self, destination, cloning=False, new_db=None, connections=1): """Retrieve data from a table and copy to another server and database. Reads data from a table and inserts the correct INSERT statements into the file provided. Note: if connections < 1 - retrieve the data one row at-a-time destination[in] Destination server cloning[in] If True, we are copying on the same server new_db[in] Rename the db to this name connections[in] Number of threads(connections) to use for insert """ if new_db is None: new_db = self.q_db_name else: # If need quote new_db identifier with backticks if not is_quoted_with_backticks(new_db): new_db = quote_with_backticks(new_db) num_conn = int(connections) if cloning: self._clone_data(new_db) else: # Read and copy the data pthreads = [] for rows in self.retrieve_rows(num_conn): p = self.insert_rows(rows, new_db, destination, num_conn > 1) if p is not None: p.start() pthreads.append(p) if num_conn > 1: # Wait for all threads to finish for p in pthreads: p.join()
def __init__(self, source, name, options={}): """Constructor source[in] A Server object name[in] Name of database verbose[in] print extra data during operations (optional) default value = False options[in] Array of options for controlling what is included and how operations perform (e.g., verbose) """ self.source = source # Keep database identifier considering backtick quotes if is_quoted_with_backticks(name): self.q_db_name = name self.db_name = remove_backtick_quoting(self.q_db_name) else: self.db_name = name self.q_db_name = quote_with_backticks(self.db_name) self.verbose = options.get("verbose", False) self.skip_tables = options.get("skip_tables", False) self.skip_views = options.get("skip_views", False) self.skip_triggers = options.get("skip_triggers", False) self.skip_procs = options.get("skip_procs", False) self.skip_funcs = options.get("skip_funcs", False) self.skip_events = options.get("skip_events", False) self.skip_grants = options.get("skip_grants", False) self.skip_create = options.get("skip_create", False) self.skip_data = options.get("skip_data", False) self.exclude_patterns = options.get("exclude_patterns", None) self.use_regexp = options.get("use_regexp", False) self.new_db = None self.q_new_db = None self.init_called = False self.destination = None # Used for copy mode self.cloning = False # Used for clone mode self.query_options = {"fetch": False} # Used for skipping fetch of rows self.objects = [] self.new_objects = []
def _get_rows_span(table, span): """Get the rows corresponding to a list of span values This method returns the rows from the original table that match the span value presented. TODO: This may need refactoring to make it more efficient. For example, use a WHERE clause such as: WHERE some_col IN ('a','b') table[in] Table instance span[in] span value Returns rows from original table """ server = table.server rows = [] # build WHERE clause for row in span: # Quote compare table appropriately with backticks q_tbl_name = quote_with_backticks( _COMPARE_TABLE_NAME.format(tbl=table.tbl_name)) res1 = server.exec_query( _COMPARE_DIFF.format(db=table.q_db_name, compare_tbl=q_tbl_name, span=row)) pk = res1[0][2:len(res1[0])-1] pkeys = [col[0] for col in table.get_primary_index()] where_clause = ' AND '.join("{0} = '{1}'". format(key, col) for key, col in zip(pkeys, pk)) res2 = server.exec_query( _COMPARE_SPAN_QUERY.format(db=table.q_db_name, table=table.q_tbl_name, where=where_clause)) rows.append(res2[0]) return rows
def _build_update_blob(self, row, new_db, name, blob_col): """Build an UPDATE statement to update blob fields. row[in] a row to process new_db[in] new database name name[in] name of the table conn_val[in] connection information for the destination server query[in] the INSERT string for executemany() blob_col[in] number of the column containing the blob Returns tuple (UPDATE string, blob data) """ from mysql.connector.conversion import MySQLConverter if self.column_format is None: self.get_column_metadata() blob_insert = "UPDATE %s.%s SET " % (new_db, name) where_values = [] do_commas = False has_data = False stop = len(row) for col in range(0,stop): col_name = quote_with_backticks(self.column_names[col]) if col in self.blob_columns: if row[col] is not None and len(row[col]) > 0: if do_commas: blob_insert += ", " blob_insert += "%s = " % col_name + "%s" % \ MySQLConverter().quote(row[col]) has_data = True do_commas = True else: where_values.append("%s = '%s' " % (col_name, row[col])) if has_data: return blob_insert + " WHERE " + " AND ".join(where_values) + ";" return None
def _export_data(source, server_values, db_list, output_file, options): """Export data from the specified list of databases. This private method retrieves the data for each specified databases in SQL format (e.g., INSERT statements) or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified file. This private method does not check permissions. source[in] Server instance. server_values[in] Server connection values. db_list[in] List of databases to export. output_file[in] Output file to store the export data. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug). """ frmt = options.get("format", "sql") quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) # Get tables list. table_list = [] for db_name in db_list: source_db = Database(source, db_name) # Build table list. tables = source_db.get_db_objects("TABLE") for table in tables: table_list.append((db_name, table[0])) previous_db = "" export_tbl_tasks = [] for table in table_list: # Determine start for processing table from a different database. db_name = table[0] if previous_db != db_name: previous_db = db_name if not quiet: if frmt == "sql": q_db_name = quote_with_backticks(db_name) output_file.write("USE {0};\n".format(q_db_name)) output_file.write( "# Exporting data from {0}\n".format(db_name) ) if file_per_table: output_file.write("# Writing table data to files.\n") # Print sample SOURCE command warning even in quiet mode. if file_per_table and frmt == 'sql': output_file.write("# The following are sample SOURCE commands." " If needed correct the path to match files " "location.\n") # Check multiprocess table export (only on POSIX systems). if options['multiprocess'] > 1 and os.name == 'posix': # Create export task. # Note: Server connection values are passed in the task dictionary # instead of a server instance, otherwise a multiprocessing error # is issued when assigning the task to a worker. export_task = { 'srv_con': server_values, 'table': table, 'options': options, } export_tbl_tasks.append(export_task) else: # Export data from a table (no multiprocessing). _export_table_data(source, table, output_file, options) # Print SOURCE command if --file-per-table is used and format is SQL. if file_per_table and frmt == 'sql': tbl_name = ".".join(table) output_file.write( "# SOURCE {0}\n".format(_generate_tbl_filename(tbl_name, frmt)) ) # Export tables concurrently. if export_tbl_tasks: # Create process pool. workers_pool = multiprocessing.Pool( processes=options['multiprocess'] ) # Concurrently export tables. res = workers_pool.map_async(multiprocess_tbl_export_task, export_tbl_tasks) workers_pool.close() # Get list of temporary files with the exported data. tmp_files_list = res.get() workers_pool.join() # Merge resulting temp files (if generated). for tmp_filename in tmp_files_list: if tmp_filename: tmp_file = open(tmp_filename, 'r') shutil.copyfileobj(tmp_file, output_file) tmp_file.close() os.remove(tmp_filename) if not quiet: output_file.write("#...done.\n")
def _export_metadata(source, db_list, output_file, options): """Export metadata from the specified list of databases. This private method retrieves the objects metadata for each database listed in the form of CREATE (SQL) statements or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified file. This private method does not check permissions. source[in] Server instance. db_list[in] List of databases to export. output_file[in] Output file to store the metadata information. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, debug, exclude_names, exclude_patterns) """ frmt = options.get("format", "sql") no_headers = options.get("no_headers", False) column_type = options.get("display", "brief") quiet = options.get("quiet", False) skip_create = options.get("skip_create", False) skip_tables = options.get("skip_tables", False) skip_views = options.get("skip_views", False) skip_triggers = options.get("skip_triggers", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) for db_name in db_list: # Get a Database class instance db = Database(source, db_name, options) # Export database metadata if not quiet: output_file.write( "# Exporting metadata from {0}\n".format(db.db_name) ) # Perform the extraction if frmt == "sql": db.init() if not skip_create: output_file.write( "DROP DATABASE IF EXISTS {0};\n".format(db.q_db_name) ) output_file.write( "CREATE DATABASE {0};\n".format(db.q_db_name) ) output_file.write("USE {0};\n".format(db.q_db_name)) for dbobj in db.get_next_object(): if dbobj[0] == "GRANT" and not skip_grants: if not quiet: output_file.write("# Grant:\n") if dbobj[1][3]: create_str = "GRANT {0} ON {1}.{2} TO {3};\n".format( dbobj[1][1], db.q_db_name, quote_with_backticks(dbobj[1][3]), dbobj[1][0] ) else: create_str = "GRANT {0} ON {1}.* TO {2};\n".format( dbobj[1][1], db.q_db_name, dbobj[1][0] ) output_file.write(create_str) else: if not quiet: output_file.write( "# {0}: {1}.{2}\n".format(dbobj[0], db.db_name, dbobj[1][0]) ) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): output_file.write("DELIMITER ||\n") output_file.write("{0};\n".format( db.get_create_statement(db.db_name, dbobj[1][0], dbobj[0]) )) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): output_file.write("||\n") output_file.write("DELIMITER ;\n") else: objects = [] if not skip_tables: objects.append("TABLE") if not skip_funcs: objects.append("FUNCTION") if not skip_procs: objects.append("PROCEDURE") if not skip_views: objects.append("VIEW") if not skip_triggers: objects.append("TRIGGER") if not skip_events: objects.append("EVENT") if not skip_grants: objects.append("GRANT") for obj_type in objects: output_file.write( "# {0}S in {1}:".format(obj_type, db.db_name) ) if frmt in ('grid', 'vertical'): rows = db.get_db_objects(obj_type, column_type, True) else: rows = db.get_db_objects(obj_type, column_type, True, True) if len(rows[1]) < 1: output_file.write(" (none found)\n") else: output_file.write("\n") # Cannot use print_list here because we must manipulate # the behavior of format_tabular_list. list_options = {} if frmt == "vertical": format_vertical_list(output_file, rows[0], rows[1]) elif frmt == "tab": list_options['print_header'] = not no_headers list_options['separator'] = '\t' format_tabular_list(output_file, rows[0], rows[1], list_options) elif frmt == "csv": list_options['print_header'] = not no_headers list_options['separator'] = ',' format_tabular_list(output_file, rows[0], rows[1], list_options) else: # default to table format format_tabular_list(output_file, rows[0], rows[1]) if not quiet: output_file.write("#...done.\n")
def export_metadata(source, src_val, db_list, options): """Produce rows to be used to recreate objects in a database. This method retrieves the objects for each database listed in the form of CREATE (SQL) statements or in a tabular form to the file specified. The valid values for the format parameter are SQL, CSV, TSV, VERTICAL, or GRID. source[in] Server instance src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) options[in] a dictionary containing the options for the copy: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, debug, exclude_names, exclude_patterns) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.format import format_tabular_list from mysql.utilities.common.format import format_vertical_list format = options.get("format", "sql") no_headers = options.get("no_headers", False) column_type = options.get("display", "brief") skip_create = options.get("skip_create", False) quiet = options.get("quiet", False) skip_tables = options.get("skip_tables", False) skip_views = options.get("skip_views", False) skip_triggers = options.get("skip_triggers", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) if options.get("all", False): rows = source.get_all_databases() for row in rows: db_list.append(row[0]) # Check user permissions on source for all databases for db_name in db_list: source_db = Database(source, db_name) # Make a dictionary of the options access_options = { 'skip_views' : skip_views, 'skip_procs' : skip_procs, 'skip_funcs' : skip_funcs, 'skip_grants' : skip_grants, 'skip_events' : skip_events, } source_db.check_read_access(src_val["user"], src_val["host"], access_options) for db_name in db_list: # Get a Database class instance db = Database(source, db_name, options) # Error is source database does not exist if not db.exists(): raise UtilDBError("Source database does not exist - %s" % db_name, -1, db_name) if not quiet: print "# Exporting metadata from %s" % db_name # Perform the extraction if format == "sql": db.init() # quote database name with backticks q_db_name = quote_with_backticks(db_name) if not skip_create: print "DROP DATABASE IF EXISTS %s;" % q_db_name print "CREATE DATABASE %s;" % q_db_name print "USE %s;" % q_db_name for dbobj in db.get_next_object(): if dbobj[0] == "GRANT" and not skip_grants: if not quiet: print "# Grant:" if dbobj[1][3]: create_str = "GRANT %s ON %s.%s TO %s;" % \ (dbobj[1][1], q_db_name, quote_with_backticks(dbobj[1][3]), dbobj[1][0]) else: create_str = "GRANT %s ON %s.* TO %s;" % \ (dbobj[1][1], q_db_name, dbobj[1][0]) if create_str.find("%"): create_str = re.sub("%", "%%", create_str) print create_str else: if not quiet: print "# %s: %s.%s" % (dbobj[0], db_name, dbobj[1][0]) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): print "DELIMITER ||" print "%s;" % db.get_create_statement(db_name, dbobj[1][0], dbobj[0]) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): print "||" print "DELIMITER ;" else: objects = [] if not skip_tables: objects.append("TABLE") if not skip_views: objects.append("VIEW") if not skip_triggers: objects.append("TRIGGER") if not skip_procs: objects.append("PROCEDURE") if not skip_funcs: objects.append("FUNCTION") if not skip_events: objects.append("EVENT") if not skip_grants: objects.append("GRANT") for obj_type in objects: sys.stdout.write("# %sS in %s:" % (obj_type, db_name)) if format in ('grid', 'vertical'): rows = db.get_db_objects(obj_type, column_type, True) else: rows = db.get_db_objects(obj_type, column_type, True, True) if len(rows[1]) < 1: print " (none found)" else: print # Cannot use print_list here becasue we must manipulate # the behavior of format_tabular_list list_options = {} if format == "vertical": format_vertical_list(sys.stdout, rows[0], rows[1]) elif format == "tab": list_options['print_header'] = not no_headers list_options['separator'] = '\t' format_tabular_list(sys.stdout, rows[0], rows[1], list_options) elif format == "csv": list_options['print_header'] = not no_headers list_options['separator'] = ',' format_tabular_list(sys.stdout, rows[0], rows[1], list_options) else: # default to table format format_tabular_list(sys.stdout, rows[0], rows[1]) if not quiet: print "#...done." return True
def export_data(source, src_val, db_list, options): """Produce data for the tables in a database. This method retrieves the data for each table in the databases listed in the form of BULK INSERT (SQL) statements or in a tabular form to the file specified. The valid values for the format parameter are SQL, CSV, TSV, VERITCAL, or GRID. source[in] Server instance src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) options[in] a dictionary containing the options for the copy: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.table import Table format = options.get("format", "sql") no_headers = options.get("no_headers", True) column_type = options.get("display", "brief") single = options.get("single", False) skip_blobs = options.get("skip_blobs", False) quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) skip_views = options.get("skip_views", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) if options.get("all", False): rows = source.get_all_databases() for row in rows: if row[0] not in db_list: db_list.append(row[0]) # Check if database exists and user permissions on source for all databases table_lock_list = [] table_list = [] for db_name in db_list: source_db = Database(source, db_name) # Make a dictionary of the options access_options = { 'skip_views' : skip_views, 'skip_procs' : skip_procs, 'skip_funcs' : skip_funcs, 'skip_grants' : skip_grants, 'skip_events' : skip_events, } # Error is source database does not exist if not source_db.exists(): raise UtilDBError("Source database does not exist - %s" % db_name, -1, db_name) source_db.check_read_access(src_val["user"], src_val["host"], access_options) # Build table list tables = source_db.get_db_objects("TABLE") for table in tables: table_list.append((db_name, table[0])) old_db = "" for table in table_list: db_name = table[0] tbl_name = "%s.%s" % (db_name, table[1]) # quote database and table name with backticks q_db_name = quote_with_backticks(db_name) q_tbl_name = "%s.%s" % (q_db_name, quote_with_backticks(table[1])) if not quiet and old_db != db_name: old_db = db_name if format == "sql": print "USE %s;" % q_db_name print "# Exporting data from %s" % db_name if file_per_table: print "# Writing table data to files." tbl_options = { 'verbose' : False, 'get_cols' : True, 'quiet' : quiet } cur_table = Table(source, q_tbl_name, tbl_options) if single and format not in ("sql", "grid", "vertical"): retrieval_mode = -1 first = True else: retrieval_mode = 1 first = False message = "# Data for table %s: " % q_tbl_name # switch for writing to files if file_per_table: if format == 'sql': file_name = tbl_name + ".sql" else: file_name = tbl_name + ".%s" % format.lower() outfile = open(file_name, "w") outfile.write(message + "\n") else: outfile = None print message for data_rows in cur_table.retrieve_rows(retrieval_mode): _export_row(data_rows, cur_table, format, single, skip_blobs, first, no_headers, outfile) if first: first = False if file_per_table: outfile.close() if not quiet: print "#...done." return True
def database_compare(server1_val, server2_val, db1, db2, options): """Perform a consistency check among two databases This method performs a database consistency check among two databases which ensures the databases exist, the objects match in number and type, the row counts match for all tables, and the data for each matching tables is consistent. If any errors or differences are found, the operation stops and the difference is printed. The following steps are therefore performed: 1) check to make sure the databases exist and are the same definition 2) check to make sure the same objects exist in each database 3) for each object, ensure the object definitions match among the databases 4) for each table, ensure the row counts are the same 5) for each table, ensure the data is the same By default, the operation stops on any failure of any test. The caller can override this behavior by specifying run_all_tests = True in the options dictionary. TODO: allow the user to skip object types (e.g. --skip-triggers, et. al.) server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, run_all_tests) Returns bool True if all object match, False if partial match """ _check_option_defaults(options) # Connect to servers server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) # Check to see if databases exist db1_conn = Database(server1, db1, options) if not db1_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db1)) db2_conn = Database(server2, db2, options) if not db2_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db2)) # Print a different message is server2 is not defined if not server2_val: message = "# Checking databases {0} and {1} on server1\n#" else: message = "# Checking databases {0} on server1 and {1} on server2\n#" print(message.format(db1_conn.db_name, db2_conn.db_name)) # Check for database existence and CREATE differences _check_databases(server1, server2, db1_conn.q_db_name, db2_conn.q_db_name, options) # Get common objects and report discrepancies (in_both, differs) = _check_objects(server1, server2, db1, db2, db1_conn, db2_conn, options) success = not differs reporter = _CompareDBReport(options) reporter.print_heading() # Remaining operations can occur in a loop one for each object. for item in in_both: error_list = [] # Set the object type obj_type = item[0] obj1 = "{0}.{1}".format(db1, item[1][0]) obj2 = "{0}.{1}".format(db2, item[1][0]) q_obj1 = "{0}.{1}".format(quote_with_backticks(db1), quote_with_backticks(item[1][0])) q_obj2 = "{0}.{1}".format(quote_with_backticks(db2), quote_with_backticks(item[1][0])) reporter.report_object(obj_type, item[1][0]) # Check for differences in CREATE errors = _compare_objects(server1, server2, q_obj1, q_obj2, reporter, options, obj_type) error_list.extend(errors) # Check row counts if obj_type == 'TABLE': errors = _check_row_counts(server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: error_list.extend(errors) else: reporter.report_state("-") # Check data consistency for tables if obj_type == 'TABLE': errors = _check_data_consistency(server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: error_list.extend(errors) else: reporter.report_state("-") if options['verbosity'] > 0: print get_create_object(server1, obj1, options, obj_type) get_create_object(server2, obj2, options, obj_type) reporter.report_errors(error_list) # Fail if errors are found if error_list: success = False return success
def export_metadata(source, src_val, db_list, options): """Produce rows to be used to recreate objects in a database. This method retrieves the objects for each database listed in the form of CREATE (SQL) statements or in a tabular form to the file specified. The valid values for the format parameter are SQL, CSV, TSV, VERTICAL, or GRID. source[in] Server instance src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) options[in] a dictionary containing the options for the copy: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, debug, exclude_names, exclude_patterns) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.format import format_tabular_list from mysql.utilities.common.format import format_vertical_list format = options.get("format", "sql") no_headers = options.get("no_headers", False) column_type = options.get("display", "brief") skip_create = options.get("skip_create", False) quiet = options.get("quiet", False) skip_tables = options.get("skip_tables", False) skip_views = options.get("skip_views", False) skip_triggers = options.get("skip_triggers", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) if options.get("all", False): rows = source.get_all_databases() for row in rows: db_list.append(row[0]) # Check user permissions on source for all databases for db_name in db_list: source_db = Database(source, db_name) # Make a dictionary of the options access_options = { 'skip_views': skip_views, 'skip_procs': skip_procs, 'skip_funcs': skip_funcs, 'skip_grants': skip_grants, 'skip_events': skip_events, } source_db.check_read_access(src_val["user"], src_val["host"], access_options) for db_name in db_list: # Get a Database class instance db = Database(source, db_name, options) # Error is source database does not exist if not db.exists(): raise UtilDBError("Source database does not exist - %s" % db_name, -1, db_name) if not quiet: print "# Exporting metadata from %s" % db_name # Perform the extraction if format == "sql": db.init() # quote database name with backticks q_db_name = quote_with_backticks(db_name) if not skip_create: print "DROP DATABASE IF EXISTS %s;" % q_db_name print "CREATE DATABASE %s;" % q_db_name print "USE %s;" % q_db_name for dbobj in db.get_next_object(): if dbobj[0] == "GRANT" and not skip_grants: if not quiet: print "# Grant:" if dbobj[1][3]: create_str = "GRANT %s ON %s.%s TO %s;" % \ (dbobj[1][1], q_db_name, quote_with_backticks(dbobj[1][3]), dbobj[1][0]) else: create_str = "GRANT %s ON %s.* TO %s;" % \ (dbobj[1][1], q_db_name, dbobj[1][0]) if create_str.find("%"): create_str = re.sub("%", "%%", create_str) print create_str else: if not quiet: print "# %s: %s.%s" % (dbobj[0], db_name, dbobj[1][0]) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): print "DELIMITER ||" print "%s;" % db.get_create_statement( db_name, dbobj[1][0], dbobj[0]) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): print "||" print "DELIMITER ;" else: objects = [] if not skip_tables: objects.append("TABLE") if not skip_views: objects.append("VIEW") if not skip_triggers: objects.append("TRIGGER") if not skip_procs: objects.append("PROCEDURE") if not skip_funcs: objects.append("FUNCTION") if not skip_events: objects.append("EVENT") if not skip_grants: objects.append("GRANT") for obj_type in objects: sys.stdout.write("# %sS in %s:" % (obj_type, db_name)) if format in ('grid', 'vertical'): rows = db.get_db_objects(obj_type, column_type, True) else: rows = db.get_db_objects(obj_type, column_type, True, True) if len(rows[1]) < 1: print " (none found)" else: print # Cannot use print_list here becasue we must manipulate # the behavior of format_tabular_list list_options = {} if format == "vertical": format_vertical_list(sys.stdout, rows[0], rows[1]) elif format == "tab": list_options['print_header'] = not no_headers list_options['separator'] = '\t' format_tabular_list(sys.stdout, rows[0], rows[1], list_options) elif format == "csv": list_options['print_header'] = not no_headers list_options['separator'] = ',' format_tabular_list(sys.stdout, rows[0], rows[1], list_options) else: # default to table format format_tabular_list(sys.stdout, rows[0], rows[1]) if not quiet: print "#...done." return True
def database_compare(server1_val, server2_val, db1, db2, options): """Perform a consistency check among two databases This method performs a database consistency check among two databases which ensures the databases exist, the objects match in number and type, the row counts match for all tables, and the data for each matching tables is consistent. If any errors or differences are found, the operation stops and the difference is printed. The following steps are therefore performed: 1) check to make sure the databases exist and are the same definition 2) check to make sure the same objects exist in each database 3) for each object, ensure the object definitions match among the databases 4) for each table, ensure the row counts are the same 5) for each table, ensure the data is the same By default, the operation stops on any failure of any test. The caller can override this behavior by specifying run_all_tests = True in the options dictionary. TODO: allow the user to skip object types (e.g. --skip-triggers, et. al.) server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, run_all_tests) Returns bool True if all object match, False if partial match """ _check_option_defaults(options) # Connect to servers server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) # Check to see if databases exist db1_conn = Database(server1, db1, options) if not db1_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db1)) db2_conn = Database(server2, db2, options) if not db2_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db2)) # Print a different message is server2 is not defined if not server2_val: message = "# Checking databases {0} and {1} on server1\n#" else: message = "# Checking databases {0} on server1 and {1} on server2\n#" print(message.format(db1_conn.db_name, db2_conn.db_name)) # Check for database existence and CREATE differences _check_databases(server1, server2, db1_conn.q_db_name, db2_conn.q_db_name, options) # Get common objects and report discrepancies (in_both, differs) = _check_objects(server1, server2, db1, db2, db1_conn, db2_conn, options) success = not differs reporter = _CompareDBReport(options) reporter.print_heading() # Remaining operations can occur in a loop one for each object. for item in in_both: error_list = [] debug_msgs = [] # Set the object type obj_type = item[0] q_obj1 = "{0}.{1}".format(quote_with_backticks(db1), quote_with_backticks(item[1][0])) q_obj2 = "{0}.{1}".format(quote_with_backticks(db2), quote_with_backticks(item[1][0])) reporter.report_object(obj_type, item[1][0]) # Check for differences in CREATE errors = _compare_objects(server1, server2, q_obj1, q_obj2, reporter, options, obj_type) error_list.extend(errors) # Check row counts if obj_type == 'TABLE': errors = _check_row_counts(server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: error_list.extend(errors) else: reporter.report_state("-") # Check data consistency for tables if obj_type == 'TABLE': errors, debug_msgs = _check_data_consistency( server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: error_list.extend(errors) else: reporter.report_state("-") if options['verbosity'] > 0: print get_create_object(server1, q_obj1, options, obj_type) get_create_object(server2, q_obj2, options, obj_type) if debug_msgs and options['verbosity'] > 2: reporter.report_errors(debug_msgs) reporter.report_errors(error_list) # Fail if errors are found if error_list: success = False return success
def _export_table_data(source_srv, table, output_file, options): """Export the table data. This private method retrieves the data for the specified table in SQL format (e.g., INSERT statements) or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified output file or a separated file, according to the defined options. source_srv[in] Server instance or dictionary with connection values. table[in] Table to export, tuple with database name and table name. output_file[in] Output file to store the export data. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug). return a filename if a temporary file is created to store the output result (used for multiprocessing) otherwise None. """ frmt = options.get("format", "sql") no_headers = options.get("no_headers", True) single = options.get("single", False) skip_blobs = options.get("skip_blobs", False) quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) # Handle source server instance or server connection values. # Note: For multiprocessing the use of connection values instead of a # server instance is required to avoid internal errors. if isinstance(source_srv, Server): source = source_srv else: # Get source server instance from connection values. conn_options = { 'quiet': True, # Avoid repeating output for multiprocessing. 'version': "5.1.30", } servers = connect_servers(source_srv, None, conn_options) source = servers[0] # Must be after the connection test to get SQL_MODE sql_mode = source.select_variable("SQL_MODE") # Handle qualified table name (with backtick quotes). db_name = table[0] tbl_name = "{0}.{1}".format(db_name, table[1]) q_db_name = quote_with_backticks(db_name, sql_mode) q_tbl_name = "{0}.{1}".format(q_db_name, quote_with_backticks(table[1], sql_mode)) # Determine output file to store exported table data. if file_per_table: # Store result of table export to a separated file. file_name = _generate_tbl_filename(tbl_name, frmt) outfile = open(file_name, "w+") tempfile_used = False else: if output_file: # Output file to store result is defined. outfile = output_file tempfile_used = False else: # Store result in a temporary file (merged later). # Used by multiprocess export. tempfile_used = True outfile = tempfile.NamedTemporaryFile(delete=False) message = "# Data for table {0}:".format(q_tbl_name) outfile.write("{0}\n".format(message)) tbl_options = {'verbose': False, 'get_cols': True, 'quiet': quiet} cur_table = Table(source, q_tbl_name, tbl_options) if single and frmt not in ("sql", "grid", "vertical"): retrieval_mode = -1 first = True else: retrieval_mode = 1 first = False # Find if we have some UNIQUE NOT NULL column indexes. unique_indexes = len(cur_table.get_not_null_unique_indexes()) # If all columns are BLOBS or there aren't any UNIQUE NOT NULL indexes # then rows won't be correctly copied using the update statement, # so we must warn the user. if (not skip_blobs and frmt == "sql" and (cur_table.blob_columns == len(cur_table.column_names) or (not unique_indexes and cur_table.blob_columns))): print("# WARNING: Table {0}.{1} contains only BLOB and TEXT " "fields. Rows will be generated with separate INSERT " "statements.".format(cur_table.q_db_name, cur_table.q_tbl_name)) for data_rows in cur_table.retrieve_rows(retrieval_mode): _export_row(data_rows, cur_table, frmt, single, skip_blobs, first, no_headers, outfile) if first: first = False if file_per_table: outfile.close() return outfile.name if tempfile_used else None
def database_diff(server1_val, server2_val, db1, db2, options): """Find differences among objects from two databases. This method compares the object definitions among two databases. If any differences are found, the differences are printed in the format chosen and the method returns False. A True result is returned only when all object definitions match. The method will stop and return False on the first difference found unless the option force is set to True (default = False). server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, force) Returns bool True if all object match, False if partial match """ force = options.get("force", False) server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2, True, options) direction = options.get("changes-for", None) if direction == 'server1' or direction is None: include = [ '{}.{}'.format(db2, tb[1][0]) if tb[0] == 'TABLE' else '' for tb in in_db2 ] server = '{}:{}@{}:{}'.format(server2_val.get('user', 'root'), server2_val.get('passwd', '123456'), server2_val.get('host', 'localhost'), server2_val.get('port', '3306')) db = db2 else: include = [ '{}.{}'.format(db1, tb[1][0]) if tb[0] == 'TABLE' else '' for tb in in_db1 ] server = '{}:{}@{}:{}'.format(server1_val.get('user', 'root'), server1_val.get('passwd', '123456'), server1_val.get('host', 'localhost'), server1_val.get('port', '3306')) db = db1 if include: default = [ '--server={}'.format(server), '--skip-blobs', '--skip-gtid', '--skip-fkey-checks', '--multiprocess=0', '--output-file={}'.format(options.get("output", "")), '--skip=views,triggers,procedures,functions,events,grants,data,create_db' ] for tb in include: default.append('--include={}'.format(tb)) parser = parse_options() if parser: export_db(parser, args=default, values=[db]) in_both.sort() if (len(in_db1) > 0 or len(in_db2) > 0) and not force: return False # Get sql_mode value set on servers server1_sql_mode = server1.select_variable("SQL_MODE") server2_sql_mode = server2.select_variable("SQL_MODE") # Quote database names with backticks. q_db1 = db1 if is_quoted_with_backticks(db1, server1_sql_mode) \ else quote_with_backticks(db1, server1_sql_mode) q_db2 = db2 if is_quoted_with_backticks(db2, server2_sql_mode) \ else quote_with_backticks(db2, server2_sql_mode) # Do the diff for the databases themselves result = object_diff(server1, server2, q_db1, q_db2, options, 'DATABASE') if result is not None: success = False if not force: return False # For each that match, do object diff success = True for item in in_both: # Quote object name with backticks with sql_mode from server1 q_obj_name1 = item[1][0] if \ is_quoted_with_backticks(item[1][0], server1_sql_mode) \ else quote_with_backticks(item[1][0], server1_sql_mode) # Quote object name with backticks with sql_mode from server2 q_obj_name2 = item[1][0] if \ is_quoted_with_backticks(item[1][0], server2_sql_mode) \ else quote_with_backticks(item[1][0], server2_sql_mode) object1 = "{0}.{1}".format(q_db1, q_obj_name1) object2 = "{0}.{1}".format(q_db2, q_obj_name2) result = object_diff(server1, server2, object1, object2, options, item[0]) if result is not None: success = False if not force: return False return success
def _export_metadata(source, db_list, output_file, options): """Export metadata from the specified list of databases. This private method retrieves the objects metadata for each database listed in the form of CREATE (SQL) statements or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified file. This private method does not check permissions. source[in] Server instance. db_list[in] List of databases to export. output_file[in] Output file to store the metadata information. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, debug, exclude_names, exclude_patterns) """ frmt = options.get("format", "sql") no_headers = options.get("no_headers", False) column_type = options.get("display", "brief") quiet = options.get("quiet", False) skip_create = options.get("skip_create", False) skip_tables = options.get("skip_tables", False) skip_views = options.get("skip_views", False) skip_triggers = options.get("skip_triggers", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) sql_mode = source.select_variable("SQL_MODE") for db_name in db_list: # Get a Database class instance db = Database(source, db_name, options) # Export database metadata if not quiet: output_file.write("# Exporting metadata from {0}\n".format( db.q_db_name)) # Perform the extraction if frmt == "sql": db.init() if not skip_create: output_file.write("DROP DATABASE IF EXISTS {0};\n".format( db.q_db_name)) output_file.write("CREATE DATABASE {0};\n".format( db.q_db_name)) output_file.write("USE {0};\n".format(db.q_db_name)) for dbobj in db.get_next_object(): if dbobj[0] == "GRANT" and not skip_grants: if not quiet: output_file.write("# Grant:\n") if dbobj[1][3]: create_str = "GRANT {0} ON {1}.{2} TO {3};\n".format( dbobj[1][1], db.q_db_name, quote_with_backticks(dbobj[1][3], sql_mode), dbobj[1][0]) else: create_str = "GRANT {0} ON {1}.* TO {2};\n".format( dbobj[1][1], db.q_db_name, dbobj[1][0]) output_file.write(create_str) else: if not quiet: output_file.write("# {0}: {1}.{2}\n".format( dbobj[0], db.q_db_name, quote_with_backticks(dbobj[1][0], sql_mode))) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): output_file.write("DELIMITER ||\n") output_file.write("{0};\n".format( db.get_create_statement(db.db_name, dbobj[1][0], dbobj[0]))) if (dbobj[0] == "PROCEDURE" and not skip_procs) or \ (dbobj[0] == "FUNCTION" and not skip_funcs) or \ (dbobj[0] == "EVENT" and not skip_events) or \ (dbobj[0] == "TRIGGER" and not skip_triggers): output_file.write("||\n") output_file.write("DELIMITER ;\n") else: objects = [] if not skip_tables: objects.append("TABLE") if not skip_funcs: objects.append("FUNCTION") if not skip_procs: objects.append("PROCEDURE") if not skip_views: objects.append("VIEW") if not skip_triggers: objects.append("TRIGGER") if not skip_events: objects.append("EVENT") if not skip_grants: objects.append("GRANT") for obj_type in objects: output_file.write("# {0}S in {1}:".format( obj_type, db.q_db_name)) if frmt in ('grid', 'vertical'): rows = db.get_db_objects(obj_type, column_type, True) else: rows = db.get_db_objects(obj_type, column_type, True, True) if len(rows[1]) < 1: output_file.write(" (none found)\n") else: output_file.write("\n") # Cannot use print_list here because we must manipulate # the behavior of format_tabular_list. list_options = {} if frmt == "vertical": format_vertical_list(output_file, rows[0], rows[1]) elif frmt == "tab": list_options['print_header'] = not no_headers list_options['separator'] = '\t' format_tabular_list(output_file, rows[0], rows[1], list_options) elif frmt == "csv": list_options['print_header'] = not no_headers list_options['separator'] = ',' format_tabular_list(output_file, rows[0], rows[1], list_options) else: # default to table format format_tabular_list(output_file, rows[0], rows[1]) if not quiet: output_file.write("#...done.\n")
def get_grants_for_object(self, qualified_obj_name, obj_type_str, global_privs=False): """ Retrieves the list of grants that the current user has that that have effect over a given object. qualified_obj_name[in] String with the qualified name of the object. obj_type_str[in] String with the type of the object that we are working with, must be one of 'ROUTINE', 'TABLE' or 'DATABASE'. global_privs[in] If True, the wildcard'%' host privileges are also taken into account This method takes the MySQL privilege hierarchy into account, e.g, if the qualified object is a table, it returns all the grant statements for this user regarding that table, as well as the grant statements for this user regarding the db where the table is at and finally any global grants that the user might have. Returns a list of strings with the grant statements. """ grant_stm_lst = self.get_grants(global_privs) obj_name_regexp = re.compile(REGEXP_QUALIFIED_OBJ_NAME) m_obj = obj_name_regexp.match(qualified_obj_name) grants = [] if not m_obj: raise UtilError("Cannot parse the specified qualified name " "'{0}'".format(qualified_obj_name)) else: db_name, obj_name = m_obj.groups() # Quote database and object name if necessary if not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) if obj_name and obj_name != '*': if not is_quoted_with_backticks(obj_name): obj_name = quote_with_backticks(obj_name) # For each grant statement look for the ones that apply to this # user and object for grant_stm in grant_stm_lst: grant_tpl = self._parse_grant_statement(grant_stm[0]) if grant_tpl: # Check if any of the privileges applies to this object # and if it does then check if it inherited from this # statement if filter_grants(grant_tpl.privileges, obj_type_str): # Add global grants if grant_tpl.db == '*': grants.append(grant_stm[0]) continue # Add database level grants if grant_tpl.db == db_name and grant_tpl.object == '*': grants.append(grant_stm[0]) continue # If it is an object, add existing object level grants # as well. if obj_name: if (grant_tpl.db == db_name and grant_tpl.object == obj_name): grants.append(grant_stm[0]) return grants
def get_result(self): msg = [] copied_db_on_server2 = ['util_db_clone', 'util_test', 'util_test_multi', 'db`:db_clone', 'db`:db', 'views_test_clone', 'util_db_privileges', 'blob_test_clone', 'blob_test_multi', 'blob_test_no_backslash_escapes'] copied_db_on_server1 = ["util_test_default_collation_copy", "util_test_default_charset_copy"] # Check databases existence. query = "SHOW DATABASES LIKE '{0}'" for db in copied_db_on_server2: try: res = self.server2.exec_query(query.format(db)) try: if res[0][0] != db: msg.append("Database {0} not found in {1}.\n" "".format(db, self.server2.role)) except IndexError: msg.append("Database {0} not found in {1}.\n" "".format(db, self.server2.role)) except UtilDBError as err: raise MUTLibError(err.errmsg) for db in copied_db_on_server1: try: res = self.server1.exec_query(query.format(db)) try: if res[0][0] != db: msg.append("Database {0} not found in {1}.\n" "".format(db, self.server1.role)) except IndexError: msg.append("Database {0} not found in {1}.\n" "".format(db, self.server1.role)) except UtilDBError as err: raise MUTLibError(err.errmsg) # Compare table checksums. dbs2compare = [ ('`util_test`', ('`util_test`', '`util_db_clone`', '`util_test_multi`', '`util_db_privileges`')), ('`db``:db`', ('`db``:db`', '`db``:db_clone`')), ('`views_test`', ('`views_test_clone`',)), ('blob_test', ('blob_test_clone', 'blob_test_multi', 'blob_test_no_backslash_escapes')) ] for cmp_data in dbs2compare: self.server1.exec_query("USE {0}".format(cmp_data[0])) res = self.server1.exec_query("SHOW TABLES") for row in res: table = quote_with_backticks( row[0], self.server1.select_variable("SQL_MODE") ) base_checksum = self.server1.exec_query( "CHECKSUM TABLE {0}".format(table) )[0][1] for i in range(len(cmp_data[1])): tbl_checksum = self.server2.exec_query( "CHECKSUM TABLE {0}.{1}".format(cmp_data[1][i], table) )[0][1] if tbl_checksum != base_checksum: msg.append("Different table checksum for table " "{0}.{1}, got {2} expected " "{3}.".format(cmp_data[1][i], table, tbl_checksum, base_checksum)) # Check attributes (character set and collation). qry_db = ("SELECT {0} FROM INFORMATION_SCHEMA.SCHEMATA " "WHERE SCHEMA_NAME = '{1}'") qry_tb = ("SELECT CCSA.{0} " "FROM information_schema.`TABLES` T, " "information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY`" " CCSA WHERE CCSA.collation_name = T.table_collation " " AND T.table_schema = '{1}' AND T.table_name = 't1'") check_db_info_on_server1 = [("util_test_default_collation_copy", "DEFAULT_COLLATION_NAME", "utf8_general_ci", "COLLATION_NAME"), ( "util_test_default_charset_copy", "DEFAULT_CHARACTER_SET_NAME", "utf8", "CHARACTER_SET_NAME")] for db in check_db_info_on_server1: try: res = self.server1.exec_query(qry_db.format(db[1], db[0])) try: if res[0][0] != db[2]: msg.append("For database {0} attribute {1} copy " "failed, got {2} expected {3}.\n" "".format(db[0], db[1], res[0][0], db[2])) except IndexError: msg.append("For database {0} no value found for attribute " "{1}.\n".format(db[0], db[1])) res = self.server1.exec_query(qry_tb.format(db[3], db[0])) try: if res[0][0] != db[2]: msg.append("For table {0} attribute {1} copy " "failed, got {2} expected {3}.\n" "".format(db[0], db[3], res[0][0], db[2])) except IndexError: msg.append("For table {0} no value found for attribute " "{1}.\n".format(db[0], db[3])) except UtilDBError as err: raise MUTLibError(err.errmsg) if msg: return False, ("Result failure.\n", "\n".join(msg)) else: return True, ""
def validate_obj_type_dict(server, obj_type_dict): """Validates the dictionary of objects against the specified server This function builds a dict with the types of the objects in obj_type_dict, filtering out non existing databases and objects. Returns a dictionary with only the existing objects, using object_types as keys and as values a list of tuples (<DB NAME>, <OBJ_NAME>). """ valid_obj_dict = defaultdict(list) server_dbs = set(row[0] for row in server.get_all_databases( ignore_internal_dbs=False)) argument_dbs = set(obj_type_dict.keys()) # Get non existing_databases and dbs to check non_existing_dbs = argument_dbs.difference(server_dbs) dbs_to_check = server_dbs.intersection(argument_dbs) if non_existing_dbs: if len(non_existing_dbs) > 1: plurals = ('s', '', 'them') else: plurals = ('', 'es', 'it') print('# WARNING: specified database{0} do{1} not ' 'exist on base server and will be skipped along ' 'any tables and routines belonging to {2}: ' '{3}.'.format(plurals[0], plurals[1], plurals[2], ", ".join(non_existing_dbs))) # Now for each db that actually exists, get the type of the specified # objects for db_name in dbs_to_check: db = Database(server, db_name) # quote database name if necessary quoted_db_name = db_name if not is_quoted_with_backticks(db_name): quoted_db_name = quote_with_backticks(db_name) for obj_name in obj_type_dict[db_name]: if obj_name is None: # We must consider the database itself valid_obj_dict[DATABASE_TYPE].append((quoted_db_name, quoted_db_name)) else: # get quoted name for obj_name quoted_obj_name = obj_name if not is_quoted_with_backticks(obj_name): quoted_obj_name = quote_with_backticks(obj_name) # Test if the object exists and if it does, test if it # is one of the supported object types, else # print a warning and skip the object obj_type = db.get_object_type(obj_name) if obj_type is None: print("# WARNING: specified object does not exist. " "{0}.{1} will be skipped." "".format(quoted_db_name, quoted_obj_name)) elif 'PROCEDURE' in obj_type or 'FUNCTION' in obj_type: valid_obj_dict[ROUTINE_TYPE].append((quoted_db_name, quoted_obj_name)) elif 'TABLE' in obj_type: valid_obj_dict[TABLE_TYPE].append((quoted_db_name, quoted_obj_name)) else: print('# WARNING: specified object is not supported ' '(not a DATABASE, FUNCTION, PROCEDURE or TABLE),' ' as such it will be skipped: {0}.{1}.' ''.format(quoted_db_name, quoted_obj_name)) return valid_obj_dict
def _export_table_data(source_srv, table, output_file, options): """Export the table data. This private method retrieves the data for the specified table in SQL format (e.g., INSERT statements) or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified output file or a separated file, according to the defined options. source_srv[in] Server instance or dictionary with connection values. table[in] Table to export, tuple with database name and table name. output_file[in] Output file to store the export data. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug). return a filename if a temporary file is created to store the output result (used for multiprocessing) otherwise None. """ frmt = options.get("format", "sql") no_headers = options.get("no_headers", True) single = options.get("single", False) skip_blobs = options.get("skip_blobs", False) quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) # Handle source server instance or server connection values. # Note: For multiprocessing the use of connection values instead of a # server instance is required to avoid internal errors. if isinstance(source_srv, Server): source = source_srv else: # Get source server instance from connection values. conn_options = { 'quiet': True, # Avoid repeating output for multiprocessing. 'version': "5.1.30", } servers = connect_servers(source_srv, None, conn_options) source = servers[0] # Handle qualified table name (with backtick quotes). db_name = table[0] tbl_name = "{0}.{1}".format(db_name, table[1]) q_db_name = quote_with_backticks(db_name) q_tbl_name = "{0}.{1}".format(q_db_name, quote_with_backticks(table[1])) # Determine output file to store exported table data. if file_per_table: # Store result of table export to a separated file. file_name = _generate_tbl_filename(tbl_name, frmt) outfile = open(file_name, "w+") tempfile_used = False else: if output_file: # Output file to store result is defined. outfile = output_file tempfile_used = False else: # Store result in a temporary file (merged later). # Used by multiprocess export. tempfile_used = True outfile = tempfile.NamedTemporaryFile(delete=False) message = "# Data for table {0}:".format(q_tbl_name) outfile.write("{0}\n".format(message)) tbl_options = { 'verbose': False, 'get_cols': True, 'quiet': quiet } cur_table = Table(source, q_tbl_name, tbl_options) if single and frmt not in ("sql", "grid", "vertical"): retrieval_mode = -1 first = True else: retrieval_mode = 1 first = False # Find if we have some UNIQUE NOT NULL column indexes. unique_indexes = len(cur_table.get_not_null_unique_indexes()) # If all columns are BLOBS or there aren't any UNIQUE NOT NULL indexes # then rows won't be correctly copied using the update statement, # so we must warn the user. if (not skip_blobs and frmt == "sql" and (cur_table.blob_columns == len(cur_table.column_names) or (not unique_indexes and cur_table.blob_columns))): print("# WARNING: Table {0}.{1} contains only BLOB and TEXT " "fields. Rows will be generated with separate INSERT " "statements.".format(cur_table.db_name, cur_table.tbl_name)) for data_rows in cur_table.retrieve_rows(retrieval_mode): _export_row(data_rows, cur_table, frmt, single, skip_blobs, first, no_headers, outfile) if first: first = False if file_per_table: outfile.close() return outfile.name if tempfile_used else None
def import_file(dest_val, file_name, options): """Import a file This method reads a file and, if needed, transforms the file into discrete SQL statements for execution on the destination server. It accepts any of the formal structured files produced by the mysqlexport utility including formats SQL, CSV, TAB, GRID, and VERTICAL. It will read these files and skip or include the definitions or data as specified in the options. An error is raised for any conversion errors or errors while executing the statements. Users are highly encouraged to use the --dryrun option which will print the SQL statements without executing them. dest_val[in] a dictionary containing connection information for the destination including: (user, password, host, port, socket) file_name[in] name (and path) of the file to import options[in] a dictionary containing the options for the import: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, and debug) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.options import check_engine_options from mysql.utilities.common.table import Table from mysql.utilities.common.server import connect_servers # Helper method to dig through the definitions for create statements def _process_definitions(statements, table_col_list, db_name): # First, get the SQL strings sql_strs = _build_create_objects(obj_type, db_name, definitions) statements.extend(sql_strs) # Now, save the column list col_list = _build_col_metadata(obj_type, definitions) if len(col_list) > 0: table_col_list.extend(col_list) def _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs): # if there is data here, build bulk inserts # First, create table reference, then call insert_rows() tbl = Table(destination, tbl_name) # Need to check to see if table exists! if tbl.exists(): tbl.get_column_metadata() col_meta = True elif len(table_col_list) > 0: col_meta = _get_column_metadata(tbl, table_col_list) else: fix_cols = [] fix_cols.append((tbl.tbl_name, columns)) col_meta = _get_column_metadata(tbl, fix_cols) if not col_meta: raise UtilError("Cannot build bulk insert statements without " "the table definition.") ins_strs = tbl.make_bulk_insert(table_rows, tbl.q_db_name) if len(ins_strs[0]) > 0: statements.extend(ins_strs[0]) if len(ins_strs[1]) > 0 and not skip_blobs: for update in ins_strs[1]: statements.append(update) # Gather options format = options.get("format", "sql") no_headers = options.get("no_headers", False) quiet = options.get("quiet", False) import_type = options.get("import_type", "definitions") single = options.get("single", True) dryrun = options.get("dryrun", False) do_drop = options.get("do_drop", False) skip_blobs = options.get("skip_blobs", False) skip_gtid = options.get("skip_gtid", False) # Attempt to connect to the destination server conn_options = { 'quiet' : quiet, 'version' : "5.1.30", } servers = connect_servers(dest_val, None, conn_options) destination = servers[0] # Check storage engines check_engine_options(destination, options.get("new_engine", None), options.get("def_engine", None), False, options.get("quiet", False)) if not quiet: if import_type == "both": str = "definitions and data" else: str = import_type print "# Importing %s from %s." % (str, file_name) # Setup variables we will need skip_header = not no_headers if format == "sql": skip_header = False get_db = True check_privileges = False db_name = None file = open(file_name) columns = [] read_columns = False table_rows = [] obj_type = "" definitions = [] statements = [] table_col_list = [] tbl_name = "" skip_rpl = options.get("skip_rpl", False) gtid_command_found = False supports_gtid = servers[0].supports_gtid() == 'ON' skip_gtid_warning_printed = False gtid_version_checked = False # Read the file one object/definition group at a time for row in read_next(file, format): # Check for replication command if row[0] == "RPL_COMMAND": if not skip_rpl: statements.append(row[1]) continue if row[0] == "GTID_COMMAND": gtid_command_found = True if not supports_gtid: # only display warning once if not skip_gtid_warning_printed: print _GTID_SKIP_WARNING skip_gtid_warning_printed = True elif not skip_gtid: if not gtid_version_checked: gtid_version_checked = True # Check GTID version for complete feature support servers[0].check_gtid_version() # Check the gtid_purged value too servers[0].check_gtid_executed("import") statements.append(row[1]) continue # If this is the first pass, get the database name from the file if get_db: if skip_header: skip_header = False else: db_name = _get_db(row) # quote db_name with backticks if needed if db_name and not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) get_db = False if do_drop and import_type != "data": statements.append("DROP DATABASE IF EXISTS %s;" % db_name) if import_type != "data": if not _skip_object("CREATE_DB", options) and \ not format == 'sql': statements.append("CREATE DATABASE %s;" % db_name) # This is the first time through the loop so we must # check user permissions on source for all databases if db_name is not None: dest_db = Database(destination, db_name) # Make a dictionary of the options access_options = options.copy() dest_db.check_write_access(dest_val['user'], dest_val['host'], access_options) # Now check to see if we want definitions, data, or both: if row[0] == "sql" or row[0] in _DEFINITION_LIST: if format != "sql" and len(row[1]) == 1: raise UtilError("Cannot read an import file generated with " "--display=NAMES") if import_type in ("definitions", "both"): if format == "sql": statements.append(row[1]) else: if obj_type == "": obj_type = row[0] if obj_type != row[0]: if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) obj_type = row[0] definitions = [] if not _skip_object(row[0], options): definitions.append(row[1]) else: # see if there are any definitions to process if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] if import_type in ("data", "both"): if _skip_object("DATA", options): continue # skip data elif format == "sql": statements.append(row[1]) else: if row[0] == "BEGIN_DATA": # Start of table so first row is columns. if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] read_columns = True tbl_name = row[1] if not is_quoted_with_backticks(tbl_name): db, sep, tbl = tbl_name.partition('.') q_db = quote_with_backticks(db) q_tbl = quote_with_backticks(tbl) tbl_name = ".".join([q_db, q_tbl]) else: if read_columns: columns = row[1] read_columns = False else: if not single: table_rows.append(row[1]) else: str = _build_insert_data(columns, tbl_name, row[1]) statements.append(str) # Process remaining definitions if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] # Process remaining data rows if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] # Now process the statements _exec_statements(statements, destination, format, options, dryrun) file.close() # Check gtid process if supports_gtid and not gtid_command_found: print _GTID_MISSING_WARNING if not quiet: print "#...done." return True
def check_index(src_val, table_args, options): """Check for duplicate or redundant indexes for one or more tables This method will examine the indexes for one or more tables and identify any indexes that are potential duplicates or redundant. It prints the equivalent DROP statements if selected. src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) table_args[in] list of tables in the form 'db.table' or 'db' options[in] dictionary of options to include: show-drops : show drop statements for dupe indexes skip : skip non-existent tables verbosity : print extra information show-indexes : show all indexes for each table index-format : index format = sql, table, tab, csv worst : show worst performing indexes best : show best performing indexes report-indexes : reports tables without PK or UK Returns bool True = success, raises UtilError if error """ # Get options show_drops = options.get("show-drops", False) skip = options.get("skip", False) verbosity = options.get("verbosity", False) show_indexes = options.get("show-indexes", False) index_format = options.get("index-format", False) stats = options.get("stats", False) first_indexes = options.get("best", None) last_indexes = options.get("worst", None) report_indexes = options.get("report-indexes", False) # Try to connect to the MySQL database server. conn_options = { 'quiet': verbosity == 1, 'version': "5.0.0", } servers = connect_servers(src_val, None, conn_options) source = servers[0] db_list = [] # list of databases table_list = [] # list of all tables to process # Build a list of objects to process # 1. start with db_list if no objects present on command line # 2. process command line options. # 3. loop through database list and add all tables # 4. check indexes # Get sql_mode value set on servers sql_mode = source.select_variable("SQL_MODE") # Perform the options check here. Loop through objects presented. for obj in table_args: m_obj = parse_object_name(obj, sql_mode) # Check if a valid database/table name is specified. if m_obj[0] is None: raise UtilError( PARSE_ERR_OBJ_NAME_FORMAT.format( obj_name=obj, option="the database/table arguments")) else: db_name, obj_name = m_obj if obj_name: # Table specified table_list.append(obj) # Else we are operating on a specific database. else: # Remove backtick quotes. db_name = remove_backtick_quoting(db_name, sql_mode) \ if is_quoted_with_backticks(db_name, sql_mode) else db_name db_list.append(db_name) # Loop through database list adding tables for db in db_list: db_source = Database(source, db) db_source.init() tables = db_source.get_db_objects("TABLE") if not tables and verbosity >= 1: print("# Warning: database %s does not exist. Skipping." % (db)) for table in tables: table_list.append("{0}.{1}".format( quote_with_backticks(db, sql_mode), quote_with_backticks(table[0], sql_mode))) # Fail if no tables to check if not table_list: raise UtilError("No tables to check.") if verbosity > 1: print("# Checking indexes...") # Check indexes for each table in the list # pylint: disable=R0101 for table_name in table_list: tbl_options = { 'verbose': verbosity >= 1, 'get_cols': False, 'quiet': verbosity is None or verbosity < 1 } tbl = Table(source, table_name, tbl_options) exists = tbl.exists() if not exists and not skip: raise UtilError("Table %s does not exist. Use --skip " "to skip missing tables." % table_name) if exists: if not tbl.get_indexes(): if verbosity > 1 or report_indexes: print("# Table %s is not indexed." % (table_name)) else: if show_indexes: tbl.print_indexes(index_format, verbosity) # Show if table has primary key if verbosity > 1 or report_indexes: if not tbl.has_primary_key(): if not tbl.has_unique_key(): print("# Table {0} does not contain neither a " "PRIMARY nor UNIQUE key.".format(table_name)) else: print("# Table {0} does not contain a PRIMARY key." "".format(table_name)) tbl.check_indexes(show_drops) # Show best and/or worst indexes if stats: if first_indexes is not None: tbl.show_special_indexes(index_format, first_indexes, True) if last_indexes is not None: tbl.show_special_indexes(index_format, last_indexes) if verbosity > 1: print("#") if verbosity > 1: print("# ...done.")
def database_diff(server1_val, server2_val, db1, db2, options): """Find differences among objects from two databases. This method compares the object definitions among two databases. If any differences are found, the differences are printed in the format chosen and the method returns False. A True result is returned only when all object definitions match. The method will stop and return False on the first difference found unless the option force is set to True (default = False). server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, force) Returns bool True if all object match, False if partial match """ force = options.get("force", False) server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2, True, options) in_both.sort() if (len(in_db1) > 0 or len(in_db2) > 0) and not force: return False # Get sql_mode value set on servers server1_sql_mode = server1.select_variable("SQL_MODE") server2_sql_mode = server2.select_variable("SQL_MODE") # Quote database names with backticks. q_db1 = db1 if is_quoted_with_backticks(db1, server1_sql_mode) \ else quote_with_backticks(db1, server1_sql_mode) q_db2 = db2 if is_quoted_with_backticks(db2, server2_sql_mode) \ else quote_with_backticks(db2, server2_sql_mode) # Do the diff for the databases themselves result = object_diff(server1, server2, q_db1, q_db2, options, 'DATABASE') if result is not None: success = False if not force: return False # For each that match, do object diff success = True for item in in_both: # Quote object name with backticks with sql_mode from server1 q_obj_name1 = item[1][0] if \ is_quoted_with_backticks(item[1][0], server1_sql_mode) \ else quote_with_backticks(item[1][0], server1_sql_mode) # Quote object name with backticks with sql_mode from server2 q_obj_name2 = item[1][0] if \ is_quoted_with_backticks(item[1][0], server2_sql_mode) \ else quote_with_backticks(item[1][0], server2_sql_mode) object1 = "{0}.{1}".format(q_db1, q_obj_name1) object2 = "{0}.{1}".format(q_db2, q_obj_name2) result = object_diff(server1, server2, object1, object2, options, item[0]) if result is not None: success = False if not force: return False return success
def _build_create_table(db_name, tbl_name, engine, columns, col_ref={}): """Build the CREATE TABLE command for a table. This method uses the data from the _read_next() method to build a table from its parts as read from a non-SQL formatted file. db_name[in] Database name for the object tbl_name[in] Name of the table engine[in] Storage engine name for the table columns[in] A list of the column definitions for the table col_ref[in] A dictionary of column names/indexes Returns (string) the CREATE TABLE statement. """ # Quote db_name and tbl_name with backticks if needed if not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) if not is_quoted_with_backticks(tbl_name): tbl_name = quote_with_backticks(tbl_name) create_str = "CREATE TABLE %s.%s (\n" % (db_name, tbl_name) stop = len(columns) pri_keys = [] keys = [] key_str = "" col_name_index = col_ref.get("COLUMN_NAME", 0) col_type_index = col_ref.get("COLUMN_TYPE", 1) is_null_index = col_ref.get("IS_NULLABLE", 2) def_index = col_ref.get("COLUMN_DEFAULT", 3) col_key_index = col_ref.get("COLUMN_KEY", 4) const_name_index = col_ref.get("CONSTRAINT_NAME", 7) ref_tbl_index = col_ref.get("REFERENCED_TABLE_NAME", 8) ref_col_index = col_ref.get("COL_NAME", 13) ref_col_ref = col_ref.get("REFERENCED_COLUMN_NAME", 15) constraints = [] for column in range(0,stop): cur_col = columns[column] # Quote column name with backticks if needed col_name = cur_col[col_name_index] if not is_quoted_with_backticks(col_name): col_name = quote_with_backticks(col_name) create_str = "%s %s %s" % (create_str, col_name, cur_col[col_type_index]) if cur_col[is_null_index].upper() != "YES": create_str += " NOT NULL" if len(cur_col[def_index]) > 0 and cur_col[def_index].upper() != "NONE": create_str += " DEFAULT %s" % cur_col[def_index] elif cur_col[is_null_index].upper == "YES": create_str += " DEFAULT NULL" if len(cur_col[col_key_index]) > 0: if cur_col[col_key_index] == "PRI": pri_keys.append(cur_col[col_name_index]) else: keys.append(cur_col[col_name_index]) if column+1 < stop: create_str += ",\n" if len(pri_keys) > 0: key_list = pri_keys key_str = ",\n PRIMARY KEY(" elif len(keys) > 0: key_list = keys # Quote constraint name with backticks if needed const_name = cur_col[const_name_index] if const_name and not is_quoted_with_backticks(const_name): const_name = quote_with_backticks(const_name) key_str = ",\n KEY %s (" % const_name constraints.append([const_name, cur_col[ref_tbl_index], cur_col[ref_col_index], cur_col[ref_col_ref]]) if len(key_str) > 0: stop = len(key_list) for key in range(0,stop): # Quote keys with backticks if needed if key_list[key] and not is_quoted_with_backticks(key_list[key]): key_list[key] = quote_with_backticks(key_list[key]) key_str += "%s" % key_list[key] if key+1 < stop-1: key_str += ", " key_str += ")" create_str += key_str if len(constraints) > 0: for constraint in constraints: # Quote keys with backticks if needed for key in constraint: if key and not is_quoted_with_backticks(key): key = quote_with_backticks(key) c_str = (" CONSTRAINT {cstr} FOREIGN KEY ({fk}) REFERENCES " "{ref1} ({ref2})") constraint_str = c_str.format(cstr=constraint[0], fk=constraint[2], ref1=constraint[1], ref2=constraint[3]) create_str = "%s,\n%s" % (create_str, constraint_str) create_str = "%s\n)" % create_str if engine and len(engine) > 0: create_str = "%s ENGINE=%s" % (create_str, engine) create_str = "%s;" % create_str return create_str
def export_data(source, src_val, db_list, options): """Produce data for the tables in a database. This method retrieves the data for each table in the databases listed in the form of BULK INSERT (SQL) statements or in a tabular form to the file specified. The valid values for the format parameter are SQL, CSV, TSV, VERITCAL, or GRID. source[in] Server instance src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) options[in] a dictionary containing the options for the copy: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.table import Table format = options.get("format", "sql") no_headers = options.get("no_headers", True) column_type = options.get("display", "brief") single = options.get("single", False) skip_blobs = options.get("skip_blobs", False) quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) skip_views = options.get("skip_views", False) skip_procs = options.get("skip_procs", False) skip_funcs = options.get("skip_funcs", False) skip_events = options.get("skip_events", False) skip_grants = options.get("skip_grants", False) if options.get("all", False): rows = source.get_all_databases() for row in rows: if row[0] not in db_list: db_list.append(row[0]) # Check if database exists and user permissions on source for all databases table_lock_list = [] table_list = [] for db_name in db_list: source_db = Database(source, db_name) # Make a dictionary of the options access_options = { 'skip_views': skip_views, 'skip_procs': skip_procs, 'skip_funcs': skip_funcs, 'skip_grants': skip_grants, 'skip_events': skip_events, } # Error is source database does not exist if not source_db.exists(): raise UtilDBError("Source database does not exist - %s" % db_name, -1, db_name) source_db.check_read_access(src_val["user"], src_val["host"], access_options) # Build table list tables = source_db.get_db_objects("TABLE") for table in tables: table_list.append((db_name, table[0])) old_db = "" for table in table_list: db_name = table[0] tbl_name = "%s.%s" % (db_name, table[1]) # quote database and table name with backticks q_db_name = quote_with_backticks(db_name) q_tbl_name = "%s.%s" % (q_db_name, quote_with_backticks(table[1])) if not quiet and old_db != db_name: old_db = db_name if format == "sql": print "USE %s;" % q_db_name print "# Exporting data from %s" % db_name if file_per_table: print "# Writing table data to files." tbl_options = {'verbose': False, 'get_cols': True, 'quiet': quiet} cur_table = Table(source, q_tbl_name, tbl_options) if single and format not in ("sql", "grid", "vertical"): retrieval_mode = -1 first = True else: retrieval_mode = 1 first = False message = "# Data for table %s: " % q_tbl_name # switch for writing to files if file_per_table: if format == 'sql': file_name = tbl_name + ".sql" else: file_name = tbl_name + ".%s" % format.lower() outfile = open(file_name, "w") outfile.write(message + "\n") else: outfile = None print message for data_rows in cur_table.retrieve_rows(retrieval_mode): _export_row(data_rows, cur_table, format, single, skip_blobs, first, no_headers, outfile) if first: first = False if file_per_table: outfile.close() if not quiet: print "#...done." return True
def _export_data(source, server_values, db_list, output_file, options): """Export data from the specified list of databases. This private method retrieves the data for each specified databases in SQL format (e.g., INSERT statements) or in a tabular form (GRID, TAB, CSV, VERTICAL) to the specified file. This private method does not check permissions. source[in] Server instance. server_values[in] Server connection values. db_list[in] List of databases to export. output_file[in] Output file to store the export data. options[in] Dictionary containing the options for the export: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, file_per_tbl, and debug). """ frmt = options.get("format", "sql") quiet = options.get("quiet", False) file_per_table = options.get("file_per_tbl", False) sql_mode = source.select_variable("SQL_MODE") # Get tables list. table_list = [] for db_name in db_list: source_db = Database(source, db_name, options) # Build table list. tables = source_db.get_db_objects("TABLE") for table in tables: table_list.append((db_name, table[0])) previous_db = "" export_tbl_tasks = [] for table in table_list: # Determine start for processing table from a different database. db_name = table[0] if previous_db != db_name: previous_db = db_name if not quiet: q_db_name = quote_with_backticks(db_name, sql_mode) if frmt == "sql": output_file.write("USE {0};\n".format(q_db_name)) output_file.write( "# Exporting data from {0}\n".format(q_db_name)) if file_per_table: output_file.write("# Writing table data to files.\n") # Print sample SOURCE command warning even in quiet mode. if file_per_table and frmt == 'sql': output_file.write("# The following are sample SOURCE commands." " If needed correct the path to match files " "location.\n") # Check multiprocess table export (only on POSIX systems). if options['multiprocess'] > 1 and os.name == 'posix': # Create export task. # Note: Server connection values are passed in the task dictionary # instead of a server instance, otherwise a multiprocessing error # is issued when assigning the task to a worker. export_task = { 'srv_con': server_values, 'table': table, 'options': options, } export_tbl_tasks.append(export_task) else: # Export data from a table (no multiprocessing). _export_table_data(source, table, output_file, options) # Print SOURCE command if --file-per-table is used and format is SQL. if file_per_table and frmt == 'sql': tbl_name = ".".join(table) output_file.write("# SOURCE {0}\n".format( _generate_tbl_filename(tbl_name, frmt))) # Export tables concurrently. if export_tbl_tasks: # Create process pool. workers_pool = multiprocessing.Pool(processes=options['multiprocess']) # Concurrently export tables. res = workers_pool.map_async(multiprocess_tbl_export_task, export_tbl_tasks) workers_pool.close() # Get list of temporary files with the exported data. tmp_files_list = res.get() workers_pool.join() # Merge resulting temp files (if generated). for tmp_filename in tmp_files_list: if tmp_filename: tmp_file = open(tmp_filename, 'r') shutil.copyfileobj(tmp_file, output_file) tmp_file.close() os.remove(tmp_filename) if not quiet: output_file.write("#...done.\n")
def _build_create_objects(obj_type, db, definitions): """Build the CREATE and GRANT SQL statments for object definitions. This method takes the object information read from the file using the _read_next() method and constructs SQL definition statements for each object. It receives a block of objects and creates a statement for each object. obj_type[in] The object type db[in] The database definitions[in] The list of object definition data from the file Returns (string[]) - a list of SQL statements for the objects """ create_strings = [] skip_header = True obj_db = "" obj_name = "" col_list = [] stop = len(definitions) col_ref = {} engine = None # Now the tricky part. for i in range(0,stop): if skip_header: skip_header = False col_ref = _build_column_ref(definitions[i]) continue defn = definitions[i] # Read engine from first row and save old value. old_engine = engine engine = defn[col_ref.get("ENGINE",2)] create_str = "" if obj_type == "TABLE": if (obj_db == "" and obj_name == ""): obj_db = defn[col_ref.get("TABLE_SCHEMA",0)] obj_name = defn[col_ref.get("TABLE_NAME",1)] if (obj_db == defn[col_ref.get("TABLE_SCHEMA",0)] and \ obj_name == defn[col_ref.get("TABLE_NAME",1)]): col_list.append(defn) else: create_str = _build_create_table(obj_db, obj_name, old_engine, col_list, col_ref) create_strings.append(create_str) obj_db = defn[col_ref.get("TABLE_SCHEMA",0)] obj_name = defn[col_ref.get("TABLE_NAME",1)] col_list = [] col_list.append(defn) # check for end. if i+1 == stop: create_str = _build_create_table(obj_db, obj_name, engine, col_list, col_ref) create_strings.append(create_str) elif obj_type == "VIEW": # Quote table schema and name with backticks if needed if not is_quoted_with_backticks(defn[col_ref.get("TABLE_SCHEMA", 0)]): obj_db = quote_with_backticks(defn[col_ref.get("TABLE_SCHEMA", 0)]) else: obj_db = defn[col_ref.get("TABLE_SCHEMA", 0)] if not is_quoted_with_backticks(defn[col_ref.get("TABLE_NAME", 1)]): obj_name = quote_with_backticks(defn[col_ref.get("TABLE_NAME", 1)]) else: obj_name = defn[col_ref.get("TABLE_NAME", 1)] # Create VIEW statement create_str = ("CREATE ALGORITHM=UNDEFINED DEFINER={defr} " "SQL SECURITY {sec} VIEW {scma}.{tbl} AS {defv}; " ).format(defr=defn[col_ref.get("DEFINER", 2)], sec=defn[col_ref.get("SECURITY_TYPE", 3)], scma=obj_db, tbl=obj_name, defv=defn[col_ref.get("VIEW_DEFINITION", 4)]) create_strings.append(create_str) elif obj_type == "TRIGGER": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("TRIGGER_NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("TRIGGER_NAME", 0)]) else: obj_name = defn[col_ref.get("TRIGGER_NAME", 0)] if not is_quoted_with_backticks( defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)]): evt_scma = quote_with_backticks( defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)]) else: evt_scma = defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)] if not is_quoted_with_backticks( defn[col_ref.get("EVENT_OBJECT_TABLE", 4)]): evt_tbl = quote_with_backticks( defn[col_ref.get("EVENT_OBJECT_TABLE", 4)]) else: evt_tbl = defn[col_ref.get("EVENT_OBJECT_TABLE", 4)] # Create TRIGGER statement # Important Note: There is a bug in the server when backticks are # used in the trigger statement, i.e. the ACTION_STATEMENT value in # INFORMATION_SCHEMA.TRIGGERS is incorrect (see BUG##16291011). create_str = ("CREATE DEFINER={defr} " "TRIGGER {scma}.{trg} {act_t} {evt_m} " "ON {evt_s}.{evt_t} FOR EACH {act_o} {act_s};" ).format(defr=defn[col_ref.get("DEFINER", 1)], scma=obj_db, trg=obj_name, act_t=defn[col_ref.get("ACTION_TIMING", 6)], evt_m=defn[col_ref.get("EVENT_MANIPULATION", 2)], evt_s=evt_scma, evt_t=evt_tbl, act_o=defn[col_ref.get("ACTION_ORIENTATION", 5)], act_s=defn[col_ref.get("ACTION_STATEMENT", 7)]) create_strings.append(create_str) elif obj_type in ("PROCEDURE", "FUNCTION"): # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create PROCEDURE or FUNCTION statement if obj_type == "FUNCTION": func_str = " RETURNS %s" % defn[col_ref.get("RETURNS", 7)] if defn[col_ref.get("IS_DETERMINISTI", 3)] == 'YES': func_str = "%s DETERMINISTIC" % func_str else: func_str = "" create_str = ("CREATE DEFINER={defr}" " {type} {scma}.{name}({par_lst})" "{func_ret} {body};" ).format(defr=defn[col_ref.get("DEFINER", 5)], type=obj_type, scma=obj_db, name=obj_name, par_lst=defn[col_ref.get("PARAM_LIST", 6)], func_ret=func_str, body=defn[col_ref.get("BODY", 8)]) create_strings.append(create_str) elif obj_type == "EVENT": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create EVENT statement create_str = ("CREATE EVENT {scma}.{name} " "ON SCHEDULE EVERY {int_v} {int_f} " "STARTS '{starts}' " ).format(scma=obj_db, name=obj_name, int_v=defn[col_ref.get("INTERVAL_VALUE", 5)], int_f=defn[col_ref.get("INTERVAL_FIELD", 6)], starts=defn[col_ref.get("STARTS", 8)] ) ends_index = col_ref.get("ENDS", 9) if len(defn[ends_index]) > 0 and \ defn[ends_index].upper() != "NONE": create_str = "%s ENDS '%s' " % (create_str, defn[ends_index]) if defn[col_ref.get("ON_COMPLETION", 11)] == "DROP": create_str = "%s ON COMPLETION NOT PRESERVE " % create_str if defn[col_ref.get("STATUS", 10)] == "DISABLED": create_str = "%s DISABLE " % create_str create_str = "%s DO %s;" % (create_str, defn[col_ref.get("BODY", 2)]) create_strings.append(create_str) elif obj_type == "GRANT": try: user, priv, db, tbl = defn[0:4] except: raise UtilError("Object data invalid: %s : %s" % \ (obj_type, defn)) if not tbl: tbl = "*" elif tbl.upper() == "NONE": tbl = "*" # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db obj_tbl = quote_with_backticks(tbl) \ if (tbl != '*' and not is_quoted_with_backticks(tbl)) else tbl # Create GRANT statement create_str = "GRANT %s ON %s.%s TO %s" % (priv, obj_db, obj_tbl, user) create_strings.append(create_str) elif obj_type in ["RPL_COMMAND", "GTID_COMMAND"]: create_strings.append([defn]) else: raise UtilError("Unknown object type discovered: %s" % obj_type) return create_strings
# check unique keys ukey_regexp = re.compile(r'(?:(?:;){{0,1}}{0}\.{1})' ''.format(ukey_regex_server1, ukey_regex_server2)) # Split the table names considering backtick quotes if opt.use_indexes: grp = ukey_regexp.findall(opt.use_indexes) if not grp: parser.error("Can't parse the specified --use-indexes argument {0}" "".format(opt.use_indexes)) db_idxes_l = [] for table, index in grp: table_uc = (table if is_quoted_with_backticks(table, server1_sql_mode) else quote_with_backticks(table, server1_sql_mode)) index_uc = (index if is_quoted_with_backticks(index, server1_sql_mode) else quote_with_backticks(index, server1_sql_mode)) db_idxes_l.append((table_uc, index_uc)) options["use_indexes"] = db_idxes_l # Check --span-key-size value. if opt.span_key_size is not None: if opt.span_key_size < DEFAULT_SPAN_KEY_SIZE: parser.error( PARSE_ERR_SPAN_KEY_SIZE_TOO_LOW.format( s_value=opt.span_key_size, default=DEFAULT_SPAN_KEY_SIZE)) if opt.span_key_size > MAX_SPAN_KEY_SIZE: parser.error( PARSE_ERR_SPAN_KEY_SIZE_TOO_HIGH.format(
def check_index(src_val, table_args, options): """Check for duplicate or redundant indexes for one or more tables This method will examine the indexes for one or more tables and identify any indexes that are potential duplicates or redundant. It prints the equivalent DROP statements if selected. src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) table_args[in] list of tables in the form 'db.table' or 'db' options[in] dictionary of options to include: show-drops : show drop statements for dupe indexes skip : skip non-existent tables verbosity : print extra information show-indexes : show all indexes for each table index-format : index format = sql, table, tab, csv worst : show worst performing indexes best : show best performing indexes report-indexes : reports tables without PK or UK Returns bool True = success, raises UtilError if error """ # Get options show_drops = options.get("show-drops", False) skip = options.get("skip", False) verbosity = options.get("verbosity", False) show_indexes = options.get("show-indexes", False) index_format = options.get("index-format", False) stats = options.get("stats", False) first_indexes = options.get("best", None) last_indexes = options.get("worst", None) report_indexes = options.get("report-indexes", False) # Try to connect to the MySQL database server. conn_options = { 'quiet': verbosity == 1, 'version': "5.0.0", } servers = connect_servers(src_val, None, conn_options) source = servers[0] db_list = [] # list of databases table_list = [] # list of all tables to process # Build a list of objects to process # 1. start with db_list if no objects present on command line # 2. process command line options. # 3. loop through database list and add all tables # 4. check indexes # Perform the options check here. Loop through objects presented. for obj in table_args: # If a . appears, we are operating on a specific table idx = obj.count(".") if (idx == 1): table_list.append(obj) # Else we are operating on a specific database. else: db_list.append(obj) # Loop through database list adding tables for db in db_list: db_source = Database(source, db) db_source.init() tables = db_source.get_db_objects("TABLE") if not tables and verbosity >= 1: print "# Warning: database %s does not exist. Skipping." % (db) for table in tables: table_list.append("{0}.{1}".format(quote_with_backticks(db), quote_with_backticks(table[0]))) # Fail if no tables to check if not table_list: raise UtilError("No tables to check.") if verbosity > 1: print "# Checking indexes..." # Check indexes for each table in the list for table_name in table_list: tbl_options = { 'verbose': verbosity >= 1, 'get_cols': False, 'quiet': verbosity is None or verbosity < 1 } tbl = Table(source, table_name, tbl_options) exists = tbl.exists() if not exists and not skip: raise UtilError("Table %s does not exist. Use --skip " "to skip missing tables." % table_name) if exists: if not tbl.get_indexes(): if verbosity > 1 or report_indexes: print "# Table %s is not indexed." % (table_name) else: if show_indexes: tbl.print_indexes(index_format, verbosity) # Show if table has primary key if verbosity > 1 or report_indexes: if not tbl.has_primary_key(): if not tbl.has_unique_key(): print("# Table {0} does not contain neither a " "PRIMARY nor UNIQUE key.".format(table_name)) else: print("# Table {0} does not contain a PRIMARY key." "".format(table_name)) tbl.check_indexes(show_drops) # Show best and/or worst indexes if stats: if first_indexes is not None: tbl.show_special_indexes(index_format, first_indexes, True) if last_indexes is not None: tbl.show_special_indexes(index_format, last_indexes) if verbosity > 1: print "#" if verbosity > 1: print "# ...done."