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
Copy after login
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
Copy after login
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
