xtrabackup與mysqldump的對比測試

本篇內容介紹了“xtrabackup與mysqldump的對比測試”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

xtrabackup

版本：xtrabackup version 2.0.8

CPUs

：24

Innodb buffer pool：200M

結論：xtrabackup占用的CPU與內存較少，消耗的IO相對較大，備份后的文件較大。

MySQL版本：
mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.43-log | 
+------------+
1 row in set (0.00 sec)


xtrabackup版本：
[root@hd-119-186 percona-xtrabackup-2.1.9-Linux-x86_64]#xtrabackup --version
xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 744)


innodb buffer pool為200M：
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 209715200 | 
+-------------------------+-----------+
1 row in set (0.01 sec)


數據量8.87G:
mysql> select sum(data_length/1024/1024/1024) from information_schema.tables;       
+---------------------------------+
| sum(data_length/1024/1024/1024) |
+---------------------------------+
|                  8.872452066280 | 
+---------------------------------+
1 row in set (1.91 sec)


數據文件總大小9947M：
[root@hd-119-186 test]# cd /usr/local/mysql/var/
[root@hd-119-186 var]# du -sm
9947    .


數據文件在根目錄下，對應的盤為/dev/cciss/c0d0p3：
mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/mysql/var/ | 
+---------------+-----------------------+
1 row in set (0.00 sec)


mysql> system df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p3     201G   34G  158G  18% /
/dev/cciss/c0d1p1     3.3T  1.8T  1.4T  57% /oracle
/dev/cciss/c0d0p1     190M   12M  169M   7% /boot
tmpfs                  32G  4.0G   28G  13% /dev/shm

二、xtrabackup全備：

innobackupex --user='root' --password='root' /root/test/mysqlbackup


[root@hd-119-186 ~]# top
top - 17:04:17 up 205 days, 21:47,  5 users,  load average: 0.79, 0.61, 0.60
Tasks: 1197 total,   1 running, 1195 sleeping,   0 stopped,   1 zombie
Cpu(s):  5.6%us,  3.0%sy,  0.0%ni, 89.9%id,  1.3%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  65968524k total, 65744344k used,   224180k free,   187208k buffers
Swap: 65537156k total,  2681584k used, 62855572k free, 58876140k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                        
11016 oracle    19   0 2622m 1.4g 1.4g S 88.8  2.2   1:56.26 oracle                                                                   
 4790 root      15   0  141m 5480 1564 S 62.9  0.0   0:13.66 xtrabackup 
 
nmon：

  Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteMB|0          |25         |50          |75       100||
|iss/c0d0  40%   89.4   90.2|RRRRRRRRRRRWWWWWWWWWW      WWWWWWWWWWW>          ||
|s/c0d0p1   0%    0.0    0.0|>                                                ||
|s/c0d0p2   0%    0.0    0.0|>                                                ||
|s/c0d0p3  41%   89.4   90.2|RRRRRRRRRRRWWWWWWWWWW       WWWWWWWWWW>          ||
|iss/c0d1  65%    0.0    0.3|RRRWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW  >             ||
|s/c0d1p1  65%    0.0  149.3|RRRWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW  >             ||
|Totals Read-MB/s=178.9   1Writes1.4/s=181.0    Transfers/sec=4815.3

iostat -m 2

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.83    0.00    1.71    1.35    0.00   95.11

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0     2550.50        81.00        81.14        162        162
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3   2550.50        81.00        81.14        162        162
cciss/c0d1       28.50         0.00         0.80          0          1
cciss/c0d1p1     28.50         0.00         0.80          0          1

[root@hd-119-186 mysqlbackup]# innobackupex --user='root' --password='root' /root/test/mysqlbackup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

140528 17:25:47  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
140528 17:25:47  innobackupex: Connected to database with mysql child process (pid=6674)
140528 17:25:53  innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (C) 2000-2008 MySQL AB

innobackupex: Created backup directory /root/test/mysqlbackup/2014-05-28_17-25-53
140528 17:25:53  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
140528 17:25:53  innobackupex: Connected to database with mysql child process (pid=6719)
140528 17:25:55  innobackupex: Connection to database server closed

140528 17:25:55  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/root/test/mysqlbackup/2014-05-28_17-25-53 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=6729) to suspend
innobackupex: Suspend file '/root/test/mysqlbackup/2014-05-28_17-25-53/xtrabackup_suspended'

xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/var
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (30236348779)
[01] Copying ./ibdata1 to /root/test/mysqlbackup/2014-05-28_17-25-53/ibdata1
>> log scanned up to (30236348779)
>> log scanned up to (30236350135)
>> log scanned up to (30236350363)
>> log scanned up to (30236350835)
>> log scanned up to (30236354475)
>> log scanned up to (30236357069)
>> log scanned up to (30236358916)
>> log scanned up to (30236360660)
>> log scanned up to (30236367110)
>> log scanned up to (30236372544)
>> log scanned up to (30236383294)
>> log scanned up to (30236389966)
>> log scanned up to (30236399765)
>> log scanned up to (30236399765)
>> log scanned up to (30236403689)
>> log scanned up to (30236408152)
>> log scanned up to (30236417455)
>> log scanned up to (30236418327)
>> log scanned up to (30236418327)
>> log scanned up to (30236421679)
>> log scanned up to (30236427418)
>> log scanned up to (30236435866)
>> log scanned up to (30236439677)
>> log scanned up to (30236456249)
>> log scanned up to (30236461441)
>> log scanned up to (30236462130)
>> log scanned up to (30236462130)
>> log scanned up to (30236463653)
>> log scanned up to (30236464765)
>> log scanned up to (30236465897)
>> log scanned up to (30236465897)
>> log scanned up to (30236467790)
>> log scanned up to (30236468018)
>> log scanned up to (30236468262)
>> log scanned up to (30236469130)
>> log scanned up to (30236469374)
>> log scanned up to (30236469374)
>> log scanned up to (30236469514)
>> log scanned up to (30236470174)
>> log scanned up to (30236470174)
>> log scanned up to (30236470646)
>> log scanned up to (30236471962)
>> log scanned up to (30236472434)
>> log scanned up to (30236472662)
>> log scanned up to (30236472906)
>> log scanned up to (30236473566)
>> log scanned up to (30236475110)
>> log scanned up to (30236475110)
>> log scanned up to (30236475149)
>> log scanned up to (30236475149)
>> log scanned up to (30236476053)
>> log scanned up to (30236476281)
>> log scanned up to (30236476281)
>> log scanned up to (30236476997)
>> log scanned up to (30236477469)
>> log scanned up to (30236478581)
>> log scanned up to (30236478581)
>> log scanned up to (30236479693)
>> log scanned up to (30236481795)
>> log scanned up to (30236482407)
>> log scanned up to (30236482407)
[01]        ...done
xtrabackup: Creating suspend file '/root/test/mysqlbackup/2014-05-28_17-25-53/xtrabackup_suspended' with pid '6729'


140528 17:26:57  innobackupex: Continuing after ibbackup has suspended
140528 17:26:57  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
140528 17:26:57  innobackupex: Connected to database with mysql child process (pid=6804)
>> log scanned up to (30236483529)
>> log scanned up to (30236483773)
140528 17:26:59  innobackupex: Starting to lock all tables...
>> log scanned up to (30236485585)
>> log scanned up to (30236488789)
>> log scanned up to (30236491053)
>> log scanned up to (30236492145)
>> log scanned up to (30236497374)
>> log scanned up to (30236500046)
>> log scanned up to (30236500046)
>> log scanned up to (30236500264)
>> log scanned up to (30236500264)
>> log scanned up to (30236500264)
140528 17:27:09  innobackupex: All tables locked and flushed to disk

