JezK

Edit File: mysqldbexport

#!/usr/bin/python
#
# Copyright (c) 2010, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#

"""
This file contains the export database utility which allows users to export
metadata for objects in a database and data for tables.
"""

from mysql.utilities.common.tools import check_python_version

# Check Python version compatibility
check_python_version()

import multiprocessing
import os
import shutil
import sys
import tempfile
import time

from mysql.utilities.command.dbexport import export_databases
from mysql.utilities.command.dbexport import multiprocess_db_export_task
from mysql.utilities.common.ip_parser import parse_connection
from mysql.utilities.common.options import (
    add_all, add_character_set_option, add_format_option, add_locking,
    add_no_headers_option, add_regexp, add_rpl_mode, add_rpl_user,
    add_skip_options, add_verbosity, check_all, check_rpl_options,
    check_skip_options, check_verbosity, setup_common_options,
    check_password_security, get_ssl_dict, add_exclude, check_exclude_pattern
)
from mysql.utilities.common.server import connect_servers
from mysql.utilities.common.sql_transform import (is_quoted_with_backticks,
                                                  remove_backtick_quoting)
from mysql.utilities.common.tools import (check_connector_python,
                                          print_elapsed_time)

from mysql.utilities.exception import FormatError
from mysql.utilities.exception import UtilError

# Constants
NAME = "MySQL Utilities - mysqldbexport "
DESCRIPTION = "mysqldbexport - export metadata and data from databases"
USAGE = "%prog --server=user:pass@host:port:socket db1, db2, db3"

_PERMITTED_DISPLAY = ["names", "brief", "full"]
_PERMITTED_EXPORTS = ["data", "definitions", "both"]

# Check for connector/python
if not check_connector_python():
    sys.exit(1)

if __name__ == '__main__':
    # Needed for freeze support to avoid RuntimeError when running as a Windows
    # executable, otherwise ignored.
    multiprocessing.freeze_support()

# Setup the command parser and setup server, help
    parser = setup_common_options(os.path.basename(sys.argv[0]),
                                  DESCRIPTION, USAGE)

# Setup utility-specific options:

# Add character set option
    add_character_set_option(parser)

# Output format
    add_format_option(parser, "display the output in either sql (default), "
                      "grid, tab, csv, or vertical format", "sql", True)

# Display format
    parser.add_option("-d", "--display", action="store", dest="display",
                      default="brief", help="control the number of columns "
                      "shown: 'brief' = minimal columns for object creation "
                      "(default), 'full' = all columns, 'names' = only object "
                      "names (not valid for --format=sql)", type="choice",
                      choices=_PERMITTED_DISPLAY)

# Export mode
    parser.add_option("-e", "--export", action="store", dest="export",
                      default="definitions", help="control the export of "
                      "either 'data' = only the table data for the tables in "
                      "the database list, 'definitions' = export only the "
                      "definitions for the objects in the database list, or "
                      "'both' = export the metadata followed by the data "
                      "(default: export definitions)", type="choice",
                      choices=_PERMITTED_EXPORTS)

# Single insert mode
    parser.add_option("-b", "--bulk-insert", action="store_true",
                      dest="bulk_import", default=False,
                      help="use bulk insert statements for data "
                           "(default:False)")

# No header option
    add_no_headers_option(parser, restricted_formats=['tab', 'csv'])

# Skip blobs for export
    parser.add_option("--skip-blobs", action="store_true", dest="skip_blobs",
                      default=False, help="do not export blob data.")

# File-per-table mode
    parser.add_option("--file-per-table", action="store_true",
                      dest="file_per_tbl", default=False, help="write table "
                      "data to separate files. Valid only for --export=data "
                      "or --export=both.")

# Add the exclude database option
    add_exclude(parser)

# Add the all database options
    add_all(parser, "databases")

# Add the skip common options
    add_skip_options(parser)

# Add verbosity and quiet (silent) mode
    add_verbosity(parser, True)

# Add regexp
    add_regexp(parser)

# Add locking
    add_locking(parser)

# Replication user and password
    add_rpl_user(parser)

