mydumper使用參考

mydumper導(dǎo)出和導(dǎo)入有諸多優(yōu)點，特別是在并行方面，網(wǎng)上文章很多，不做過多解釋，不過隨著mysqlpump的發(fā)布和增強(qiáng)，mydumper在并行方面優(yōu)勢將不復(fù)存在，
以下為mydumper的日常使用，僅記錄做參考：
mydumper下載地址，直接下載rpm包進(jìn)行安裝：
https://github.com/maxbube/mydumper/releases

以下下載地址已經(jīng)不推薦：
https://launchpad.net/mydumper

rpm -ivh mydumper-0.9.5-2.el7.x86_64.rpm
查看安裝后的文件路徑：
[root@db1 soft]# rpm -ql mydumper
/usr/bin/mydumper
/usr/bin/myloader

導(dǎo)出指定的庫：
mydumper -u root -h localhost -p MYsql#123 -e -B app --threads=18 -o /root/backup1/ --logfile=mydumper20180108.log
恢復(fù)導(dǎo)入：
myloader -u root -p MYsql#123 -h localhost -P 3306 -B app -d /root/backup/  --threads=8

導(dǎo)出指定的表：
mydumper -u root -h localhost -p MYsql#123 -e -B app -T t_bz_sys_users,t_bz_sys_gallery -o /root/backup1/

mydumper導(dǎo)出的文件分為如下幾部分：
database_name-schema-create.sql  創(chuàng)建數(shù)據(jù)庫腳本；
database_name.table_name-schema.sql  創(chuàng)建表的腳本
database_name.table_name.sql   具體的數(shù)據(jù)插入sql語句
metadata  SHOW MASTER STATUS信息，相當(dāng)于mysqldump加上--single-transaction --master-data

mydumper和myloader詳細(xì)的使用參數(shù)請加help：
[root@db2 backup]# mydumper --help
Usage:
  mydumper [OPTION?] multi-threaded MySQL dumping

Help Options:
  -?, --help                  Show help options

Application Options:
  -B, --database              Database to dump
  -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
  -O, --omit-from-file        File containing a list of database.table entries to skip, one per line (skips before applying regex option)
  -o, --outputdir             Directory to output files to
  -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
  -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
  -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
  -c, --compress              Compress output files
  -e, --build-empty-files     Build dump files even if no data available from table
  -x, --regex                 Regular expression for 'db.table' matching
  -i, --ignore-engines        Comma delimited list of storage engines to ignore
  -N, --insert-ignore         Dump rows with INSERT IGNORE
  -m, --no-schemas            Do not dump table schemas with the data
  -d, --no-data               Do not dump table data
  -G, --triggers              Dump triggers
  -E, --events                Dump events
  -R, --routines              Dump stored procedures and functions
  -W, --no-views              Do not dump VIEWs
  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
  --no-backup-locks           Do not use Percona backup locks
  --less-locking              Minimize locking time on InnoDB tables.
  -l, --long-query-guard      Set long query timer in seconds, default 60
  -K, --kill-long-queries     Kill long running queries (instead of aborting)
  -D, --daemon                Enable daemon mode
  -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
  -L, --logfile               Log file name to use, by default stdout is used
  --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
  --skip-tz-utc               
  --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
  --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
  --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
  -U, --updated-since         Use Update_time to dump only tables updated in the last U days
  --trx-consistency-only      Transactional consistency only
  --complete-insert           Use complete INSERT statements that include column names
  -h, --host                  The host to connect to
  -u, --user                  Username with the necessary privileges
  -p, --password              User password
  -a, --ask-password          Prompt For User password
  -P, --port                  TCP/IP port to connect to
  -S, --socket                UNIX domain socket file to use for connection
  -t, --threads               Number of threads to use, default 4
  -C, --compress-protocol     Use compression on the MySQL connection
  -V, --version               Show the program version and exit
  -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  --defaults-file             Use a specific defaults file
  --ssl                       Connect using SSL
  --key                       The path name to the key file
  --cert                      The path name to the certificate file
  --ca                        The path name to the certificate authority file
  --capath                    The path name to a directory that contains trusted SSL CA certificates in PEM format
  --cipher                    A list of permissible ciphers to use for SSL encryption


[root@db2 backup]# myloader --help
Usage:
  myloader [OPTION?] multi-threaded MySQL loader

Help Options:
  -?, --help                        Show help options

Application Options:
  -d, --directory                   Directory of the dump to import
  -q, --queries-per-transaction     Number of queries per transaction, default 1000
  -o, --overwrite-tables            Drop tables if they already exist
  -B, --database                    An alternative database to restore into
  -s, --source-db                   Database to restore
  -e, --enable-binlog               Enable binary logging of the restore data
  -h, --host                        The host to connect to
  -u, --user                        Username with the necessary privileges
  -p, --password                    User password
  -a, --ask-password                Prompt For User password
  -P, --port                        TCP/IP port to connect to
  -S, --socket                      UNIX domain socket file to use for connection
  -t, --threads                     Number of threads to use, default 4
  -C, --compress-protocol           Use compression on the MySQL connection
  -V, --version                     Show the program version and exit
  -v, --verbose                     Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  --defaults-file                   Use a specific defaults file