140528 17:27:09  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/usr/local/mysql/var'
innobackupex: Backing up files '/usr/local/mysql/var/hdalmonitordb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (82 files)
>> log scanned up to (30236500264)
>> log scanned up to (30236500264)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
>> log scanned up to (30236500274)
innobackupex: Backing up files '/usr/local/mysql/var/rsfmonitordb2/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (19 files)
innobackupex: Backing up files '/usr/local/mysql/var/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (69 files)
innobackupex: Backing up file '/usr/local/mysql/var/zentao/db.opt'
innobackupex: Backing up files '/usr/local/mysql/var/configdb2/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (31 files)
innobackupex: Backing up files '/usr/local/mysql/var/rsfmonitordb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (24 files)
innobackupex: Backing up files '/usr/local/mysql/var/registerdb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (22 files)
>> log scanned up to (30236500274)
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/global_query_review.frm'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review.frm'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review_history.MYD'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/global_query_review_history.frm'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review.MYD'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review_history.MYI'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review.MYI'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/db.opt'
innobackupex: Backing up file '/usr/local/mysql/var/slow_query_log/review_history.frm'
innobackupex: Backing up files '/usr/local/mysql/var/interegdb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (11 files)
innobackupex: Backing up files '/usr/local/mysql/var/registerdb2/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (22 files)
>> log scanned up to (30236500274)
innobackupex: Backing up file '/usr/local/mysql/var/test/t.MYD'
innobackupex: Backing up file '/usr/local/mysql/var/test/sbtest.frm'
innobackupex: Backing up file '/usr/local/mysql/var/test/oauth3_client.frm'
innobackupex: Backing up file '/usr/local/mysql/var/test/t1.frm'
innobackupex: Backing up file '/usr/local/mysql/var/test/oauth3_user.frm'
innobackupex: Backing up file '/usr/local/mysql/var/test/t.MYI'
innobackupex: Backing up file '/usr/local/mysql/var/test/t.frm'
innobackupex: Backing up files '/usr/local/mysql/var/configdb/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (37 files)
innobackupex: Backing up file '/usr/local/mysql/var/huanghaodb1/huanghaott1.frm'
innobackupex: Backing up file '/usr/local/mysql/var/huanghaodb1/huanghaott1.MYI'
innobackupex: Backing up file '/usr/local/mysql/var/huanghaodb1/db.opt'
innobackupex: Backing up file '/usr/local/mysql/var/huanghaodb1/huanghaott1.MYD'
140528 17:27:43  innobackupex: Finished backing up non-InnoDB tables and files

140528 17:27:43  innobackupex: Waiting for log copying to finish

>> log scanned up to (30236500274)
xtrabackup: The latest check point (for incremental): '30236500274'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (30236500274)

xtrabackup: Creating suspend file '/root/test/mysqlbackup/2014-05-28_17-25-53/xtrabackup_suspended' with pid '6729'
140528 17:27:46  innobackupex: All tables unlocked
140528 17:27:46  innobackupex: Waiting for ibbackup (pid=6729) to finish
xtrabackup: Transaction log of lsn (30236346947) to (30236500274) was copied.
140528 17:27:58  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/root/test/mysqlbackup/2014-05-28_17-25-53'
innobackupex: MySQL binlog position: filename 'mysql-bin.000420', position 106
140528 17:27:58  innobackupex: completed OK!

完成時間131秒


三、mysqldump全備：

mysqldump -uroot -proot --single-transaction -q -R --triggers -A > fullbak.sql

[root@hd-119-186 ~]# top
top - 17:14:09 up 205 days, 21:57,  6 users,  load average: 1.19, 0.67, 0.61
Tasks: 1198 total,   4 running, 1194 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.9%us,  0.6%sy,  0.0%ni, 91.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  65968524k total, 65661200k used,   307324k free,   161320k buffers
Swap: 65537156k total,  2681820k used, 62855336k free, 58826876k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                    
 5983 root      25   0 47844 2036 1172 R 99.9  0.0   0:24.44 mysqldump                                              
10145 mysql     15   0  933m 345m 5680 S 99.3  0.5 547:26.78 mysqld  

iostat -m 2

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.91    0.00    1.31    0.02    0.00   91.76

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0      439.00         0.00       127.59          0        255
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3    439.00         0.00       127.59          0        255
cciss/c0d1       11.50         0.00         0.16          0          0
cciss/c0d1p1     11.50         0.00         0.16          0          0

nmon:

+nmon-14g---------------------Hostname=hd-119-186---Refresh= 2secs ---17:15.02
| Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteMB|0          |25         |50          |75       100||
|iss/c0d0   5%    0.0   56.0|RWW          RRRRRRRRRRRRRRRRRRRRRRR            >||
|s/c0d0p1   0%    0.0    0.0|>                                                ||
|s/c0d0p2   0%    0.0    0.0|    >                                            ||
|s/c0d0p3   5%    0.0   56.0|RWW| |       RRRRRRRRRRRRRRRRRRRRRRR            >||
|iss/c0d1   00   0.1|    0.2|W  |        WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW>|
|s/c0d1p1   10   0.1|    0.2|W  2        5| WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW>|
|Totals Read-MB/s=0.0     1Writes80./s=112.3    Transfers/sec=564.6    

[root@hd-119-186 mysqlbackup]# time mysqldump -uroot -proot --single-transaction -q -R --triggers -A > fullbak.sql
real    3m28.993s
user    2m31.339s
sys     0m25.548s

完成時間208秒

兩種備份方式，備份文件大小對比：
[root@hd-119-186 mysqlbackup]# ll 
total 6874492
drwxr-xr-x 15 root root       4096 May 28 17:27 2014-05-28_17-25-53
-rw-r--r--  1 root root 7032594061 May 28 17:33 fullbak.sql

