Summary of mydumper/myloader usage

mydumper installation:

mydumper principle:

myloader principle:

1. mydumper backup

1. Start 16 backup processes, back up all libraries and binary log files to the specified directory /docker/mydumper/all_backup下

##mydumper -u root -p root123 --threads=16 -o /docker/mydumper/all_backup

The --threads=16 here specifies the number of 16 backup threads to be enabled. This can be seen by showing processlist in MySQL.

After the backup is completed, each table will have two files:

One is the table structure file database.table-schema.sql;

One is the table data file database .table.sql.

In addition, there is a binary log file metadata.

$

cat metadata

Started dump at: 2017-06-15 10:57:24

SHOW MASTER STATUS:

Log: mysql-bin.000025

Pos: 681395159

GTID:6fbc72af-348e-11e7-865c-fa163e5807c3:1-22,

ad9b2529-348d-11e7 -bef0-fa163e9e3c14:1-2663607

SHOW SLAVE STATUS:

Host: 10.157.24.87

Log: mysql-bin.000047

POS: 144635857

## This GTID: 6FBC72AF-348E-11E7-865C-FA163E5807C3: 1-22,

#AD9B2529-348D-11E7-FA163C14: 1-2663607

Finished dump at: 2017-06-15 10:58:50

Note: If the output directory is not specified: mydumper -u root - p root123

The export-20170615-105920 directory is automatically generated by default (export-year, month, day-hour, minute and second)

2. Back up all libraries Go to the specified folder and compress the backup file (this backup compression process will consume too much time)

mydumper -u root -p root123 -c -o /docker/mydumper/all_backup_compress

will compress both table structure files and table data files, leaving the binary log files unchanged. As shown below, the file size is compressed from 17G to 7.7G.

root@dpsvstadbs05 11:20:02:mydumper$

du -sh *

17G

all_backup

7.7G

all_backup_compress

3. Back up the table structure of all tables in the sampson library, without backing up data, to the specified output directory

/docker/ mydumper/sampson-d下mydumper -u root -p root123 -B sampson -d -o /docker/mydumper/sampson-d

There are only metadata and sampson.table-schema.sql files in the /docker/mydumper/sampson-d directory.

4. Back up the data of all tables in the sampson library, without backing up the table structure, to the specified output directory

/docker/mydumper/sampson-m 下mydumper -u root -p root123 -B sampson -m -o /docker/mydumper/sampson-m

Then there are only metadata and sampson.table.sql files in the /docker/mydumper/sampson-m directory.

5. Back up all dsns and t1 tables of the sampson library to the specified output directory

/docker/mydumper/sampson-T下mydumper -u root -p root123 -B sampson -T dsns,t1 -o /docker/mydumper/sampson-T

then/ There are metadata and sampson database files as well as backup files of dsns and t1 tables in the docker/mydumper/sampson-T directory.

Note: If the backup needs to run in the background, you can add --daemon to execute the export command in the background.

## 2.

myloader

Recovery

##1. Restore the sampson library from the complete directory:

myloader -u root -p root123 --threads=16 --database =sampson -d /docker/mydumper/all_backup

2. Restore the sampson library table structure to

## from the directory where only the table structure was backed up. #sampson-d library

：

myloader -u root -p root123 --database=sampson-d -d /docker/mydumper/sampson-d

3. Restore the sampson library from the complete folder

to the samp library:

myloader -u root -p root123 --database=samp --source-db=sampson -d /docker/mydumper/all_backup

Note: It is recommended to add parameter -v 3 when backing up and restoring to display detailed logs.

root@dpsvstadbs05 12:13:35:mydumper$ myloader -u root -p root123  -d /docker/mydumper/all_backup --database=huihui --source-db=lizhi -v 3

** Message: 4 threads created

** Message: Creating database `huihui`

** Message: Creating table `huihui`.`my1`

** Message: Creating table `huihui`.`my2`

** Message: Creating table `huihui`.`my3`

** Message: Creating table `huihui`.`my4`

** Message: Creating table `huihui`.`my5`

** Message: Creating table `huihui`.`my6`

** Message: Creating table `huihui`.`my7`

** Message: Creating table `huihui`.`t1`

** Message: Thread 1 restoring `lizhi`.`my1` part 0

** Message: Thread 3 restoring `lizhi`.`my2` part 0

** Message: Thread 2 restoring `lizhi`.`my3` part 0

** Message: Thread 4 restoring `lizhi`.`my4` part 0

** Message: Thread 3 restoring `lizhi`.`my5` part 0

** Message: Thread 1 restoring `lizhi`.`my6` part 0

** Message: Thread 4 restoring `lizhi`.`my7` part 0

** Message: Thread 2 restoring `lizhi`.`t1` part 0

** Message: Thread 2 shutting down

** Message: Thread 1 shutting down

** Message: Thread 4 shutting down

** Message: Thread 3 shutting down

 

附：

 

mydumper参数详解

 

$ mydumper --helpUsage:
  mydumper [OPTION...] multi-threaded MySQL dumping

Help Options:  -?, --help                  Show help optionsApplication Options:  -B, --database              要备份的数据库，不指定则备份所有库
  -T, --tables-list           需要备份的表，名字用逗号隔开
  -o, --outputdir             备份文件输出的目录
  -s, --statement-size        生成的insert语句的字节数，默认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     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
  -x, --regex                 Regular expression for 'db.table' matching 使用正则表达式匹配'db.table'
  -i, --ignore-engines        Comma delimited list of storage engines to 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备份存储过程和函数
  -k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致
  --less-locking              Minimize locking time on InnoDB tables.减少对InnoDB表的锁施加时间
  -l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
  -K, --kill-long-queries     Kill long running queries (instead of aborting)杀掉长查询 (不退出)
  -D, --daemon                Enable daemon mode启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备
  -I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下
  -L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
  --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            使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
  --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
  -h, --host                  连接的主机名
  -u, --user                  用来备份的用户名
  -p, --password              用户密码
  -P, --port                  连接端口
  -S, --socket                使用socket通信时的socket文件
  -t, --threads               开启的备份线程数，默认是4
  -C, --compress-protocol     压缩与mysql通信的数据
  -V, --version               显示版本号
  -v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

 

myloader参数详解

 

$ myloader --helpUsage:
  myloader [OPTION...] multi-threaded MySQL loader

Help Options:  -?, --help                        Show help optionsApplication Options:  -d, --directory                   Directory of the dump to import之前备份好的现在需要导入的文件夹
  -q, --queries-per-transaction     Number of queries per transaction, default 1000每次事物执行的查询数量，默认是1000
  -o, --overwrite-tables            Drop tables if they already exist如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
  -B, --database                    An alternative database to restore into还原到指定的数据库
  -s, --source-db                   Database to restore选择被还原的数据库，将这个数据库数据还原到--database指定的数据库里
  -e, --enable-binlog               Enable binary logging of the restore data启用还原数据的二进制日志
  -h, --host                        连接的主机名
  -u, --user                        用来备份的用户名
  -p, --password                    用户密码
  -P, --port                        连接端口
  -S, --socket                      使用socket通信时的socket文件
  -t, --threads                     开启的备份线程数，默认是4
  -C, --compress-protocol           压缩与mysql通信的数据
  -V, --version                     显示版本号
  -v, --verbose                     输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

 

The above is the detailed content of Summary of mydumper/myloader usage. For more information, please follow other related articles on the PHP Chinese website!