MySql Innodb存储引擎--备份和优化

 

备份的目的

做灾难恢复:对损坏的数据进行恢复和还原

需求改变:因需求改变而需要把数据还原到改变以前

测试:测试新功能是否可用

 

备份需要考虑的问题

可以容忍丢失多长时间的数据;

恢复数据要在多长时间内完; 

恢复的时候是否需要持续提供服务;

恢复的对象,是整个库,多个表,还是单个库,单个表。

 

备份的类型

1、根据是否需要数据库离线

冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;

温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;

热备(hot backup):备份的同时,业务不受影响。

注:

1、这种类型的备份,取决于业务的需求,而不是备份工具

2、MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具

2、根据要备份的数据集合的范围

完全备份:full backup,备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。

差异备份:differential backup 上次完全备份以来改变了的数据。

建议的恢复策略:

完全+增量+二进制日志

完全+差异+二进制日志 

 

备份的对象

1、 数据;

2、配置文件;

3、代码:存储过程、存储函数、触发器

4、os相关的配置文件

5、复制相关的配置

6、二进制日志

 

 

 

 

mysqldump基本语法

mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql使用root用户备份test数据库下的person表

mysqldump -u root -p test person > D:\backup.sql

 

mysqlimport

mysqlimport [options] db_name textfile1 [textfile2 ...]
--同时导入两个文件
mysqlimport --use-threads=2 test /home/mysql/t.txt /home/mysql/s.txt

 

 

select into , load data, source

SELECT ... FROM TABLE_A
INTO OUTFILE "/path/to/file"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE "/path/to/file" INTO TABLE table_name;
注意:如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句,那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。
mysql>use dbtest;
mysql>set names utf8;
mysql>source D:/www/sql/back.sql;

 

mysqlbinlog

mysqlbinlog [options] log_file ...
--将xxx.binlog.000001 导入到c盘
mysqlbinlog --start-position=4 --stop-position=106 xxx_binglog.000001 > c:\\test1.txt

 

热备工具

ibbackup

XtraBackup

 

其他

LVM快照备份,通过文件系统的快照来备份

replication备份

replication+LVM备份

 

复制replication是MySql提供的一种高可用解决方案,复制的原理

1.主服务器master把数据更改近路到二进制日志binlog中

2.从服务器slave把主服务器的二进制日志复制到自己的中继日志relay log中

3.从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性

需要注意的是因为延迟复制不能达到实时同步


 

如果在主服务器上DBA做了一些误删除操作,那么从服务器也一样会同步这些操作,这样错误的操作也会被同步就达不到备份的目的了,所以用复制+快照来实现更好的备份


 

 

 

 

 

 

性能优化

OLTP在线事务处理对CPU要求不高但要更多内存

OLAP在线分析处理需要更多的查询和连接对CPU要求高 

 

当内存超过了数据量总大小时,性能会直线上升,但继续增加内存效果就不明显了

inodb_read相关参数

SHOW GLOBAL STATUS LIKE 'innodb%read%';

innodb_buffer_pool_reads 表示从物理磁盘读取页的次数

innodb_buffer_pool_read_ahead 预读的次数

innodb_buffer_pool_read_ahead_evicted 预读的页,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读取的效率

innodb_buffer_pool_read_requests 从缓冲池中读取页的次数

innodb_data_read 总共读入的字节数

innodb_data_reads 发起读取请求的次数,每次读取可能需要读取多少页

 

可以将多块盘组成RAID来提供性能和高可用,也可以用SSD提供性能

RAID-0性能最好,但是安全性最低


 

RAID-1 安全性最好,但是利用率最低


 

RAID-5综合了RAID-0和RAID-1的特点,加入了奇偶校验,将盘的校验数据放入其他盘,这样即使坏掉已坏盘也不怕


 

RAID10和RAID01则综合了RAID1和RAID0的特点

RAID10有更好的读取速度,RAID01比RAID10有更好的写入速度,但是安全性要差一点,现在一般用的是

