xtrabackup与mysqldump的比较测试
发布时间：2022-01-16 09:53:52 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了xtrabackup与mysqldump的对比测试的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ xtrabackup 版本：xtrabackup version 2.0.8 CPUs ：
|
本篇内容介绍了“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 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
