def check_read_permissions(server, db_list, options):
"""
Check user permissions on server for specified databases.
This method checks if the user used to establish the connection to the
server has read permissions to access the specified lists of databases.
server[in] Server instance.
db_list[in] List of databases to check.
options[in] Dictionary with access options:
skip_views True = no views processed
skip_proc True = no procedures processed
skip_func True = no functions processed
skip_grants True = no grants processed
skip_events True = no events processed
Returns an UtilDBError error if the server user does not have read
permissions to access all specified databases or if any of them does not
exist.
"""
for db_name in db_list:
source_db = Database(server, db_name, options)
# Error if source database does not exist.
if not source_db.exists():
raise UtilDBError(
"Source database does not exist - "
"{0}".format(db_name), -1, db_name)
# Check privileges to access database.
source_db.check_read_access(server.user, server.host, options)
def get_create_object(server, object_name, options):
"""Get the object's create statement.
This method retrieves the object create statement from the database.
server[in] server connection
object_name[in] name of object in the form db.objectname
options[in] options: verbosity, quiet
Returns string : create statement or raise error if object or db not exist
"""
from mysql.utilities.common.database import Database
verbosity = options.get("verbosity", 0)
quiet = options.get("quiet", False)
db_name, sep, obj_name = object_name.partition(".")
object = [db_name]
db = Database(server, object[0], options)
# Error if atabase does not exist
if not db.exists():
raise UtilDBError("The database does not exist: {0}".format(object[0]))
if not obj_name:
object.append(object[0])
obj_type = "DATABASE"
else:
object.append(obj_name)
obj_type = db.get_object_type(object[1])
if obj_type is None:
raise UtilDBError(
"The object {0} does not exist.".format(object_name))
create_stmt = db.get_create_statement(object[0], object[1], obj_type)
if verbosity > 0 and not quiet:
print "\n# Definition for object {0}:".format(object_name)
print create_stmt
return create_stmt
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 exec_query(self, query_str, options={}):
"""Execute a query and return result set
This is the singular method to execute queries. It should be the only
method used as it contains critical error code to catch the issue
with mysql.connector throwing an error on an empty result set.
Note: will handle exception and print error if query fails
Note: if fetchall is False, the method returns the cursor instance
query_str[in] The query to execute
options[in] Options to control behavior:
params Parameters for query
columns Add column headings as first row
(default is False)
fetch Execute the fetch as part of the operation and
use a buffered cursor
(default is True)
raw If True, use a buffered raw cursor
(default is True)
Returns result set or cursor
"""
params = options.get('params', ())
columns = options.get('columns', False)
fetch = options.get('fetch', True)
raw = options.get('raw', True)
# Guard for connect() prerequisite
assert self.db_conn, "You must call connect before executing a query."
results = ()
# If we are fetching all, we need to use a buffered
if fetch:
if raw:
cur = self.db_conn.cursor(
cursor_class=mysql.connector.cursor.MySQLCursorBufferedRaw)
else:
cur = self.db_conn.cursor(buffered=True)
else:
cur = self.db_conn.cursor(raw=True)
try:
if params == ():
res = cur.execute(query_str)
else:
res = cur.execute(query_str, params)
except mysql.connector.Error, e:
cur.close()
raise UtilDBError("Query failed. " + e.__str__())
def check_read_access(self, user, host, options):
"""Check access levels for reading database objects
This method will check the user's permission levels for copying a
database from this server.
It will also skip specific checks if certain objects are not being
copied (i.e., views, procs, funcs, grants).
user[in] user name to check
host[in] host name to check
options[in] dictionary of values to include:
skip_views True = no views processed
skip_proc True = no procedures processed
skip_func True = no functions processed
skip_grants True = no grants processed
skip_events True = no events processed
Returns True if user has permissions and raises a UtilDBError if the
user does not have permission with a message that includes
the server context.
"""
# Build minimal list of privileges for source access
source_privs = []
priv_tuple = (self.db_name, "SELECT")
source_privs.append(priv_tuple)
# if views are included, we need SHOW VIEW
if not options.get('skip_views', False):
priv_tuple = (self.db_name, "SHOW VIEW")
source_privs.append(priv_tuple)
# if procs or funcs are included, we need read on mysql db
if not options.get('skip_proc', False) or \
not options.get('skip_func', False):
priv_tuple = ("mysql", "SELECT")
source_privs.append(priv_tuple)
# if events, we need event
if not options.get('skip_events', False):
priv_tuple = (self.db_name, "EVENT")
source_privs.append(priv_tuple)
# Check permissions on source
for priv in source_privs:
if not self._check_user_permissions(user, host, priv):
raise UtilDBError("User %s on the %s server does not have "
"permissions to read all objects in %s. " %
(user, self.source.role, self.db_name) +
"User needs %s privilege on %s." %
(priv[1], priv[0]), -1, priv[0])
return True
def __create_object(self, obj_type, obj, show_grant_msg,
quiet=False, new_engine=None, def_engine=None):
"""Create a database object.
obj_type[in] Object type (string) e.g. DATABASE
obj[in] A row from the get_db_object_names() method
that contains the elements of the object
show_grant_msg[in] If true, display diagnostic information
quiet[in] do not print informational messages
new_engine[in] Use this engine if not None for object
def_engine[in] If target storage engine doesn't exist, use
this engine.
Note: will handle exception and print error if query fails
"""
create_str = None
if obj_type == _TABLE and self.cloning:
create_str = "CREATE TABLE %s.%s LIKE %s.%s" % \
(self.q_new_db, obj[0], self.q_db_name, obj[0])
else:
create_str = self.__make_create_statement(obj_type, obj)
if obj_type == _TABLE:
tbl_name = "%s.%s" % (self.q_new_db, obj[0])
create_str = self.destination.substitute_engine(tbl_name,
create_str,
new_engine,
def_engine,
quiet)
str = "# Copying"
if not quiet:
if obj_type == _GRANT:
if show_grant_msg:
print "%s GRANTS from %s" % (str, self.db_name)
else:
print "%s %s %s.%s" % \
(str, obj_type, self.db_name, obj[0])
if self.verbose:
print create_str
res = None
try:
res = self.destination.exec_query("USE %s" % self.q_new_db,
self.query_options)
except:
pass
try:
res = self.destination.exec_query(create_str, self.query_options)
except Exception, e:
raise UtilDBError("Cannot operate on %s object. Error: %s" %
(obj_type, e.errmsg), -1, self.db_name)
def _get_objects(server, database, options):
"""Get all objects from the database (except grants)
server[in] connected server object
database[in] database names
options[in] global options
Returns list - objects in database
"""
options["skip_grants"] = True # Tell db class to skip grants
db_obj = Database(server, database, options)
if not db_obj.exists():
raise UtilDBError("The database does not exist: {0}".format(database))
db_obj.init()
db_objects = db_obj.objects
db_objects.sort()
return db_objects
def create(self, new_user=None, authentication=None):
"""Create the user
Attempts to create the user. If the operation fails, an error is
generated and printed.
new_user[in] MySQL user string (user@host:passwd)
(optional) If omitted, operation is performed
on the class instance user name.
authentication[in] Special authentication clause for non-native
authentication plugins
"""
auth_str = "SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE " \
"PLUGIN_NAME = '{0}' AND PLUGIN_STATUS = 'ACTIVE';"
query_str = "CREATE USER "
user, passwd, host = None, None, None
if new_user:
user, passwd, host = parse_user_host(new_user)
user_host_str = "'{0}'@'{1}' ".format(user, host)
else:
user_host_str = "'{0}'@'{1}' ".format(self.user, self.host)
passwd = self.passwd
query_str += user_host_str
if passwd and authentication:
print(
"WARNING: using a password and an authentication plugin is "
"not permited. The password will be used instead of the "
"authentication plugin.")
if passwd:
query_str += "IDENTIFIED BY '{0}'".format(passwd)
elif authentication:
# need to validate authentication plugin
res = self.server1.exec_query(auth_str.format(authentication))
if (res is None) or (res == []):
raise UtilDBError("Plugin {0} not loaded or not active. "
"Cannot create user.".format(authentication))
query_str += "IDENTIFIED WITH '{0}'".format(authentication)
if self.verbosity > 0:
print query_str
self.server1.exec_query(query_str, self.query_options)
def check_write_access(self, user, host, options):
"""Check access levels for creating and writing database objects
This method will check the user's permission levels for copying a
database to this server.
It will also skip specific checks if certain objects are not being
copied (i.e., views, procs, funcs, grants).
user[in] user name to check
host[in] host name to check
options[in] dictionary of values to include:
skip_views True = no views processed
skip_proc True = no procedures processed
skip_func True = no functions processed
skip_grants True = no grants processed
skip_events True = no events processed
Returns True if user has permissions and raises a UtilDBError if the
user does not have permission with a message that includes
the server context.
"""
dest_privs = [(self.db_name, "CREATE"),
(self.db_name, "SUPER"),
("*", "SUPER")]
if not options.get('skip_grants', False):
priv_tuple = (self.db_name, "WITH GRANT OPTION")
dest_privs.append(priv_tuple)
# Check privileges on destination
for priv in dest_privs:
if not self._check_user_permissions(user, host, priv):
raise UtilDBError("User %s on the %s server does not "
"have permissions to create all objects "
"in %s. User needs %s privilege on %s." %
(user, self.source.role, priv[0], priv[1],
priv[0]), -1, priv[0])
return True
def get_create_object(server, object_name, options, object_type):
"""Get the object's create statement.
This method retrieves the object create statement from the database.
server[in] server connection
object_name[in] name of object in the form db.objectname
options[in] options: verbosity, quiet
object_type[in] type of the specified object (e.g, TABLE, PROCEDURE,
etc.).
Returns string : create statement or raise error if object or db not exist
"""
verbosity = options.get("verbosity", 0)
quiet = options.get("quiet", False)
m_obj = re.match(REGEXP_QUALIFIED_OBJ_NAME, object_name)
db_name, obj_name = m_obj.groups()
obj = [db_name]
db = Database(server, obj[0], options)
# Error if database does not exist
if not db.exists():
raise UtilDBError("The database does not exist: {0}".format(obj[0]))
if not obj_name or object_type == 'DATABASE':
obj.append(db_name)
else:
obj.append(obj_name)
create_stmt = db.get_create_statement(obj[0], obj[1], object_type)
if verbosity > 0 and not quiet:
print "\n# Definition for object {0}:".format(object_name)
print create_stmt
return create_stmt
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 object_diff(server1_val,
server2_val,
object1,
object2,
options,
object_type=None):
"""diff the definition of two objects
Find the difference among two object definitions.
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)
object1[in] the first object in the compare in the form: (db.name)
object2[in] the second object in the compare in the form: (db.name)
options[in] a dictionary containing the options for the operation:
(quiet, verbosity, difftype)
object_type[in] type of the objects to be compared (e.g., TABLE,
PROCEDURE, etc.). By default None (not defined).
Returns None = objects are the same, diff[] = tables differ
"""
objectype = options.get("objectype", 'ALL').upper()
if not object_type and objectype != 'ALL':
object_type = objectype
if object_type and objectype != 'ALL' and object_type != objectype:
print('The object type {} is skip'.format(object_type))
return None
server1, server2 = server_connect(server1_val, server2_val, object1,
object2, options)
force = options.get("force", None)
# Get the object type if unknown considering that objects of different
# types can be found with the same name.
result = []
if not object_type:
# Get object types of object1
sql_mode = server1.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object1, sql_mode)
db = Database(server1, db_name, options)
obj1_types = db.get_object_type(obj_name)
if not obj1_types:
msg = "The object {0} does not exist.".format(object1)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Get object types of object2
sql_mode = server2.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object2, sql_mode)
db = Database(server2, db_name, options)
obj2_types = db.get_object_type(obj_name)
if not obj2_types:
msg = "The object {0} does not exist.".format(object2)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Merge types found for both objects
obj_types = set(obj1_types + obj2_types)
# Diff objects considering all types found
for obj_type in obj_types:
res = diff_objects(server1, server2, object1, object2, options,
obj_type)
if res:
result.append(res)
else:
# Diff objects of known type
res = diff_objects(server1, server2, object1, object2, options,
object_type)
if res:
result.append(res)
if len(result) > 0 and options.get(
"difftype", None) == 'sql' and options.get(
"output", None) and options.get("output", '').endswith('.sql'):
with open(options.get("output"), 'a', encoding='utf8') as fp:
for res in result:
if isinstance(res, list):
for r in res:
if r and r.strip().startswith('#'):
continue
fp.write('{}\n'.format(r))
else:
fp.write('{}\n'.format(res))
return result if len(result) > 0 else None
def object_diff(server1_val, server2_val, object1, object2, options,
object_type=None):
"""diff the definition of two objects
Find the difference among two object definitions.
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)
object1[in] the first object in the compare in the form: (db.name)
object2[in] the second object in the compare in the form: (db.name)
options[in] a dictionary containing the options for the operation:
(quiet, verbosity, difftype)
object_type[in] type of the objects to be compared (e.g., TABLE,
PROCEDURE, etc.). By default None (not defined).
Returns None = objects are the same, diff[] = tables differ
"""
server1, server2 = server_connect(server1_val, server2_val,
object1, object2, options)
force = options.get("force", None)
# Get the object type if unknown considering that objects of different
# types can be found with the same name.
if not object_type:
# Get object types of object1
sql_mode = server1.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object1, sql_mode)
db = Database(server1, db_name, options)
obj1_types = db.get_object_type(obj_name)
if not obj1_types:
msg = "The object {0} does not exist.".format(object1)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Get object types of object2
sql_mode = server2.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object2, sql_mode)
db = Database(server2, db_name, options)
obj2_types = db.get_object_type(obj_name)
if not obj2_types:
msg = "The object {0} does not exist.".format(object2)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Merge types found for both objects
obj_types = set(obj1_types + obj2_types)
# Diff objects considering all types found
result = []
for obj_type in obj_types:
res = diff_objects(server1, server2, object1, object2, options,
obj_type)
if res:
result.append(res)
return result if len(result) > 0 else None
else:
# Diff objects of known type
return diff_objects(server1, server2, object1, object2, options,
object_type)
def copy_objects(self, new_db, options, new_server=None,
connections=1, check_exists=True):
"""Copy the database objects.
This method will copy a database and all of its objects and data
to another, new database. Options set at instantiation will determine
if there are objects that are excluded from the copy. Likewise,
the method will also skip data if that option was set and process
an input file with INSERT statements if that option was set.
The method can also be used to copy a database to another server
by providing the new server object (new_server). Copy to the same
name by setting new_db = old_db or as a new database.
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
check_exists[in] If True, check for database existance before copy
Default is True
"""
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_objects()."
grant_msg_displayed = False
if new_db:
# Assign new database identifier considering backtick quotes.
if is_quoted_with_backticks(new_db):
self.q_new_db = new_db
self.new_db = remove_backtick_quoting(new_db)
else:
self.new_db = new_db
self.q_new_db = quote_with_backticks(new_db)
else:
# If new_db is not defined use the same as source database.
self.new_db = self.db_name
self.q_new_db = self.q_db_name
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
# Check to see if database exists
if check_exists:
exists = False
drop_server = None
if self.cloning:
exists = self.exists(self.source, new_db)
drop_server = self.source
else:
exists = self.exists(self.destination, new_db)
drop_server = self.destination
if exists:
if options.get("force", False):
self.drop(drop_server, True, new_db)
elif not self.skip_create:
raise UtilDBError("destination database exists. Use "
"--force to overwrite existing "
"database.", -1, new_db)
# Create new database first
if not self.skip_create:
if self.cloning:
self.create(self.source, new_db)
else:
self.create(self.destination, new_db)
# Create the objects in the new database
for obj in self.objects:
# Drop object if --force specified and database not dropped
# Grants do not need to be dropped for overwriting
if options.get("force", False) and obj[0] != _GRANT:
self.__drop_object(obj[0], obj[1][0])
# Create the object
self.__create_object(obj[0], obj[1], not grant_msg_displayed,
options.get("quiet", False),
options.get("new_engine", None),
options.get("def_engine", None))
if obj[0] == _GRANT and not grant_msg_displayed:
grant_msg_displayed = True
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 object_diff(server1_val,
server2_val,
object1,
object2,
options,
object_type=None):
"""diff the definition of two objects
Find the difference among two object definitions.
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)
object1[in] the first object in the compare in the form: (db.name)
object2[in] the second object in the compare in the form: (db.name)
options[in] a dictionary containing the options for the operation:
(quiet, verbosity, difftype)
object_type[in] type of the objects to be compared (e.g., TABLE,
PROCEDURE, etc.). By default None (not defined).
Returns None = objects are the same, diff[] = tables differ
"""
server1, server2 = server_connect(server1_val, server2_val, object1,
object2, options)
# Get the object type if unknown considering that objects of different
# types can be found with the same name.
if not object_type:
#Get object types of object1
regexp_obj = re.compile(REGEXP_QUALIFIED_OBJ_NAME)
m_obj = regexp_obj.match(object1)
db_name, obj_name = m_obj.groups()
db = Database(server1, db_name, options)
obj1_types = db.get_object_type(obj_name)
if not obj1_types:
raise UtilDBError("The object {0} does not exist.".format(object1))
# Get object types of object2
m_obj = regexp_obj.match(object2)
db_name, obj_name = m_obj.groups()
db = Database(server2, db_name, options)
obj2_types = db.get_object_type(obj_name)
if not obj2_types:
raise UtilDBError("The object {0} does not exist.".format(object2))
# Merge types found for both objects
obj_types = set(obj1_types + obj2_types)
# Diff objects considering all types found
result = []
for obj_type in obj_types:
res = diff_objects(server1, server2, object1, object2, options,
obj_type)
if res:
result.append(res)
return result if len(result) > 0 else None
else:
# Diff objects of known type
return diff_objects(server1, server2, object1, object2, options,
object_type)
def __init__(self, server, table_list, options={}):
"""Constructor
Lock a list of tables based on locking type. Locking types and their
behavior is as follows:
- (default) use consistent read with a single transaction
- lock all tables without consistent read and no transaction
- no locks, no transaction, no consistent read
- flush (replication only) - issue a FTWRL command
server[in] Server instance of server to run locks
table_list[in] list of tuples (table_name, lock_type)
options[in] dictionary of options
locking = [snapshot|lock-all|no-locks|flush],
verbosity int
silent bool
rpl_mode string
"""
self.locked = False
self.silent = options.get('silent', False)
# Determine locking type
self.locking = options.get('locking', 'snapshot')
self.verbosity = options.get('verbosity', 0)
if self.verbosity is None:
self.verbosity = 0
else:
self.verbosity = int(self.verbosity)
self.server = server
self.table_list = table_list
# If no locking, we're done
if self.locking == 'no-locks':
return
elif self.locking == 'lock-all':
# Check lock requests for validity
table_locks = []
for tablename, locktype in table_list:
if locktype.upper() not in LOCK_TYPES:
raise UtilDBError(
"Invalid lock type '%s' for table '%s'." %
(locktype, tablename))
# Build LOCK TABLE command
table_locks.append("%s %s" % (tablename, locktype))
lock_str = "LOCK TABLE "
lock_str += ', '.join(table_locks)
if self.verbosity >= 3 and not self.silent:
print '# LOCK STRING:', lock_str
# Execute the lock
self.server.exec_query(lock_str)
self.locked = True
elif self.locking == 'snapshot':
self.server.exec_query(_SESSION_ISOLATION_LEVEL)
self.server.exec_query(_START_TRANSACTION)
# Execute a FLUSH TABLES WITH READ LOCK for replication uses only
elif self.locking == 'flush' and options.get("rpl_mode", None):
if self.verbosity >= 3 and not self.silent:
print "# LOCK STRING: %s" % _FLUSH_TABLES_READ_LOCK
self.server.exec_query(_FLUSH_TABLES_READ_LOCK)
self.locked = True
else:
raise UtilError("Invalid locking type: '%s'." % self.locking)
def object_diff(server1_val,
server2_val,
object1,
object2,
options,
object_type=None):
"""diff the definition of two objects
Find the difference among two object definitions.
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)
object1[in] the first object in the compare in the form: (db.name)
object2[in] the second object in the compare in the form: (db.name)
options[in] a dictionary containing the options for the operation:
(quiet, verbosity, difftype)
object_type[in] type of the objects to be compared (e.g., TABLE,
PROCEDURE, etc.). By default None (not defined).
Returns None = objects are the same, diff[] = tables differ
"""
if isinstance(server1_val, dict): # dict or common.server.Server object
server1, server2 = server_connect(server1_val, server2_val, object1,
object2, options)
else:
# to save connection
server1, server2 = server1_val, server2_val
force = options.get("force", None)
# compare db's all objects
include_create = options.get("include_create", False)
# db1.*:db2.*
if include_create and object1.endswith('.*') and object2.endswith('.*'):
direction = options.get("changes-for", None)
reverse = options.get("reverse", False)
db_name1, _ = parse_object_name(object1,
server1.select_variable("SQL_MODE"))
db_name2, _ = parse_object_name(object2,
server2.select_variable("SQL_MODE"))
in_both, in_db1, in_db2 = get_common_objects(server1, server2,
db_name1, db_name2, True,
options)
# create/alter/drop need all objects compare
all_object = set(in_both + in_db1 + in_db2)
# call myself recusively to compare all objects
for this_obj in all_object:
object1 = db_name1 + "." + this_obj[1][0]
object2 = db_name2 + "." + this_obj[1][0]
# share the same connection in this loop. object_type=None
object_diff(server1,
server2,
object1,
object2,
options,
object_type=None)
return []
# Get the object type if unknown considering that objects of different
# types can be found with the same name.
if not object_type:
# Get object types of object1
sql_mode = server1.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object1, sql_mode)
db = Database(server1, db_name, options)
obj1_types = db.get_object_type(obj_name)
if not obj1_types:
if include_create:
# if allow generating create object ddl, give 'NULL' object here to tell common.dbcompare.py to handle
obj1_types = ['NULL']
else:
msg = "The object {0} does not exist.".format(object1)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Get object types of object2
sql_mode = server2.select_variable("SQL_MODE")
db_name, obj_name = parse_object_name(object2, sql_mode)
db = Database(server2, db_name, options)
obj2_types = db.get_object_type(obj_name)
if not obj2_types:
if include_create:
obj2_types = ['NULL']
else:
msg = "The object {0} does not exist.".format(object2)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
# Merge types found for both objects
obj_types = set(obj1_types + obj2_types)
if obj_types == set(['NULL']):
msg = "The object {0} or {1} does not exist in the source side.".format(
object1, object2)
if not force:
raise UtilDBError(msg)
print("ERROR: {0}".format(msg))
return []
elif 'NULL' in obj_types:
# at least one object exist in db1 and db2
# new db object like TABLE-NULL or NULL-TABLE , 'TABLE' is needed for after use in diff_objects()
obj_types = set(['-'.join(obj1_types + obj2_types)])
# Diff objects considering all types found
result = []
for obj_type in obj_types:
res = diff_objects(server1, server2, object1, object2, options,
obj_type)
if res:
result.append(res)
return result if len(result) > 0 else None
else:
# Diff objects of known type
return diff_objects(server1, server2, object1, object2, options,
object_type)