MySQL Utilities (mysqldbcopy)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqldbcopy copy databases from one server to another

DB全体をコピーする為のコマンド
もちろんDump&Restoreでも可能だが、場合によっては簡単にコピー出来るので
検証環境にデータコピーするなどに便利かと。(個人情報等を含む場合は、データの入れ替えが必要だが..)

mysqluc> mysqldbcopy --help
MySQL Utilities mysqldbcopy.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqldbcopy.exe --source=user:pass@host:port:socket --destination=user:pass@host:port:socket orig_db:new_db

mysqldbcopy - copy databases from one server to another

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --source=SOURCE       connection information for source server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --destination=DESTINATION
                        connection information for destination server in the
                        form: <user>[:<password>]@<host>[:<port>][:<socket>]
                        or <login-path>[:<port>][:<socket>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -d, --drop-first      drop the new database or object if it exists
  -x EXCLUDE, --exclude=EXCLUDE
                        exclude one or more objects from the operation using
                        either a specific name (e.g. db1.t1), a LIKE pattern
                        (e.g. db1.t% or db%.%) or a REGEXP search pattern. To
                        use a REGEXP search pattern for all exclusions, you
                        must also specify the --regexp option. Repeat the
                        --exclude option for multiple exclusions.
  -a, --all             include all databases
  --skip=SKIP_OBJECTS   specify objects to skip in the operation in the form
                        of a comma-separated list (no spaces). Valid values =
                        tables, views, triggers, procedures, functions,
                        events, grants, data, create_db
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  --new-storage-engine=NEW_ENGINE
                        change all tables to use this storage engine if
                        storage engine exists on the destination.
  --default-storage-engine=DEF_ENGINE
                        change all tables to use this storage engine if the
                        original storage engine does not exist on the
                        destination.
  --locking=LOCKING     choose the lock type for the operation: no-locks = do
                        not use any table locks, lock-all = use table locks
                        but no transaction and no consistent read, snaphot
                        (default): consistent read using a single transaction.
  -G, --basic-regexp, --regexp
                        use 'REGEXP' operator to match pattern. Default is to
                        use 'LIKE'.
  --rpl-user=RPL_USER   the user and password for the replication user
                        requirement, in the form: <user>[:<password>] or
                        <login-path>. E.g. rpl:passwd - By default = none
  --rpl=RPL_MODE, --replication=RPL_MODE
                        include replication information. Choices: 'master' =
                        include the CHANGE MASTER command using the source
                        server as the master, 'slave' = include the CHANGE
                        MASTER command for the source server's master (only
                        works if the source server is a slave).
  --skip-gtid           skip creation and execution of GTID statements during
                        copy.
  --multiprocess=MULTIPROCESS
                        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).

mysqluc>

■データコピー前の確認とデータコピー後の確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> 
mysqluc> mysqldbcopy --source=root:password@localhost --destination=root:password@localhost test:copy_test
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database test renamed as copy_test
# Copying TABLE test.db8
# Copying TABLE test.language
# Copying TABLE test.lck
# Copying TABLE test.montable
# Copying TABLE test.t
# Copying data for TABLE test.db8
# Copying data for TABLE test.language
# Copying data for TABLE test.lck
# Copying data for TABLE test.montable
# Copying data for TABLE test.t
#...done.

mysqluc>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| copy_test          |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql>

■データベースオブジェクトやデータの確認

mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| db8            |
| language       |
| lck            |
| montable       |
| t              |
+----------------+
5 rows in set (0.00 sec)

mysql> select count(*) from language;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> use copy_test
Database changed
mysql> show tables;
+---------------------+
| Tables_in_copy_test |
+---------------------+
| db8                 |
| language            |
| lck                 |
| montable            |
| t                   |
+---------------------+
5 rows in set (0.00 sec)

mysql> select count(*) from language;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql>

■リモートサーバーにコピーした時など、mysqldiffやmysqldbcompareを利用してDBが同じかどうか確認出来る。

mysqluc> mysqldiff --help
MySQL Utilities mysqldiff.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqldiff.exe --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

mysqldiff - compare object definitions among objects where the difference is
how db1.obj1 differs from db2.obj2

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server1=SERVER1     connection information for first server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --server2=SERVER2     connection information for second server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  --width=WIDTH         display width
  --force               do not abort when a diff test fails
  -c, --compact         compact output from a diff.
  --skip-table-options  skip check of all table options (e.g., AUTO_INCREMENT,
                        ENGINE, CHARSET, etc.).
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -d DIFFTYPE, --difftype=DIFFTYPE
                        display differences in context format in one of the
                        following formats: [unified|context|differ|sql]
                        (default: unified).
  --changes-for=CHANGES_FOR
                        specify the server to show transformations to match
                        the other server. For example, to see the
                        transformation for transforming server1 to match
                        server2, use --changes-for=server1. Valid values are
                        'server1' or 'server2'. The default is 'server1'.
  --show-reverse        produce a transformation report containing the SQL
                        statements to transform the object definitions
                        specified in reverse. For example if --changes-for is
                        set to server1, also generate the transformation for
                        server2. Note: the reverse changes are annotated and
                        marked as comments.

mysqluc>
mysqluc> mysqldbcompare --help
MySQL Utilities mysqldbcompare.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqldbcompare.exe --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1:db2

mysqldbcompare - compare databases for consistency

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server1=SERVER1     connection information for first server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --server2=SERVER2     connection information for second server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format
  --skip-checksum-table
                        skip CHECKSUM TABLE step in data consistency check.
  --skip-object-compare
                        skip object comparison step.
  --skip-row-count      skip row count step.
  --skip-diff           skip the object diff step.
  --skip-data-check     skip data consistency check.
  --skip-table-options  skip check of all table options (e.g., AUTO_INCREMENT,
                        ENGINE, CHARSET, etc.).
  --width=WIDTH         display width
  -a, --run-all-tests   do not abort when a diff test fails
  -c, --compact         compact output from a diff.
  --disable-binary-logging
                        turn binary logging off during operation if enabled
                        (SQL_LOG_BIN=1). Note: may require SUPER privilege.
                        Prevents compare operations from being written to the
                        binary log.
  --span-key-size=SPAN_KEY_SIZE
                        changes the size of the key used for compare table
                        contents. A higher value can help to get more accurate
                        results comparing large databases, but may slow the
                        algorithm. Default value is 8.
  --use-indexes=USE_INDEXES
                        for each table, indicate which index to use as if were
                        a primary key (each of his columns must not allow null
                        values).
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -d DIFFTYPE, --difftype=DIFFTYPE
                        display differences in context format in one of the
                        following formats: [unified|context|differ|sql]
                        (default: unified).
  --changes-for=CHANGES_FOR
                        specify the server to show transformations to match
                        the other server. For example, to see the
                        transformation for transforming server1 to match
                        server2, use --changes-for=server1. Valid values are
                        'server1' or 'server2'. The default is 'server1'.
  --show-reverse        produce a transformation report containing the SQL
                        statements to transform the object definitions
                        specified in reverse. For example if --changes-for is
                        set to server1, also generate the transformation for
                        server2. Note: the reverse changes are annotated and
                        marked as comments.

mysqluc>

Comments are closed.

Post Navigation