# Add replication options
    add_rpl_mode(parser)

parser.add_option("--skip-gtid", action="store_true", default=False,
                      dest="skip_gtid", help="skip creation of GTID_PURGED "
                      "statements.")

# Add comment replication output
    parser.add_option("--comment-rpl", action="store_true", default=False,
                      dest="comment_rpl", help="place the replication "
                      "statements in comment statements. Valid only with "
                      "--rpl option.")

parser.add_option("--skip-fkey-checks", action="store_true", default=False,
                      dest="skip_fkeys", help="skip creation of foreign key "
                      "disable/enable statements.")

# Add multiprocessing option.
    parser.add_option("--multiprocess", action="store", dest="multiprocess",
                      type="int", default="1", help="use multiprocessing, "
                      "number of processes to use for concurrent execution. "
                      "Special values: 0 (number of processes equal to the "
                      "CPUs detected) and 1 (default - no concurrency).")

# Add output file option.
    parser.add_option("--output-file", action="store", dest="output_file",
                      help="path and file name to store the generated output, "
                           "by default the standard output (no file).")

# Now we process the rest of the arguments.
    opt, args = parser.parse_args()

# Check security settings
    check_password_security(opt, args, "# ")

# Warn if quiet and verbosity are both specified
    check_verbosity(opt)

try:
        skips = check_skip_options(opt.skip_objects)
    except UtilError:
        _, err, _ = sys.exc_info()
        print("ERROR: {0}".format(err.errmsg))
        sys.exit(1)

# Fail if no db arguments or all
    if len(args) == 0 and not opt.all:
        parser.error("You must specify at least one database to export or "
                     "use the --all option to export all databases.")

# Check replication options
    check_rpl_options(parser, opt)

# Fail if we have arguments and all databases option listed.
    check_all(parser, opt, args, "databases")

if opt.skip_blobs and not opt.export == "data":
        print("# WARNING: --skip-blobs option ignored for metadata export.")

if opt.file_per_tbl and opt.export in ("definitions", "both"):
        print("# WARNING: --file-per-table option ignored for metadata "
              "export.")

if "data" in skips and opt.export == "data":
        print("ERROR: You cannot use --export=data and --skip-data when "
              "exporting table data.")
        sys.exit(1)

# Process --exclude values to remove unnecessary quotes (when used) in
    # order to avoid further matching issues.
    if opt.exclude:
        # Remove unnecessary outer quotes.
        exclude_list = [pattern.strip("'\"") for pattern in opt.exclude]
    else:
        exclude_list = opt.exclude

# Check for regexp symbols
    check_exclude_pattern(exclude_list, opt.use_regexp)

# Check multiprocessing options.
    if opt.multiprocess < 0:
        parser.error("Number of processes '{0}' must be greater or equal than "
                     "zero.".format(opt.multiprocess))
    num_cpu = multiprocessing.cpu_count()
    if opt.multiprocess > num_cpu and not opt.quiet:
        print("# WARNING: Number of processes '{0}' is greater than the "
              "number of CPUs '{1}'.".format(opt.multiprocess, num_cpu))

# Warning for non-posix (windows) systems if too many process are used.
    num_db = len(args)
    if (os.name != 'posix' and num_db and opt.multiprocess > num_db
            and not opt.quiet):
        print("# WARNING: Number of processes '{0}' is greater than the "
              "number of databases to export '{1}'.".format(opt.multiprocess,
                                                            num_db))

# Check output_file option.
    if opt.output_file:
        # Check if file already exists.
        if os.path.exists(opt.output_file) and not opt.quiet:
            print("# WARNING: Specified output file already exists. The file "
                  "will be overwritten.")
        output_filename = opt.output_file
        try:
            output_file = open(output_filename, 'w')
        except IOError:
            parser.error("Unable to create file (check path and access "
                         "privileges): {0}".format(opt.output_file))
    else:
        # Always send output to a file for performance reasons (contents sent
        # at the end to the stdout).
        output_file = tempfile.NamedTemporaryFile(delete=False)
        output_filename = None

