Comparative Test of xtrabackup and mysqldump 03/26 Update SLTechnology News&Howtos

Comparative Test of xtrabackup and mysqldump

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the Comparative Test of xtrabackup and mysqldump". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Xtrabackup

Version: xtrabackup version 2.0.8

CPUs

: 24

Innodb buffer pool:200M

Xtrabackup

Xtrabackup compressed backup

Mysqldump

Mysqldump compressed backup

CPU

62.9%

105%

200 per cent

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% (within 64m)

0.005% (within 64m)

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

Conclusion: xtrabackup occupies less CPU and memory, consumes relatively large IO, and the files after backup are larger.

MySQL version:

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.1.43-log |

+-+

1 row in set (0.00 sec)

Xtrabackup version:

[root@hd-119-186percona-xtrabackup-2.1.9-Linux-x86_64] # xtrabackup--version

Xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86 for Percona Server 64) (revision id: 744)

Innodb buffer pool is 200m:

Mysql > show variables like 'innodb_buffer_pool_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_size | 209715200 | |

+-+ +

1 row in set (0.01 sec)

Data volume 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)

Total data file size 9947m:

[root@hd-119-186 test] # cd / usr/local/mysql/var/

[root@hd-119-186var] # du-sm

9947.

The data file is in the root directory, and the corresponding disk is / 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

2. Xtrabackup is complete:

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

[root@hd-119-186a] # 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 take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates

| | DiskName Busy Read WriteMB | 0 | 25 | 50 | 75100 |

| | iss/c0d0 40% 89.4 90.2 | RRRRRRRRRRRWWWWWWWWWW WWWWWWWWWWW > |

| | s/c0d0p1 0%, 0.0% | > |

| | s/c0d0p2 0%, 0.0% | > |

| | s/c0d0p3 41% 89.4 90.2 | RRRRRRRRRRRWWWWWWWWWW WWWWWWWWWW > |

| | iss/c0d1 65% 0.00.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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

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-186mysqlbackup] # innobackupex-- user='root'-- password='root' / root/test/mysqlbackup

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: Created backup directory / root/test/mysqlbackup/2014-05-288 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-28th 17-25-53-- tmpdir=/tmp

Innobackupex: Waiting for ibbackup (pid=6729) to suspend

Innobackupex: Suspend file'/ root/test/mysqlbackup/2014-05-28 October 17-25-53 Universe xtrabackupply suspended`

Xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86 for Percona Server 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 (30236403689)

> > log scanned up to (30236408152)

> > log scanned up to (30236417455)

> > 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 (30236463653)

> > log scanned up to (30236464765)

> > 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 (30236469514)

> > 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 (30236475149)

> > log scanned up to (30236476053)

> > log scanned up to (30236476281)

> > log scanned up to (30236476997)

> > log scanned up to (30236477469)

> > log scanned up to (30236478581)

> > log scanned up to (30236479693)

> > log scanned up to (30236481795)

> > log scanned up to (30236482407)

[01]... done

Xtrabackup: Creating suspend file'/ root/test/mysqlbackup/2014-05-289 17-25-53 xtrabackupply 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 (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 (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-289 17-25-53 xtrabackupply 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!

Completion time 131 seconds

3. Mysqldump is complete:

Mysqldump-uroot-proot-- single-transaction-Q-R-- triggers-A > fullbak.sql

[root@hd-119-186a] # 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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

Cciss/c0d0p3 439.00 0.00 127.59 0 255

Cciss/c0d1 11.50 0.00 0.16 00

Cciss/c0d1p1 11.50 0.00 0.16 00

Nmon:

+ nmon-14g-Hostname=hd-119-186murmurmurafure = 2secs-1715. 02

| | Disk I take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates |

| | DiskName Busy Read WriteMB | 0 | 25 | 50 | 75100 |

| | iss/c0d0 5% 0.056.0 | RWW RRRRRRRRRRRRRRRRRRRRRRR > |

| | s/c0d0p1 0%, 0.0% | > |

| | s/c0d0p2 0%, 0.0% | > |

| | iss/c0d1 00 0.1 | 0.2 | W | WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW > | |

