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."