RAID10比较多,此外还有RAID50


 

 

RAID卡 write-through和write-back模式

Write-Through 模式: 

1. DB向Cell发送一个写请求, cellsrv进行验证确认其请求有效; 

2. cellsrv将发送指令将其写入到物理磁盘; 

3. 写完成以后,给DB确认已经写成功; 

4. cellsrv判断次数据是否适合缓存到cache中,如果满足条件则缓存,否则不缓存。

Write-Back 模式: 

1. DB向Cell发送一个写请求, cellsrv进行验证确认其请求有效; 

2. cellsrv将发送指令将其写入到磁盘Cache; 

3. 将此数据的状态置为dirty的状态。(直到下次临界条件将脏数据刷新到磁盘,并判断此数据是否适合缓存,如果不适合,刷新完成以后会丢弃,刷新和缓存过程是异步的,并不在步骤3来完成) 

4. 写完成以后,给DB确认已经写成功;


这两种模式相比,明显write-back效率要高很多,因为每次都是写入到RAID卡的缓存中,再异步刷新到磁盘上,RAID卡也带了备用电池,所以当开启电池后可以安全的使用这种模式,当电池在充电或者没电的时候就会使用write-through模式

 

 

不同的操作系统,文件系统对性能影响并不大

两个基准测试工具

sysbench

mysql-tpcc

 

 

 

 

 

参考

MySQL select into outfile用法

使用mysqlimport 将格式化文件导入mysql表

MySQL 数据备份与还原

[InnoDB系列] -- 实测ibbackup vs mysqldump

使用Xtrabackup来备份你的mysql 

MySQL实验室DMB数据库监控及灾备系统 之 [备份模式的选择]

Cache写策略——write-through与write-back

sysbench安装、使用、结果解读

[Mysql高可用]——双主互备+keepalived

基于keepalived 实现VIP转移,lvs,nginx的高可用

VRRP虚拟路由器冗余协议

针对SSD的MySQL IO优化

mysql优化参数

Innodb存储引擎的编译和调试

 

MYSQL备份与恢复》之 Innodb与 MyISAM引擎 一、系统环境 1.1 ubuntu 12.0.4 X86_64 1.2 percona-xtrabackup-2.0.3.tar.gz 1.3 xtrabackup简介 xtrabackup是一个对InnoDB数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。 xtrabackup有两个主要的工具:xtrabackup、innobackupex (1)xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表 (2)innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的,innobackupex是一个perl脚本封装,封装了xtrabackup,所以能同时备份处理innodb和myisam,但在处理myisam时需要加一个读锁。并且加入了一些使用的选项,如slave-info可以记录备份恢复后,作为slave需要的一些信息,根据这些信息,可以很方便的利用备份来重做slave。 (3)使用帮助:http://www.percona.com/docs/wiki/percona-xtrabackup:start  (4)Xtrabackup可以做什么   在线(热)备份整个库的InnoDB、 XtraDB表   在xtrabackup的上一次整库备份基础上做增量备份(innodb only)   以流的形式产生备份,可以直接保存到远程机器上(本机硬盘空间不足时很有用)   MySQL数据库本身提供的工具并不支持真正的增量备份,二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。Xtrabackup工具支持对InnoDB存储引擎的增量备份,工作原理如下: xtrabackup备份原理   xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的data file,由于不锁表,复制出来的数据是不一致的,在恢复的时候使用crash-recovery,使得数据恢复一致。   InnoDB维护了一个redo log,又称为transaction log,事务日志,它包含了innodb数据的所有改动情况。当InnoDB启动的时候,它会先去检查data file和transaction log,并且会做二步操作:   xtraBackup在备份的时候, 一页一页地复制innodb的数据,而且不锁定表,与此同时,XtraBackup还有另外一个线程监视着transactions log,一旦log发生变化,就把变化过的log pages复制走。为什么要急着复制走呢? 因为transactions log文件大小有限,写满之后,就会从头再开始写,所以新数据可能会覆盖到旧的数据。   在prepare过程,XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值