Shulou(Shulou.com)06/01 Report--

The following mainly brings you testing MySQL logical physical backup tutorial, I hope these contents can bring you practical use, this is also my main purpose of editing testing MySQL logical physical backup tutorial. All right, don't talk too much nonsense, let's just read the following.

MySQL logical physical backup test

Catalogue

Logical backup

Mysqldump normal backup

Mysqlpump parallel backup

Mysqlpump compressed parallel backup

Mydumper parallel backup

Mydumper parallel compressed backup

Summary

Physical backup

Xtrabackup compressed backup

Xtrabackup parallel compressed backup

Innobackupex parallel compressed backup

Innobackupex xbstream streaming backup

Innobackupex xbstream streaming compressed parallel backup

Innobackupex tar streaming backup

Innobackupex tar streaming compressed backup

Innobackupex tar streaming compressed parallel backup

Summary

Logical backup

Mysqldump normal backup

Time mysqldump-uadmin-padmin-h227.0.0.1-p3306-routines-- set-gtid-purged = off-- triggers-- events-- single-transaction-- master-data = 2-- all-databases > / data/compressed/backup.sqlreal 0m19.349s user 0m3.879s sys 0m0.951sdu-sh / data/compressed/ & & rm-rf / data/compressed/*

362m / data / compression /

Mysqlpump parallel backup

Time mysqlpump-uadmin-padmin-h227.0.0.1-p3306-- set-gtid-purged = off-- triggers-- default-parallelism = 4-- routines-- events-- single-transaction-- all-databases > / data/compressed/backup.sql

The real 0m7.714s

User 0m7.149s

Sys 0m0.611s

Du-sh / data/compressed/

213m / data / compression /

Time lz4_decompress / data/compressed/backup.sql / data/backup.sql ll-h / data/backup.sql-rw-r-r-muri-1 root root 363m sep 16 19:53 / data/backup.sql

Mydumper parallel backup

Time mydumper-u admin-p admin-h 127.0.0.1-p 3306-- routines-- triggers-t 4-- events-o / data / compressed

The real 0m7.582s

User 0m1.810s

Sys 0m0.715s

Du-sh / data/compressed/

119 m / data / compression /

Be careful

Mydumper backup mysql gtid is stored separately in the metafile cat / data / compressed / metadata start dump:2018-09-16 17:33:32 show master status:log:mysql-bin.000002 pos:47557983 gtid:898db7fe-f80a-11e7-b8d4-fa163efd7144:1-75 complete dump: 2018-09-16 17:33:45

Summary

For logical backup of 2.3g datadir, the shortest time is mysqlpump parallel backup (7s), and the smallest space is mydumper parallel compressed backup (119m). For small databases, mysqldpump or mydumper parallel backup can be used, and the backup speed is better than mysqldump. Physical backup xtrabackup-version xtrabackup version 2.4.8ll / usr / bin / innobackupex lrwxrwxrwx. 1 root root 10 jan 22 2018 / usr / bin / innobackupex-> xtrabackup innobackupex is a soft link to xtrabackup, du-sh / opt/mysql/data/3306/2.3g / opt/mysql/data/3306/ xtrabackup compressed backup removed in the percona-xtrabackup8.0 version? 1time xtrabackup-- defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-- socket=/opt/mysql/data/3306/mysqld.sock-- backup-- compress-- target-dir=/data/compressed/ real 0m44.597s user 0m4.905s sys 0m1.562sdu-sh / data/compressed/675m / data / compression / yum-y install qpresstime xtrabackup-- decompress-- target-dir=/data/compressed/ real 0m28.961s user 0m4.767s sys 0m5.995sxtrabackup parallel compressed backup? 1time xtrabackup-defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-socket=/opt/mysql/data/3306/mysqld.sock-backup-compress--compress-threads=4-parallel=4-target-dir=/data/compressed/

The real 0m31.813s

User 0m5.159s

Sys 0m1.803s

Du-sh / data/compressed/

673m / data / compression /

Time xtrabackup-decompress-parallel=4-target-dir=/data/compressed/

The real 0m24.684s

User 0m5.384s

Sys 0m8.028s

Innobackupex parallel compressed backup? 1time / usr/bin/innobackupex-defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-socket=/opt/mysql/data/3306/mysqld.sock-compress--compress-threads=4-parallel=4 / data/compressed/

The real 0m29.864s

User 0m4.680s

Sys 0m1.460s

Innobackupex xbstream streaming backup mkdir-p / data/backuptime innobackupex-- defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-- socket=/opt/mysql/data/3306/mysqld.sock-- stream=xbstream / data/backup/ > / data/backup/backup.xbstream

The real 0m42.509s

User 0m0.947s

Sys 0m2.642s

Du-ch / data/backup/

1.9g / data / backup /

A total of 1.9 g

Time xbstream-x / data/backup/backup.xbstream

The real 0m35.992s

User 0m4.988s

Sys 0m1.909s

Du-sh / data/backup/

672m / data / backup /

Innobackupex tar streaming backup

Time innobackupex-defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-socket=/opt/mysql/data/3306/mysqld.sock-stream=tar / data/backup/ > / data/backup/out.tar

Actual 0m40.338s

User 0m0.641s

Sys 0m2.726s

Du-sh / data/backup/

1.9g / data / backup /

Innobackupex tar streaming compressed backup

Time innobackupex-- defaults-file=/opt/mysql/etc/3306/my.cnf-uadmin-padmin-- socket=/opt/mysql/data/3306/mysqld.sock-- stream=tar / data/backup/ | gzip > / data/backup/out.tar.gz

The real 1m47.964s

User 1m19.120s

Sys 0m2.883s

Du-ch / data/backup/476m / data/backup/ innobackupex tar streaming compressed parallel backup is invalid time innobackupex-- defaults-file=/opt/mysql/etc/3306/my.cnf-- parallel=4-uadmin-padmin-- socket=/opt/mysql/data/3306/mysqld.sock-- stream=tar / data/backup/ | gzip > / data/backup/out.tar.gz

The real 1m50.192s

User 1m18.925s

Sys 0m2.768s

Xtrabackup: using o_direct

Innodb: number of pools: 1

Xtrabackup:warning:-the parallel option has no effect when streaming in the "tar" format. You can use the 'xbstream' format.

Summary

For physical backup of 2.3 g datadir, the shortest backup time was innobackupex compressed parallel backup (30s) and xtrabackup compressed parallel backup (31s), followed by innobackupex xbstream streaming compressed parallel backup (35s), innobackupex tar streaming compressed backup (476m) and innobackupex tar streaming backup did not support parallel backup.

If you care more about backup time, xtrabackup / innobackupex compressed parallel backup is recommended. If you care more about backup space, innobackupex tar streaming compressed backup is recommended.

Large amount of data is suitable for physical backup

For the above about testing MySQL logical physical backup tutorial, you do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.