[root@hd-119-186 mysqlbackup]# du -sm 2014-05-28_17-25-53/
9936    2014-05-28_17-25-53/

9936M   xtrabackup
6706M   mysqldump


四、xtrabackup壓縮全備：

innobackupex --socket=/tmp/mysql.sock --user='root' --password='root' --no-timestamp --stream=tar /root/test/mysqlbackup 2>/root/test/mysqlbackup/xbackup.log | gzip 1>/root/test/mysqlbackup/xbackup.tar.gz

[root@hd-119-186 ~]# top
top - 17:56:13 up 205 days, 22:39,  6 users,  load average: 0.69, 0.52, 0.80
Tasks: 1195 total,   2 running, 1192 sleeping,   0 stopped,   1 zombie
Cpu(s):  4.5%us,  0.4%sy,  0.0%ni, 94.9%id,  0.2%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  65968524k total, 61296676k used,  4671848k free,   138112k buffers
Swap: 65537156k total,  2683288k used, 62853868k free, 54543356k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                           
 9080 root      18   0  4044  612  248 R 89.1  0.0   0:46.74 gzip                                                              
 9117 root      18   0  141m 5552 1620 S 17.7  0.0   0:08.87 xtrabackup 
 
[root@hd-119-186 ~]# top
top - 18:01:42 up 205 days, 22:44,  6 users,  load average: 5.34, 2.58, 1.51
Tasks: 1250 total,   2 running, 1248 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.2%us,  0.3%sy,  0.0%ni, 95.2%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  65968524k total, 64078736k used,  1889788k free,   143008k buffers
Swap: 65537156k total,  2682488k used, 62854668k free, 56976976k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                        
 9080 root      25   0  4044  628  248 R 99.9  0.0   5:49.79 gzip                                                   
 9766 root      18   0 76172 1056  888 S  2.6  0.0   0:02.67 tar 

iostat -m 2

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.29    0.00    0.48    0.08    0.00   95.15

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0     1038.50        32.05         1.56         64          3
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3   1038.50        32.05         1.56         64          3
cciss/c0d1        4.50         0.00         0.07          0          0
cciss/c0d1p1      4.50         0.00         0.07          0          0 

.....................................................................
.....................................................................
.....................................................................

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.25    0.00    0.50    0.08    0.00   94.17

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0      138.00        28.50         3.20         57          6
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3    138.00        28.50         3.20         57          6
cciss/c0d1       14.00         0.00         0.20          0          0
cciss/c0d1p1     14.00         0.00         0.20          0          0

nmon：

+nmon-14g------[H for help]---Hostname=hd-119-186---Refresh= 2secs ---08:59.22
| Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteMB|0          |25         |50          |75       100|
|iss/c0d0   6%   30.3    1.5|RRR>  |                                          |
|s/c0d0p1   0%    0.0    0.0|>------------------------------------------------|
|s/c0d0p2   0%    0.0    0.0|>                                                |
|s/c0d0p3   6%   30.3    1.5|RRR>                                             |
|iss/c0d1  10%    0.1    0.3|RWWWW>                                           |
|s/c0d1p1  10%    0.1    0.3|RWWW>                                            |
|Totals Read-MB/s=60.6     Writes-MB/s=3.7      Transfers/sec=2065.8
................................................................................
................................................................................
................................................................................
+nmon-14g------[H for help]---Hostname=hd-119-186---Refresh= 2secs ---17:56.45
| Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteMB|0          |25         |50          |75       100||
|iss/c0d0   2%   26.5    1.7|R           RRRRRRRRRRWWWWWWWWRRRRRRRRRW         >|
|s/c0d0p1   0%    0.0    0.0|>                                                ||
|s/c0d0p2   0%    0.0    0W0|    >                                            ||
|s/c0d0p3   2%   26.5    1.7|RR          RRRRRRRRRRWWWWWWWWRRRRRRRRRW         >|
|iss/c0d1   00   0.3|R   0.2|   WWWWWWWWWWWWWWWWRRRRRRWWWWWWWWWWWWWWWWWWWWWWWW>|
|s/c0d1p1   00   0.3|R   0.2|   WWWWWWWWWWWWWWWWRRRRRRWWWWWWWWWWWWWWWWWWWWWWWW>|
|Totals Read-MB/s=52.9     Wr14.041B0.63.8  .3  Transfers/sec=279.6  


