mydumper的使用

mydumper是支持多线程工作的mysql逻辑导数工具

安装
yum install -y  /mydumper-0.9.5-2.el6.x86_64.rpm

参数
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

版本查看
mydumper -V
mydumper 0.9.5, built against MySQL 5.7.21-21

默认用4个线程导数，一般企业服务器建议8以上，非事务引擎会锁表，建议用slave导出，使用压缩

备份全库
mydumper -u root -p 'xxx'  -h xxx  -o /xxx/gw

备份指定数据库
mydumper -u root -p 'xxx'  -h xxx  -B gw -t 6 -o /xxx/gw

备份多张表(tableA,tableB)
mydumper -u root -p 'xxx'  -h xxx  -B gw -T testA,testB -t 6 -o /xxx/gw

备份tableA表的数据，并进行压缩
mydumper -u root -p 'xxx'  -h xxx  -B gw -T testA -t 6 -c -o /xxx/gw

关闭写入日志到二进制中。主从不会复制
myloader -u root -p 'xxx' -h xxx -B gw -o -t 4 -v 3 -d /xxx/gw/ -S /db/mysql_3306/mysql.sock

开启 -e
myloader -u root -p 'xxx' -h xxx -B gw -o -t 4 -v 3 -e -d /xxx/gw/ -S /db/mysql_3306/mysql.sock

全备，并备份二进制日志文件
mydumper -u root -p 'xxx' -h xxx -o /xxx/gw

备份表，不备份表结构
mydumper -u root -p 'xxx' -h xxx -B gw -T testA -m -o /xxx/gw

指定备份数据库：备份mysql和test库
mydumper -u root -p 'xxx'  -h xxx -P 3306 -t 3 -c -l 3600 -s 10000000 -e --regex '^(mysql\.|test\.)' -o /xxx/gw

不备份以test开头的库
--regex '^(?!(test))'