def show_file_statistics(file_name, wild=False, out_format="GRID"): """Show file statistics for file name specified file_name[in] target file name and path wild[in] if True, get file statistics for all files with prefix of file_name. Default is False out_format[in] output format to print file statistics. Default is GRID. """ def _get_file_stats(path, file_name): """Return file stats """ stats = os.stat(os.path.join(path, file_name)) return ((file_name, stats.st_size, time.ctime(stats.st_ctime), time.ctime(stats.st_mtime))) columns = ["File", "Size", "Created", "Last Modified"] rows = [] path, filename = os.path.split(file_name) if wild: for _, _, files in os.walk(path): for f in files: if f.startswith(filename): rows.append(_get_file_stats(path, f)) else: rows.append(_get_file_stats(path, filename)) print_list(sys.stdout, out_format, columns, rows)
def show_database_usage(server, datadir, dblist, options): """Show database usage. Display a list of databases and their disk space usage. The method accepts a list of databases to list or None or [] for all databases. server[in] Connected server to operate against datadir[in] The datadir for the server dblist[in] List of databases options[in] Required options for operation: format, no_headers, verbosity, have_read, include_empty returns True or 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) include_empty = options.get("do_empty", True) do_all = options.get("do_all", True) quiet = options.get("quiet", False) if verbosity is None: verbosity = 0 locale.setlocale(locale.LC_ALL, '') # Check to see if we're doing all databases. if len(dblist) > 0: include_list = "(" stop = len(dblist) for i in range(0,stop): include_list += "'%s'" % dblist[i] if i < stop-1: include_list += ", " include_list += ")" where_clause = "WHERE table_schema IN %s" % include_list where_clause += " AND table_schema != 'INFORMATION_SCHEMA'" else: where_clause = "WHERE table_schema != 'INFORMATION_SCHEMA'" res = server.exec_query(_QUERY_DBSIZE % where_clause) # Get list of databases with sizes and formatted when necessary columns, rows, db_total = _build_db_list(server, res, dblist, datadir, format == "grid", have_read, verbosity, include_empty or do_all) if not quiet: print "# Database totals:" print_list(sys.stdout, format, columns, rows, no_headers) if not quiet: _print_size("\nTotal database disk usage = ", db_total) print return True
def execute(self, connections, **kwrds): """Execute the search for processes, queries, or connections This method searches for processes, queriers, or connections to either kill or display the matches for one or more servers. connections[in] list of connection parameters kwrds[in] dictionary of options output file stream to display information default = sys.stdout connector connector to use default = mysql.connector format format for display default = GRID """ output = kwrds.get('output', sys.stdout) connector = kwrds.get('connector', mysql.connector) fmt = kwrds.get('format', "grid") charset = kwrds.get('charset', None) headers = ("Connection", "Id", "User", "Host", "Db", "Command", "Time", "State", "Info") entries = [] # Build SQL statement for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info,) raise FormatError(msg) if charset: conn['charset'] = charset info = conn connection = connector.connect(**info) if not charset: # If no charset provided, get it from the # "character_set_client" server variable. cursor = connection.cursor() cursor.execute("SHOW VARIABLES LIKE 'character_set_client'") res = cursor.fetchall() connection.set_charset_collation(charset=str(res[0][1])) cursor.close() cursor = connection.cursor() cursor.execute(self.__select) for row in cursor: if KILL_QUERY in self.__actions: cursor.execute("KILL {0}".format(row[0])) if KILL_CONNECTION in self.__actions: cursor.execute("KILL {0}".format(row[0])) if PRINT_PROCESS in self.__actions: entries.append(tuple([_spec(info)] + list(row))) # If output is None, nothing is printed if len(entries) > 0 and output: entries.sort(key=lambda fifth: fifth[5]) print_list(output, fmt, headers, entries) elif PRINT_PROCESS in self.__actions: raise EmptyResultError("No matches found")
def _get_formatted_rows(rows, table, format="GRID"): """Get a printable representation of the data rows This method generates a formatted view of the rows from a table. The output format can be in one of GRID, CSV, TAB, or VERTICAL. This output is returned as a list of strings for use in storing the output for later presentation. rows[in] missing rows table[in] a Table instance of the table obj1_str[in] full table name for base table obj2_str[in] full table name for other table format[in] format to print Returns list of formatted rows """ import os import tempfile from mysql.utilities.common.format import print_list result_rows = [] outfile = tempfile.TemporaryFile() print_list(outfile, format, table.get_col_names(), rows) outfile.seek(0) for line in outfile.readlines(): result_rows.append(line.strip("\n")) return result_rows
def show_users(src_val, verbosity, fmt, dump=False): """Show all users except root and anonymous users on the server. src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) verbosty[in] level of information to display fmt[in] format of output dump[in] if True, dump the grants for all users default = False """ conn_options = {"version": "5.1.0"} servers = connect_servers(src_val, None, conn_options) source = servers[0] if verbosity <= 1: _QUERY = """ SELECT user, host FROM mysql.user WHERE user.user != '' """ cols = ("user", "host") else: _QUERY = """ SELECT user.user, user.host, db FROM mysql.user LEFT JOIN mysql.db ON user.user = db.user AND user.host = db.host WHERE user.user != '' """ cols = ("user", "host", "database") users = source.exec_query(_QUERY) print "# All Users:" print_list(sys.stdout, fmt, cols, users) if dump: for user in users: _show_user_grants(source, None, "'%s'@'%s'" % user[0:2], verbosity)
def show_logfile_usage(server, options): """Show log file disk space usage. Display log file information if logs are 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 """ 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) quiet = options.get("quiet", False) if not quiet: print "# Log information." total = 0 _LOG_NAMES = [ ('general_log', '_file'), ('slow_query_log', '_file'), ('log_error', '') ] logs = [] for log_name in _LOG_NAMES: log, size = _get_log_information(server, log_name[0], log_name[1]) if log is not None: logs.append((log, size)) total += size fmt_logs = [] columns = ['log_name', 'size'] if len(logs) > 0: if format == 'grid': max_col = _get_formatted_max_width(logs, columns, 1) if max_col < len('size'): max_col = len('size') size = "{0:>{1}}".format('size', max_col) columns = ['log_name',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 print_list(sys.stdout, format, columns, fmt_logs, no_headers) if not quiet: _print_size("\nTotal size of logs = ", total) print return True
def print_charsets(self): """Print the character set list """ print_list(sys.stdout, self.format, ["id", "character_set_name", "collation_name", "maxlen", "is_default"], self.charset_map) print len(self.charset_map), "rows in set."
def execute(self, connections, output=sys.stdout, connector=mysql.connector, **kwrds): """Execute the search for objects This method searches for objects that match a search criteria for one or more servers. connections[in] list of connection parameters output[in] file stream to display information default = sys.stdout connector[in] connector to use default = mysql.connector kwrds[in] dictionary of options format format for display default = GRID """ fmt = kwrds.get('format', "grid") charset = kwrds.get('charset', None) ssl_opts = kwrds.get('ssl_opts', {}) entries = [] for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info,) raise FormatError(msg) if charset: conn['charset'] = charset info = conn conn['host'] = conn['host'].replace("[", "") conn['host'] = conn['host'].replace("]", "") if connector == mysql.connector: set_ssl_opts_in_connection_info(ssl_opts, info) connection = connector.connect(**info) if not charset: # If no charset provided, get it from the # "character_set_client" server variable. cursor = connection.cursor() cursor.execute("SHOW VARIABLES LIKE 'character_set_client'") res = cursor.fetchall() connection.set_charset_collation(charset=str(res[0][1])) cursor.close() cursor = connection.cursor() cursor.execute(self.__sql) entries.extend([tuple([_spec(info)] + list(row)) for row in cursor]) headers = ["Connection"] headers.extend(col[0].title() for col in cursor.description) if len(entries) > 0 and output: print_list(output, fmt, headers, entries) else: msg = "Nothing matches '%s' in any %s" % \ (self.__pattern, _join_words(self.__types, conjunction="or")) raise EmptyResultError(msg)
def show_logfile_usage(server, options): """Show log file disk space usage. Display log file information if logs are 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) quiet = options.get("quiet", False) if not quiet: print "# Log information." total = 0 _LOG_NAMES = [("general_log", "_file"), ("slow_query_log", "_file"), ("log_error", "")] logs = [] for log_name in _LOG_NAMES: (log, size) = _get_log_information(server, log_name[0], log_name[1], is_remote) if log is not None: logs.append((log, size)) total += size fmt_logs = [] columns = ["log_name", "size"] if len(logs) > 0: if fmt == "grid": max_col = _get_formatted_max_width(logs, columns, 1) if max_col < len("size"): max_col = len("size") size = "{0:>{1}}".format("size", max_col) columns = ["log_name", 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 print_list(sys.stdout, fmt, columns, fmt_logs, no_headers) if not quiet: _print_size("\nTotal size of logs = ", total) print return True
def show_topology(master_vals, options=None): """Show the slaves/topology map for a master. This method find the slaves attached to a server if it is a master. It can also discover the replication topology if the recurse option is True (default = False). It prints a tabular list of the master(s) and slaves found. If the show_list option is True, it will also print a list of the output (default = False). master_vals[in] Master connection in form user:passwd@host:port:socket or login-path:port:socket. options[in] dictionary of options recurse If True, check each slave found for additional slaves Default = False prompt_user If True, prompt user if slave connection fails with master connection parameters Default = False num_retries Number of times to retry a failed connection attempt Default = 0 quiet if True, print only the data Default = False format Format of list Default = Grid width width of report Default = 75 max_depth maximum depth of recursive search Default = None """ if options is None: options = {} topo = TopologyMap(master_vals, options) topo.generate_topology_map(options.get('max_depth', None)) if not options.get("quiet", False) and topo.depth(): print "\n# Replication Topology Graph" if not topo.slaves_found(): print "No slaves found." topo.print_graph() print if options.get("show_list", False): from mysql.utilities.common.format import print_list # make a list from the topology topology_list = topo.get_topology_map() print_list(sys.stdout, options.get("format", "GRID"), ["Master", "Slave"], topology_list, False, True)
def show_options(self): """ Show all audit log variables. """ server = Server({'conn_info': self.options.get("server_vals", None)}) server.connect() rows = server.show_server_variable("audit%") server.disconnect() if rows: print "#\n# Audit Log Variables and Options\n#" print_list(sys.stdout, "GRID", ['Variable_name', 'Value'], rows) print else: raise UtilError("No audit log variables found.")
def _log_master_status(self, master): """Logs the master information. master[in] Master server instance. This method logs the master information from SHOW MASTER STATUS. """ # If no master present, don't print anything. if master is None: return print("#") self._report("# {0}:".format("Current Master Information"), logging.INFO) try: status = master.get_status()[0] except UtilError: msg = "Cannot get master status" self._report(msg, logging.ERROR, False) raise UtilRplError(msg) cols = ("Binary Log File", "Position", "Binlog_Do_DB", "Binlog_Ignore_DB") rows = (status[0] or "N/A", status[1] or "N/A", status[2] or "N/A", status[3] or "N/A") print_list(sys.stdout, self.format, cols, [rows]) self._report("# {0}".format( ", ".join(["{0}: {1}".format(*item) for item in zip(cols, rows)]), ), logging.INFO, False) # Display gtid executed set master_gtids = [] for gtid in status[4].split("\n"): if gtid: # Add each GTID to a tuple to match the required format to # print the full GRID list correctly. master_gtids.append((gtid.strip(","),)) try: if len(master_gtids) > 1: gtid_executed = "{0}[...]".format(master_gtids[0][0]) else: gtid_executed = master_gtids[0][0] except IndexError: gtid_executed = "None" self._report("# GTID Executed Set: {0}".format(gtid_executed), logging.INFO)
def show_topology(master_vals, options={}): """Show the slaves/topology map for a master. This method find the slaves attached to a server if it is a master. It can also discover the replication topology if the recurse option is True (default = False). It prints a tabular list of the master(s) and slaves found. If the show_list option is True, it will also print a list of the output (default = False). master_vals[in] Master connection in form user:passwd@host:port:socket or login-path:port:socket. options[in] dictionary of options recurse If True, check each slave found for additional slaves Default = False prompt_user If True, prompt user if slave connection fails with master connection parameters Default = False num_retries Number of times to retry a failed connection attempt Default = 0 quiet if True, print only the data Default = False format Format of list Default = Grid width width of report Default = 75 max_depth maximum depth of recursive search Default = None """ from mysql.utilities.common.topology_map import TopologyMap topo = TopologyMap(master_vals, options) topo.generate_topology_map(options.get('max_depth', None)) if not options.get("quiet", False) and topo.depth(): print "\n# Replication Topology Graph" if not topo.slaves_found(): print "No slaves found." topo.print_graph() print if options.get("show_list", False): from mysql.utilities.common.format import print_list # make a list from the topology topology_list = topo.get_topology_map() print_list(sys.stdout, options.get("format", "GRID"), ["Master", "Slave"], topology_list, False, True)
def _log_data(self, title, labels, data, print_format=True): """Helper method to log data. title[in] Title to log. labels[in] List of labels. data[in] List of data rows. """ self._report("# {0}".format(title), logging.INFO) for row in data: msg = ", ".join( ["{0}: {1}".format(*col) for col in zip(labels, row)]) self._report("# {0}".format(msg), logging.INFO, False) if print_format: print_list(sys.stdout, self.format, labels, data)
def _log_data(self, title, labels, data, print_format=True): """Helper method to log data. title[in] Title to log. labels[in] List of labels. data[in] List of data rows. """ self._report("# {0}".format(title), logging.INFO) for row in data: msg = ", ".join( ["{0}: {1}".format(*col) for col in zip(labels, row)] ) self._report("# {0}".format(msg), logging.INFO, False) if print_format: print_list(sys.stdout, self.format, labels, data)
def __print_index_list(indexes, fmt, no_header=False): """Print the list of indexes indexes[in] list of indexes to print fmt[in] format out output = sql, table, tab, csv no_header[in] (optional) if True, do not print the header """ if fmt == "sql": for index in indexes: index.print_index_sql() else: cols = ("database", "table", "name", "type", "columns") rows = [] for index in indexes: rows.append(index.get_row()) print_list(sys.stdout, fmt, cols, rows, no_header)
def execute(self, connections, **kwrds): """Execute the search for processes, queries, or connections This method searches for processes, queriers, or connections to either kill or display the matches for one or more servers. connections[in] list of connection parameters kwrds[in] dictionary of options output file stream to display information default = sys.stdout connector connector to use default = mysql.connector format format for display default = GRID """ output = kwrds.get('output', sys.stdout) connector = kwrds.get('connector', mysql.connector) format = kwrds.get('format', "grid") headers = ("Connection", "Id", "User", "Host", "Db", "Command", "Time", "State", "Info") entries = [] # Build SQL statement for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info, ) raise FormatError(msg) info = conn connection = connector.connect(**info) cursor = connection.cursor() cursor.execute(self.__select) for row in cursor: if KILL_QUERY in self.__actions: cursor.execute("KILL {0}".format(row[0])) if KILL_CONNECTION in self.__actions: cursor.execute("KILL {0}".format(row[0])) if PRINT_PROCESS in self.__actions: entries.append(tuple([_spec(info)] + list(row))) # If output is None, nothing is printed if len(entries) > 0 and output: entries.sort(key=lambda fifth: fifth[5]) print_list(output, format, headers, entries) elif PRINT_PROCESS in self.__actions: raise EmptyResultError("No matches found")
def _print_list(self, refresh=True, comment=None): """Display the list information This method displays the list information using the start_list and end_list member variables to control the view of the data. This permits users to scroll through the data should it be longer than the space permitted on the screen. """ # If no data to print, exit if self.list_data is None: return if refresh: self.clear() self._print_header() self._print_master_status() # Print list name if comment is None: comment = self.comment print comment self.rows_printed += 1 # Print the list in the remaining space footer_len = 2 remaining_rows = self.max_rows - self.rows_printed - 4 - footer_len if len(self.list_data[1][self.start_list:self.end_list]) > \ remaining_rows: rows = self.list_data[1][self.start_list:self.start_list + remaining_rows] self.end_list = self.start_list + remaining_rows self.scroll_on = True else: if len(self.list_data[1]) == self.end_list and \ self.start_list == 0: self.scroll_on = False rows = self.list_data[1][self.start_list:self.end_list] if len(rows) > 0: self.scroll_size = len(rows) print_list(sys.stdout, 'GRID', self.list_data[0], rows) self.rows_printed += self.scroll_size + 4 else: print "0 Rows Found." self.rows_printed += 1 if refresh: self._print_footer(self.scroll_on)
def show_statistics(self): """Display statistical information about audit log including: - size, date, etc. - Audit log entries """ out_format = self.options.get("format", "GRID") log_name = self.options.get("log_name", None) # Print file statistics: print "#\n# Audit Log File Statistics:\n#" show_file_statistics(log_name, False, out_format) # Print audit log 'AUDIT' entries print "\n#\n# Audit Log Startup Entries:\n#\n" cols, rows = convert_dictionary_list(self.log.header_rows) # Note: No need to sort rows, retrieved with the same order # as read (i.e., sorted by timestamp) print_list(sys.stdout, out_format, cols, rows)
def _show_health(self): """Run a command on a list of slaves. This method will display the replication health of the topology. This includes the following for each server. - host : host name - port : connection port - role : "MASTER" or "SLAVE" - state : UP = connected, WARN = cannot connect but can ping, DOWN = cannot connect nor ping - gtid : ON = gtid supported and turned on, OFF = supported but not enabled, NO = not supported - rpl_health : (master) binlog enabled, (slave) IO tread is running, SQL thread is running, no errors, slave delay < max_delay, read log pos + max_position < master's log position Note: Will show 'ERROR' if there are multiple errors encountered otherwise will display the health check that failed. If verbosity is set, it will show the following additional information. (master) - server version, binary log file, position (slaves) - server version, master's binary log file, master's log position, IO_Thread, SQL_Thread, Secs_Behind, Remaining_Delay, IO_Error_Num, IO_Error """ from mysql.utilities.common.format import print_list format = self.options.get("format", "grid") quiet = self.options.get("quiet", False) cols, rows = self.topology.get_health() if not quiet: print "#" print "# Replication Topology Health:" # Print health report print_list(sys.stdout, format, cols, rows) return
def execute(self, connections, output=sys.stdout, connector=mysql.connector, **kwrds): """Execute the search for objects This method searches for objects that match a search criteria for one or more servers. connections[in] list of connection parameters output[in] file stream to display information default = sys.stdout connector[in] connector to use default = mysql.connector kwrds[in] dictionary of options format format for display default = GRID """ from mysql.utilities.exception import FormatError, EmptyResultError format = kwrds.get('format', "grid") entries = [] for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info, ) raise FormatError(msg) info = conn connection = connector.connect(**info) cursor = connection.cursor() cursor.execute(self.__sql) entries.extend( [tuple([_spec(info)] + list(row)) for row in cursor]) headers = ["Connection"] headers.extend(col[0].title() for col in cursor.description) if len(entries) > 0 and output: print_list(output, format, headers, entries) else: msg = "Nothing matches '%s' in any %s" % ( self.__pattern, _join_words(self.__types, conjunction="or")) raise EmptyResultError(msg)
def show_users(src_val, verbosity, format, dump=False): """Show all users except root and anonymous users on the server. src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) verbosty[in] level of information to display format[in] format of output dump[in] if True, dump the grants for all users default = False """ from mysql.utilities.common.server import connect_servers from mysql.utilities.common.format import print_list conn_options = { 'version': "5.1.0", } servers = connect_servers(src_val, None, conn_options) source = servers[0] if verbosity <= 1: _QUERY = """ SELECT user, host FROM mysql.user WHERE user.user != '' """ cols = ("user", "host") else: _QUERY = """ SELECT user.user, user.host, db FROM mysql.user LEFT JOIN mysql.db ON user.user = db.user AND user.host = db.host WHERE user.user != '' """ cols = ("user", "host", "database") users = source.exec_query(_QUERY) print "# All Users:" print_list(sys.stdout, format, cols, users) if dump: for user in users: _show_user_grants(source, None, "'%s'@'%s'" % user[0:2], verbosity)
def execute(self, connections, output=sys.stdout, connector=mysql.connector, **kwrds): """Execute the search for objects This method searches for objects that match a search criteria for one or more servers. connections[in] list of connection parameters output[in] file stream to display information default = sys.stdout connector[in] connector to use default = mysql.connector kwrds[in] dictionary of options format format for display default = GRID """ from mysql.utilities.exception import FormatError, EmptyResultError format = kwrds.get('format', "grid") entries = [] for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info,) raise FormatError(msg) info = conn connection = connector.connect(**info) cursor = connection.cursor() cursor.execute(self.__sql) entries.extend([tuple([_spec(info)] + list(row)) for row in cursor]) headers = ["Connection"] headers.extend(col[0].title() for col in cursor.description) if len(entries) > 0 and output: print_list(output, format, headers, entries) else: msg = "Nothing matches '%s' in any %s" % (self.__pattern, _join_words(self.__types, conjunction="or")) raise EmptyResultError(msg)
def output_formatted_log(self): """Output the parsed log entries according to the specified format. Print the entries resulting from the parsing process to the standard output in the specified format. If no entries are found (i.e., none match the defined search criterion) a notification message is print. """ log_rows = self.log.retrieve_rows() if log_rows: out_format = self.options.get("format", "GRID") if out_format == 'raw': for row in log_rows: sys.stdout.write(row) else: # Convert the results to the appropriate format cols, rows = convert_dictionary_list(log_rows) # Note: No need to sort rows, retrieved with the same order # as read (i.e., sorted by timestamp) print_list(sys.stdout, out_format, cols, rows) else: # Print message notifying that no entry was found no_entry_msg = "#\n# No entry found!\n#" print no_entry_msg
def _print_logs(logs, total, options): """Display list of log files. logs[in] List of log rows; total[in] Total logs size; options[in] Dictionary with the options used to print the log files, namely: format, no_headers and quiet. """ out_format = options.get("format", "grid") no_headers = options.get("no_headers", False) log_type = options.get("log_type", "binary log") quiet = options.get("quiet", False) columns = ["log_file"] fmt_logs = [] if out_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, out_format, columns, fmt_logs, no_headers) if not quiet: _print_size("\nTotal size of {0}s = ".format(log_type), total) print
def _print_logs(logs, total, options): """Display list of log files. logs[in] List of log rows; total[in] Total logs size; options[in] Dictionary with the options used to print the log files, namely: format, no_headers and quiet. """ out_format = options.get("format", "grid") no_headers = options.get("no_headers", False) log_type = options.get("log_type", "binary log") quiet = options.get("quiet", False) columns = ['log_file'] fmt_logs = [] if out_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, out_format, columns, fmt_logs, no_headers) if not quiet: _print_size("\nTotal size of {0}s = ".format(log_type), total) print
def show_users(src_val, verbosity, format): """Show all users except root and anonymous users on the server. src_val[in] a dictionary containing connection information for the source including: (user, password, host, port, socket) verbosty[in] level of information to display format[in] format of output """ from mysql.utilities.common.server import connect_servers from mysql.utilities.common.format import print_list conn_options = { 'version' : "5.1.0", } servers = connect_servers(src_val, None, conn_options) source = servers[0] if verbosity <= 1: _QUERY = """ SELECT user, host FROM mysql.user WHERE user != 'root' and user != '' """ cols = ("user", "host") else: _QUERY = """ SELECT user.user, user.host, db FROM mysql.user LEFT JOIN mysql.db ON user.user = db.user AND user.host = db.host WHERE user.user != 'root' and user.user != '' """ cols = ("user", "host", "database") users = source.exec_query(_QUERY) print_list(sys.stdout, format, cols, users)
def _get_formatted_rows(rows, table, fmt='GRID'): """Get a printable representation of the data rows This method generates a formatted view of the rows from a table. The output format can be in one of GRID, CSV, TAB, or VERTICAL. This output is returned as a list of strings for use in storing the output for later presentation. rows[in] missing rows table[in] a Table instance of the table obj1_str[in] full table name for base table obj2_str[in] full table name for other table fmt[in] format to print Returns list of formatted rows """ result_rows = [] outfile = tempfile.TemporaryFile() print_list(outfile, fmt, table.get_col_names(), rows) outfile.seek(0) for line in outfile.readlines(): result_rows.append(line.strip('\n')) return result_rows
def _show_gtid_data(self): """Display the GTID lists from the servers. This method displays the three GTID lists for all of the servers. Each server is listed with its entries in each list. If a list has no entries, that list is not printed. """ from mysql.utilities.common.format import print_list if not self.topology.gtid_enabled(): self._report( "# WARNING: GTIDs are not supported on this topology.", logging.WARN) return format = self.options.get("format", "grid") # Get UUIDs uuids = self.topology.get_server_uuids() if len(uuids): print "#" print "# UUIDS for all servers:" print_list(sys.stdout, format, ['host', 'port', 'role', 'uuid'], uuids) # Get GTID lists executed, purged, owned = self.topology.get_gtid_data() if len(executed): print "#" print "# Transactions executed on the server:" print_list(sys.stdout, format, _GTID_COLS, executed) if len(purged): print "#" print "# Transactions purged from the server:" print_list(sys.stdout, format, _GTID_COLS, purged) if len(owned): print "#" print "# Transactions owned by another server:" print_list(sys.stdout, format, _GTID_COLS, owned)
def _show_gtid_data(self): """Display the GTID lists from the servers. This method displays the three GTID lists for all of the servers. Each server is listed with its entries in each list. If a list has no entries, that list is not printed. """ from mysql.utilities.common.format import print_list if not self.topology.gtid_enabled(): self._report("# WARNING: GTIDs are not supported on this topology.", logging.WARN) return format = self.options.get("format", "grid") # Get UUIDs uuids = self.topology.get_server_uuids() if len(uuids): print "#" print "# UUIDS for all servers:" print_list(sys.stdout, format, ['host','port','role','uuid'], uuids) # Get GTID lists executed, purged, owned = self.topology.get_gtid_data() if len(executed): print "#" print "# Transactions executed on the server:" print_list(sys.stdout, format, _GTID_COLS, executed) if len(purged): print "#" print "# Transactions purged from the server:" print_list(sys.stdout, format, _GTID_COLS, purged) if len(owned): print "#" print "# Transactions owned by another server:" print_list(sys.stdout, format, _GTID_COLS, owned)
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) """ from mysql.utilities.common.server import test_connect from mysql.utilities.common.format import print_list no_headers = options.get("no_headers", False) format = 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) verbosity = options.get("verbosity", 0) show_servers = options.get("show_servers", 0) if show_servers: if os.name == 'nt': ports = options.get("ports", "3306:3333") start, end = ports.split(":") _show_running_servers(start, end) else: _show_running_servers() defaults_rows = [] rows = [] server_val = {} get_defaults = True for server in servers: new_server = None try: test_connect(server, True) except UtilError as util_error: if util_error.errmsg.startswith("Server connection values invalid:"): raise util_error # If we got an exception it may means that the server is offline # in that case we will try to turn a clone to extract the info # if the user passed the necessary parameters. pattern = ".*?: (.*?)\((.*)\)" res = re.match(pattern, util_error.errmsg, re.S) if not res: er = ["error: <%s>" % util_error.errmsg] else: er = res.groups() if (re.search("refused", "".join(er)) or re.search("Can't connect to local MySQL server through socket", "".join(er))): er = ["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: er.append(", ".join(opts[0:-1])) if len(opts) > 1: er.append(" and the ") er.append(opts[-1]) er.append(" option") raise UtilError("".join(er)) if not start: raise UtilError("".join(er)) 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, defaults = _server_info(server, get_defaults, options) if info: rows.append(info) if defaults and len(defaults_rows) == 0: defaults_rows = defaults get_defaults = False if new_server: # Need to stop the server! new_server.disconnect() res = _stop_server(server_val, basedir, options) print_list(sys.stdout, format, _COLUMNS, rows, no_headers) if show_defaults and len(defaults_rows) > 0: for row in defaults_rows: print " %s" % row
def execute(self, connections, output=sys.stdout, connector=mysql.connector, **kwrds): """Execute the search for objects This method searches for objects that match a search criteria for one or more servers. connections[in] list of connection parameters output[in] file stream to display information default = sys.stdout connector[in] connector to use default = mysql.connector kwrds[in] dictionary of options format format for display default = GRID """ fmt = kwrds.get('format', "grid") charset = kwrds.get('charset', None) ssl_opts = kwrds.get('ssl_opts', {}) entries = [] for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info, ) raise FormatError(msg) if charset: conn['charset'] = charset info = conn conn['host'] = conn['host'].replace("[", "") conn['host'] = conn['host'].replace("]", "") if connector == mysql.connector: set_ssl_opts_in_connection_info(ssl_opts, info) connection = connector.connect(**info) if not charset: # If no charset provided, get it from the # "character_set_client" server variable. cursor = connection.cursor() cursor.execute("SHOW VARIABLES LIKE 'character_set_client'") res = cursor.fetchall() connection.set_charset_collation(charset=str(res[0][1])) cursor.close() cursor = connection.cursor() cursor.execute(self.__sql) entries.extend( [tuple([_spec(info)] + list(row)) for row in cursor]) headers = ["Connection"] headers.extend(col[0].title() for col in cursor.description) if len(entries) > 0 and output: print_list(output, fmt, headers, entries) else: msg = "Nothing matches '%s' in any %s" % \ (self.__pattern, _join_words(self.__types, conjunction="or")) raise EmptyResultError(msg)
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_special_indexes(self, format, limit, best=False): """Display a list of the best or worst queries for this table. This shows the best (first n) or worst (last n) performing queries for a given table. format[in] format out output = sql, table, tab, csv limit[in] number to limit the display best[in] (optional) if True, print best performing indexes if False, print worst performing indexes """ _QUERY = """ SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `index name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL( IF(t.TABLE_ROWS < s.CARDINALITY, s.CARDINALITY, t.TABLE_ROWS), 0.01)) * 100), 2) AS `sel_percent` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME != 'PRIMARY' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 /* Only tables with some rows */ AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL( IF(t.TABLE_ROWS < s.CARDINALITY, s.CARDINALITY, t.TABLE_ROWS), 0.01)) <= 1.00 ORDER BY `sel_percent` """ from mysql.utilities.common.format import print_list query_options = { 'params' : (self.db_name, self.tbl_name,) } rows = [] type = "best" if not best: type = "worst" if best: rows= self.server.exec_query(_QUERY + "DESC LIMIT %s" % limit, query_options) else: rows= self.server.exec_query(_QUERY + "LIMIT %s" % limit, query_options) if rows: print "#" print "# Showing the top 5 %s performing indexes from %s:\n#" % \ (type, self.table) cols = ("database", "table", "name", "column", "sequence", "num columns", "cardinality", "est. rows", "percent") print_list(sys.stdout, format, cols, rows)
def show_special_indexes(self, fmt, limit, best=False): """Display a list of the best or worst queries for this table. This shows the best (first n) or worst (last n) performing queries for a given table. fmt[in] format out output = sql, table, tab, csv limit[in] number to limit the display best[in] (optional) if True, print best performing indexes if False, print worst performing indexes """ _QUERY = """ SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `index name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL( IF(t.TABLE_ROWS < s.CARDINALITY, s.CARDINALITY, t.TABLE_ROWS), 0.01)) * 100), 2) AS `sel_percent` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME != 'PRIMARY' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 /* Only tables with some rows */ AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL( IF(t.TABLE_ROWS < s.CARDINALITY, s.CARDINALITY, t.TABLE_ROWS), 0.01)) <= 1.00 ORDER BY `sel_percent` """ query_options = { 'params': (self.db_name, self.tbl_name,) } rows = [] idx_type = "best" if not best: idx_type = "worst" if best: rows = self.server.exec_query(_QUERY + "DESC LIMIT %s" % limit, query_options) else: rows = self.server.exec_query(_QUERY + "LIMIT %s" % limit, query_options) if rows: print("#") if limit == 1: print("# Showing the {0} performing index from " "{1}:".format(idx_type, self.table)) else: print("# Showing the top {0} {1} performing indexes from " "{2}:".format(limit, idx_type, self.table)) print("#") cols = ("database", "table", "name", "column", "sequence", "num columns", "cardinality", "est. rows", "percent") print_list(sys.stdout, fmt, cols, rows) else: print("# WARNING: Not enough data to calculate " "best/worst indexes.")
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() row_dict_lst = [] warnings = [] server_val = {} for server in servers: new_server = None try: test_connect(server, True) except UtilError as util_error: conn_dict = get_connection_dictionary(server) 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, but if path to socket not empty, server could be # turned off. if util_error.errno == CR_CONNECTION_ERROR: socket = conn_dict.get("unix_socket", "") if socket: mydir = os.path.split(socket)[0] if os.path.isdir(mydir) and len(os.listdir(mydir)) != 0: server_is_off = True # If we got errno 2003 and this is a windows, we do not have # socket, instead we check if server is localhost. elif (util_error.errno == CR_CONN_HOST_ERROR and os.name == 'nt' 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' er = ("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: er = [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): er = ["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: er.append(", ".join(opts[0:-1])) if len(opts) > 1: er.append(" and the ") er.append(opts[-1]) er.append(" option") raise UtilError("".join(er)) if not start: raise UtilError("".join(er)) 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 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) """ from mysql.utilities.common.server import test_connect from mysql.utilities.common.format import print_list no_headers = options.get("no_headers", False) format = 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) verbosity = options.get("verbosity", 0) show_servers = options.get("show_servers", 0) if show_servers: if os.name == 'nt': ports = options.get("ports", "3306:3333") start, end = ports.split(":") _show_running_servers(start, end) else: _show_running_servers() defaults_rows = [] rows = [] server_val = {} get_defaults = True for server in servers: server_alive = True server_started = False if not test_connect(server): if basedir is None or datadir is None: raise UtilError("Server is offline. To connection, " "you must provide basedir, datadir, " "and the start option") else: if start: try: server_val = parse_connection(server) except: raise UtilError("Source connection values in" "valid or cannot be parsed.") res = _start_server(server_val, basedir, datadir, options) server_started = True else: server_alive = False if server_alive: info, defaults = _server_info(server, get_defaults, options) if info is not None: rows.append(info) if defaults is not None and len(defaults_rows) == 0: defaults_rows = defaults get_defaults = False if server_started: # Need to stop the server! res = _stop_server(server_val, basedir, options) print_list(sys.stdout, format, _COLUMNS, rows, no_headers) if show_defaults and len(defaults_rows) > 0: for row in defaults_rows: print " %s" % row
def execute(self, connections, **kwrds): """Execute the search for processes, queries, or connections This method searches for processes, queriers, or connections to either kill or display the matches for one or more servers. connections[in] list of connection parameters kwrds[in] dictionary of options output file stream to display information default = sys.stdout connector connector to use default = mysql.connector format format for display default = GRID """ output = kwrds.get('output', sys.stdout) connector = kwrds.get('connector', mysql.connector) fmt = kwrds.get('format', "grid") charset = kwrds.get('charset', None) ssl_opts = kwrds.get('ssl_opts', {}) headers = ("Connection", "Id", "User", "Host", "Db", "Command", "Time", "State", "Info") entries = [] # Build SQL statement for info in connections: conn = parse_connection(info) if not conn: msg = "'%s' is not a valid connection specifier" % (info, ) raise FormatError(msg) if charset: conn['charset'] = charset info = conn if connector == mysql.connector: set_ssl_opts_in_connection_info(ssl_opts, info) connection = connector.connect(**info) if not charset: # If no charset provided, get it from the # "character_set_client" server variable. cursor = connection.cursor() cursor.execute("SHOW VARIABLES LIKE 'character_set_client'") res = cursor.fetchall() connection.set_charset_collation(charset=str(res[0][1])) cursor.close() cursor = connection.cursor() cursor.execute(self.__select) print_rows = [] cols = [ "Id", "User", "Host", "db", "Command", "Time", "State", "Info" ] for row in cursor: if (KILL_QUERY in self.__actions) or \ (KILL_CONNECTION in self.__actions): print_rows.append(row) cursor.execute("KILL {0}".format(row[0])) if PRINT_PROCESS in self.__actions: entries.append(tuple([_spec(info)] + list(row))) if print_rows: print "# The following KILL commands were executed:" print_list(output, fmt, cols, print_rows) # If output is None, nothing is printed if len(entries) > 0 and output: entries.sort(key=lambda fifth: fifth[5]) print_list(output, fmt, headers, entries) elif PRINT_PROCESS in self.__actions: raise EmptyResultError("No matches found")
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") 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: size = int(tablespace[1]) / _MB print "Tablespace %s can be " % tablespace[3] + "extended by using %s:%sM[...]\n" % ( parts[0], 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: size = int(res[0][0]) if not quiet: _print_size("InnoDB freespace = ", size) print return True
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) 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') 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 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: size = int(tablespace[1]) / _MB print "Tablespace %s can be " % tablespace[3] + \ "extended by using %s:%sM[...]\n" % \ (parts[0], size) 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: size = int(res[0][0]) if not quiet: _print_size("InnoDB freespace = ", size) print return True