整個備份&壓縮過程，用時：470s

備份后文件總大小：1137M


五、mysqldump壓縮全備：


time mysqldump -uroot -proot --single-transaction -q -R --triggers -A |gzip>fullbak2.sql.gz

[root@hd-119-186 ~]# top
top - 08:47:16 up 206 days, 13:30,  7 users,  load average: 2.08, 1.85, 1.30
Tasks: 1199 total,   2 running, 1197 sleeping,   0 stopped,   0 zombie
Cpu(s): 11.2%us,  0.5%sy,  0.0%ni, 88.1%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  65968524k total, 58951120k used,  7017404k free,   188472k buffers
Swap: 65537156k total,   740944k used, 64796212k free, 52223856k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                        
29568 root      18   0  4044  632  248 R 99.6  0.0   0:30.25 gzip                                                                                           
29567 root      18   0 47844 2036 1172 S 95.6  0.0   0:26.62 mysqldump                                                                                      
10145 mysql     15   0  933m 347m 5792 S 68.6  0.5 618:30.13 mysqld 

iostat -m 2

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.85    0.00    1.10    0.17    0.00   87.88

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0       46.00         0.00         4.06          0          8
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3     46.00         0.00         4.06          0          8
cciss/c0d1     4144.50         0.00        43.06          0         86
cciss/c0d1p1   4144.50         0.00        43.06          0         86
......................................................................
......................................................................
......................................................................
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.55    0.00    3.44    4.23    0.00   73.79

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
cciss/c0d0      495.50        59.01         1.04        118          2
cciss/c0d0p1      0.00         0.00         0.00          0          0
cciss/c0d0p2      0.00         0.00         0.00          0          0
cciss/c0d0p3    495.50        59.01         1.04        118          2
cciss/c0d1     1519.50         0.07        20.65          0         41
cciss/c0d1p1   1519.50         0.07        20.65          0         41

nmon：

+nmon-14g---------------------Hostname=hd-119-186---Refresh= 2secs ---08:52.51
| Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteKB|0          |25         |50          |75       100|
|iss/c0d0   0%    0.0 4259.5|>     |                                          |
|s/c0d0p1   0%    0.0    0.0|>------------------------------------------------|
|s/c0d0p2   0%    0.0    0.0|>                                                |
|s/c0d0p3   0%    0.0 4259.572.0                                              |
|iss/c0d1   1%    2.0  207.7|R R                                              |
|s/c0d1p1   1%    2.0  207.7|R R                                              |
|Totals Read-MB/s=0.0      Writes-MB/s=8.7      Transfers/sec=82.9 
...............................................................................
...............................................................................
...............................................................................
nmon-14g------[H for help]---Hostname=hd-119-186---Refresh= 2secs ---09:14.26
| Disk I/O --/proc/diskstats----mostly in KB/s-----Warning:contains duplicates
|DiskName Busy  Read WriteKB|0          |25         |50          |75       100|
|iss/c0d0  99%  973.5   99.7|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRWWWW>
|s/c0d0p1   0%    0.0    0.0|>------------------------------------------------|
|s/c0d0p2   0%    0.0    0.0|>                                                |
|s/c0d0p3  99%  973.5   99.7|RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRWWWW>
|iss/c0d1  61% 1029.4 4967.5RWWWWRRWWRWWWWWWWWWWWWWWWWWWWWWW             >    |
|s/c0d1p1  61% 1029.4 4967.5RWWWWRRWWRWWWWWWWWWWWWWWWWWWWWWW             >    |
|Totals Read-MB/s=3.9      Writes-MB/s=9.9      Transfers/sec=982.5 

完成時間342秒

[root@hd-119-186 mysqlbackup]# time mysqldump -uroot -proot --single-transaction -q -R --triggers -A |gzip>fullbak2.sql.gz

real    5m42.572s
user    6m31.244s
sys     0m24.523s             

備份文件大小694M：
[root@hd-119-186 mysqlbackup]# ll
drwxr-xr-x 15 root root       4096 May 28 17:27 2014-05-28_17-25-53
-rw-r--r--  1 root root  727427417 May 29 08:50 fullbak2.sql.gz
-rw-r--r--  1 root root 7032594061 May 28 17:33 fullbak.sql
-rw-r--r--  1 root root      22505 May 28 18:03 xbackup.log
-rw-r--r--  1 root root 1192070606 May 28 18:03 xbackup.tar.gz

“xtrabackup與mysqldump的對比測試”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！