![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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>