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>