Back up a MySQL instance of about 250G "Xtrabackup backup scenario Comparison"

back up a MySQL instance of about 250GA. Using Xtrabackup Backup and gzip single-threaded compression

Time Innobackupex--user=test--password=test--socket=/home/mysqld.sock--slave-info--defaults-file=/etc/mysql/my . CNF--stream=tar/home/backup | gzip > Mysql-backup.tgz

Real 271m15.984s
User 275m44.840s
SYS 6m34.393s

Summarize:

Backup time: 6 hours 30 minutes or more
Occupying space: 87G
CPU occupied: CPU with a single core is full

Disadvantage: Long backup time, a single core CPU is full, the recovery is time-consuming decompression, because the backup time window is longer, so the apply-log time is also relatively longer
Advantages: High Compression ratio

B. Using concurrent compression with Xtrabackup

Real 8m56.112s
User 36m18.179s
SYS 4m1.395s

Summarize:

Backup time: About 10 minutes
Occupying space: 123G
CPU usage: No core CPU is full

Disadvantage: Compression ratio is not high, recovery needs to solve two times the package
Advantages: Fast backup speed, CPU core will not be full, recovery time is short, because the backup window is short, so Apply-log will be completed soon

Recovery method:

1. Unlock the Xbstream file
Time Xbstream-c/home/data/backup/-x-v < Mysql-backup.xbstream

Real 8m3.373s
User 2m49.465s
SYS 3m31.498s

2. unzip the compressed QP file

CD /home/data/backup/

Time Innobackupex--decompress--parallel=32.
Real 6m24.422s
User 17m45.832s
SYS 15m17.286s

3.apply Log

CD /home/data/backup/

Innobackupex--apply-log./

4.copy back

CD /home/data/backup/

Innobackupex--copy-back--defaults-file=/etc/mysql/my.cnf.

This step if use copy-back words will take time, there is a time-saving method is to directly apply the data MV to the need to restore the directory (transient)

For example: cd/home/data/backup/&& mv./*/home/mysqlrecover/data/

Restore time: About 25 minutes

It is worthwhile to sacrifice some space for the time to change ....

Back up a MySQL instance of about 250G "Xtrabackup backup scenario Comparison"