mysqldbcopy

mysqldbcopy在实例之间复制库

$ ./mysqldbcopy --help
Usage: mysqldbcopy --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
  --source=SOURCE       connection information for source server in the form:
                        <user>:<password>@<host>:<port>:<socket>
  --destination=DESTINATION
                        connection information for destination server in the
                        form: <user>:<password>@<host>:<port>:<socket>
  -f, --force           drop the new database or object if it exists
  --threads=THREADS     use multiple threads (connections) for insert
  -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 source server
                        as the master, 'slave' = include the CHANGE MASTER
                        command using the destination server's master
                        information.
  --skip-gtid           skip creation and execution of GTID statements during
                        copy.

eg:instance 1的所有对象及记录cp到instance 2中,库名改为luoxuan_cp,因为–locking默认是获得一致性读,不锁表
instance 1:

$ ./mysqldbcopy --source=root@localhost:3306 --destination=root@localhost:3506  luoxuan:luoxuan_cp
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database luoxuan renamed as luoxuan_cp
# Copying TABLE luoxuan.test_4
# Copying PROCEDURE luoxuan.sp_test_4
# Copying PROCEDURE luoxuan.sp_test_select
# Copying data for TABLE luoxuan.test_4
#...done.

instance 2:

root@(none) 03:59:22>use luoxuan_cp
Database changed
root@luoxuan_cp 03:59:44>show tables;
+----------------------+
| Tables_in_luoxuan_cp |
+----------------------+
| test_4               |
+----------------------+
1 row in set (0.00 sec)
 
root@luoxuan_cp 03:59:46>select * from test_4 limit 1;
+---+---+------+---+
| a | b | c    | d |
+---+---+------+---+
| 1 | 1 | 0    | 1 |
+---+---+------+---+

注意:
1、如果存在非事务的表,为了一致性,需要–locking=lock-all,但会加WRITE locks,读都不行

eg2:建先有master的备库 3406做为3506的主库,脚本执行完后,3506就会指向3406

$ sudo ./mysqldbcopy --source=root@localhost:3406 --destination=root@localhost:3506 --all --rpl=slave --rpl-user=slave:slave --force
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Including all databases.
# Copying database luoxuan 
# Copying TABLE luoxuan.test_4
# Copying PROCEDURE luoxuan.sp_test_4
# Copying PROCEDURE luoxuan.sp_test_select
# Copying database xiyan 
# Copying data for TABLE luoxuan.test_4
# Connecting to the current server's master
#...done.

注意:master-info-file来指定master.info文件名及位置,因为我这边环境master.info是放在/u01/mysql2/log目标下,这个应该代码问题,可以fixed

$ sudo ./mysqldbcopy --source=root@localhost:3406 --destination=root@localhost:3506 --all --rpl=slave --rpl-user=slave:slave 
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Including all databases.
ERROR: Cannot find master information file: /u01/mysql2/data/master.info.

因为要复制库已经存在,必须–force才能跑起

$ sudo ./mysqldbcopy --source=root@localhost:3406 --destination=root@localhost:3506 --all --rpl=slave --rpl-user=slave:slave
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Including all databases.
# Copying database luoxuan 
# Copying TABLE luoxuan.test_4
# Copying PROCEDURE luoxuan.sp_test_4
# Copying PROCEDURE luoxuan.sp_test_select
# Copying database test 
ERROR: destination database exists. Use --force to overwrite existing database.

Post a Comment