def _print_master_status(self):
"""Display the master information
This method displays the master information from SHOW MASTER STATUS.
"""
from mysql.utilities.common.format import format_tabular_list
# If no master present, don't print anything.
if self.master is None:
return
try:
status = self.master.get_status()[0]
if self.logging:
logging.info("Master status: binlog: %s, position:%s" %
(status[0], status[1]))
except:
raise UtilRplError("Cannot get master status")
print "Master Information"
print "------------------"
cols = ("Binary Log File", "Position",
"Binlog_Do_DB", "Binlog_Ignore_DB")
fmt_opts = {
"print_header" : True,
"separator" : None,
"quiet" : True,
"print_footer" : False,
}
logfile = status[0][0:20] if len(status[0]) > 20 else status[0]
rows = [(logfile, status[1], status[2], status[3])]
format_tabular_list(sys.stdout, cols, rows, fmt_opts)
print
self.rows_printed += 4
def run(self):
test_file = open('format_test', 'w')
rows_1 = [('one', ), ('two', ), ('three', )]
cols_1 = ['a']
rows_2 = [('one', None), ('two', None), ('three', None)]
cols_2 = ['a', 'b']
rows_3 = [('one', None, 31), ('two', None, 32), ('three', None, 33)]
cols_3 = ['a', 'b', 'c']
rows_4 = [(u'á', u'é', u'í'), (u'á', u'é', u'í'), (u'á', u'é', u'í')]
cols_4 = [u'á', u'é', u'í']
format_tabular_list(test_file, cols_1, rows_1)
format_tabular_list(test_file, cols_2, rows_2)
format_tabular_list(test_file, cols_3, rows_3)
format_tabular_list(test_file, cols_4, rows_4)
# Force usage of csv module
format_tabular_list(test_file, cols_4, rows_4, {'separator': ','})
format_vertical_list(test_file, cols_1, rows_1)
format_vertical_list(test_file, cols_2, rows_2)
format_vertical_list(test_file, cols_3, rows_3)
format_vertical_list(test_file, cols_4, rows_4)
test_file.close()
test_file = open('format_test', 'r')
for line in test_file.readlines():
self.results.append(line)
test_file.close()
return True
def run(self):
self.server1 = self.servers.get_server(0)
self.res_fname = "result.txt"
from_conn = "--source={0}".format(
self.build_connection_string(self.server1))
to_conn = "--destination={0}".format(
self.build_connection_string(self.server1))
# dump if debug run
if self.debug:
print "\n# Dump of data to be cloned:"
rows = self.server1.exec_query("SELECT * FROM apostrophe.t1")
format_tabular_list(sys.stdout, ['char_field', 'blob_field'], rows)
# Test case 1 - clone a sample database
cmd = ("mysqldbcopy.py {0} {1} apostrophe:apostrophe_clone "
" --skip-gtid ".format(from_conn, to_conn))
try:
res = self.exec_util(cmd, self.res_fname)
self.results.append(res)
except MUTLibError as err:
raise MUTLibError(err.errmsg)
# dump if debug run
if self.debug:
print "\n# Dump of data cloned:"
rows = self.server1.exec_query("SELECT * FROM apostrophe_clone.t1")
format_tabular_list(sys.stdout, ['char_field', 'blob_field'], rows)
return True
def dump_table(self, server):
header = "# Dump of table test_relay.t1 for server %s:\n" % server.role
self.results.append(header)
if self.debug:
print header
rows = server.exec_query("SELECT * FROM test_relay.t1")
f_out = tempfile.TemporaryFile()
format_tabular_list(f_out, ['a', 'b'], rows, {"separator": ","})
f_out.seek(0)
for row in f_out.readlines():
self.results.append(row)
if self.debug:
print row,
def dump_table(self, server):
header = "# Dump of table test_relay.t1 for server %s:\n" % server.role
self.results.append(header)
if self.debug:
print header
rows = server.exec_query("SELECT * FROM test_relay.t1")
f_out = tempfile.TemporaryFile()
format_tabular_list(f_out, ['a', 'b'], rows, {"separator" : ","})
f_out.seek(0)
for row in f_out.readlines():
self.results.append(row)
if self.debug:
print row,
def _print_master_status(self):
"""Display the master information
This method displays the master information from SHOW MASTER STATUS.
"""
from mysql.utilities.common.format import format_tabular_list
# If no master present, don't print anything.
if self.master is None:
return
try:
status = self.master.get_status()[0]
if self.logging:
logging.info("Master status: binlog: %s, position:%s" %
(status[0], status[1]))
except:
raise UtilRplError("Cannot get master status")
print "Master Information"
print "------------------"
cols = ("Binary Log File", "Position",
"Binlog_Do_DB", "Binlog_Ignore_DB")
fmt_opts = {
"print_header" : True,
"separator" : None,
"quiet" : True,
"print_footer" : False,
}
logfile = status[0][0:20] if len(status[0]) > 20 else status[0]
rows = [(logfile, status[1], status[2], status[3])]
format_tabular_list(sys.stdout, cols, rows, fmt_opts)
# Display gtid executed set
self.master_gtids = []
for gtid in status[4].split("\n"):
if len(gtid):
# Add each GTID to a tuple to match the required format to
# print the full GRID list correctly.
self.master_gtids.append((gtid.strip(","),))
print "\nGTID Executed Set"
try:
print self.master_gtids[0][0],
except IndexError:
print "None",
if len(self.master_gtids) > 1:
print "[...]"
else:
print
print
self.rows_printed += 7
def _print_master_status(self):
"""Display the master information
This method displays the master information from SHOW MASTER STATUS.
"""
# If no master present, don't print anything.
if self.master is None:
return
try:
status = self.master.get_status()[0]
if self.logging:
logging.info("Master status: binlog: {0}, position:{1}"
"".format(status[0], status[1]))
except Exception as err:
raise UtilRplError("Cannot get master status: {0}".format(err))
print "Master Information"
print "------------------"
cols = ("Binary Log File", "Position",
"Binlog_Do_DB", "Binlog_Ignore_DB")
fmt_opts = {
"print_header": True,
"separator": None,
"quiet": True,
"print_footer": False,
}
logfile = status[0][0:20] if len(status[0]) > 20 else status[0]
rows = [(logfile, status[1], status[2], status[3])]
format_tabular_list(sys.stdout, cols, rows, fmt_opts)
# Display gtid executed set
self.master_gtids = []
for gtid in status[4].split("\n"):
if len(gtid):
# Add each GTID to a tuple to match the required format to
# print the full GRID list correctly.
self.master_gtids.append((gtid.strip(","),))
print "\nGTID Executed Set"
try:
print self.master_gtids[0][0],
except IndexError:
print "None",
if len(self.master_gtids) > 1:
print "[...]"
else:
print
print
self.rows_printed += 7
def dump_table(self, server):
"""Dumps the test_relay table.
server[in] Server instance.
"""
header = "# Dump of table test_relay.t1 for server {0}:\n".format(
server.role)
self.results.append(header)
if self.debug:
print header
rows = server.exec_query("SELECT * FROM test_relay.t1")
f_out = tempfile.TemporaryFile()
format_tabular_list(f_out, ['a', 'b'], rows, {"separator": ","})
f_out.seek(0)
for row in f_out.readlines():
# Convert line terminator to '\n' for test to pass on Windows.
self.results.append('{0}\n'.format(row.rstrip()))
if self.debug:
print row,
def dump_table(self, server):
"""Dumps the test_relay table.
server[in] Server instance.
"""
header = "# Dump of table test_relay.t1 for server {0}:\n".format(
server.role)
self.results.append(header)
if self.debug:
print header
rows = server.exec_query("SELECT * FROM test_relay.t1")
f_out = tempfile.TemporaryFile()
format_tabular_list(f_out, ['a', 'b'], rows, {"separator": ","})
f_out.seek(0)
for row in f_out.readlines():
# Convert line terminator to '\n' for test to pass on Windows.
self.results.append('{0}\n'.format(row.rstrip()))
if self.debug:
print row,
def run(self):
from mysql.utilities.common.format import format_tabular_list
from mysql.utilities.common.format import format_vertical_list
rows_1 = [('one',),('two',),('three',)]
cols_1 = ['a']
rows_2 = [('one',None),('two',None),('three',None)]
cols_2 = ['a','b']
rows_3 = [('one',None,31),('two',None,32),('three',None,33)]
cols_3 = ['a','b','c']
format_tabular_list(self.test_file, cols_1, rows_1)
format_tabular_list(self.test_file, cols_2, rows_2)
format_tabular_list(self.test_file, cols_3, rows_3)
format_vertical_list(self.test_file, cols_1, rows_1)
format_vertical_list(self.test_file, cols_2, rows_2)
format_vertical_list(self.test_file, cols_3, rows_3)
self.test_file.close()
self.test_file = open('format_test', 'r')
for line in self.test_file.readlines():
self.results.append(line)
self.test_file.close()
return True
def run(self):
from mysql.utilities.common.format import format_tabular_list
from mysql.utilities.common.format import format_vertical_list
rows_1 = [('one', ), ('two', ), ('three', )]
cols_1 = ['a']
rows_2 = [('one', None), ('two', None), ('three', None)]
cols_2 = ['a', 'b']
rows_3 = [('one', None, 31), ('two', None, 32), ('three', None, 33)]
cols_3 = ['a', 'b', 'c']
format_tabular_list(self.test_file, cols_1, rows_1)
format_tabular_list(self.test_file, cols_2, rows_2)
format_tabular_list(self.test_file, cols_3, rows_3)
format_vertical_list(self.test_file, cols_1, rows_1)
format_vertical_list(self.test_file, cols_2, rows_2)
format_vertical_list(self.test_file, cols_3, rows_3)
self.test_file.close()
self.test_file = open('format_test', 'r')
for line in self.test_file.readlines():
self.results.append(line)
self.test_file.close()
return True
def run(self):
test_file = open('format_test', 'w')
rows_1 = [('one',), ('two',), ('three',)]
cols_1 = ['a']
rows_2 = [('one', None), ('two', None), ('three', None)]
cols_2 = ['a', 'b']
rows_3 = [('one', None, 31), ('two', None, 32), ('three', None, 33)]
cols_3 = ['a', 'b', 'c']
format_tabular_list(test_file, cols_1, rows_1)
format_tabular_list(test_file, cols_2, rows_2)
format_tabular_list(test_file, cols_3, rows_3)
format_vertical_list(test_file, cols_1, rows_1)
format_vertical_list(test_file, cols_2, rows_2)
format_vertical_list(test_file, cols_3, rows_3)
test_file.close()
test_file = open('format_test', 'r')
for line in test_file.readlines():
self.results.append(line)
test_file.close()
return True
def run(self):
test_file = open('format_test', 'w')
rows_1 = [('one', ), ('two', ), ('three', )]
cols_1 = ['a']
rows_2 = [('one', None), ('two', None), ('three', None)]
cols_2 = ['a', 'b']
rows_3 = [('one', None, 31), ('two', None, 32), ('three', None, 33)]
cols_3 = ['a', 'b', 'c']
format_tabular_list(test_file, cols_1, rows_1)
format_tabular_list(test_file, cols_2, rows_2)
format_tabular_list(test_file, cols_3, rows_3)
format_vertical_list(test_file, cols_1, rows_1)
format_vertical_list(test_file, cols_2, rows_2)
format_vertical_list(test_file, cols_3, rows_3)
test_file.close()
test_file = open('format_test', 'r')
for line in test_file.readlines():
self.results.append(line)
test_file.close()
return True
def _export_row(data_rows,
cur_table,
out_format,
single,
skip_blobs,
first=False,
no_headers=False,
outfile=None):
"""Export a row
This method will print a row to stdout based on the format chosen -
either SQL statements, GRID, CSV, TSV, or VERTICAL.
datarows[in] one or more rows for exporting
cur_table[in] Table class instance
out_format[in] desired output format
skip_blobs[in] if True, skip blob data
single[in] if True, generate single INSERT statements (valid
only for format=SQL)
first[in] if True, this is the first row to be exported - this
causes the header to be printed if chosen.
no_headers[in] if True, do not print headers
outfile[in] if is not None, write table data to this file.
"""
tbl_name = cur_table.tbl_name
q_db_name = cur_table.q_db_name
full_name = cur_table.q_table
list_options = {'none_to_null': True}
# if outfile is not set, use stdout.
if outfile is None:
outfile = sys.stdout # default file handle
if out_format == 'sql':
if single:
if single:
data = data_rows
else:
data = data_rows[1]
blob_rows = []
for row in data:
columns = cur_table.get_column_string(row, q_db_name,
skip_blobs)
if len(columns[1]) > 0:
blob_rows.extend(columns[1])
if columns[0]:
row_str = "INSERT INTO {0} VALUES{1};\n".format(
full_name, columns[0])
outfile.write(row_str)
else:
# Generate bulk insert statements
data_lists = cur_table.make_bulk_insert(data_rows,
q_db_name,
skip_blobs=skip_blobs)
rows = data_lists[0]
blob_rows = data_lists[1]
if len(rows) > 0:
for row in rows:
outfile.write("{0};\n".format(row))
else:
outfile.write("# Table {0} has no data.\n"
"".format(cur_table.q_tbl_name))
if len(blob_rows) > 0:
if skip_blobs:
outfile.write("# WARNING : Table {0} has blob data that "
"has been excluded by --skip-blobs."
"\n".format(cur_table.q_tbl_name))
else:
outfile.write("# Blob data for table "
"{0}:\n".format(cur_table.q_tbl_name))
for blob_row in blob_rows:
outfile.write("{0}\n".format(blob_row))
# Cannot use print_list here because we must manipulate
# the behavior of format_tabular_list
elif out_format == "vertical":
format_vertical_list(outfile, cur_table.get_col_names(), data_rows,
list_options)
elif out_format == "tab":
list_options['print_header'] = first
list_options['separator'] = '\t'
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(True), data_rows,
list_options)
elif out_format == "csv":
list_options['print_header'] = first
list_options['separator'] = ','
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(True), data_rows,
list_options)
else: # default to table format - header is always printed
format_tabular_list(outfile, cur_table.get_col_names(), data_rows,
list_options)
def _export_row(data_rows, cur_table, out_format, single, skip_blobs,
first=False, no_headers=False, outfile=None):
"""Export a row
This method will print a row to stdout based on the format chosen -
either SQL statements, GRID, CSV, TSV, or VERTICAL.
datarows[in] one or more rows for exporting
cur_table[in] Table class instance
out_format[in] desired output format
skip_blobs[in] if True, skip blob data
single[in] if True, generate single INSERT statements (valid
only for format=SQL)
first[in] if True, this is the first row to be exported - this
causes the header to be printed if chosen.
no_headers[in] if True, do not print headers
outfile[in] if is not None, write table data to this file.
"""
tbl_name = cur_table.tbl_name
q_db_name = cur_table.q_db_name
full_name = cur_table.q_table
list_options = {'none_to_null': True}
# if outfile is not set, use stdout.
if outfile is None:
outfile = sys.stdout # default file handle
if out_format == 'sql':
if single:
if single:
data = data_rows
else:
data = data_rows[1]
blob_rows = []
for row in data:
columns = cur_table.get_column_string(row, q_db_name,
skip_blobs)
if len(columns[1]) > 0:
blob_rows.extend(columns[1])
if columns[0]:
row_str = "INSERT INTO {0} VALUES{1};\n".format(full_name,
columns[0])
outfile.write(row_str)
else:
# Generate bulk insert statements
data_lists = cur_table.make_bulk_insert(data_rows, q_db_name,
skip_blobs=skip_blobs)
rows = data_lists[0]
blob_rows = data_lists[1]
if len(rows) > 0:
for row in rows:
outfile.write("{0};\n".format(row))
else:
outfile.write("# Table {0} has no data.\n".format(tbl_name))
if len(blob_rows) > 0:
if skip_blobs:
outfile.write("# WARNING : Table {0} has blob data that "
"has been excluded by --skip-blobs."
"\n".format(tbl_name))
else:
outfile.write("# Blob data for table {0}:\n".format(tbl_name))
for blob_row in blob_rows:
outfile.write("{0}\n".format(blob_row))
# Cannot use print_list here because we must manipulate
# the behavior of format_tabular_list
elif out_format == "vertical":
format_vertical_list(outfile, cur_table.get_col_names(),
data_rows, list_options)
elif out_format == "tab":
list_options['print_header'] = first
list_options['separator'] = '\t'
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(True),
data_rows, list_options)
elif out_format == "csv":
list_options['print_header'] = first
list_options['separator'] = ','
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(True),
data_rows, list_options)
else: # default to table format - header is always printed
format_tabular_list(outfile, cur_table.get_col_names(),
data_rows, list_options)
def _export_metadata(source, db_list, output_file, options):
"""Export metadata from the specified list of databases.
This private method retrieves the objects metadata for each database listed
in the form of CREATE (SQL) statements or in a tabular form (GRID, TAB,
CSV, VERTICAL) to the specified file.
This private method does not check permissions.
source[in] Server instance.
db_list[in] List of databases to export.
output_file[in] Output file to store the metadata information.
options[in] Dictionary containing the options for the export:
(skip_tables, skip_views, skip_triggers, skip_procs,
skip_funcs, skip_events, skip_grants, skip_create,
skip_data, no_header, display, format,
debug, exclude_names, exclude_patterns)
"""
frmt = options.get("format", "sql")
no_headers = options.get("no_headers", False)
column_type = options.get("display", "brief")
quiet = options.get("quiet", False)
skip_create = options.get("skip_create", False)
skip_tables = options.get("skip_tables", False)
skip_views = options.get("skip_views", False)
skip_triggers = options.get("skip_triggers", False)
skip_procs = options.get("skip_procs", False)
skip_funcs = options.get("skip_funcs", False)
skip_events = options.get("skip_events", False)
skip_grants = options.get("skip_grants", False)
sql_mode = source.select_variable("SQL_MODE")
for db_name in db_list:
# Get a Database class instance
db = Database(source, db_name, options)
# Export database metadata
if not quiet:
output_file.write("# Exporting metadata from {0}\n".format(
db.q_db_name))
# Perform the extraction
if frmt == "sql":
db.init()
if not skip_create:
output_file.write("DROP DATABASE IF EXISTS {0};\n".format(
db.q_db_name))
output_file.write("CREATE DATABASE {0};\n".format(
db.q_db_name))
output_file.write("USE {0};\n".format(db.q_db_name))
for dbobj in db.get_next_object():
if dbobj[0] == "GRANT" and not skip_grants:
if not quiet:
output_file.write("# Grant:\n")
if dbobj[1][3]:
create_str = "GRANT {0} ON {1}.{2} TO {3};\n".format(
dbobj[1][1], db.q_db_name,
quote_with_backticks(dbobj[1][3], sql_mode),
dbobj[1][0])
else:
create_str = "GRANT {0} ON {1}.* TO {2};\n".format(
dbobj[1][1], db.q_db_name, dbobj[1][0])
output_file.write(create_str)
else:
if not quiet:
output_file.write("# {0}: {1}.{2}\n".format(
dbobj[0], db.q_db_name,
quote_with_backticks(dbobj[1][0], sql_mode)))
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
output_file.write("DELIMITER ||\n")
output_file.write("{0};\n".format(
db.get_create_statement(db.db_name, dbobj[1][0],
dbobj[0])))
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
output_file.write("||\n")
output_file.write("DELIMITER ;\n")
else:
objects = []
if not skip_tables:
objects.append("TABLE")
if not skip_funcs:
objects.append("FUNCTION")
if not skip_procs:
objects.append("PROCEDURE")
if not skip_views:
objects.append("VIEW")
if not skip_triggers:
objects.append("TRIGGER")
if not skip_events:
objects.append("EVENT")
if not skip_grants:
objects.append("GRANT")
for obj_type in objects:
output_file.write("# {0}S in {1}:".format(
obj_type, db.q_db_name))
if frmt in ('grid', 'vertical'):
rows = db.get_db_objects(obj_type, column_type, True)
else:
rows = db.get_db_objects(obj_type, column_type, True, True)
if len(rows[1]) < 1:
output_file.write(" (none found)\n")
else:
output_file.write("\n")
# Cannot use print_list here because we must manipulate
# the behavior of format_tabular_list.
list_options = {}
if frmt == "vertical":
format_vertical_list(output_file, rows[0], rows[1])
elif frmt == "tab":
list_options['print_header'] = not no_headers
list_options['separator'] = '\t'
format_tabular_list(output_file, rows[0], rows[1],
list_options)
elif frmt == "csv":
list_options['print_header'] = not no_headers
list_options['separator'] = ','
format_tabular_list(output_file, rows[0], rows[1],
list_options)
else: # default to table format
format_tabular_list(output_file, rows[0], rows[1])
if not quiet:
output_file.write("#...done.\n")
def _export_metadata(source, db_list, output_file, options):
"""Export metadata from the specified list of databases.
This private method retrieves the objects metadata for each database listed
in the form of CREATE (SQL) statements or in a tabular form (GRID, TAB,
CSV, VERTICAL) to the specified file.
This private method does not check permissions.
source[in] Server instance.
db_list[in] List of databases to export.
output_file[in] Output file to store the metadata information.
options[in] Dictionary containing the options for the export:
(skip_tables, skip_views, skip_triggers, skip_procs,
skip_funcs, skip_events, skip_grants, skip_create,
skip_data, no_header, display, format,
debug, exclude_names, exclude_patterns)
"""
frmt = options.get("format", "sql")
no_headers = options.get("no_headers", False)
column_type = options.get("display", "brief")
quiet = options.get("quiet", False)
skip_create = options.get("skip_create", False)
skip_tables = options.get("skip_tables", False)
skip_views = options.get("skip_views", False)
skip_triggers = options.get("skip_triggers", False)
skip_procs = options.get("skip_procs", False)
skip_funcs = options.get("skip_funcs", False)
skip_events = options.get("skip_events", False)
skip_grants = options.get("skip_grants", False)
for db_name in db_list:
# Get a Database class instance
db = Database(source, db_name, options)
# Export database metadata
if not quiet:
output_file.write(
"# Exporting metadata from {0}\n".format(db.db_name)
)
# Perform the extraction
if frmt == "sql":
db.init()
if not skip_create:
output_file.write(
"DROP DATABASE IF EXISTS {0};\n".format(db.q_db_name)
)
output_file.write(
"CREATE DATABASE {0};\n".format(db.q_db_name)
)
output_file.write("USE {0};\n".format(db.q_db_name))
for dbobj in db.get_next_object():
if dbobj[0] == "GRANT" and not skip_grants:
if not quiet:
output_file.write("# Grant:\n")
if dbobj[1][3]:
create_str = "GRANT {0} ON {1}.{2} TO {3};\n".format(
dbobj[1][1], db.q_db_name,
quote_with_backticks(dbobj[1][3]), dbobj[1][0]
)
else:
create_str = "GRANT {0} ON {1}.* TO {2};\n".format(
dbobj[1][1], db.q_db_name, dbobj[1][0]
)
output_file.write(create_str)
else:
if not quiet:
output_file.write(
"# {0}: {1}.{2}\n".format(dbobj[0], db.db_name,
dbobj[1][0])
)
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
output_file.write("DELIMITER ||\n")
output_file.write("{0};\n".format(
db.get_create_statement(db.db_name, dbobj[1][0],
dbobj[0])
))
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
output_file.write("||\n")
output_file.write("DELIMITER ;\n")
else:
objects = []
if not skip_tables:
objects.append("TABLE")
if not skip_funcs:
objects.append("FUNCTION")
if not skip_procs:
objects.append("PROCEDURE")
if not skip_views:
objects.append("VIEW")
if not skip_triggers:
objects.append("TRIGGER")
if not skip_events:
objects.append("EVENT")
if not skip_grants:
objects.append("GRANT")
for obj_type in objects:
output_file.write(
"# {0}S in {1}:".format(obj_type, db.db_name)
)
if frmt in ('grid', 'vertical'):
rows = db.get_db_objects(obj_type, column_type, True)
else:
rows = db.get_db_objects(obj_type, column_type, True, True)
if len(rows[1]) < 1:
output_file.write(" (none found)\n")
else:
output_file.write("\n")
# Cannot use print_list here because we must manipulate
# the behavior of format_tabular_list.
list_options = {}
if frmt == "vertical":
format_vertical_list(output_file, rows[0], rows[1])
elif frmt == "tab":
list_options['print_header'] = not no_headers
list_options['separator'] = '\t'
format_tabular_list(output_file, rows[0], rows[1],
list_options)
elif frmt == "csv":
list_options['print_header'] = not no_headers
list_options['separator'] = ','
format_tabular_list(output_file, rows[0], rows[1],
list_options)
else: # default to table format
format_tabular_list(output_file, rows[0], rows[1])
if not quiet:
output_file.write("#...done.\n")
format_tabular_list(sys.stdout, ['char_field', 'blob_field'], rows)
# Test case 1 - clone a sample database
cmd = "mysqldbcopy.py %s %s apostrophe:apostrophe_clone " \
" --skip-gtid " % (from_conn, to_conn)
try:
res = self.exec_util(cmd, self.res_fname)
self.results.append(res)
except MUTLibError, e:
raise MUTLibError(e.errmsg)
# dump if debug run
if self.debug:
print "\n# Dump of data cloned:"
rows = self.server1.exec_query("SELECT * FROM apostrophe_clone.t1")
format_tabular_list(sys.stdout, ['char_field', 'blob_field'], rows)
return True
def get_result(self):
msg = None
if self.server1 and self.results[0] == 0:
query = "SHOW DATABASES LIKE 'apostrophe_%'"
try:
res = self.server1.exec_query(query)
if res and res[0][0] == 'apostrophe_clone':
return (True, None)
except UtilDBError, e:
raise MUTLibError(e.errmsg)
return (False, ("Result failure.\n", "Database clone not found.\n"))
def _export_row(data_rows, cur_table, format, single, skip_blobs, first=False,
no_headers=False, outfile=None):
"""Export a row
This method will print a row to stdout based on the format chosen -
either SQL statements, GRID, CSV, TSV, or VERTICAL.
datarows[in] one or more rows for exporting
cur_table[in] Table class instance
format[in] desired output format
skip_blobs[in] if True, skip blob data
single[in] if True, generate single INSERT statements (valid
only for format=SQL)
first[in] if True, this is the first row to be exported - this
causes the header to be printed if chosen.
no_headers[in] if True, do not print headers
outfile[in] if is not None, write table data to this file.
"""
from mysql.utilities.common.format import format_tabular_list
from mysql.utilities.common.format import format_vertical_list
tbl_name = cur_table.tbl_name
db_name = cur_table.db_name
full_name = "`%s`.`%s`" % (db_name, tbl_name)
list_options = {}
# if outfile is not set, use stdout.
if outfile is None:
outfile = sys.stdout # default file handle
if format == 'sql':
if single:
if single:
data = data_rows
else:
data = data_rows[1]
blob_rows = []
for row in data:
columns = cur_table.get_column_string(row, full_name)
if len(columns[1]) > 0:
blob_rows.extend(columns[1])
row_str = "INSERT INTO %s VALUES%s;" % (full_name, columns[0])
outfile.write(row_str + "\n")
else:
# Generate bulk insert statements
data_lists = cur_table.make_bulk_insert(data_rows, db_name)
rows = data_lists[0]
blob_rows = data_lists[1]
if len(rows) > 0:
for row in rows:
outfile.write("%s;\n" % row)
else:
print "# Table %s has no data." % tbl_name
if len(blob_rows) > 0:
if skip_blobs:
print "# WARNING : Table %s has blob data that " \
"has been excluded by --skip-blobs." % \
tbl_name
else:
print "# Blob data for table %s:" % tbl_name
for blob_row in blob_rows:
outfile.write(blob_row + "\n")
# Cannot use print_list here becasue we must manipulate
# the behavior of format_tabular_list
elif format == "vertical":
format_vertical_list(outfile, cur_table.get_col_names(),
data_rows)
elif format == "tab":
list_options['print_header'] = first
list_options['separator'] = '\t'
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(),
data_rows, list_options)
elif format == "csv":
list_options['print_header'] = first
list_options['separator'] = ','
list_options['quiet'] = not no_headers
format_tabular_list(outfile, cur_table.get_col_names(),
data_rows, list_options)
else: # default to table format - header is always printed
format_tabular_list(outfile, cur_table.get_col_names(),
data_rows)
def export_metadata(source, src_val, db_list, options):
"""Produce rows to be used to recreate objects in a database.
This method retrieves the objects for each database listed in the form
of CREATE (SQL) statements or in a tabular form to the file specified.
The valid values for the format parameter are SQL, CSV, TSV, VERTICAL,
or GRID.
source[in] Server instance
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
options[in] a dictionary containing the options for the copy:
(skip_tables, skip_views, skip_triggers, skip_procs,
skip_funcs, skip_events, skip_grants, skip_create,
skip_data, no_header, display, format,
debug, exclude_names, exclude_patterns)
Returns bool True = success, False = error
"""
from mysql.utilities.common.database import Database
from mysql.utilities.common.format import format_tabular_list
from mysql.utilities.common.format import format_vertical_list
format = options.get("format", "sql")
no_headers = options.get("no_headers", False)
column_type = options.get("display", "brief")
skip_create = options.get("skip_create", False)
quiet = options.get("quiet", False)
skip_tables = options.get("skip_tables", False)
skip_views = options.get("skip_views", False)
skip_triggers = options.get("skip_triggers", False)
skip_procs = options.get("skip_procs", False)
skip_funcs = options.get("skip_funcs", False)
skip_events = options.get("skip_events", False)
skip_grants = options.get("skip_grants", False)
if options.get("all", False):
rows = source.get_all_databases()
for row in rows:
db_list.append(row[0])
# Check user permissions on source for all databases
for db_name in db_list:
source_db = Database(source, db_name)
# Make a dictionary of the options
access_options = {
'skip_views' : skip_views,
'skip_procs' : skip_procs,
'skip_funcs' : skip_funcs,
'skip_grants' : skip_grants,
'skip_events' : skip_events,
}
source_db.check_read_access(src_val["user"], src_val["host"],
access_options)
for db_name in db_list:
# Get a Database class instance
db = Database(source, db_name, options)
# Error is source database does not exist
if not db.exists():
raise UtilDBError("Source database does not exist - %s" % db_name,
-1, db_name)
if not quiet:
print "# Exporting metadata from %s" % db_name
# Perform the extraction
if format == "sql":
db.init()
# quote database name with backticks
q_db_name = quote_with_backticks(db_name)
if not skip_create:
print "DROP DATABASE IF EXISTS %s;" % q_db_name
print "CREATE DATABASE %s;" % q_db_name
print "USE %s;" % q_db_name
for dbobj in db.get_next_object():
if dbobj[0] == "GRANT" and not skip_grants:
if not quiet:
print "# Grant:"
if dbobj[1][3]:
create_str = "GRANT %s ON %s.%s TO %s;" % \
(dbobj[1][1], q_db_name,
quote_with_backticks(dbobj[1][3]),
dbobj[1][0])
else:
create_str = "GRANT %s ON %s.* TO %s;" % \
(dbobj[1][1], q_db_name, dbobj[1][0])
if create_str.find("%"):
create_str = re.sub("%", "%%", create_str)
print create_str
else:
if not quiet:
print "# %s: %s.%s" % (dbobj[0], db_name,
dbobj[1][0])
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
print "DELIMITER ||"
print "%s;" % db.get_create_statement(db_name,
dbobj[1][0],
dbobj[0])
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
print "||"
print "DELIMITER ;"
else:
objects = []
if not skip_tables:
objects.append("TABLE")
if not skip_views:
objects.append("VIEW")
if not skip_triggers:
objects.append("TRIGGER")
if not skip_procs:
objects.append("PROCEDURE")
if not skip_funcs:
objects.append("FUNCTION")
if not skip_events:
objects.append("EVENT")
if not skip_grants:
objects.append("GRANT")
for obj_type in objects:
sys.stdout.write("# %sS in %s:" % (obj_type, db_name))
if format in ('grid', 'vertical'):
rows = db.get_db_objects(obj_type, column_type, True)
else:
rows = db.get_db_objects(obj_type, column_type, True, True)
if len(rows[1]) < 1:
print " (none found)"
else:
print
# Cannot use print_list here becasue we must manipulate
# the behavior of format_tabular_list
list_options = {}
if format == "vertical":
format_vertical_list(sys.stdout, rows[0], rows[1])
elif format == "tab":
list_options['print_header'] = not no_headers
list_options['separator'] = '\t'
format_tabular_list(sys.stdout, rows[0], rows[1],
list_options)
elif format == "csv":
list_options['print_header'] = not no_headers
list_options['separator'] = ','
format_tabular_list(sys.stdout, rows[0], rows[1],
list_options)
else: # default to table format
format_tabular_list(sys.stdout, rows[0], rows[1])
if not quiet:
print "#...done."
return True
def export_metadata(source, src_val, db_list, options):
"""Produce rows to be used to recreate objects in a database.
This method retrieves the objects for each database listed in the form
of CREATE (SQL) statements or in a tabular form to the file specified.
The valid values for the format parameter are SQL, CSV, TSV, VERTICAL,
or GRID.
source[in] Server instance
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
options[in] a dictionary containing the options for the copy:
(skip_tables, skip_views, skip_triggers, skip_procs,
skip_funcs, skip_events, skip_grants, skip_create,
skip_data, no_header, display, format,
debug, exclude_names, exclude_patterns)
Returns bool True = success, False = error
"""
from mysql.utilities.common.database import Database
from mysql.utilities.common.format import format_tabular_list
from mysql.utilities.common.format import format_vertical_list
format = options.get("format", "sql")
no_headers = options.get("no_headers", False)
column_type = options.get("display", "brief")
skip_create = options.get("skip_create", False)
quiet = options.get("quiet", False)
skip_tables = options.get("skip_tables", False)
skip_views = options.get("skip_views", False)
skip_triggers = options.get("skip_triggers", False)
skip_procs = options.get("skip_procs", False)
skip_funcs = options.get("skip_funcs", False)
skip_events = options.get("skip_events", False)
skip_grants = options.get("skip_grants", False)
if options.get("all", False):
rows = source.get_all_databases()
for row in rows:
db_list.append(row[0])
# Check user permissions on source for all databases
for db_name in db_list:
source_db = Database(source, db_name)
# Make a dictionary of the options
access_options = {
'skip_views': skip_views,
'skip_procs': skip_procs,
'skip_funcs': skip_funcs,
'skip_grants': skip_grants,
'skip_events': skip_events,
}
source_db.check_read_access(src_val["user"], src_val["host"],
access_options)
for db_name in db_list:
# Get a Database class instance
db = Database(source, db_name, options)
# Error is source database does not exist
if not db.exists():
raise UtilDBError("Source database does not exist - %s" % db_name,
-1, db_name)
if not quiet:
print "# Exporting metadata from %s" % db_name
# Perform the extraction
if format == "sql":
db.init()
# quote database name with backticks
q_db_name = quote_with_backticks(db_name)
if not skip_create:
print "DROP DATABASE IF EXISTS %s;" % q_db_name
print "CREATE DATABASE %s;" % q_db_name
print "USE %s;" % q_db_name
for dbobj in db.get_next_object():
if dbobj[0] == "GRANT" and not skip_grants:
if not quiet:
print "# Grant:"
if dbobj[1][3]:
create_str = "GRANT %s ON %s.%s TO %s;" % \
(dbobj[1][1], q_db_name,
quote_with_backticks(dbobj[1][3]),
dbobj[1][0])
else:
create_str = "GRANT %s ON %s.* TO %s;" % \
(dbobj[1][1], q_db_name, dbobj[1][0])
if create_str.find("%"):
create_str = re.sub("%", "%%", create_str)
print create_str
else:
if not quiet:
print "# %s: %s.%s" % (dbobj[0], db_name, dbobj[1][0])
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
print "DELIMITER ||"
print "%s;" % db.get_create_statement(
db_name, dbobj[1][0], dbobj[0])
if (dbobj[0] == "PROCEDURE" and not skip_procs) or \
(dbobj[0] == "FUNCTION" and not skip_funcs) or \
(dbobj[0] == "EVENT" and not skip_events) or \
(dbobj[0] == "TRIGGER" and not skip_triggers):
print "||"
print "DELIMITER ;"
else:
objects = []
if not skip_tables:
objects.append("TABLE")
if not skip_views:
objects.append("VIEW")
if not skip_triggers:
objects.append("TRIGGER")
if not skip_procs:
objects.append("PROCEDURE")
if not skip_funcs:
objects.append("FUNCTION")
if not skip_events:
objects.append("EVENT")
if not skip_grants:
objects.append("GRANT")
for obj_type in objects:
sys.stdout.write("# %sS in %s:" % (obj_type, db_name))
if format in ('grid', 'vertical'):
rows = db.get_db_objects(obj_type, column_type, True)
else:
rows = db.get_db_objects(obj_type, column_type, True, True)
if len(rows[1]) < 1:
print " (none found)"
else:
print
# Cannot use print_list here becasue we must manipulate
# the behavior of format_tabular_list
list_options = {}
if format == "vertical":
format_vertical_list(sys.stdout, rows[0], rows[1])
elif format == "tab":
list_options['print_header'] = not no_headers
list_options['separator'] = '\t'
format_tabular_list(sys.stdout, rows[0], rows[1],
list_options)
elif format == "csv":
list_options['print_header'] = not no_headers
list_options['separator'] = ','
format_tabular_list(sys.stdout, rows[0], rows[1],
list_options)
else: # default to table format
format_tabular_list(sys.stdout, rows[0], rows[1])
if not quiet:
print "#...done."
return True