# Set options for database operations.
    options = {
        "skip_tables": "tables" in skips,
        "skip_views": "views" in skips,
        "skip_triggers": "triggers" in skips,
        "skip_procs": "procedures" in skips,
        "skip_funcs": "functions" in skips,
        "skip_events": "events" in skips,
        "skip_grants": "grants" in skips,
        "skip_create": "create_db" in skips,
        "skip_data": "data" in skips,
        "skip_blobs": opt.skip_blobs,
        "skip_fkeys": opt.skip_fkeys,
        "format": opt.format,
        "no_headers": opt.no_headers,
        "display": opt.display,
        "single": not opt.bulk_import,
        "quiet": opt.quiet,
        "verbosity": opt.verbosity,
        "debug": opt.verbosity >= 3,
        "file_per_tbl": opt.file_per_tbl,
        "exclude_patterns": exclude_list,
        "all": opt.all,
        "use_regexp": opt.use_regexp,
        "locking": opt.locking,
        "rpl_user": opt.rpl_user,
        "rpl_mode": opt.rpl_mode,
        "rpl_file": opt.rpl_file,
        "comment_rpl": opt.comment_rpl,
        "export": opt.export,
        "skip_gtid": opt.skip_gtid,
        "charset": opt.charset,
        "multiprocess": num_cpu if opt.multiprocess == 0 else opt.multiprocess,
        "output_filename": output_filename,
    }

# Parse server connection values
    try:
        options.update(get_ssl_dict(opt))
        server_values = parse_connection(opt.server, None, options)
    except FormatError:
        _, err, _ = sys.exc_info()
        parser.error("Server connection values invalid: {0}.".format(err))
    except UtilError:
        _, err, _ = sys.exc_info()
        parser.error("Server connection values invalid: "
                     "{0}.".format(err.errmsg))

# Get the sql_mode set on source and destination server
    conn_opts = {
        'quiet': True,
        'version': "5.1.30",
    }
    try:
        servers = connect_servers(server_values, None, conn_opts)
        server_sql_mode = servers[0].select_variable("SQL_MODE")
    except UtilError:
        server_sql_mode = ''

# Build list of databases to copy
    db_list = []
    for db in args:
        # Remove backtick quotes (handled later)
        db = remove_backtick_quoting(db, server_sql_mode) \
            if is_quoted_with_backticks(db, server_sql_mode) else db
        db_list.append(db)

try:
        # record start time
        if opt.verbosity >= 3:
            start_export_time = time.time()

# Export databases concurrently for non posix systems (windows).
        if options['multiprocess'] > 1 and os.name != 'posix':
            # Create export databases tasks.
            export_db_tasks = []
            for db in db_list:
                export_task = {
                    'srv_con': server_values,
                    'db_list': [db],
                    'options': options,
                }
                export_db_tasks.append(export_task)

# Create process pool.
            workers_pool = multiprocessing.Pool(
                processes=options['multiprocess']
            )

# Concurrently export databases.
            res = workers_pool.map_async(multiprocess_db_export_task,
                                         export_db_tasks)
            workers_pool.close()
            # Get list of temporary files with the exported data.
            tmp_files_list = res.get()
            workers_pool.join()

# Merge resulting temp files (if generated).
            for tmp_filename in tmp_files_list:
                if tmp_filename:
                    tmp_file = open(tmp_filename, 'r')
                    shutil.copyfileobj(tmp_file, output_file)
                    tmp_file.close()
                    os.remove(tmp_filename)
        else:
            # Export all specified databases (no database level concurrency).
            # Note: on POSIX systems multiprocessing is applied at the table
            # level (not database).
            export_databases(server_values, db_list, output_file, options)

if output_filename is None:
            # Dump the export output to the stdout.
            output_file.seek(0)
            shutil.copyfileobj(output_file, sys.stdout)
            output_file.close()
            os.remove(output_file.name)

# record elapsed time
        if opt.verbosity >= 3:
            sys.stdout.flush()
            print_elapsed_time(start_export_time)

except UtilError:
        _, err, _ = sys.exc_info()
        print("ERROR: {0}".format(err.errmsg))
        sys.exit(1)

sys.exit()