千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

xtrabackup与mysqldump的对比测试

发表于：2024-12-13 作者：千家信息网编辑
千家信息网最后更新 2024年12月13日，本篇内容介绍了"xtrabackup与mysqldump的对比测试"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能
千家信息网最后更新 2024年12月13日xtrabackup与mysqldump的对比测试

本篇内容介绍了"xtrabackup与mysqldump的对比测试"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

xtrabackup

版本：xtrabackup version 2.0.8

CPUs

：24

Innodb buffer pool：200M

xtrabackup

xtrabackup压缩备份

mysqldump

mysqldump压缩备份

CPU

62.9%

105%

200%

260%

Disk IO

iostat tps 2550

nmon busy 41%

iostat tps 1038->138

nmon busy 6%->2%

iostat tps 450

nmon busy 5%

iostat tps 46->495

nmon busy 1-99%

Memory

0.0% (64M以内)

0.00% (64M以内)

0.05% (322M)

0.05% (322M)

Main processes

xtrabackup

xtrabackup&gzip&tar

mysqldump & mysqld

mysqldump& mysqld & gzip

Backup file size

9936M

1137M

6706M

694M

Time

131s

470s

208s

342s













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

很赞哦！
备份 文件 大小 全备 数据 时间 版本 测试 较大 内容 更多 知识 过程 实用 学有所成 接下来 内存 困境 实际 情况 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 软考数据库工程师怎么复习 软件开发需要哪些东西 海陵区个性化网络技术多少钱 数据库怎么技术调用次数 贵阳软件开发公司都有哪些 天刀万雪窟在哪个服务器 榆树智能网络技术服务参考价格 创建数据库 主键 用服务器架设网站 互联网软件开发工程师发展 火车头采集如何保存尽数据库 慕课网络安全与支付测试答案 rem清理系统图标数据库 佛山app软件开发方案 长度测量结果传输给服务器 收到的软件开发费如何入账 2005数据库执行有误 中国病原体大数据库 少年三国志服务器有哪些 温州达内时代科技集团互联网 软件开发需求管理软件设计 用友u8安装数据库连接不成功 网络安全 本科专业 当前网络技术如何发展 青少年网络安全竞赛 查看数据库编码方式命令 数据库快捷键是什么 网络安全法规定国家保护 中标网络安全产业基地 幻塔几个服务器互通吗

相关文章

0