| | s/c0d1p1 100.1 | 0.2 | W25 | WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW > | |

| | Totals Read-MB/s=0.0 1Writes80./s=112.3 Transfers/sec=564.6 |

[root@hd-119-186mysqlbackup] # time mysqldump-uroot-proot-- single-transaction-Q-R-- triggers-A > fullbak.sql

Real 3m28.993s

User 2m31.339s

Sys 0m25.548s

Completion time 208 seconds

Comparison of backup file size between the two backup methods:

[root@hd-119-186 mysqlbackup] # ll

Total 6874492

Drwxr-xr-x 15 root root 4096 May 28 17:27 2014-05-289 17-25-53

-rw-r--r-- 1 root root 7032594061 May 28 17:33 fullbak.sql

[root@hd-119-186mysqlbackup] # du-sm 2014-05-288 17-25-53 /

9936 2014-05-28 17-25-53 /

9936M xtrabackup

6706M mysqldump

4. Xtrabackup compression is complete:

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-186a] # 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-186a] # 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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

Cciss/c0d0p3 1038.50 32.05 1.56 64 3

Cciss/c0d1 4.50 0.00 0.07 00

Cciss/c0d1p1 4.50 0.00 0.07 00

....

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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

Cciss/c0d0p3 138.00 28.50 3.20 57 6

Cciss/c0d1 14.00 0.00 0.20 00

Cciss/c0d1p1 14.00 0.00 0.20 00

Nmon:

+ nmon-14g- [H for help]-Hostname=hd-119-186 Murray Murray rewards = 2secs-08VR 59.22

| | Disk I take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates |

| | DiskName Busy Read WriteMB | 0 | 25 | 50 | 75100 | |

| | iss/c0d0 6% 30.3 1.5 | RRR > |

| | s/c0d0p1 0%, 0.0% | >-- | |

| | s/c0d0p2 0%, 0.0% | > | |

| | s/c0d0p3 6% 30.3 1.5 | RRR > | |

| | Totals Read-MB/s=60.6 Writes-MB/s=3.7 Transfers/sec=2065.8 |

....

+ nmon-14g- [H for help]-Hostname=hd-119-186 Murmurmuri rewards = 2secs-17VOV 56.45

| | Disk I take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates |

| | DiskName Busy Read WriteMB | 0 | 25 | 50 | 75100 |

| | s/c0d0p1 0%, 0.0% | > |

| | s/c0d0p2 0% 0.0 0W0 | > |

| | s/c0d0p3 2% 26.5 1.7 | RR RRRRRRRRRRWWWWWWWWRRRRRRRRRW > |

| | Totals Read-MB/s=52.9 Wr14.041B0.63.8. 3 Transfers/sec=279.6 |

The whole backup-compression process, time: 470s

Total file size after backup: 1137m

5. Mysqldump compression is complete:

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

[root@hd-119-186a] # top

Top-08:47:16 up 206days, 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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

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 00

Cciss/c0d0p2 0.00 0.00 0.00 00

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-186murmurmurafure = 2secs-08Rose 52.51

| | Disk I take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates |

| | DiskName Busy Read WriteKB | 0 | 25 | 50 | 75100 | |

| | iss/c0d0 0% 0.0 4259.5 | > |

| | s/c0d0p1 0%, 0.0% | >-- | |

| | s/c0d0p2 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-186Murray Murray Retreat = 2secs-09RV 14.26

| | Disk I take O-/ proc/diskstats----mostly in KB/s-Warning:contains duplicates |

| | DiskName Busy Read WriteKB | 0 | 25 | 50 | 75100 | |

| | iss/c0d0 99% 973.5 99.7 | RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRWWWW > |

| | s/c0d0p1 0%, 0.0% | >-- | |

| | s/c0d0p2 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 |

The completion time is 342 seconds

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

Real 5m42.572s

User 6m31.244s

Sys 0m24.523s

Backup file size 694m:

[root@hd-119-186 mysqlbackup] # ll

Drwxr-xr-x 15 root root 4096 May 28 17:27 2014-05-289 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

That's all for the comparison Test between xtrabackup and mysqldump. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.