开始迁移数据，因为历史问题，也因为部门的数据统一性，很多不同的数据库表在一个节点上，然后很多来自…… 先看看我们数据库的大小...，再加上一个binlog日志，会更大...当然，对于我们的数据迁移来说，我们只需要传输数据即可。既然是迁移， 那么大家一定想实现无缝迁移……首先，在mysqlmaster上做好备份。我们在上海机房搭建一个slave服务节点，拉取文件，然后与master同步……数据校验ok后，修改所有配置ip地址。 老规矩，最近爬虫太狠了，经常爬我的页面，然后作者换成了自己的...http://xiaorui.cc/?p=1755 3.4T,可以说是我见过的数据量最大的一次。我们公司本身是做大数据的，但是大数据基本在hbase，Elasticsearch，solr。可能有人会问，给毛分库分表，因为以前的应用太多了，不可能把每一个select的逻辑都当成分库分表的逻辑查询……其实，最好的办法就是用中间件来进行语句的时间范围切分，但是这也需要开发成本，一些开源的mysql代理，是做不到这个语句切分的，需要一定的二次开发...说起各种开源的mysql中间件，好像还不够强大，不能完美支持statement分库分表..... 废话不多说，这就是我们数据库的规模...[root@bj-buzz-db01ssd]#du-shmysql/.4Tmysql/ mysql备份肯定不会用mysqldump，锁表，必须用Percona备份工具。 XtraBackup有两个工具：xtrabackup和innobackupex： xtrabackup本身只能备份InnoDB和XtraDB，不能备份XtraDBMyISAM； innobackupex本身是从HotBackup脚本修改而来的。可以同时备份MyISAM和InnoDB，但是MyISAM的备份需要读锁。 官网：http://www.percona.com/software/percona-xtrabackup 文档：http://www.percona.com/doc/percona-xtrabackup/2.2/index.html 因为考虑到file如果太大，一定要好好压缩，不然scp和rsync都会很痛苦。 我先用gzip压缩，发现速度有点慢。linuxpipeline的速度没什么好质疑的。。。瓶颈应该在gzip压缩上。。。 innobackupex–defaults-file=/etc/mysql/my.cnf–stream=tar/data/7_15|gzip>/data/7_15.tar.gz 查了percona关于compression的题目，官方推荐使用上面的方法。不过我只是把问题解释清楚了，gzip只是一个单进程应用，怎么跑满CPU的……其实xtrabackup本身也有压缩功能参数。压缩级别范围从0-9.1表示快速压缩，9表示最佳压缩，0表示不压缩。默认为1。 –compress-threads=5#并发压缩线程数，默认为1 –compress-chunk-size=64K#每个压缩线程使用的buffer，默认为64K percona也有加密的功能.... #加密 –encrypt=AES256#启用加密，目前支持的算法有AES128、AES192和AES256 –encrypt-key=3c0efcea569021b49245e47b5d6a0e28#32位密钥,但是不推荐这样做,最好把key存在文件中,使用encrypt-key-file参数参考 –encrypt-threads=5#加密线程数,默认为1 使用tar模式 innobackupex--defaults-file=/etc/mysql/my.cnf--stream=tar--compress./>7_15 使用xbstream innobackupex--defaults-file=/etc/mysql/my.cnf--stream=xbstream/tmp>/backup/bak.xbstream 使用了压缩和多线程，速度好像不是很厉害。help，这个是用来控制Concurrent的。。。但是经过我的测试，效果一点都不明显。说白了，没有任何作用……当然也有可能是我的场景不适合？还是没有正确使用？ 默认情况下，只有在备份一个进程备份数据文件时才会启用xtrabackup。如果配置参数--parallel=N，xtrabackup可以启动N个子进程并发备份多个数据文件，可以加快备份速度。当然，还要考虑服务器的IO处理能力和对服务器的影响，所以还要配合使用另一个参数--throttle=IOS。该参数用于限制备份过程中每秒的IO次数。是一种保护。 innobackupex--defaults-file=/etc/mysql/my.cnf--parallel=10--stream=tar/data/7_15|gzip>/data/7_15.tar.gz 这是最后的办法....既然gzip速度慢，那就用gzip的多线程增强版pigz改造加固吧。..个人觉得–parallel–compress-threads看起来不错，但是性能提升不是很理想.. innobackupex–defaults-file=/etc/mysql/my.cnf–stream=tar/data/7_15|pigz-9-p32>/data/7_15.tar.gz 这是pigz的安装方式.wgethttp://zlib.net/pigz/pigz-2.3.3.tar.gztarzxvfpigz-2.3.3.tar...pigz.opigz.c pigz.c:365:73:error:zlib.h:Nosuchfileordirectory pigz.c:372:4:error:#errorNeedzlibversion1.2.3or后来 pigz.c:Infunction'put_header': pigz.c:1000:error:'Z_DEFAULT_COMPRESSION'undeclared(firstuseinthisfunction) pigz.c:1000:error:(Eachundeclared标识符仅报告一次 pigz.c:1000:error:foreachfunctionitappearsin。在'*'标记之前 pigz.c:Infunction'compress_thread': pigz.c:1495:error:'z_stream'undeclared(firstuseinthisfunction) pigz.c:1495:error:expected';'before'strm' pigz.c:1502:error:'strm'undeclared(firstuseinthisfunction) pigz.c:1502:error:'Z_NULL'undeclared(firstuseinthisfunction) pigz.c:1505:warning:implicitdeclarationoffunction'deflateInit2' pigz.c:1505:error:'Z_DEFLATED'undeclared(firstuseinthisfunction) pigz.c:1505:error:'Z_DEFAULT_STRATEGY'undeclared(firstuseinthisfunction) pigz.c:1506:error:'Z_MEM_ERROR'undeclared(firstuseinthisfunction) pigz.c:1508:error:'Z_OK'undeclared(firstuseinthisfunction) pigz.c:1530:warning:implicitdeclarationoffunction'deflateReset' pigz.c:1531:warning:implicitdeclarationoffunction'deflateParams' pigz.c:1546:警告：隐式声明函数'deflateSetDictionary' pigz.c:1595:warning:implicitdeclarationoffunction'deflate_engine' pigz.c:1595:error:'Z_NO_FLUSH'undeclared(firstuseinthisfunction) pigz.c:1620:错误：'Z_SYNC_FLUSH'undeclared(firstuseinthisfunction) pigz.c:1624:error:'Z_FINISH'undeclared(firstuseinthisfunction) pigz.c:1687:warning:implicitdeclarationoffunction'adler32' pigz.c:1687:warning:implicitdeclarationoffunction'crc32' pigz.c:1706:warning:implicitdeclarationoffunction'deflateEnd' pigz.c:Infunction'write_thread': pigz.c:1737:error:'Z_NULL'undeclared(firstuseinthisfunction) pigz.c:Infunction'single_compress': pigz.c:2039:error:expected'=',',',';','asm'or'__attribute__'before'*'token pigz.c:2039:error:'strm'undeclared(firstuseinthisfunction) pigz.c:2062:error:'z_stream'undeclared(firstuseinthisfunction) pigz.c:2063:error:'Z_NULL'undeclared(firstuseinthisfunction) pigz.c:2066:error:'Z_DEFLATED'undeclared(firstuseinthisfunction) pigz.c:2066:error:'Z_DEFAULT_STRATEGY'undeclared(firstuseinthisfunction) pigz.c:2067:error:'Z_MEM_ERROR'undeclared(firstuseinthisfunction) pigz.c:2069:error:'Z_OK'undeclared(firstuseinthisfunction)) pigz.c:2173:warning:implicitdeclarationoffunction'deflate' pigz.c:2173:error:'Z_NO_FLUSH'undeclared(firstuseinthisfunction) pigz.c:2198:error:'Z_SYNC_FLUSH'undeclared(firstuseinthisfunction) pigz.c:2202:error:'Z_FINISH'undeclared(firstuseinthisfunction) pigz.c:Infunction'infchk': pigz.c:3044:error:'z_stream'undeclared(firstuseinthisfunction) pigz.c:3044:error:expected';'在'strm之前' pigz.c:3054:error:'Z_NULL'undeclared(firstuseinthisfunction) pigz.c:3055:error:'strm'undeclared(firstuseinthisfunction) pigz.c:3058:warning:implicitdeclarationoffunction'inflateBackInit' pigz.c:3059:error:'Z_MEM_ERROR'undeclared(firstuseinthisfunction) pigz.c:3061:error:'Z_OK'未声明（第一次在这个函数中使用） pigz.c:3067:warning:implicitdeclarationoffunction'inflateBack' pigz.c:3068:warning:implicitdeclarationoffunction'inflateBackEnd' pigz.c:3069:error:'Z_DATA_ERROR'undeclared(第一次在这个函数中使用) pigz.c:3072:error:'Z_BUF_ERROR'undeclared(firstuseinthisfunction) pigz.c:3074:error:'Z_STREAM_END'undeclared(firstuseinthisfunction) pigz.c:Infunction'defaults': pigz.c:3828:error:'Z_DEFAULT_COMPRESSION'undeclared(firstuseinthisfunction) make:***[pigz.o]Error1 表示错误，因为有没有zlib开发包的原因.... yum-yinstallzlib-devel 然后我们再用pigz压缩一下，下面是CPU占用情况。 任务：总共446个，运行1个，睡眠444个，停止0个，僵尸1个 Cpu0：69.2%us，2.6%sy，0.0%ni，23.5%id，4.6%wa，0.0%hi，0.0%si,0.0%st Cpu1:45.4%us,1.0%sy,0.0%ni,53.6%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu2:51.0%us,5.0%sy,0.0%ni,29.1%id,14.6%wa,0.0%hi,0.3%si,0.0%st Cpu3:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu4:34.0%us,3.0%sy,0.0%ni,60.4%id,2.6%wa,0.0%hi,0.0%si,0.0%st Cpu5:69.3%us,0.7%sy,0.0%ni,30.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu6:33.4%us,15.4%sy,0.0%ni,27.4%id,17.4%wa,0.0%hi,6.4%si,0.0%st Cpu7:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu8:55.6%us,1.3%sy,0.0%ni,36.8%id,6.3%wa,0.0%hi,0.0%si,0.0%st Cpu9:87.8%us,0.3%sy,0.0%ni,11.9%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu10:99.3%us,0.0%sy,0.0%ni,0.7%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu11:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu12:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu13:99.7%us,0.0%sy,0.0%ni,0.3%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu14:99.7%us,0.0%sy,0.0%ni,0.3%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu15:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu16:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu17:99.7%我们,0.0%sy,0.0%ni,0.3%id,0.0%wa,0.0%hi,0.0%si,0.0%st CPU18:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu19:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu20:99.7%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.3%si,0.0%st Cpu21:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st Cpu22:98.7%us,0.0%sy,0.0%ni,0.3%id,0.0%wa,0.0%hi,1.0%si,0.0%st Cpu23:100.0%us,0.0%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%st 再看盘，没有压力……这里就不贴图了，我只记得贴一下iostat-x的结果1、发现备份已经完成。..下午在xtrabackup备份压缩的过程中，其他访问没有感觉到对性能有什么影响。。。 再看看大小。。。965G。。。压缩效果中规中矩。。。。。消耗的时间是，7:38–1:18，将近6个半小时。.... 然后我在读取mysql数据文件的时候加入了并发，通过--parallel参数让xtrabackup启动了N个子进程来并发备份多个数据文件。 innobackupex–defaults-file=/etc/mysql/my.cnf–user=root–password=xxx–parallel=5–stream=tar/data/7_15_p|pigz-8-p15>/data/7_15_p.tar.gz 先看它的io情况。我们会发现数据盘没有压力，但是备份存储盘io却已经报错了……什么意思呢？说明xtrabackup在添加并发文件读取的时候不仅要考虑mysql数据盘的io，还要考虑目标存储盘的io能否承受。...设备：RRQM/SWRQM/SR/SR/SRSEC/SWSEC/SAVGRQ-SZAVGQUSVCTM％UTILSDA0.0035.000.000.007.000.000.000.000.000.000.00046.860.860.011.431.431.431.0041.004.004.004.004.004.004.0046.0046.0046.004.00.00b00.004.00.00b00.0046.004.00b00.004.00.arbivic000.00328.008.000.235.510.241.00dm-10.000.000.000.000.000.000.000.000.000.000.00dm-20.000.000.000.000.000.000.000.000.000.000.00memdiska0.000.0063.0037.002672.00296.0029.680.010.070.060.60
