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的对比测试"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！