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)
m_objs = parse_object_name(qualified_obj_name, self.sql_mode)
grants = []
if not m_objs:
raise UtilError("Cannot parse the specified qualified name "
"'{0}'".format(qualified_obj_name))
else:
db_name, obj_name = m_objs
# Quote database and object name if necessary
if not is_quoted_with_backticks(db_name, self.sql_mode):
db_name = quote_with_backticks(db_name, self.sql_mode)
if obj_name and obj_name != '*':
if not is_quoted_with_backticks(obj_name, self.sql_mode):
obj_name = quote_with_backticks(obj_name, self.sql_mode)
# 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], self.sql_mode)
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_frm_path(dbtablename, datadir, new_db=None):
"""Form the path and discover the db and name of a frm file.
dbtablename[in] the database.table name in the format db:table
datadir[in] the path to the data directory
new_db[in] a new database name
default = None == use existing db
Returns tuple - (db, name, path) or raises an error if .frm file
cannot be read
"""
# Form the path to the .frm file. There are two possibilities:
# a) the user has specified a full path
# b) the user has specified a db:table combination (with/without .frm)
path_parts = os.path.split(dbtablename)
if ':' in dbtablename and len(path_parts[0]) == 0:
# here we use the datadir to form the path
path_parts = dbtablename.split(":")
db = path_parts[0]
table = path_parts[1]
if datadir is None:
datadir = ""
frm_path = os.path.join(datadir, table)
elif len(path_parts) == 2 and ":" in path_parts[1]:
db, table = path_parts[1].split(":", 1)
frm_path = os.path.join(path_parts[0], table)
else:
# here we decipher the last folder as the database name
frm_path = dbtablename
db = None
if len(path_parts) == 2:
path, table = path_parts
if path == '':
db = None
path = None
elif len(path_parts) == 1:
db = None
path = None
table = dbtablename
if db is None and path:
# find database from path
folders = path.split(os.path.sep)
if len(folders):
db = folders[len(folders) - 1]
# Check that the frm_path name has .frm.
if not frm_path.lower().endswith(".frm"):
frm_path += ".frm"
# Strip the .frm from table
if table.lower().endswith('.frm'):
table = os.path.splitext(table)[0]
if not os.access(frm_path, os.R_OK):
raise UtilError("Cannot read .frm file from %s." % frm_path)
if new_db:
db = new_db
return (db, table, frm_path)
def get_tool_path(basedir,
tool,
fix_ext=True,
required=True,
defaults_paths=None,
search_PATH=False,
quote=False):
"""Search for a MySQL tool and return the full path
basedir[in] The initial basedir to search (from mysql server)
tool[in] The name of the tool to find
fix_ext[in] If True (default is True), add .exe if running on
Windows.
required[in] If True (default is True), and error will be
generated and the utility aborted if the tool is
not found.
defaults_paths[in] Default list of paths to search for the tool.
By default an empty list is assumed, i.e. [].
search_PATH[in] Boolean value that indicates if the paths specified by
the PATH environment variable will be used to search
for the tool. By default the PATH will not be searched,
i.e. search_PATH=False.
quote[in] If True, the result path is surrounded with the OS
quotes.
Returns (string) full path to tool
"""
if not defaults_paths:
defaults_paths = []
search_paths = []
if quote:
if os.name == "posix":
quote_char = "'"
else:
quote_char = '"'
else:
quote_char = ''
if basedir:
# Add specified basedir path to search paths
_add_basedir(search_paths, basedir)
if defaults_paths and len(defaults_paths):
# Add specified default paths to search paths
for path in defaults_paths:
search_paths.append(path)
else:
# Add default basedir paths to search paths
_add_basedir(search_paths, "/usr/local/mysql/")
_add_basedir(search_paths, "/usr/sbin/")
_add_basedir(search_paths, "/usr/share/")
# Search in path from the PATH environment variable
if search_PATH:
for path in os.environ['PATH'].split(os.pathsep):
search_paths.append(path)
if os.name == "nt" and fix_ext:
tool = tool + ".exe"
# Search for the tool
for path in search_paths:
norm_path = os.path.normpath(path)
if os.path.isdir(norm_path):
toolpath = os.path.join(norm_path, tool)
if os.path.isfile(toolpath):
return r"%s%s%s" % (quote_char, toolpath, quote_char)
else:
if tool == "mysqld.exe":
toolpath = os.path.join(norm_path, "mysqld-nt.exe")
if os.path.isfile(toolpath):
return r"%s%s%s" % (quote_char, toolpath, quote_char)
if required:
raise UtilError("Cannot find location of %s." % tool)
return None
def show_innodb_usage(server, datadir, options):
"""Show InnoDB tablespace disk space usage.
Display InnoDB tablespace information if InnoDB turned on.
server[in] Connected server to operate against
datadir[in] The datadir for the server
options[in] Required options for operation: format, no_headers
return True or raise exception on error
"""
fmt = options.get("format", "grid")
no_headers = options.get("no_headers", False)
is_remote = options.get("is_remote", False)
verbosity = options.get("verbosity", 0)
quiet = options.get("quiet", False)
# Check to see if we have innodb
res = server.show_server_variable('have_innodb')
if res != [] and res[0][1].upper() in ("NO", "DISABLED"):
print "# InnoDB is disabled on this server."
return True
# Modified check for version 5.5
res = server.exec_query("USE INFORMATION_SCHEMA")
res = server.exec_query("SELECT engine, support "
"FROM INFORMATION_SCHEMA.ENGINES "
"WHERE engine='InnoDB'")
if res != [] and res[0][1].upper() == "NO":
print "# InnoDB is disabled on this server."
return True
# Check to see if innodb_file_per_table is ON
res = server.show_server_variable('innodb_file_per_table')
# pylint: disable=R0102
if res != [] and res[0][1].upper() == "ON":
innodb_file_per_table = True
else:
innodb_file_per_table = False
# Get path
res = server.show_server_variable('innodb_data_home_dir')
if res != [] and len(res[0][1]) > 0:
innodb_dir = res[0][1]
else:
innodb_dir = datadir
if not is_remote and os.access(innodb_dir, os.R_OK):
if not quiet:
print "# InnoDB tablespace information:"
res = server.show_server_variable('innodb_data_file_path')
tablespaces = []
if res != [] and len(res[0][1]) > 0:
parts = res[0][1].split(";")
for part in parts:
tablespaces.append(part)
innodb, total = _build_innodb_list(innodb_file_per_table, innodb_dir,
datadir, tablespaces, verbosity)
if innodb == []:
raise UtilError("InnoDB is enabled but there is a problem "
"reading the tablespace files.")
columns = ['innodb_file', 'size']
if verbosity > 0:
columns.append('type')
columns.append('specificaton')
size = 'size'
fmt_innodb = []
if fmt.upper() == 'GRID':
max_col = _get_formatted_max_width(innodb, columns, 1)
if max_col < len('size'):
max_col = len('size')
size = "{0:>{1}}".format('size', max_col)
columns = ['innodb_file']
columns.append(size)
if verbosity > 0:
columns.append('type')
columns.append('specificaton')
for row in innodb:
# Add commas
size = locale.format("%d", row[1], grouping=True)
# Make justified strings
size = "{0:>{1}}".format(size, max_col)
if verbosity > 0:
fmt_innodb.append((row[0], size, row[2], row[3]))
else:
fmt_innodb.append((row[0], size))
else:
fmt_innodb = innodb
print_list(sys.stdout, fmt, columns, fmt_innodb, no_headers)
if not quiet:
_print_size("\nTotal size of InnoDB files = ", total)
print
if verbosity > 0 and not innodb_file_per_table and not quiet:
for tablespace in innodb:
if tablespace[1] != 'log file':
parts = tablespace[3].split(":")
if len(parts) > 2:
ts_size = int(tablespace[1]) / _MB
print "Tablespace %s can be " % tablespace[3] + \
"extended by using %s:%sM[...]\n" % \
(parts[0], ts_size)
elif is_remote:
print("# InnoDB data information not accessible from a remote host.")
else:
print "# InnoDB data file information is not accessible. " + \
"Check your permissions."
if not innodb_file_per_table:
res = server.exec_query(_QUERY_DATAFREE)
if res != []:
if len(res) > 1:
raise UtilError("Found multiple rows for freespace.")
else:
fs_size = int(res[0][0])
if not quiet:
_print_size("InnoDB freespace = ", fs_size)
print
return True
def _spawn_server(options):
"""Spawn a server to use for reading .frm files
This method spawns a new server instance on the port specified by the
user in the options dictionary.
options[in] Options from user
Returns tuple - (Server instance, new datdir) or raises exception on error
"""
verbosity = int(options.get("verbosity", 0))
quiet = options.get("quiet", False)
new_port = options.get("port", 3310)
user = options.get("user", None)
start_timeout = int(options.get("start_timeout", 10))
# 1) create a directory to use for new datadir
# If the user is not the same as the user running the script...
if user_change_as_root(options):
# Since Python libraries correctly restrict temporary folders to
# the user who runs the script and /tmp is protected on some
# platforms, we must create the folder in the current folder
temp_datadir = os.path.join(os.getcwd(), str(uuid.uuid4()))
os.mkdir(temp_datadir)
else:
temp_datadir = tempfile.mkdtemp()
if verbosity > 1 and not quiet:
print("# Creating a temporary datadir =", temp_datadir)
# 2) spawn a server pointed to temp
if not quiet:
if user:
print("# Spawning server with --user={0}.".format(user))
print("# Starting the spawned server on port %s ..." % new_port, )
sys.stdout.flush()
bootstrap_options = {
'new_data': temp_datadir,
'new_port': new_port,
'new_id': 101,
'root_pass': "******",
'mysqld_options': None,
'verbosity': verbosity if verbosity > 1 else 0,
'basedir': options.get("basedir"),
'delete': True,
'quiet': True if verbosity <= 1 else False,
'user': user,
'start_timeout': start_timeout,
}
if verbosity > 1 and not quiet:
print
try:
serverclone.clone_server(None, bootstrap_options)
except UtilError as error:
if error.errmsg.startswith("Unable to communicate"):
err = ". Clone server error: {0}".format(error.errmsg)
proc_id = int(error.errmsg.split("=")[1].strip('.'))
print("ERROR Attempting to stop failed spawned server. "
" Process id = {0}.".format(proc_id))
if os.name == "posix":
try:
os.kill(proc_id, subprocess.signal.SIGTERM)
except OSError:
pass
else:
try:
subprocess.Popen("taskkill /F /T /PID %i" % proc_id,
shell=True)
except:
pass
raise UtilError(_SPAWN_SERVER_ERROR.format(err))
else:
raise
if verbosity > 1 and not quiet:
print("# Connecting to spawned server")
conn = {
"user": "******",
"passwd": "root",
"host": "127.0.0.1",
"port": options.get("port"),
}
server_options = {
'conn_info': conn,
'role': "frm_reader_bootstrap",
}
server = Server(server_options)
try:
server.connect()
except UtilError:
raise UtilError(_SPAWN_SERVER_ERROR.format(""))
if not quiet:
print("done.")
return (server, temp_datadir)
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
obj_name_regexp = re.compile(REGEXP_QUALIFIED_OBJ_NAME)
# Perform the options check here. Loop through objects presented.
for obj in table_args:
m_obj = obj_name_regexp.match(obj)
# Check if a valid database/table name is specified.
if not m_obj:
raise UtilError(PARSE_ERR_OBJ_NAME_FORMAT.format(
obj_name=obj, option="the database/table arguments"))
else:
db_name, obj_name = m_obj.groups()
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) \
if is_quoted_with_backticks(db_name) 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),
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."
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
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)
# 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(db + "." + 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:
print "# Table %s is not indexed." % (table_name)
else:
if show_indexes:
tbl.print_indexes(index_format)
# Show if table has primary key
if not tbl.has_primary_key():
if verbosity > 1:
print "# Table %s does not contain a PRIMARY key."
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 show_server_info(servers, options):
"""Show server information for a list of servers
This method will gather information about a running server. If the
show_defaults option is specified, the method will also read the
configuration file and return a list of the server default settings.
If the format option is set, the output will be in the format specified.
If the no_headers option is set, the output will not have a header row (no
column names) except for format = vertical.
If the basedir and start options are set, the method will attempt to start
the server in read only mode to get the information. Specifying only
basedir will not start the server. The extra start option is designed to
make sure the user wants to start the offline server. The user may not wish
to do this if there are certain error conditions and/or logs in place that
may be overwritten.
servers[in] list of server connections in the form
<user>:<password>@<host>:<port>:<socket>
options[in] dictionary of options (no_headers, format, basedir,
start, show_defaults)
Returns tuple ((server information), defaults)
"""
no_headers = options.get("no_headers", False)
fmt = options.get("format", "grid")
show_defaults = options.get("show_defaults", False)
basedir = options.get("basedir", None)
datadir = options.get("datadir", None)
start = options.get("start", False)
show_servers = options.get("show_servers", 0)
if show_servers:
if os.name == 'nt':
ports = options.get("ports", "3306:3333")
start_p, end_p = ports.split(":")
_show_running_servers(start_p, end_p)
else:
_show_running_servers()
ssl_dict = {}
ssl_dict['ssl_cert'] = options.get("ssl_cert", None)
ssl_dict['ssl_ca'] = options.get("ssl_ca", None)
ssl_dict['ssl_key'] = options.get("ssl_key", None)
ssl_dict['ssl'] = options.get("ssl", None)
row_dict_lst = []
warnings = []
server_val = {}
for server in servers:
new_server = None
try:
test_connect(server, throw_errors=True, ssl_dict=ssl_dict)
except UtilError as util_error:
conn_dict = get_connection_dictionary(server, ssl_dict=ssl_dict)
server1 = Server(options={'conn_info': conn_dict})
server_is_off = False
# If we got errno 2002 it means can not connect through the
# given socket.
if util_error.errno == CR_CONNECTION_ERROR:
socket = conn_dict.get("unix_socket", "")
if socket:
msg = ("Unable to connect to server using socket "
"'{0}'.".format(socket))
if os.path.isfile(socket):
err_msg = ["{0} Socket file is not valid.".format(msg)]
else:
err_msg = ["{0} Socket file does not "
"exist.".format(msg)]
# If we got errno 2003 and we do not have
# socket, instead we check if server is localhost.
elif (util_error.errno == CR_CONN_HOST_ERROR and
server1.is_alias("localhost")):
server_is_off = True
# If we got errno 1045 it means Access denied,
# notify the user if a password was used or not.
elif util_error.errno == ER_ACCESS_DENIED_ERROR:
use_pass = '******' if conn_dict['passwd'] else 'NO'
err_msg = ("Access denied for user '{0}'@'{1}' using "
"password: {2}".format(conn_dict['user'],
conn_dict['host'],
use_pass))
# Use the error message from the connection attempt.
else:
err_msg = [util_error.errmsg]
# To propose to start a cloned server for extract the info,
# can not predict if the server is really off, but we can do it
# in case of socket error, or if one of the related
# parameter was given.
if server_is_off or basedir or datadir or start:
err_msg = ["Server is offline. To connect, "
"you must also provide "]
opts = ["basedir", "datadir", "start"]
for opt in tuple(opts):
try:
if locals()[opt] is not None:
opts.remove(opt)
except KeyError:
pass
if opts:
err_msg.append(", ".join(opts[0:-1]))
if len(opts) > 1:
err_msg.append(" and the ")
err_msg.append(opts[-1])
err_msg.append(" option")
raise UtilError("".join(err_msg))
if not start:
raise UtilError("".join(err_msg))
else:
try:
server_val = parse_connection(server, None, options)
except:
raise UtilError("Source connection values invalid"
" or cannot be parsed.")
new_server = _start_server(server_val, basedir,
datadir, options)
info_dict, defaults = _server_info(server, show_defaults, options)
warnings.extend(info_dict['warnings'])
if info_dict:
row_dict_lst.append(info_dict)
if new_server:
# Need to stop the server!
new_server.disconnect()
_stop_server(server_val, basedir, options)
# Get the row values stored in the dictionaries
rows = [[row_dict[key] for key in _COLUMNS] for row_dict in row_dict_lst]
print_list(sys.stdout, fmt, _COLUMNS, rows, no_headers)
if warnings:
print("\n# List of Warnings: \n")
for warning in warnings:
print("WARNING: {0}\n".format(warning))
# Print the default configurations.
if show_defaults and len(defaults) > 0:
for row in defaults:
print(" {0}".format(row))
def show_log_usage(server, datadir, options):
"""Show binary or relay log disk space usage.
Display binary log file information if binlog turned on if log_type =
'binary log' (default) or show relay log file information is server is
a slave and relay log is engaged.
server[in] Connected server to operate against
datadir[in] The datadir for the server
options[in] Required options for operation: format, no_headers.
log_type
return True or raise exception on error
"""
from mysql.utilities.common.format import print_list
format = options.get("format", "grid")
no_headers = options.get("no_headers", False)
verbosity = options.get("verbosity", 0)
have_read = options.get("have_read", False)
log_type = options.get("log_type", "binary log")
quiet = options.get("quiet", False)
current_log = None
# Check for binlog on first.
if log_type == 'binary log':
res = server.show_server_variable('log_bin')
if res != [] and res[0][1].upper() == 'OFF':
print "# Binary logging is turned off on the server."
return True
else:
try:
res = server.exec_query("SHOW SLAVE STATUS")
if res != [] and res is not None:
current_log = res[0][7]
except:
raise UtilError("Cannot get relay log information")
if res == []:
print "# Server is not an active slave - no relay log information."
return True
if os.access(datadir, os.R_OK):
if not quiet:
print "# %s information:" % log_type
if log_type == 'binary log':
try:
res = server.exec_query("SHOW MASTER STATUS")
if res != []:
current_log = res[0][0]
except:
raise UtilError("Cannot get binary log information.")
if current_log is None:
print "# Cannot access %s files.\n" % log_type
return False
if not quiet:
print "Current %s file = %s" % (log_type, current_log)
# As of 5.6.2, users can specify location of binlog and relaylog.
if server.check_version_compat(5, 6, 2):
if log_type == 'binary log':
res = server.show_server_variable("log_bin_basename")[0]
else:
res = server.show_server_variable("relay_log_basename")[0]
parts = os.path.split(res[1])
log_path = os.path.join(parts[:len(parts) - 1])[0]
log_prefix = parts[len(parts) - 1]
else:
log_path = datadir
log_prefix = os.path.splitext(current_log)[0]
if log_path == '':
log_path = datadir
logs, total = _build_log_list(log_path, log_prefix)
if logs == []:
raise UtilError("The %s are missing." % log_type)
columns = ['log_file']
size = 'size'
fmt_logs = []
if format == 'GRID':
max_col = _get_formatted_max_width(logs, ('log_file', 'size'), 1)
if max_col < len('size'):
max_col = len('size')
size = "{0:>{1}}".format('size', max_col)
columns.append(size)
for row in logs:
# Add commas
size = locale.format("%d", row[1], grouping=True)
# Make justified strings
size = "{0:>{1}}".format(size, max_col)
fmt_logs.append((row[0], size))
else:
fmt_logs = logs
columns.append('size')
print_list(sys.stdout, format, columns, fmt_logs, no_headers)
if not quiet:
_print_size("\nTotal size of %ss = " % log_type, total)
print
else:
print "# Binlog information not accessible. " + \
"Check your permissions."
return True
def _get_transform(server1, server2, object1, object2, options):
"""Get the transformation SQL statements
This method generates the SQL statements to transform the destination
object based on direction of the compare.
server1[in] first server connection
server2[in] second server connection
object1 the first object in the compare in the form: (db.name)
object2 the second object in the compare in the form: (db.name)
options[in] a dictionary containing the options for the operation:
(quiet, etc.)
Returns tuple - (bool - same db name?, list of transformation statements)
"""
from mysql.utilities.common.database import Database
from mysql.utilities.common.sql_transform import SQLTransformer
obj_type = None
direction = options.get("changes-for", "server1")
# If there is no dot, we do not have the format 'db_name.obj_name' for
# object1 and therefore must treat it as a database name.
if object1.find('.') == -1:
obj_type = "DATABASE"
# We are working with databases so db and name need to be set
# to the database name to tell the get_object_definition() method
# to retrieve the database information.
db1 = object1
db2 = object2
name1 = object1
name2 = object2
else:
try:
db1, name1 = object1.split('.')
db2, name2 = object2.split('.')
except:
raise UtilError("Invalid object name arguments for _get_transform"
"(): %s, %s." % (object1, object2))
db_1 = Database(server1, db1, options)
db_2 = Database(server2, db2, options)
if obj_type is None:
obj_type = db_1.get_object_type(name1)
transform_str = []
obj1 = db_1.get_object_definition(db1, name1, obj_type)
obj2 = db_2.get_object_definition(db2, name2, obj_type)
# Get the transformation based on direction.
transform_str = []
same_db_name = True
xform = SQLTransformer(db_1, db_2, obj1[0], obj2[0], obj_type,
options.get('verbosity', 0))
differences = xform.transform_definition()
if differences is not None and len(differences) > 0:
transform_str.extend(differences)
return transform_str
def _server_info(server_val, get_defaults=False, options=None):
"""Show information about a running server
This method gathers information from a running server. This information is
returned as a tuple to be displayed to the user in a format specified. The
information returned includes the following:
* server connection information
* version number of the server
* data directory path
* base directory path
* plugin directory path
* configuration file location and name
* current binary log file
* current binary log position
* current relay log file
* current relay log position
server_val[in] the server connection values or a connected server
get_defaults[in] if True, get the default settings for the server
options[in] options for connecting to the server
Return tuple - information about server
"""
if options is None:
options = {}
# Parse source connection values
source_values = parse_connection(server_val, None, options)
# Connect to the server
conn_options = {
'version': "5.1.30",
}
servers = connect_servers(source_values, None, conn_options)
server = servers[0]
params_dict = defaultdict(str)
# Initialize list of warnings
params_dict['warnings'] = []
# Identify server by string: 'host:port[:socket]'.
server_id = "{0}:{1}".format(source_values['host'], source_values['port'])
if source_values.get('socket', None):
server_id = "{0}:{1}".format(server_id, source_values.get('socket'))
params_dict['server'] = server_id
# Get _SERVER_VARIABLES values from the server
for server_var in _SERVER_VARIABLES:
res = server.show_server_variable(server_var)
if res:
params_dict[server_var] = res[0][1]
else:
raise UtilError("Unable to determine {0} of server '{1}'"
".".format(server_var, server_id))
# Verify if the server is a local server.
server_is_local = server.is_alias('localhost')
# Get _LOG_FILES_VARIABLES values from the server
for msg, log_tpl in _LOG_FILES_VARIABLES.iteritems():
res = server.show_server_variable(log_tpl.log_name)
if res:
# Check if log is turned off
params_dict[log_tpl.log_name] = res[0][1]
# If logs are turned off, skip checking information about the file
if res[0][1] in ('', 'OFF'):
continue
# Logging is enabled, so we can get get information about log_file
# unless it is log_error because in that case we already have it.
if log_tpl.log_file is not None: # if it is not log_error
log_file = server.show_server_variable(
log_tpl.log_file)[0][1]
params_dict[log_tpl.log_file] = log_file
else: # log error, so log_file_name is already on params_dict
log_file = params_dict[log_tpl.log_name]
# Size can only be obtained from the files of a local server.
if not server_is_local:
params_dict[log_tpl.log_file_size] = 'UNAVAILABLE'
# Show warning about log size unaviable.
params_dict['warnings'].append("Unable to get information "
"regarding variable '{0}' "
"from a remote server."
"".format(msg))
# If log file is stderr, we cannot get the correct size.
elif log_file in ["stderr", "stdout"]:
params_dict[log_tpl.log_file_size] = 'UNKNOWN'
# Show warning about log unknown size.
params_dict['warnings'].append("Unable to get size information"
" from '{0}' for '{1}'."
"".format(log_file, msg))
else:
# Now get the information about the size of the logs
try:
# log_file might be a relative path, in which case we need
# to prepend the datadir path to it
if not os.path.isabs(log_file):
log_file = os.path.join(params_dict['datadir'],
log_file)
params_dict[log_tpl.log_file_size] = "{0} bytes".format(
os.path.getsize(log_file))
except os.error:
# if we are unable to get the log_file_size
params_dict[log_tpl.log_file_size] = ''
warning_msg = _WARNING_TEMPLATE.format(msg, log_file)
params_dict['warnings'].append(warning_msg)
else:
params_dict['warnings'].append("Unable to get information "
"regarding variable '{0}'"
).format(msg)
# if audit_log plugin is installed and enabled
if server.supports_plugin('audit'):
res = server.show_server_variable('audit_log_file')
if res:
# Audit_log variable might be a relative path to the datadir,
# so it needs to be treated accordingly
if not os.path.isabs(res[0][1]):
params_dict['audit_log_file'] = os.path.join(
params_dict['datadir'], res[0][1])
else:
params_dict['audit_log_file'] = res[0][1]
# Add audit_log field to the _COLUMNS List unless it is already
# there
if 'audit_log_file' not in _COLUMNS_SET:
_COLUMNS.append('audit_log_file')
_COLUMNS.append('audit_log_file_size')
_COLUMNS_SET.add('audit_log_file')
try:
params_dict['audit_log_file_size'] = "{0} bytes".format(
os.path.getsize(params_dict['audit_log_file']))
except os.error:
# If we are unable to get the size of the audit_log_file
params_dict['audit_log_file_size'] = ''
warning_msg = _WARNING_TEMPLATE.format(
"audit log",
params_dict['audit_log_file']
)
params_dict['warnings'].append(warning_msg)
# Build search path for config files
if os.name == "posix":
my_def_search = ["/etc/my.cnf", "/etc/mysql/my.cnf",
os.path.join(params_dict['basedir'], "my.cnf"),
"~/.my.cnf"]
else:
my_def_search = [r"c:\windows\my.ini", r"c:\my.ini", r"c:\my.cnf",
os.path.join(os.curdir, "my.ini")]
my_def_search.append(os.path.join(os.curdir, "my.cnf"))
# Get server's default configuration values.
defaults = []
if get_defaults:
# Can only get defaults for local servers (need to access local data).
if server_is_local:
try:
my_def_path = get_tool_path(params_dict['basedir'],
"my_print_defaults", quote=True)
except UtilError as err:
raise UtilError("Unable to retrieve the defaults data "
"(requires access to my_print_defaults): {0} "
"(basedir: {1})".format(err.errmsg,
params_dict['basedir'])
)
out_file = tempfile.TemporaryFile()
# Execute tool: <basedir>/my_print_defaults mysqld
cmd_list = shlex.split(my_def_path)
cmd_list.append("mysqld")
subprocess.call(cmd_list, stdout=out_file)
out_file.seek(0)
# Get defaults data from temp output file.
defaults.append("\nDefaults for server {0}".format(server_id))
for line in out_file.readlines():
defaults.append(line.rstrip())
else:
# Remote server; Cannot get the defaults data.
defaults.append("\nWARNING: The utility can not get defaults from "
"a remote host.")
# Find config file
config_file = ""
for search_path in my_def_search:
if os.path.exists(search_path):
if len(config_file) > 0:
config_file = "{0}, {1}".format(config_file, search_path)
else:
config_file = search_path
params_dict['config_file'] = config_file
# Find binary log, relay log
params_dict['binary_log'], params_dict['binary_log_pos'] = _get_binlog(
server)
params_dict['relay_log'], params_dict['relay_log_pos'] = _get_relay_log(
server)
server.disconnect()
return params_dict, defaults
def _check_tables_structure(server1, server2, object1, object2, options,
diff_type):
"""Check if the tables have the same structure.
This method compares the tables structure ignoring the order of the
columns and retrieves the differences between the table options.
server1[in] first server connection.
server2[in] second server connection.
object1 the first object in the compare in the form: (db.name).
object2 the second object in the compare in the form: (db.name).
options[in] a dictionary containing the options for the operation:
(quiet, verbosity, difftype, width, suppress_sql).
diff_type[in] difference type.
Returns a tuple (bool, list, bool) - The first tuple value is a boolean
that indicates if both tables have the same structure (i.e. column
definitions). The second returns the table options differences. Finally,
the third is a boolean indicating if the partition options are the same.
"""
try:
m_obj1 = re.match(REGEXP_QUALIFIED_OBJ_NAME, object1)
db1, name1 = m_obj1.groups()
m_obj2 = re.match(REGEXP_QUALIFIED_OBJ_NAME, object2)
db2, name2 = m_obj2.groups()
except:
raise UtilError("Invalid object name arguments for diff_objects(): "
"{0}, {1}.".format(object1, object2))
# If the second part of the object qualified name is None, then the format
# is not 'db_name.obj_name' for object1 and therefore must treat it as a
# database name.
if not name1:
return None, None, None
db_1 = Database(server1, db1, options)
db_2 = Database(server2, db2, options)
# Get tables definitions.
table_1 = db_1.get_object_definition(db1, name1, 'TABLE')[0]
table_2 = db_2.get_object_definition(db2, name2, 'TABLE')[0]
# Check table options.
table1_opts = db_1.get_table_options(db1, name1)
table2_opts = db_2.get_table_options(db2, name2)
diff = _get_diff(table1_opts, table2_opts, object1, object2, diff_type)
# Check if both tables have the same columns definition.
# Discard column order.
table_1_cols = [col[1:] for col in table_1[1]]
table_2_cols = [col[1:] for col in table_2[1]]
same_cols_def = set(table_1_cols) == set(table_2_cols)
# Check if both tables have the same partition options.
# Discard partition name.
table_1_part = [part[1:] for part in table_1[2]]
table_2_part = [part[1:] for part in table_2[2]]
same_partition_opts = set(table_1_part) == set(table_2_part)
# Return tables check results.
return same_cols_def, diff, same_partition_opts
def __init__(self, server, table_list, options=None):
"""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
"""
if options is None:
options = {}
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
self.query_opts = {'fetch': False, 'commit': False}
# 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.query_opts)
self.locked = True
elif self.locking == 'snapshot':
self.server.exec_query(_SESSION_ISOLATION_LEVEL, self.query_opts)
self.server.exec_query(_START_TRANSACTION, self.query_opts)
# 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.query_opts)
self.locked = True
else:
raise UtilError("Invalid locking type: '%s'." % self.locking)
def execute_custom_command(self, command, parameters):
"""Execute the utility
This method executes the utility with the parameters specified by the
user. All output is displayed and control returns to the console class.
command[in] Name of the utility to execute
parameters[in] All options and parameters specified by the user
"""
if not command.lower().startswith('mysql'):
command = 'mysql' + command
# look in to the collected utilities
for util_info in self.utils.util_list:
if util_info["name"] == command:
# Get the command used to obtain the help from the utility
cmd = list(util_info["cmd"])
cmd.extend(parameters)
# Add quotes for Windows
if (os.name == "nt"):
# If there is a space in the command, quote it!
if (" " in cmd[0]):
cmd[0] = '"{0}"'.format(cmd[0])
# if cmd is freeze code utility, subprocess just need the
# executable part not absolute path using shell=False or
# Windows will complain about the path. The base path of
# mysqluc is used as location dir base of the subprocess.
if '.exe' in cmd[0]:
_, ut_cmd = os.path.split(cmd[0])
cmd[0] = ut_cmd.replace('"', '')
# If the second part has .py in it and spaces, quote it!
if len(cmd) > 1 and (" " in cmd[1]) and ('.py' in cmd[0]):
cmd[1] = '"{0}"'.format(cmd[1])
if self.quiet:
proc = subprocess.Popen(cmd,
shell=False,
stdout=self.f_out,
stderr=self.f_out)
else:
proc = subprocess.Popen(cmd,
shell=False,
stderr=subprocess.PIPE)
print
# check the output for errors
_, stderr_temp = proc.communicate()
return_code = proc.returncode
err_msg = ("\nThe console has detected that the utility '{0}' "
"ended with an error code.\nYou can get more "
"information about the error by running the console"
" command 'show last error'.").format(command)
if not self.quiet and return_code and stderr_temp:
print(err_msg)
if parameters:
msg = ("\nExecution of utility: '{0} {1}' ended with "
"return code '{2}' and with the following "
"error message:\n"
"{3}").format(command, ' '.join(parameters),
return_code, stderr_temp)
else:
msg = ("\nExecution of utility: '{0}' ended with "
"return code '{1}' and with the following "
"error message:\n{2}").format(
command, return_code, stderr_temp)
self.errors.append(msg)
elif not self.quiet and return_code:
if parameters:
msg = ("\nExecution of utility: '{0} {1}' ended with "
"return code '{2}' but no error message was "
"streamed to the standard error, please review "
"the output from its execution."
"").format(command, ' '.join(parameters),
return_code)
else:
msg = ("\nExecution of utility: '{0}' ended with "
"return code '{1}' but no error message was "
"streamed to the standard error, please review "
"the output from its execution."
"").format(command, return_code)
print(msg)
return
# if got here, is because the utility was not found.
raise UtilError("The utility {0} is not accessible (from the path: "
"{1}).".format(command, self.path))
def copy_db(src_val, dest_val, db_list, options):
"""Copy a database
This method will copy a database and all of its objects and data from
one server (source) to another (destination). Options are available to
selectively ignore each type of object. The force parameter is
used to permit the copy to overwrite an existing destination database
(default is to not overwrite).
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
dest_val[in] a dictionary containing connection information for the
destination 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, verbose, force, quiet,
connections, debug, exclude_names, exclude_patterns)
Notes:
force - if True, the database on the destination will be dropped
if it exists (default is False)
quiet - do not print any information during operation
(default is False)
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.server import connect_servers
from mysql.utilities.command.dbexport import get_change_master_command
from mysql.utilities.command.dbexport import get_gtid_commands
verbose = options.get("verbose", False)
quiet = options.get("quiet", 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)
skip_data = options.get("skip_data", False)
skip_triggers = options.get("skip_triggers", False)
skip_tables = options.get("skip_tables", False)
skip_gtid = options.get("skip_gtid", False)
locking = options.get("locking", "snapshot")
rpl_info = ([], None)
conn_options = {
'quiet': quiet,
'version': "5.1.30",
}
servers = connect_servers(src_val, dest_val, conn_options)
cloning = (src_val == dest_val) or dest_val is None
source = servers[0]
if cloning:
destination = servers[0]
else:
destination = servers[1]
src_gtid = source.supports_gtid() == 'ON'
dest_gtid = destination.supports_gtid() == 'ON' if destination else False
# Get list of all databases from source if --all is specified.
# Ignore system databases.
if options.get("all", False):
# The --all option is valid only if not cloning.
if not cloning:
if not quiet:
print "# Including all databases."
rows = source.get_all_databases()
for row in rows:
db_list.append((row[0], None)) # Keep same name
else:
raise UtilError("Cannot copy all databases on the same server.")
elif not skip_gtid and src_gtid:
# Check to see if this is a full copy (complete backup)
all_dbs = source.exec_query("SHOW DATABASES")
dbs = [db[0] for db in db_list]
for db in all_dbs:
if db[0].upper() in [
"MYSQL", "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA"
]:
continue
if not db[0] in dbs:
print _GTID_BACKUP_WARNING
break
# Do error checking and preliminary work:
# - Check user permissions on source and destination for all databases
# - Check to see if executing on same server but same db name (error)
# - Build list of tables to lock for copying data (if no skipping data)
# - Check storage engine compatibility
for db_name in db_list:
source_db = Database(source, db_name[0])
if destination is None:
destination = source
if db_name[1] is None:
db = db_name[0]
else:
db = db_name[1]
dest_db = Database(destination, db)
# 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)
dest_db.check_write_access(dest_val['user'], dest_val['host'],
access_options)
# Error is source db and destination db are the same and we're cloning
if destination == source and db_name[0] == db_name[1]:
raise UtilError("Destination database name is same as "
"source - source = %s, destination = %s" %
(db_name[0], db_name[1]))
# Error is source database does not exist
if not source_db.exists():
raise UtilError("Source database does not exist - %s" % db_name[0])
# Check storage engines
check_engine_options(destination, options.get("new_engine", None),
options.get("def_engine", None), False,
options.get("quiet", False))
# Get replication commands if rpl_mode specified.
# if --rpl specified, dump replication initial commands
rpl_info = None
# Turn off foreign keys if they were on at the start
destination.disable_foreign_key_checks(True)
# Get GTID commands
new_opts = options.copy()
if not skip_gtid:
gtid_info = get_gtid_commands(source, new_opts)
if src_gtid and not dest_gtid:
print _NON_GTID_WARNING % ("destination", "source", "to")
elif not src_gtid and dest_gtid:
print _NON_GTID_WARNING % ("source", "destination", "from")
else:
gtid_info = None
if src_gtid and not cloning:
print _GTID_WARNING
# If cloning, turn off gtid generation
if gtid_info and cloning:
gtid_info = None
# if GTIDs enabled, write the GTID commands
if gtid_info and dest_gtid:
# Check GTID version for complete feature support
destination.check_gtid_version()
# Check the gtid_purged value too
destination.check_gtid_executed()
for cmd in gtid_info[0]:
print "# GTID operation:", cmd
destination.exec_query(cmd)
if options.get("rpl_mode", None):
new_opts = options.copy()
new_opts['multiline'] = False
new_opts['strict'] = True
rpl_info = get_change_master_command(src_val, new_opts)
destination.exec_query("STOP SLAVE;")
# Copy objects
# We need to delay trigger and events to after data is loaded
new_opts = options.copy()
new_opts['skip_triggers'] = True
new_opts['skip_events'] = True
# Get the table locks unless we are cloning with lock-all
if not (cloning and locking == 'lock-all'):
my_lock = get_copy_lock(source, db_list, options, True)
_copy_objects(source, destination, db_list, new_opts)
# If we are cloning, take the write locks prior to copying data
if cloning and locking == 'lock-all':
my_lock = get_copy_lock(source, db_list, options, True, cloning)
# Copy data
if not skip_data and not skip_tables:
# Copy tables
for db_name in db_list:
# Get a Database class instance
db = Database(source, db_name[0], options)
# Perform the copy
db.init()
db.copy_data(db_name[1], options, destination,
options.get("threads", False))
# if cloning with lock-all unlock here to avoid system table lock conflicts
if cloning and locking == 'lock-all':
my_lock.unlock()
# Create triggers for all databases
if not skip_triggers:
new_opts = options.copy()
new_opts['skip_tables'] = True
new_opts['skip_views'] = True
new_opts['skip_procs'] = True
new_opts['skip_funcs'] = True
new_opts['skip_events'] = True
new_opts['skip_grants'] = True
new_opts['skip_create'] = True
_copy_objects(source, destination, db_list, new_opts, False, False)
# Create events for all databases
if not skip_events:
new_opts = options.copy()
new_opts['skip_tables'] = True
new_opts['skip_views'] = True
new_opts['skip_procs'] = True
new_opts['skip_funcs'] = True
new_opts['skip_triggers'] = True
new_opts['skip_grants'] = True
new_opts['skip_create'] = True
_copy_objects(source, destination, db_list, new_opts, False, False)
if not (cloning and locking == 'lock-all'):
my_lock.unlock()
# if GTIDs enabled, write the GTID-related commands
if gtid_info and dest_gtid:
print "# GTID operation:", gtid_info[1]
destination.exec_query(gtid_info[1])
if options.get("rpl_mode", None):
for cmd in rpl_info[_RPL_COMMANDS]:
if cmd[0] == '#' and not quiet:
print cmd
else:
if verbose:
print cmd
destination.exec_query(cmd)
destination.exec_query("START SLAVE;")
# Turn on foreign keys if they were on at the start
destination.disable_foreign_key_checks(False)
if not quiet:
print "#...done."
return True
def parse_connection(connection_values, my_defaults_reader=None, options=None):
"""Parse connection values.
The function parses a connection specification of one of the forms::
- user[:password]@host[:port][:socket]
- login-path[:port][:socket]
A dictionary is returned containing the connection parameters. The
function is designed so that it shall be possible to use it with a
``connect`` call in the following manner::
options = parse_connection(spec)
conn = mysql.connector.connect(**options)
conn_values[in] Connection values in the form:
user:password@host:port:socket
or login-path:port:socket
my_defaults_reader[in] Instance of MyDefaultsReader to read the
information of the login-path from configuration
files. By default, the value is None.
options[in] Dictionary of options (e.g. basedir), from the used
utility. By default, it set with an empty
dictionary. Note: also supports options values
from optparse.
Notes:
This method validates IPv4 addresses and standard IPv6 addresses.
This method accepts quoted host portion strings. If the host is marked
with quotes, the code extracts this without validation and assigns it to
the host variable in the returned tuple. This allows users to specify host
names and IP addresses that are outside of the supported validation.
Returns dictionary (user, passwd, host, port, socket)
or raise an exception if parsing error
"""
if options is None:
options = {}
def _match(pattern, search_str):
"""Returns the groups from string search or raise FormatError if it
does not match with the pattern.
"""
grp = pattern.match(search_str)
if not grp:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
return grp.groups()
# Split on the '@' to determine the connection string format.
# The user/password may have the '@' character, split by last occurrence.
conn_format = connection_values.rsplit('@', 1)
if len(conn_format) == 1:
# No '@' then handle has in the format: login-path[:port][:socket]
login_path, port, socket = _match(_CONN_LOGINPATH, conn_format[0])
#Check if the login configuration file (.mylogin.cnf) exists
if login_path and not my_login_config_exists():
raise UtilError(".mylogin.cnf was not found at is default "
"location: %s."
"Please configure your login-path data before "
"using it (use the mysql_config_editor tool)." %
my_login_config_path())
# If needed, create a MyDefaultsReader and search for my_print_defaults
# tool.
if not my_defaults_reader:
my_defaults_reader = MyDefaultsReader(options)
elif not my_defaults_reader.tool_path:
my_defaults_reader.search_my_print_defaults_tool()
# Check if the my_print_default tool is able to read a login-path from
# the mylogin configuration file
if not my_defaults_reader.check_login_path_support():
raise UtilError("the used my_print_defaults tool does not "
"support login-path options: %s. "
"Please confirm that the location to a tool with "
"login-path support is included in the PATH "
"(at the beginning)." %
my_defaults_reader.tool_path)
# Read and parse the login-path data (i.e., user, password and host)
login_path_data = my_defaults_reader.get_group_data(login_path)
if login_path_data:
user = login_path_data.get('user', None)
passwd = login_path_data.get('password', None)
host = login_path_data.get('host', None)
if not port:
port = login_path_data.get('port', 3306)
if not socket:
socket = login_path_data.get('socket', None)
else:
raise UtilError("No login credentials found for login-path: %s. "
"Please review the used connection string: %s" %
(login_path, connection_values))
elif len(conn_format) == 2:
# Handle as in the format: user[:password]@host[:port][:socket]
userpass, hostportsock = conn_format
# Get user, password
match = _CONN_USERPASS.match(userpass)
if not match:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
user = match.group('user')
if user is None:
# No password provided
user = match.group('suser').rstrip(':')
passwd = match.group('passwd')
# Handle host, port and socket
if len(hostportsock) <= 0:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
if hostportsock[0] in ['"', "'"]:
# need to strip the quotes
host, port, socket = _match(_CONN_QUOTEDHOST, hostportsock)
if host[0] == '"':
host = host.strip('"')
if host[0] == "'":
host = host.strip("'")
else:
host, port, socket, _ = parse_server_address(hostportsock)
else:
# Unrecognized format
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
# Get character-set from options
if isinstance(options, dict):
charset = options.get("charset", None)
else:
# options is an instance of optparse.Values
try:
charset = options.charset # pylint: disable=E1103
except AttributeError:
charset = None
# Set parsed connection values
connection = {
"user": user,
"host": host,
"port": int(port) if port else 3306,
"passwd": passwd if passwd else ''
}
if charset:
connection["charset"] = charset
# Handle optional parameters. They are only stored in the dict if
# they were provided in the specifier.
if socket is not None and os.name == "posix":
connection['unix_socket'] = socket
return connection
class Table(object):
"""
The Table class encapsulates a table for a given database. The class
has the following capabilities:
- Check to see if the table exists
- Check indexes for duplicates and redundancies
- Print list of indexes for the table
- Extract table data
- Import table data
- Copy table data
"""
def __init__(self, server1, name, options=None):
"""Constructor
server[in] A Server object
name[in] Name of table in the form (db.table)
options[in] options for class: verbose, quiet, get_cols,
quiet If True, do not print information messages
verbose print extra data during operations (optional)
(default is False)
get_cols If True, get the column metadata on construction
(default is False)
"""
if options is None:
options = {}
self.verbose = options.get('verbose', False)
self.quiet = options.get('quiet', False)
self.server = server1
# Keep table identifier considering backtick quotes
if is_quoted_with_backticks(name):
self.q_table = name
self.q_db_name, self.q_tbl_name = Database.parse_object_name(name)
self.db_name = remove_backtick_quoting(self.q_db_name)
self.tbl_name = remove_backtick_quoting(self.q_tbl_name)
self.table = ".".join([self.db_name, self.tbl_name])
else:
self.table = name
self.db_name, self.tbl_name = Database.parse_object_name(name)
self.q_db_name = quote_with_backticks(self.db_name)
self.q_tbl_name = quote_with_backticks(self.tbl_name)
self.q_table = ".".join([self.q_db_name, self.q_tbl_name])
self.obj_type = "TABLE"
self.pri_idx = None
# We store each type of index in a separate list to make it easier
# to manipulate
self.btree_indexes = []
self.hash_indexes = []
self.rtree_indexes = []
self.fulltext_indexes = []
self.text_columns = []
self.blob_columns = []
self.column_format = None
self.column_names = []
self.q_column_names = []
if options.get('get_cols', False):
self.get_column_metadata()
self.dest_vals = None
self.storage_engine = None
# Get max allowed packet
res = self.server.exec_query("SELECT @@session.max_allowed_packet")
if res:
self.max_packet_size = res[0][0]
else:
self.max_packet_size = _MAXPACKET_SIZE
# Watch for invalid values
if self.max_packet_size > _MAXPACKET_SIZE:
self.max_packet_size = _MAXPACKET_SIZE
self._insert = "INSERT INTO %s.%s VALUES "
self.query_options = { # Used for skipping fetch of rows
'fetch': False
}
def exists(self, tbl_name=None):
"""Check to see if the table exists
tbl_name[in] table name (db.table)
(optional) If omitted, operation is performed
on the class instance table name.
return True = table exists, False = table does not exist
"""
db, table = (None, None)
if tbl_name:
db, table = Database.parse_object_name(tbl_name)
else:
db = self.db_name
table = self.tbl_name
res = self.server.exec_query("SELECT TABLE_NAME " +
"FROM INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_SCHEMA = '%s'" % db +
" and TABLE_NAME = '%s'" % table)
return (res is not None and len(res) >= 1)
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 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 _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 UPDATE string
"""
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 = self.q_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:
# Convert None values to NULL (not '' to NULL)
if row[col] is None:
value = 'NULL'
else:
value = "'{0}'".format(row[col])
where_values.append("{0} = {1}".format(col_name, value))
if has_data:
return blob_insert + " WHERE " + " AND ".join(where_values) + ";"
return None
def get_column_string(self, row, new_db):
"""Return a formatted list of column data.
row[in] a row to process
new_db[in] new database name
Returns (string) column list
"""
if self.column_format is None:
self.get_column_metadata()
blob_inserts = []
values = list(row)
# Find blobs
for col in self.blob_columns:
# Save blob updates for later...
blob = self._build_update_blob(row, new_db, self.q_tbl_name, col)
if blob is not None:
blob_inserts.append(blob)
values[col] = "NULL"
# Replace single quotes located in the value for a text field with the
# correct special character escape sequence. This fixes SQL errors
# related to using single quotes in a string value that is single
# quoted. For example, 'this' is it' is changed to 'this\' is it'
for col in self.text_columns:
#Check if the value is not None before replacing quotes
if values[col]:
# Apply escape sequences to special characters
values[col] = convert_special_characters(values[col])
# Build string (add quotes to "string" like types)
val_str = self.column_format % tuple(values)
# Change 'None' occurrences with "NULL"
val_str = val_str.replace(", None", ", NULL")
val_str = val_str.replace("(None", "(NULL")
val_str = val_str.replace(", 'None'", ", NULL")
val_str = val_str.replace("('None'", "(NULL")
return (val_str, blob_inserts)
def make_bulk_insert(self, rows, new_db, columns_names=None):
"""Create bulk insert statements for the data
Reads data from a table (rows) and builds group INSERT statements for
bulk inserts.
Note: This method does not print any information to stdout.
rows[in] a list of rows to process
new_db[in] new database name
Returns (tuple) - (bulk insert statements, blob data inserts)
"""
if self.column_format is None:
self.get_column_metadata()
data_inserts = []
blob_inserts = []
row_count = 0
data_size = 0
val_str = None
for row in rows:
if row_count == 0:
if columns_names:
insert_str = "INSERT INTO {0}.{1} ({2}) VALUES ".format(
new_db, self.q_tbl_name, ", ".join(columns_names)
)
else:
insert_str = self._insert % (new_db, self.q_tbl_name)
if val_str:
row_count += 1
insert_str += val_str
data_size = len(insert_str)
col_data = self.get_column_string(row, new_db)
val_str = col_data[0]
if len(col_data[1]) > 0:
blob_inserts.extend(col_data[1])
row_size = len(val_str)
next_size = data_size + row_size + 3
if (row_count >= _MAXBULK_VALUES) or \
(next_size > (int(self.max_packet_size) - 512)): # add buffer
data_inserts.append(insert_str)
row_count = 0
else:
row_count += 1
if row_count > 1:
insert_str += ", "
insert_str += val_str
data_size += row_size + 3
if row_count > 0:
data_inserts.append(insert_str)
return (data_inserts, blob_inserts)
def get_storage_engine(self):
"""Get the storage engine (in UPPERCASE) for the table.
Returns the name in UPPERCASE of the storage engine use for the table
or None if the information is not found.
"""
self.server.exec_query("USE {0}".format(self.q_db_name),
self.query_options)
res = self.server.exec_query(
"SHOW TABLE STATUS WHERE name = '{0}'".format(self.tbl_name)
)
try:
# Return store engine converted to UPPER cases.
return res[0][1].upper() if res[0][1] else None
except IndexError:
# Return None if table status information is not available.
return None
def get_segment_size(self, num_conn=1):
"""Get the segment size based on number of connections (threads).
num_conn[in] Number of threads(connections) to use
Default = 1 (one large segment)
Returns (int) segment_size
Note: if num_conn <= 1 - returns number of rows
"""
# Get number of rows
num_rows = 0
try:
res = self.server.exec_query("USE %s" % self.q_db_name,
self.query_options)
except:
pass
res = self.server.exec_query("SHOW TABLE STATUS LIKE '%s'" %
self.tbl_name)
if res:
num_rows = int(res[0][4])
if num_conn <= 1:
return num_rows
# Calculate number of threads and segment size to fetch
thread_limit = num_conn
if thread_limit > _MAXTHREADS_INSERT:
thread_limit = _MAXTHREADS_INSERT
if num_rows > (_MAXROWS_PER_THREAD * thread_limit):
max_threads = thread_limit
else:
max_threads = int(num_rows / _MAXROWS_PER_THREAD)
if max_threads == 0:
max_threads = 1
if max_threads > 1 and self.verbose:
print "# Using multi-threaded insert option. Number of " \
"threads = %d." % max_threads
return (num_rows / max_threads) + max_threads
def _bulk_insert(self, rows, new_db, destination=None):
"""Import data using bulk insert
Reads data from a table and builds group INSERT statements for writing
to the destination server specified (new_db.name).
This method is designed to be used in a thread for parallel inserts.
As such, it requires its own connection to the destination server.
Note: This method does not print any information to stdout.
rows[in] a list of rows to process
new_db[in] new database name
destination[in] the destination server
"""
if self.dest_vals is None:
self.dest_vals = self.get_dest_values(destination)
# Spawn a new connection
server_options = {
'conn_info': self.dest_vals,
'role': "thread",
}
dest = Server(server_options)
dest.connect()
# Issue the write lock
lock_list = [("%s.%s" % (new_db, self.q_tbl_name), 'WRITE')]
my_lock = Lock(dest, lock_list, {'locking': 'lock-all', })
# First, turn off foreign keys if turned on
dest.disable_foreign_key_checks(True)
if self.column_format is None:
self.get_column_metadata()
data_lists = self.make_bulk_insert(rows, new_db)
insert_data = data_lists[0]
blob_data = data_lists[1]
# Insert the data first
for data_insert in insert_data:
try:
dest.exec_query(data_insert, self.query_options)
except UtilError, e:
raise UtilError("Problem inserting data. "
"Error = %s" % e.errmsg)
# Now insert the blob data if there is any
for blob_insert in blob_data:
try:
dest.exec_query(blob_insert, self.query_options)
except UtilError, e:
raise UtilError("Problem updating blob field. "
"Error = %s" % e.errmsg)
def parse_connection(connection_values, my_defaults_reader=None, options=None):
"""Parse connection values.
The function parses a connection specification of one of the forms::
- user[:password]@host[:port][:socket]
- login-path[:port][:socket]
A dictionary is returned containing the connection parameters. The
function is designed so that it shall be possible to use it with a
``connect`` call in the following manner::
options = parse_connection(spec)
conn = mysql.connector.connect(**options)
conn_values[in] Connection values in the form:
user:password@host:port:socket
or login-path:port:socket
my_defaults_reader[in] Instance of MyDefaultsReader to read the
information of the login-path from configuration
files. By default, the value is None.
options[in] Dictionary of options (e.g. basedir), from the used
utility. By default, it set with an empty
dictionary. Note: also supports options values
from optparse.
Notes:
This method validates IPv4 addresses and standard IPv6 addresses.
This method accepts quoted host portion strings. If the host is marked
with quotes, the code extracts this without validation and assigns it to
the host variable in the returned tuple. This allows users to specify host
names and IP addresses that are outside of the supported validation.
Returns dictionary (user, passwd, host, port, socket)
or raise an exception if parsing error
"""
if options is None:
options = {}
def _match(pattern, search_str):
"""Returns the groups from string search or raise FormatError if it
does not match with the pattern.
"""
grp = pattern.match(search_str)
if not grp:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
return grp.groups()
# SSL options, must not be overwritten with those from options.
ssl_ca = None
ssl_cert = None
ssl_key = None
ssl = None
# Split on the '@' to determine the connection string format.
# The user/password may have the '@' character, split by last occurrence.
conn_format = connection_values.rsplit('@', 1)
if len(conn_format) == 1:
# No '@' so try config-path and login-path
# The config_path and login-path collide on their first element and
# only differs on their secondary optional values.
# 1. Try match config_path and his optional value group. If both
# matches and the connection data can be retrieved, return the data.
# If errors occurs in this step raise them immediately.
# 2. If config_path matches but group does not, and connection data
# can not be retrieved, do not raise errors and try to math
# login_path on step 4.
# 3. If connection data is retrieved on step 2, then try login_path on
# next step to overwrite values from the new configuration.
# 4. If login_path matches, check is .mylogin.cnf exists, if it doesn't
# and data configuration was found verify it for missing values and
# continue if they are not any missing.
# 5. If .mylogin.cnf exists and data configuration is found, overwrite
# any previews value from config_path if there is any.
# 6. If login_path matches a secondary value but the configuration data
# could not be retrieved, do not continue and raise any error.
# 7. In case errors have occurred trying to get data from config_path,
# and group did not matched, and in addition no secondary value,
# matched from login_path (port of socket) mention that config_path
# and login_path were not able to retrieve the connection data.
# try login_path and overwrite the values.
# Handle the format: config-path[[group]]
config_path, group = _match(_CONN_CONFIGPATH, conn_format[0])
port = None
socket = None
config_path_data = None
login_path_data = None
config_path_err_msg = None
login_path = None
if config_path:
try:
# If errors occurs, and group matched: raise any errors as the
# group is exclusive of config_path.
config_path_data = handle_config_path(config_path, group)
except UtilError as err:
if group:
raise
else:
# Convert first letter to lowercase to include in error
# message with the correct case.
config_path_err_msg = \
err.errmsg[0].lower() + err.errmsg[1:]
if group is None:
# the conn_format can still be a login_path so continue
# No '@' then handle has in the format: login-path[:port][:socket]
login_path, port, socket = _match(_CONN_LOGINPATH, conn_format[0])
# Check if the login configuration file (.mylogin.cnf) exists
if login_path and not my_login_config_exists():
if not config_path_data:
util_err_msg = (".mylogin.cnf was not found at is default "
"location: {0}. Please configure your "
"login-path data before using it (use the "
"mysql_config_editor tool)."
"".format(my_login_config_path()))
if config_path_err_msg and not (port or socket):
util_err_msg = ("{0} In addition, {1}"
"").format(util_err_msg,
config_path_err_msg)
raise UtilError(util_err_msg)
else:
# If needed, create a MyDefaultsReader and search for
# my_print_defaults tool.
if not my_defaults_reader:
try:
my_defaults_reader = MyDefaultsReader(options)
except UtilError as err:
if config_path_err_msg and not (port or socket):
util_err_msg = ("{0} In addition, {1}"
"").format(err.errmsg,
config_path_err_msg)
raise UtilError(util_err_msg)
else:
raise
elif not my_defaults_reader.tool_path:
my_defaults_reader.search_my_print_defaults_tool()
# Check if the my_print_default tool is able to read a
# login-path from the mylogin configuration file
if not my_defaults_reader.check_login_path_support():
util_err_msg = ("the used my_print_defaults tool does not "
"support login-path options: {0}. "
"Please confirm that the location to a "
"tool with login-path support is included "
"in the PATH (at the beginning)."
"".format(my_defaults_reader.tool_path))
if config_path_err_msg and not (port or socket):
util_err_msg = ("{0} In addition, {1}"
"").format(util_err_msg,
config_path_err_msg)
raise UtilError(util_err_msg)
# Read and parse the login-path data (i.e., user, password and
# host)
login_path_data = my_defaults_reader.get_group_data(login_path)
if config_path_data or login_path_data:
if config_path_data:
if not login_path_data:
login_path_data = config_path_data
else:
# Overwrite values from login_path_data
config_path_data.update(login_path_data)
login_path_data = config_path_data
user = login_path_data.get('user', None)
passwd = login_path_data.get('password', None)
host = login_path_data.get('host', None)
if not port:
port = login_path_data.get('port', None)
if not socket:
socket = login_path_data.get('socket', None)
if os.name == "posix" and socket is not None:
# if we are on unix systems and used a socket, hostname can be
# safely assumed as being localhost so it is not required
required_options = ('user', 'socket')
host = 'localhost' if host is None else host
else:
required_options = ('user', 'host', 'port')
missing_options = [
opt for opt in required_options if locals()[opt] is None
]
# If we are on unix and port is missing, user might have specified
# a socket instead
if os.name == "posix" and "port" in missing_options:
i = missing_options.index("port")
if socket: # If we have a socket, port is not needed
missing_options.pop(i)
else:
# if we don't have neither a port nor a socket, we need
# either a port or a socket
missing_options[i] = "port or socket"
if missing_options:
message = ",".join(missing_options)
if len(missing_options) > 1:
comma_idx = message.rfind(",")
message = "{0} and {1}".format(message[:comma_idx],
message[comma_idx + 1:])
pluralize = "s" if len(missing_options) > 1 else ""
raise UtilError("Missing connection value{0} for "
"{1} option{0}".format(pluralize, message))
# optional options, available only on config_path_data
if config_path_data:
ssl_ca = config_path_data.get('ssl-ca', None)
ssl_cert = config_path_data.get('ssl-cert', None)
ssl_key = config_path_data.get('ssl-key', None)
ssl = config_path_data.get('ssl', None)
else:
if login_path and not config_path:
raise UtilError("No login credentials found for login-path: "
"{0}. Please review the used connection string"
": {1}".format(login_path, connection_values))
elif not login_path and config_path:
raise UtilError("No login credentials found for config-path: "
"{0}. Please review the used connection string"
": {1}".format(login_path, connection_values))
elif login_path and config_path:
raise UtilError("No login credentials found for either "
"login-path: '{0}' nor config-path: '{1}'. "
"Please review the used connection string: {2}"
"".format(login_path, config_path,
connection_values))
elif len(conn_format) == 2:
# Handle as in the format: user[:password]@host[:port][:socket]
userpass, hostportsock = conn_format
# Get user, password
match = _CONN_USERPASS.match(userpass)
if not match:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
user = match.group('user')
if user is None:
# No password provided
user = match.group('suser').rstrip(':')
passwd = match.group('passwd')
# Handle host, port and socket
if len(hostportsock) <= 0:
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
if hostportsock[0] in ['"', "'"]:
# need to strip the quotes
host, port, socket = _match(_CONN_QUOTEDHOST, hostportsock)
if host[0] == '"':
host = host.strip('"')
if host[0] == "'":
host = host.strip("'")
else:
host, port, socket, _ = parse_server_address(hostportsock)
else:
# Unrecognized format
raise FormatError(_BAD_CONN_FORMAT.format(connection_values))
# Get character-set from options
if isinstance(options, dict):
charset = options.get("charset", None)
# If one SSL option was found before, not mix with those in options.
if not ssl_cert and not ssl_ca and not ssl_key and not ssl:
ssl_cert = options.get("ssl_cert", None)
ssl_ca = options.get("ssl_ca", None)
ssl_key = options.get("ssl_key", None)
ssl = options.get("ssl", None)
else:
# options is an instance of optparse.Values
try:
charset = options.charset # pylint: disable=E1103
except AttributeError:
charset = None
# If one SSL option was found before, not mix with those in options.
if not ssl_cert and not ssl_ca and not ssl_key and not ssl:
try:
ssl_cert = options.ssl_cert # pylint: disable=E1103
except AttributeError:
ssl_cert = None
try:
ssl_ca = options.ssl_ca # pylint: disable=E1103
except AttributeError:
ssl_ca = None
try:
ssl_key = options.ssl_key # pylint: disable=E1103
except AttributeError:
ssl_key = None
try:
ssl = options.ssl # pylint: disable=E1103
except AttributeError:
ssl = None
# Set parsed connection values
connection = {
"user": user,
"host": host,
"port": int(port) if port else 3306,
"passwd": passwd if passwd else ''
}
if charset:
connection["charset"] = charset
if ssl_cert:
connection["ssl_cert"] = ssl_cert
if ssl_ca:
connection["ssl_ca"] = ssl_ca
if ssl_key:
connection["ssl_key"] = ssl_key
if ssl:
connection["ssl"] = ssl
# Handle optional parameters. They are only stored in the dict if
# they were provided in the specifier.
if socket is not None and os.name == "posix":
connection['unix_socket'] = socket
return connection
def print_graph(self,
topology_list=None,
masters_found=None,
level=0,
preamble=""):
"""Prints a graph of the topology map to standard output.
This method traverses a list of the topology and prints a graph. The
method is designed to be recursive traversing the list to print the
slaves for each master in the topology. It will also detect a circular
replication segment and indicate it on the graph.
topology_list[in] a list in the form (master, slave) of server
masters_found[in] a list of all servers in master roles - used to
detect a circular replication topology. Initially,
this is an empty list as the master detection must
occur as the topology is traversed.
level[in] the level of indentation - increases with each
set of slaves found in topology
preamble[in] prefix calculated during recursion to indent text
"""
if not topology_list:
topology_list = []
if not masters_found:
masters_found = []
# if first iteration, use the topology list generated earlier
if topology_list == []:
if self.topology == []:
# topology not generated yet
raise UtilError("You must first generate the topology.")
topology_list = self.topology
# Detect if we are looking at a sublist or not. Get sublist.
if len(topology_list) == 1:
topology_list = topology_list[0]
master = topology_list[0]
# Save the master for circular replication identification
masters_found.append(master)
# For each slave, print the graph link
slaves = topology_list[1]
stop = len(slaves)
if stop > 0:
# Level 0 is always the first master in the topology.
if level == 0:
print("{0} (MASTER)".format(master))
for i in range(0, stop):
if len(slaves[i]) == 1:
slave = slaves[i][0]
else:
slave = slaves[i]
new_preamble = "{0} ".format(preamble)
print("{0}|".format(new_preamble))
role = "(SLAVE"
if not slave[1] == [] or slave[0] in masters_found:
role = "{0} + MASTER".format(role)
role = "{0})".format(role)
# Print threads (IO and SQL) status if verbose
t_status = ''
if self.verbose:
try:
t_status = " [IO: {0}, SQL: {1}]".format(
slave[2][0], slave[2][1])
except IndexError:
# This should never happened... (done to avoid crash)
t_status = " [IO: ??, SQL: ??]"
print "{0}+--- {1}{2}".format(new_preamble, slave[0],
t_status),
if (slave[0] in masters_found):
print "<-->",
else:
print "-",
print role
if not slave[1] == []:
if i < stop - 1:
new_preamble = "{0}|".format(new_preamble)
else:
new_preamble = "{0} ".format(new_preamble)
self.print_graph(slave, masters_found, level + 1,
new_preamble)
def compare_all_databases(server1_val, server2_val, exclude_list, options):
"""Perform a consistency check among all common databases on the servers
This method gets all databases from the servers, prints any missing
databases and performs a consistency check among all common databases.
If any errors or differences are found, the operation will print the
difference and continue.
This method will return None if no databases to compare.
"""
success = True
# Connect to servers
conn_options = {
"quiet": options.get("quiet", False),
"src_name": "server1",
"dest_name": "server2",
}
server1, server2 = connect_servers(server1_val, server2_val, conn_options)
# Check if the specified servers are the same.
if server1.port == server2.port and server1.is_alias(server2.host):
raise UtilError(
"Specified servers are the same (server1={host1}:{port1} and "
"server2={host2}:{port2}). Cannot compare all databases on the "
"same server.".format(host1=server1.host,
port1=server1.port,
host2=server2.host,
port2=server2.port))
# Get all databases, except those used in --exclude
get_dbs_query = """
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA'
AND SCHEMA_NAME != 'PERFORMANCE_SCHEMA'
AND SCHEMA_NAME != 'mysql'
{0}"""
conditions = ""
if exclude_list:
# Add extra where to exclude databases in exclude_list
operator = 'REGEXP' if options['use_regexp'] else 'LIKE'
conditions = "AND {0}".format(" AND ".join([
"SCHEMA_NAME NOT {0} '{1}'".format(operator, db)
for db in exclude_list
]))
server1_dbs = set(
[db[0] for db in server1.exec_query(get_dbs_query.format(conditions))])
server2_dbs = set(
[db[0] for db in server2.exec_query(get_dbs_query.format(conditions))])
# Check missing databases
if options['changes-for'] == 'server1':
diff_dbs = server1_dbs.difference(server2_dbs)
for db in diff_dbs:
msg = _ERROR_DB_MISSING_ON_SERVER.format(db, "server1", "server2")
print("# {0}".format(msg))
else:
diff_dbs = server2_dbs.difference(server1_dbs)
for db in diff_dbs:
msg = _ERROR_DB_MISSING_ON_SERVER.format(db, "server2", "server1")
print("# {0}".format(msg))
# Compare databases in common
common_dbs = server1_dbs.intersection(server2_dbs)
if common_dbs:
print("# Comparing databases: {0}".format(", ".join(common_dbs)))
else:
success = None
for db in common_dbs:
try:
res = database_compare(server1_val, server2_val, db, db, options)
if not res:
success = False
print("\n")
except UtilError as err:
print("ERROR: {0}\n".format(err.errmsg))
success = False
return success
def show_log_usage(server, datadir, options):
"""Show binary or relay log disk space usage.
Display binary log file information if binlog turned on if log_type =
'binary log' (default) or show relay log file information is server is
a slave and relay log is engaged.
server[in] Connected server to operate against
datadir[in] The datadir for the server
options[in] Required options for operation: format, no_headers.
log_type
return True or raise exception on error
"""
log_type = options.get("log_type", "binary log")
have_read = options.get("have_read", False)
is_remote = options.get("is_remote", False)
quiet = options.get("quiet", False)
# Check privileges to execute required queries: SUPER or REPLICATION CLIENT
user_inst = User(server, "{0}@{1}".format(server.user, server.host))
has_super = user_inst.has_privilege("*", "*", "SUPER")
has_rpl_client = user_inst.has_privilege("*", "*", "REPLICATION CLIENT")
# Verify necessary permissions (access to filesystem) and privileges
# (execute queries) to get logs usage information.
if log_type == 'binary log':
# Check for binlog ON first.
res = server.show_server_variable('log_bin')
if res and res[0][1].upper() == 'OFF':
print("# Binary logging is turned off on the server.")
return True
# Check required privileges according to the access to the datadir.
if not is_remote and have_read:
# Requires SUPER or REPLICATION CLIENT to execute:
# SHOW MASTER STATUS.
if not has_super and not has_rpl_client:
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
else:
# Requires SUPER for server < 5.6.6 or also REPLICATION CLIENT for
# server >= 5.6.6 to execute: SHOW BINARY LOGS.
if (server.check_version_compat(5, 6, 6) and not has_super
and not has_rpl_client):
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
elif not has_super:
print(
"# {0} information not accessible. User must have the "
"SUPER "
"privilege.".format(log_type.capitalize()))
return True
else: # relay log
# Requires SUPER or REPLICATION CLIENT to execute SHOW SLAVE STATUS.
if not has_super and not has_rpl_client:
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
# Can only retrieve usage information from the localhost filesystem.
if is_remote:
print("# {0} information not accessible from a remote host."
"".format(log_type.capitalize()))
return True
elif not have_read:
print(
"# {0} information not accessible. Check your permissions "
"to {1}.".format(log_type.capitalize(), datadir))
return True
# Check server status and availability of specified log file type.
if log_type == 'binary log':
try:
res = server.exec_query("SHOW MASTER STATUS")
if res:
current_log = res[0][0]
else:
print("# Cannot access files - no binary log information")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
try:
res = server.exec_query("SHOW SLAVE STATUS")
if res:
current_log = res[0][7]
else:
print(
"# Server is not an active slave - no relay log "
"information.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
# Enough permissions and privileges, get the usage information.
if not quiet:
print("# {0} information:".format(log_type.capitalize()))
print("Current {0} file = {1}".format(log_type, current_log))
if log_type == 'binary log' and (is_remote or not have_read):
# Retrieve binlog usage info from SHOW BINARY LOGS.
try:
logs = server.exec_query("SHOW BINARY LOGS")
if logs:
# Calculate total size.
total = sum([int(item[1]) for item in logs])
else:
print("# No binary logs data available.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
# Retrieve usage info from localhost filesystem.
# Note: as of 5.6.2, users can specify location of binlog and relaylog.
if server.check_version_compat(5, 6, 2):
if log_type == 'binary log':
res = server.show_server_variable("log_bin_basename")[0]
else:
res = server.show_server_variable("relay_log_basename")[0]
log_path, log_prefix = os.path.split(res[1])
# In case log_path and log_prefix are '' (not defined) set them
# to the default value.
if not log_path:
log_path = datadir
if not log_prefix:
log_prefix = os.path.splitext(current_log)[0]
else:
log_path = datadir
log_prefix = os.path.splitext(current_log)[0]
logs, total = _build_log_list(log_path, log_prefix)
if not logs:
raise UtilError("The {0}s are missing.".format(log_type))
# Print logs usage information.
_print_logs(logs, total, options)
return True
def _get_slaves(self, max_depth, seed_conn=None, masters_found=None):
"""Find the attached slaves for a list of server connections.
This method connects to each server in the list and retrieves its
slaves. It can be called recursively if the recurse option is True.
max_depth[in] Maximum depth of recursive search
seed_conn[in] Current master connection dictionary. Initially,
this is the seed server (original master defined
in constructor)
masters_found[in] a list of all servers in master roles - used to
detect a circular replication topology. Initially,
this is an empty list as the master detection must
occur as the topology is traversed.
Returns list - list of slaves connected to each server in list
"""
if not masters_found:
masters_found = []
topology = []
if seed_conn is None:
seed_conn = self.seed_server
master, master_info = self._connect(seed_conn)
if master is None:
return []
# Check user permissions
self._check_permissions(master, "REPLICATION SLAVE")
# Save the master for circular replication identification
masters_found.append(master_info)
if not self.quiet:
print "# Finding slaves for master: %s" % master_info
# See if the user wants us to discover slaves.
discover = self.options.get("discover", None)
if discover is None:
return
# Get user and password (supports login-paths)
try:
user, password = parse_user_password(discover,
options=self.options)
except FormatError:
raise UtilError(USER_PASSWORD_FORMAT.format("--discover-slaves"))
# Get replication topology
slaves = master.get_slaves(user, password)
slave_list = []
depth = 0
if len(slaves) > 0:
for slave in slaves:
if slave.find(":") > 0:
host, port = slave.split(":", 1)
else:
host = slave
port = _START_PORT # Use the default
slave_conn = self.seed_server.copy()
slave_conn['host'] = host
slave_conn['port'] = port
io_sql_running = None
# If verbose then get slave threads (IO and SQL) status
if self.verbose:
# Create slave instance
conn_dict = {
'conn_info': {
'user': user,
'passwd': password,
'host': host,
'port': port,
'socket': None
},
'role': slave,
'verbose': self.verbose
}
slave_obj = Slave(conn_dict)
# Get IO and SQL status
try:
slave_obj.connect()
thread_status = slave_obj.get_thread_status()
if thread_status:
io_sql_running = (thread_status[1],
thread_status[2])
except UtilError:
# Connection error
io_sql_running = ('ERROR', 'ERROR')
# Now check for circular replication topology - do not recurse
# if slave is also a master.
if self.recurse and slave not in masters_found and \
((max_depth is None) or (depth < max_depth)):
new_list = self._get_slaves(max_depth, slave_conn,
masters_found)
if new_list == []:
slave_list.append((slave, [], io_sql_running))
else:
# Add IO and SQL state to slave from recursion
if io_sql_running:
new_list = [(new_list[0][0], new_list[0][1],
io_sql_running)]
slave_list.append(new_list)
depth += 1
else:
slave_list.append((slave, [], io_sql_running))
topology.append((master_info, slave_list))
return topology
def clone_server(conn_val, options):
"""Clone an existing server
This method creates a new instance of a running server using a datadir
set to the new_data parametr, with a port set to new_port, server_id
set to new_id and a root password of root_pass. You can also specify
additional parameters for the mysqld command line as well as turn on
verbosity mode to display more diagnostic information during the clone
process.
The method will build a new base database installation from the .sql
files used to construct a new installation. Once the database is
created, the server will be started.
dest_val[in] a dictionary containing connection information
including:
(user, password, host, port, socket)
options[in] dictionary of options:
new_data[in] An existing path to create the new database and use
as datadir for new instance
(default = None)
new_port[in] Port number for new instance
(default = 3307)
new_id[in] Server_id for new instance
(default = 2)
root_pass[in] Password for root user on new instance (optional)
mysqld_options[in] Additional command line options for mysqld
verbosity[in] Print additional information during operation
(default is 0)
quiet[in] If True, do not print messages.
(default is False)
cmd_file[in] file name to write startup command
start_timeout[in] Number of seconds to wait for server to start
"""
new_data = os.path.abspath(options.get('new_data', None))
new_port = options.get('new_port', '3307')
root_pass = options.get('root_pass', None)
verbosity = options.get('verbosity', 0)
user = options.get('user', 'root')
quiet = options.get('quiet', False)
cmd_file = options.get('cmd_file', None)
start_timeout = int(options.get('start_timeout', 10))
mysqld_options = options.get('mysqld_options', '')
force = options.get('force', False)
quote_char = "'" if os.name == "posix" else '"'
if not check_port_in_use('localhost', int(new_port)):
raise UtilError("Port {0} in use. Please choose an "
"available port.".format(new_port))
# Check if path to database files is greater than MAX_DIR_SIZE char,
if len(new_data) > MAX_DATADIR_SIZE and not force:
raise UtilError("The --new-data path '{0}' is too long "
"(> {1} characters). Please use a smaller one. "
"You can use the --force option to skip this "
"check".format(new_data, MAX_DATADIR_SIZE))
# Clone running server
if conn_val is not None:
# Try to connect to the MySQL database server.
server1_options = {
'conn_info': conn_val,
'role': "source",
}
server1 = Server(server1_options)
server1.connect()
if not quiet:
print "# Cloning the MySQL server running on %s." % \
conn_val["host"]
# Get basedir
rows = server1.exec_query("SHOW VARIABLES LIKE 'basedir'")
if not rows:
raise UtilError("Unable to determine basedir of running server.")
basedir = os.path.normpath(rows[0][1])
# Cloning downed or offline server
else:
basedir = os.path.abspath(options.get("basedir", None))
if not quiet:
print "# Cloning the MySQL server located at %s." % basedir
new_data_deleted = False
# If datadir exists, has data, and user said it was Ok, delete it
if os.path.exists(new_data) and options.get("delete", False) and \
os.listdir(new_data):
new_data_deleted = True
shutil.rmtree(new_data, True)
# Create new data directory if it does not exist
if not os.path.exists(new_data):
if not quiet:
print "# Creating new data directory..."
try:
os.mkdir(new_data)
except OSError as err:
raise UtilError("Unable to create directory '{0}', reason: {1}"
"".format(new_data, err.strerror))
# After create the new data directory, check for free space, so the errors
# regarding invalid or inaccessible path had been dismissed already.
# If not force specified verify and stop if there is not enough free space
if not force and os.path.exists(new_data) and \
estimate_free_space(new_data) < REQ_FREE_SPACE:
# Don't leave empty folders, delete new_data if was previously deleted
if os.path.exists(new_data) and new_data_deleted:
shutil.rmtree(new_data, True)
raise UtilError(LOW_SPACE_ERRR_MSG.format(directory=new_data,
megabytes=REQ_FREE_SPACE))
# Check for warning of using --skip-innodb
mysqld_path = get_tool_path(basedir, "mysqld")
version_str = get_mysqld_version(mysqld_path)
# convert version_str from str tuple to integer tuple if possible
if version_str is not None:
version = tuple([int(digit) for digit in version_str])
else:
version = None
if mysqld_options is not None and ("--skip-innodb" in mysqld_options or
"--innodb" in mysqld_options) and version is not None and \
version >= (5, 7, 5):
print("# WARNING: {0}".format(WARN_OPT_SKIP_INNODB))
if not quiet:
print "# Configuring new instance..."
print "# Locating mysql tools..."
mysqladmin_path = get_tool_path(basedir, "mysqladmin")
mysql_basedir = basedir
if os.path.exists(os.path.join(basedir, "local/mysql/share/")):
mysql_basedir = os.path.join(mysql_basedir, "local/mysql/")
# for source trees
elif os.path.exists(os.path.join(basedir, "/sql/share/english/")):
mysql_basedir = os.path.join(mysql_basedir, "/sql/")
locations = [
("mysqld", mysqld_path),
("mysqladmin", mysqladmin_path),
]
# From 5.7.6 version onwards, bootstrap is done via mysqld with the
# --initialize-insecure option, so no need to get information about the
# sql system tables that need to be loaded.
if version < (5, 7, 6):
system_tables = get_tool_path(basedir, "mysql_system_tables.sql",
False)
system_tables_data = get_tool_path(basedir,
"mysql_system_tables_data.sql",
False)
test_data_timezone = get_tool_path(basedir,
"mysql_test_data_timezone.sql",
False)
help_data = get_tool_path(basedir, "fill_help_tables.sql", False)
locations.extend([("mysql_system_tables.sql", system_tables),
("mysql_system_tables_data.sql", system_tables_data),
("mysql_test_data_timezone.sql", test_data_timezone),
("fill_help_tables.sql", help_data),
])
if verbosity >= 3 and not quiet:
print "# Location of files:"
if cmd_file is not None:
locations.append(("write startup command to", cmd_file))
for location in locations:
print "# % 28s: %s" % location
# Create the new mysql data with mysql_import_db-like process
if not quiet:
print "# Setting up empty database and mysql tables..."
fnull = open(os.devnull, 'w')
# For MySQL versions before 5.7.6, use regular bootstrap procedure.
if version < (5, 7, 6):
# Get bootstrap SQL statements
sql = list()
sql.append("CREATE DATABASE mysql;")
sql.append("USE mysql;")
innodb_disabled = False
if mysqld_options:
innodb_disabled = '--innodb=OFF' in mysqld_options
for sqlfile in [system_tables, system_tables_data, test_data_timezone,
help_data]:
lines = open(sqlfile, 'r').readlines()
# On MySQL 5.7.5, the root@localhost account creation was
# moved from the system_tables_data sql file into the
# mysql_install_db binary. Since we don't use mysql_install_db
# directly we need to create the root user account ourselves.
if (version is not None and version == (5, 7, 5) and
sqlfile == system_tables_data):
lines.extend(_CREATE_ROOT_USER)
for line in lines:
line = line.strip()
# Don't fail when InnoDB is turned off (Bug#16369955)
# (Ugly hack)
if (sqlfile == system_tables and
"SET @sql_mode_orig==@@SES" in line and innodb_disabled):
for line in lines:
if 'SET SESSION sql_mode=@@sql' in line:
break
sql.append(line)
# Bootstap to setup mysql tables
cmd = [
mysqld_path,
"--no-defaults",
"--bootstrap",
"--datadir={0}".format(new_data),
"--basedir={0}".format(os.path.abspath(mysql_basedir)),
]
if verbosity >= 1 and not quiet:
proc = subprocess.Popen(cmd, shell=False, stdin=subprocess.PIPE)
else:
proc = subprocess.Popen(cmd, shell=False, stdin=subprocess.PIPE,
stdout=fnull, stderr=fnull)
proc.communicate('\n'.join(sql))
# From 5.7.6 onwards, mysql_install_db has been replaced by mysqld and
# the --initialize option
else:
cmd = [
mysqld_path,
"--no-defaults",
"--initialize-insecure=on",
"--datadir={0}".format(new_data),
"--basedir={0}".format(os.path.abspath(mysql_basedir))
]
if verbosity >= 1 and not quiet:
proc = subprocess.Popen(cmd, shell=False, stdin=subprocess.PIPE)
else:
proc = subprocess.Popen(cmd, shell=False, stdin=subprocess.PIPE,
stdout=fnull, stderr=fnull)
# Wait for subprocess to finish
res = proc.wait()
# Kill subprocess just in case it didn't finish - Ok if proc doesn't exist
if int(res) != 0:
if os.name == "posix":
try:
os.kill(proc.pid, subprocess.signal.SIGTERM)
except OSError:
raise UtilError("Failed to kill process with pid '{0}'"
"".format(proc.pid))
else:
ret_code = subprocess.call("taskkill /F /T /PID "
"{0}".format(proc.pid), shell=True)
# return code 0 means it was successful and 128 means it tried
# to kill a process that doesn't exist
if ret_code not in (0, 128):
raise UtilError("Failed to kill process with pid '{0}'. "
"Return code {1}".format(proc.pid,
ret_code))
# Drop the bootstrap file
if os.path.isfile("bootstrap.sql"):
os.unlink("bootstrap.sql")
# Start the instance
if not quiet:
print "# Starting new instance of the server..."
# If the user is not the same as the user running the script...
# and this is a Posix system... and we are running as root
if user_change_as_root(options):
subprocess.call(['chown', '-R', user, new_data])
subprocess.call(['chgrp', '-R', user, new_data])
socket_path = os.path.join(new_data, 'mysql.sock')
# If socket path is too long, use mkdtemp to create a tmp dir and
# use it instead to store the socket
if os.name == 'posix' and len(socket_path) > MAX_SOCKET_PATH_SIZE:
socket_path = os.path.join(tempfile.mkdtemp(), 'mysql.sock')
if not quiet:
print("# WARNING: The socket file path '{0}' is too long (>{1}), "
"using '{2}' instead".format(
os.path.join(new_data, 'mysql.sock'),
MAX_SOCKET_PATH_SIZE, socket_path))
cmd = {
'datadir': '--datadir={0}'.format(new_data),
'tmpdir': '--tmpdir={0}'.format(new_data),
'pid-file': '--pid-file={0}'.format(
os.path.join(new_data, "clone.pid")),
'port': '--port={0}'.format(new_port),
'server': '--server-id={0}'.format(options.get('new_id', 2)),
'basedir': '--basedir={0}'.format(mysql_basedir),
'socket': '--socket={0}'.format(socket_path),
}
if user:
cmd.update({'user': '******'.format(user)})
if mysqld_options:
if isinstance(mysqld_options, (list, tuple)):
cmd.update(dict(zip(mysqld_options, mysqld_options)))
else:
new_opts = mysqld_options.strip(" ")
# Drop the --mysqld=
if new_opts.startswith("--mysqld="):
new_opts = new_opts[9:]
if new_opts.startswith('"') and new_opts.endswith('"'):
list_ = shlex.split(new_opts.strip('"'))
cmd.update(dict(zip(list_, list_)))
elif new_opts.startswith("'") and new_opts.endswith("'"):
list_ = shlex.split(new_opts.strip("'"))
cmd.update(dict(zip(list_, list_)))
# Special case where there is only 1 option
elif len(new_opts.split("--")) == 1:
cmd.update({mysqld_options: mysqld_options})
else:
list_ = shlex.split(new_opts)
cmd.update(dict(zip(list_, list_)))
# set of options that must be surrounded with quotes
options_to_quote = set(["datadir", "tmpdir", "basedir", "socket",
"pid-file"])
# Strip spaces from each option
for key in cmd:
cmd[key] = cmd[key].strip(' ')
# Write startup command if specified
if cmd_file is not None:
if verbosity >= 0 and not quiet:
print "# Writing startup command to file."
cfile = open(cmd_file, 'w')
comment = " Startup command generated by mysqlserverclone.\n"
if os.name == 'posix' and cmd_file.endswith('.sh'):
cfile.write("#!/bin/sh\n")
cfile.write("#{0}".format(comment))
elif os.name == 'nt' and cmd_file.endswith('.bat'):
cfile.write("REM{0}".format(comment))
else:
cfile.write("#{0}".format(comment))
start_cmd_lst = ["{0}{1}{0} --no-defaults".format(quote_char,
mysqld_path)]
# build start command
for key, val in cmd.iteritems():
if key in options_to_quote:
val = "{0}{1}{0}".format(quote_char, val)
start_cmd_lst.append(val)
cfile.write("{0}\n".format(" ".join(start_cmd_lst)))
cfile.close()
if os.name == "nt" and verbosity >= 1:
cmd.update({"console": "--console"})
start_cmd_lst = [mysqld_path, "--no-defaults"]
sorted_keys = sorted(cmd.keys())
start_cmd_lst.extend([cmd[val] for val in sorted_keys])
if verbosity >= 1 and not quiet:
if verbosity >= 2:
print("# Startup command for new server:\n"
"{0}".format(" ".join(start_cmd_lst)))
proc = subprocess.Popen(start_cmd_lst, shell=False)
else:
proc = subprocess.Popen(start_cmd_lst, shell=False, stdout=fnull,
stderr=fnull)
# Try to connect to the new MySQL instance
if not quiet:
print "# Testing connection to new instance..."
new_sock = None
if os.name == "posix":
new_sock = socket_path
port_int = int(new_port)
conn = {
"user": "******",
"passwd": "",
"host": conn_val["host"] if conn_val is not None else "localhost",
"port": port_int,
"unix_socket": new_sock
}
server2_options = {
'conn_info': conn,
'role': "clone",
}
server2 = Server(server2_options)
i = 0
while i < start_timeout:
i += 1
time.sleep(1)
try:
server2.connect()
i = start_timeout + 1
except:
pass
finally:
if verbosity >= 1 and not quiet:
print "# trying again..."
if i == start_timeout:
raise UtilError("Unable to communicate with new instance. "
"Process id = {0}.".format(proc.pid))
elif not quiet:
print "# Success!"
# Set the root password
if root_pass:
if not quiet:
print "# Setting the root password..."
cmd = [mysqladmin_path, '--no-defaults', '-v', '-uroot']
if os.name == "posix":
cmd.append("--socket={0}".format(new_sock))
else:
cmd.append("--port={0}".format(int(new_port)))
cmd.extend(["password", root_pass])
if verbosity > 0 and not quiet:
proc = subprocess.Popen(cmd, shell=False)
else:
proc = subprocess.Popen(cmd, shell=False,
stdout=fnull, stderr=fnull)
# Wait for subprocess to finish
res = proc.wait()
if not quiet:
conn_str = "# Connection Information:\n"
conn_str += "# -uroot"
if root_pass:
conn_str += " -p%s" % root_pass
if os.name == "posix":
conn_str += " --socket=%s" % new_sock
else:
conn_str += " --port=%s" % new_port
print conn_str
print "#...done."
fnull.close()
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
def read_frm_files(file_names, options):
"""Read frm files using a spawned (bootstrapped) server.
This method reads the list of frm files by spawning a server then
copying the .frm files, changing the storage engine to memory,
issuing a SHOW CREATE command, then resetting the storage engine and
printing the resulting CREATE statement.
file_names[in] List of files to read
options[in] Options from user
Returns list - list of .frm files that cannot be read.
"""
test_port = options.get("port", None)
test_basedir = options.get("basedir", None)
test_server = options.get("server", None)
if not test_port or (not test_basedir and not test_server):
raise UtilError("Method requires basedir or server and port options.")
verbosity = int(options.get("verbosity", 0))
quiet = options.get("quiet", False)
datadir = options.get("datadir", None)
# 1) for each .frm, determine its type and db, table name
if verbosity > 1 and not quiet:
print("# Checking read access to .frm files ")
frm_files = []
for file_name in file_names:
db, table, frm_path = _get_frm_path(file_name, datadir)
if not os.access(frm_path, os.R_OK):
print("ERROR: Unable to read the file %s." % frm_path + \
"You must have read access to the .frm file.")
frm_files.append((db, table, frm_path))
# 2) Spawn the server
server, temp_datadir = _spawn_server(options)
version_str = server.get_version()
match = re.match(r'^(\d+\.\d+(\.\d+)*).*$', version_str.strip())
if match:
version = [int(x) for x in match.group(1).split('.')]
version = (version + [0])[:3] # Ensure a 3 elements list
else:
print("# WARNING: Error parsing server version %s. Cannot compare "
"version of .frm file." % version_str)
version = None
failed_reads = []
if not quiet:
print("# Reading .frm files")
try:
for frm_file in frm_files:
# 3) For each .frm file, get the CREATE statement
frm_err = _get_create_statement(server, temp_datadir, frm_file,
version, options)
if frm_err:
failed_reads.append(frm_err)
except UtilError as error:
raise UtilError(error.errmsg)
finally:
# 4) shutdown the spawned server
if verbosity > 1 and not quiet:
print("# Shutting down spawned server")
print("# Removing the temporary datadir")
if user_change_as_root(options):
try:
os.unlink(temp_datadir)
except OSError:
pass # ignore if we cannot delete
stop_running_server(server)
return failed_reads
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_python_version(min_version=PYTHON_MIN_VERSION,
max_version=PYTHON_MAX_VERSION,
raise_exception_on_fail=False,
name=None,
print_on_fail=True,
exit_on_fail=True,
return_error_msg=False):
"""Check the Python version compatibility.
By default this method uses constants to define the minimum and maximum
Python versions required. It's possible to override this by passing new
values on ``min_version`` and ``max_version`` parameters.
It will run a ``sys.exit`` or raise a ``UtilError`` if the version of
Python detected it not compatible.
min_version[in] Tuple with the minimum Python version
required (inclusive).
max_version[in] Tuple with the maximum Python version
required (exclusive).
raise_exception_on_fail[in] Boolean, it will raise a ``UtilError`` if
True and Python detected is not compatible.
name[in] String for a custom name, if not provided
will get the module name from where this
function was called.
print_on_fail[in] If True, print error else do not print
error on failure.
exit_on_fail[in] If True, issue exit() else do not exit()
on failure.
return_error_msg[in] If True, and is not compatible
returns (result, error_msg) tuple.
"""
# Only use the fields: major, minor and micro
sys_version = sys.version_info[:3]
# Test min version compatibility
is_compat = min_version <= sys_version
# Test max version compatibility if it's defined
if is_compat and max_version:
is_compat = sys_version < max_version
if not is_compat:
if not name:
# Get the utility name by finding the module
# name from where this function was called
frm = inspect.stack()[1]
mod = inspect.getmodule(frm[0])
mod_name = os.path.splitext(os.path.basename(mod.__file__))[0]
name = '%s utility' % mod_name
# Build the error message
if max_version:
max_version_error_msg = 'or higher and lower than %s' % \
'.'.join([str(el) for el in max_version])
else:
max_version_error_msg = 'or higher'
error_msg = (
'The %(name)s requires Python version %(min_version)s '
'%(max_version_error_msg)s. The version of Python detected was '
'%(sys_version)s. You may need to install or redirect the '
'execution of this utility to an environment that includes a '
'compatible Python version.') % {
'name': name,
'sys_version': '.'.join([str(el) for el in sys_version]),
'min_version': '.'.join([str(el) for el in min_version]),
'max_version_error_msg': max_version_error_msg
}
if raise_exception_on_fail:
raise UtilError(error_msg)
if print_on_fail:
print('ERROR: %s' % error_msg)
if exit_on_fail:
sys.exit(1)
if return_error_msg:
return is_compat, error_msg
return is_compat
def run(self):
quote_char = "'" if os.name == "posix" else '"'
self.server0 = self.servers.get_server(0)
cmd_str = "mysqlserverclone.py --server={0} --delete-data ".format(
self.build_connection_string(self.server0))
port1 = self.servers.get_next_port()
cmd_str = "{0} --new-port={1} --root-password=root ".format(
cmd_str, port1)
test_num = 1
comment = "Test case {0} - clone a running server".format(test_num)
self.results.append(comment + "\n")
# Create a new-dir whose size with socket file is > 107 chars
o_path_size = 108 - (len(os.getcwd()) + 22 + len(str(port1)))
full_datadir = os.path.join(
os.getcwd(), "temp_{0}_lo{1}ng".format(port1, 'o' * o_path_size))
cmd_str = ("{0}--new-data={2}{1}{2} "
"".format(cmd_str, full_datadir, quote_char))
res = self.exec_util(cmd_str, "start.txt")
with open("start.txt") as f:
for line in f:
# Don't save lines that have [Warning]
if "[Warning]" in line:
continue
self.results.append(line)
if res:
raise MUTLibError("{0}: failed".format(comment))
self.new_server = self.check_connect(port1)
# Get basedir
rows = self.server0.exec_query("SHOW VARIABLES LIKE 'basedir'")
if not rows:
raise UtilError("Unable to determine basedir of running server.")
basedir = os.path.normpath(rows[0][1])
port2 = int(self.servers.get_next_port())
cmd_str = ("mysqlserverclone.py --root-password=root --delete-data "
"--new-port={0} --basedir={2}{1}{2} "
"".format(port2, basedir, quote_char))
test_num += 1
comment = ("Test case {0} - clone a server from "
"basedir".format(test_num))
self.results.append(comment + "\n")
full_datadir = os.path.join(os.getcwd(), "temp_{0}".format(port2))
cmd_str = ("{0} --new-data={2}{1}{2} "
"".format(cmd_str, full_datadir, quote_char))
res = self.exec_util(cmd_str, "start.txt")
with open("start.txt") as f:
for line in f:
# Don't save lines that have [Warning]
if "[Warning]" in line:
continue
self.results.append(line)
if res:
raise MUTLibError("{0}: failed".format(comment))
server = self.check_connect(port2, "cloned_server_basedir")
self.servers.stop_server(server)
self.servers.clear_last_port()
# Test clone server option mysqld with SSL and basedir option
# Also Used for next test, clone a running server with SSL
test_num += 1
comment = ("Test case {0} - clone a server from "
"basedir with SSL".format(test_num))
self.results.append(comment + "\n")
cmd_str = '{0} --mysqld="{1}"'.format(cmd_str, ssl_server_opts())
res = self.exec_util(cmd_str, "start.txt")
with open("start.txt") as f:
for line in f:
# Don't save lines that have [Warning]
if "[Warning]" in line:
continue
self.results.append(line)
if res:
raise MUTLibError("{0}: failed".format(comment))
ssl_server = self.check_connect(port2, "cloned_server_basedir")
ssl_server.exec_query(CREATE_SSL_USER_2)
test_num += 1
comment = ("Test case {0} - clone a running server with SSL "
"and using spaces in the path".format(test_num))
port3 = int(self.servers.get_next_port())
self.results.append(comment + "\n")
full_datadir = os.path.join(os.getcwd(), "temp with spaces "
"{0}".format(port3))
cmd_str = ("mysqlserverclone.py --server={0} --delete-data ").format(
self.build_custom_connection_string(ssl_server, "root_ssl",
"root_ssl"))
cmd_str = ("{0} --new-data={2}{1}{2} "
"".format(cmd_str, full_datadir, quote_char))
cmd_str = ('{0} {1} --new-port={2} --root-password=root --mysqld='
'"{3}"').format(cmd_str,
SSL_OPTS_UTIL.format(STD_DATA_PATH), port3,
ssl_server_opts())
res = self.exec_util(cmd_str, "start.txt")
with open("start.txt") as f:
for line in f:
# Don't save lines that have [Warning]
if "[Warning]" in line:
continue
self.results.append(line)
if res:
raise MUTLibError("{0}: failed".format(comment))
new_server = self.check_connect(port3)
new_server.exec_query(CREATE_SSL_USER_2)
conn_ssl = {
"user": "******",
"passwd": "root_ssl",
"ssl_cert": SSL_CERT.format(STD_DATA_PATH),
"ssl_ca": SSL_CA.format(STD_DATA_PATH),
"ssl_key": SSL_KEY.format(STD_DATA_PATH),
}
new_server_ssl = self.check_connect(port3, conn_dict=conn_ssl)
self.servers.stop_server(ssl_server)
self.servers.clear_last_port()
self.servers.stop_server(new_server_ssl)
self.servers.clear_last_port()
self.replace_result(
"# Cloning the MySQL server running on",
"# Cloning the MySQL server running on xxxxx-"
"xxxxx.\n")
self.replace_result("# -uroot", "# -uroot [...]\n")
self.replace_result("# Cloning the MySQL server located at",
"# Cloning the MySQL server located at XXXX\n")
# Since it may or may not appear, depending on size of path or Windows,
# remove it
self.remove_result("# WARNING: The socket file path '")
return True
def clone_server(conn_val, options):
"""Clone an existing server
This method creates a new instance of a running server using a datadir
set to the new_data parametr, with a port set to new_port, server_id
set to new_id and a root password of root_pass. You can also specify
additional parameters for the mysqld command line as well as turn on
verbosity mode to display more diagnostic information during the clone
process.
The method will build a new base database installation from the .sql
files used to construct a new installation. Once the database is
created, the server will be started.
dest_val[in] a dictionary containing connection information
including:
(user, password, host, port, socket)
options[in] dictionary of options:
new_data[in] An existing path to create the new database and use
as datadir for new instance
(default = None)
new_port[in] Port number for new instance
(default = 3307)
new_id[in] Server_id for new instance
(default = 2)
root_pass[in] Password for root user on new instance (optional)
mysqld_options[in] Additional command line options for mysqld
verbosity[in] Print additional information during operation
(default is 0)
quiet[in] If True, do not print messages.
(default is False)
cmd_file[in] file name to write startup command
"""
from mysql.utilities.common.server import Server
from mysql.utilities.exception import UtilError
from mysql.utilities.common.tools import get_tool_path
new_data = options.get('new_data', None)
new_port = options.get('new_port', '3307')
root_pass = options.get('root_pass', None)
verbosity = options.get('verbosity', 0)
quiet = options.get('quiet', False)
cmd_file = options.get('cmd_file', None)
# Clone running server
if conn_val is not None:
# Try to connect to the MySQL database server.
server1_options = {
'conn_info': conn_val,
'role': "source",
}
server1 = Server(server1_options)
server1.connect()
if not quiet:
print "# Cloning the MySQL server running on %s." % conn_val["host"]
basedir = ""
# Get basedir
rows = server1.exec_query("SHOW VARIABLES LIKE 'basedir'")
if not rows:
raise UtilError("Unable to determine basedir of running server.")
basedir = rows[0][1]
# Cloning downed or offline server
else:
basedir = options.get("basedir", None)
if not quiet:
print "# Cloning the MySQL server located at %s." % basedir
# If datadir exists, has data, and user said it was Ok, delete it
if os.path.exists(new_data) and options.get("delete", False) and \
os.listdir(new_data):
shutil.rmtree(new_data, True)
# Create new data directory if it does not exist
if not os.path.exists(new_data):
if not quiet:
print "# Creating new data directory..."
try:
res = os.mkdir(new_data)
except:
raise UtilError("Unable to create directory '%s'" % new_data)
if not quiet:
print "# Configuring new instance..."
print "# Locating mysql tools..."
mysqld_path = get_tool_path(basedir, "mysqld")
mysqladmin_path = get_tool_path(basedir, "mysqladmin")
mysql_basedir = get_tool_path(basedir, "share/english/errgmsg.sys", False,
False)
mysql_basedir = basedir
if os.path.exists(os.path.join(basedir, "local/mysql/share/")):
mysql_basedir = os.path.join(mysql_basedir, "local/mysql/")
# for source trees
elif os.path.exists(os.path.join(basedir, "/sql/share/english/")):
mysql_basedir = os.path.join(mysql_basedir, "/sql/")
system_tables = get_tool_path(basedir, "mysql_system_tables.sql", False)
system_tables_data = get_tool_path(basedir, "mysql_system_tables_data.sql",
False)
test_data_timezone = get_tool_path(basedir, "mysql_test_data_timezone.sql",
False)
help_data = get_tool_path(basedir, "fill_help_tables.sql", False)
if verbosity >= 3 and not quiet:
print "# Location of files:"
locations = [
("mysqld", mysqld_path),
("mysqladmin", mysqladmin_path),
("mysql_system_tables.sql", system_tables),
("mysql_system_tables_data.sql", system_tables_data),
("mysql_test_data_timezone.sql", test_data_timezone),
("fill_help_tables.sql", help_data),
]
if cmd_file is not None:
locations.append(("write startup command to", cmd_file))
for location in locations:
print "# % 28s: %s" % location
# Create the new mysql data with mysql_import_db-like process
if not quiet:
print "# Setting up empty database and mysql tables..."
# Create the bootstrap file
f_boot = open("bootstrap.sql", 'w')
f_boot.write("CREATE DATABASE mysql;\n")
f_boot.write("USE mysql;\n")
f_boot.writelines(open(system_tables).readlines())
f_boot.writelines(open(system_tables_data).readlines())
f_boot.writelines(open(test_data_timezone).readlines())
f_boot.writelines(open(help_data).readlines())
f_boot.close()
# Bootstap to setup mysql tables
fnull = open(os.devnull, 'w')
cmd = mysqld_path + " --no-defaults --bootstrap " + \
" --datadir=%s --basedir=%s " % (new_data, mysql_basedir) + \
" < bootstrap.sql"
proc = None
if verbosity >= 1 and not quiet:
proc = subprocess.Popen(cmd, shell=True)
else:
proc = subprocess.Popen(cmd, shell=True, stdout=fnull, stderr=fnull)
# Wait for subprocess to finish
res = proc.wait()
# Kill subprocess just in case it didn't finish - Ok if proc doesn't exist
if int(res) != 0:
if os.name == "posix":
try:
os.kill(proc.pid, subprocess.signal.SIGTERM)
except OSError:
pass
else:
try:
retval = subprocess.Popen("taskkill /F /T /PID %i" % proc.pid,
shell=True)
except:
pass
# Drop the bootstrap file
if os.path.isfile("bootstrap.sql"):
os.unlink("bootstrap.sql")
# Start the instance
if not quiet:
print "# Starting new instance of the server..."
cmd = mysqld_path + " --no-defaults "
if options.get('mysqld_options', None):
cmd += options.get('mysqld_options') + " --user=root "
cmd += "--datadir=%s " % (new_data)
cmd += "--tmpdir=%s " % (new_data)
cmd += "--pid-file=%s " % os.path.join(new_data, "clone.pid")
cmd += "--port=%s " % (new_port)
cmd += "--server-id=%s " % (options.get('new_id', 2))
cmd += "--basedir=%s " % (mysql_basedir)
cmd += "--socket=%s/mysql.sock " % (new_data)
# Write startup command if specified
if cmd_file is not None:
if verbosity >= 0 and not quiet:
print "# Writing startup command to file."
cfile = open(cmd_file, 'w')
if os.name == 'posix' and cmd_file.endswith('.sh'):
cfile.write("#!/bin/sh\n")
cfile.write("# Startup command generated by mysqlserverclone.\n")
cfile.write("%s\n" % cmd)
cfile.close()
if verbosity >= 1 and not quiet:
if verbosity >= 2:
print "# Startup command for new server:\n%s" % cmd
proc = subprocess.Popen(cmd, shell=True)
else:
proc = subprocess.Popen(cmd, shell=True, stdout=fnull, stderr=fnull)
# Try to connect to the new MySQL instance
if not quiet:
print "# Testing connection to new instance..."
new_sock = None
port_int = None
if os.name == "posix":
new_sock = os.path.join(new_data, "mysql.sock")
port_int = int(new_port)
conn = {
"user": "******",
"passwd": "",
"host": conn_val["host"] if conn_val is not None else "localhost",
"port": port_int,
"unix_socket": new_sock
}
server2_options = {
'conn_info': conn,
'role': "clone",
}
server2 = Server(server2_options)
stop = 10 # stop after 10 attempts
i = 0
while i < stop:
i += 1
time.sleep(1)
try:
server2.connect()
i = stop + 1
except:
pass
finally:
if verbosity >= 1 and not quiet:
print "# trying again..."
if i == stop:
raise UtilError("Unable to communicate with new instance.")
elif not quiet:
print "# Success!"
# Set the root password
if root_pass:
if not quiet:
print "# Setting the root password..."
if os.name == "posix":
cmd = mysqladmin_path + " --no-defaults -v -uroot " + \
"--socket=%s password %s " % (new_sock, root_pass)
else:
cmd = mysqladmin_path + " --no-defaults -v -uroot " + \
"password %s --port=%s" % (root_pass, int(new_port))
if verbosity > 0 and not quiet:
proc = subprocess.Popen(cmd, shell=True)
else:
proc = subprocess.Popen(cmd,
shell=True,
stdout=fnull,
stderr=fnull)
# Wait for subprocess to finish
res = proc.wait()
if not quiet:
conn_str = "# Connection Information:\n"
conn_str += "# -uroot"
if root_pass:
conn_str += " -p%s" % root_pass
if os.name == "posix":
conn_str += " --socket=%s" % new_sock
else:
conn_str += " --port=%s" % new_port
print conn_str
print "#...done."
fnull.close()
def print_graph(self,
topology_list=[],
masters_found=[],
level=0,
preamble=""):
"""Prints a graph of the topology map to standard output.
This method traverses a list of the topology and prints a graph. The
method is designed to be recursive traversing the list to print the
slaves for each master in the topology. It will also detect a circular
replication segment and indicate it on the graph.
topology_list[in] a list in the form (master, slave) of server
masters_found[in] a list of all servers in master roles - used to
detect a circular replication topology. Initially,
this is an empty list as the master detection must
occur as the toplogy is traversed.
level[in] the level of indentation - increases with each
set of slaves found in topology
preamble[in] prefix calculated during recursion to indent text
"""
# if first iteration, use the topology list generated earlier
if topology_list == []:
if self.topology == []:
# topology not generated yet
raise UtilError("You must first generate the topology.")
topology_list = self.topology
# Detect if we are looking at a sublist or not. Get sublist.
if len(topology_list) == 1:
topology_list = topology_list[0]
master = topology_list[0]
# Save the master for circular replication identification
masters_found.append(master)
# For each slave, print the graph link
slaves = topology_list[1]
stop = len(slaves)
if stop > 0:
# Level 0 is always the first master in the topology.
if level == 0:
print "%s (MASTER)" % master
for i in range(0, stop):
if len(slaves[i]) == 1:
slave = slaves[i][0]
else:
slave = slaves[i]
new_preamble = preamble + " "
print new_preamble + "|"
role = "(SLAVE"
if not slave[1] == [] or slave[0] in masters_found:
role += " + MASTER"
role += ")"
print "%s+--- %s" % (new_preamble, slave[0]),
if (slave[0] in masters_found):
print "<-->",
else:
print "-",
print "%s" % role
if not slave[1] == []:
if i < stop - 1:
new_preamble += "|"
else:
new_preamble += " "
self.print_graph(slave, masters_found, level + 1,
new_preamble)
