MySQL备份恢复
2017-12-30 本文已影响0人
显卡hg
备份重要性
- 重要性
- 备份是DBA的救命稻草
- 没有备份,就没有复活的机会
- 备份也可以让数据回档到某一时刻
- 误区
- 备份占用太多的资源,也不能促进生产
- 可以考虑利用备份恢复生产,提高应用的可用性
- 备份就可以随便找个地方丢,丢了也无所谓
备份场景
- 功能开发环境
- 配置类型库
- 测试环境
- 性能监控相关的数据
- 生产环境
- 几乎全备
mysqldump
- 重要命令
- mysqldump --master-data=2 --single-transaction -A > db-`data+%Y%M%d`.sql
- 命名规则:dbname-port-日期.sql
- 重要参数
- order-by-primary MyISAM>InnoDB
- opt 默认带的一个参数
- dump-slave 从库上dump数据
- set-gtid-purged=OFF|ON|AUTO
- mysqldump支持where条件导出
- mysqldump --where='1=1 limit 10000' dbname > dbname_1w.sql
select outfile + awk
- select id,col1 from zst01.tb1 where id < 1000 into outfile '/tmp/zst01_tb1.sql';
- update zst01.tb1 set col1='abc' where id <1000;
- cat /tmp/zst01_tb1.sql|awk '{print "update zst01.tb1 set col1="$2," where id="$1";"}'>roll_zst01_tb1.sql
- mysql zst01<roll_zst01_tb1.sql
- 5.5,5.6,5.7版本并且binlog是row格式可以使用binlog2sql命令还原
mysqldump,mysqlpump原理
- flush tables; #可以拿到metadata lock
- flush tables with read lock; #整个实例只读
- set session transaction isolation repeatable read; #可重复读
- start transaction /*!40100 with consistent snapshot */; #开启事务快照,/*!NNNNN */的意思是大于NNN版本的才生效中间的语句,否则不生效
- show @@global.gtid_executed;
- show master status;
- dump non-InnoDB tables,首先导出非事务引擎表
- unlock tables;
- show databases;
- show create database if not exists 'dbname';
- savepoint sp; #还原点,特殊语法,如果下面东西出错可以回到这个点上
- show table status like 'tb_xx';
- set session character_set_results='binary'; #以二进制格式存储表结构
- show create table 'tb_xx';
- set session character_set_results='utf8'; #以utf8格式备份表数据
- select /*!40100 SQL_NO_CACHE */ * from tb_xx;
- rollback to savepoint sp; #回到断点处,备份下一张表
- ...
- 最后是:rollback to savepoint sp; RELEASE SAVEPOINT sp #释放断点
mydumper
- 特性
- 并行,高性能的MySQL逻辑导出导入工具
- 非常方便管理导出的文件(文件拆分成单表储存,metadata信息)
- 导入时支持正则表达式
- 实现过程
- 主线程flush tables with read lock,施加全局只读锁,以阻止DML语句写入,保证数据的一致性
- 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中,义工即使点恢复使用
- N个(线程数可以指定,默认是4)dump线程start transaction with consistent snapshot;开启读一致的事务
- dump non-InnoDB tables,首先导出非事务引擎表
- 主线程unlock tables非事务引擎备份完后,释放全局只读锁
- dump InnoDB tables;基于事务导出InnoDB表 #这个地方之前也有一个savepoint sp,和mysqldump一样
- 事务结束
如何从一个mysqldump文件中提取其中一个表
- sed -n -e '/CREATE TABLE.*`tb_name`,/UNLOCK_TABLES/p' db_bak.sql > tb_name.sql
- 还有其他方法吗
- 可以用权限管理
- grant all privileges on db.tb1 to 'hetan'@'127.0.0.1' identified by 'hetan';
- mysql -f -h127.0.0.1 -uhetan -p db<db_bak.sql
- 这样就只恢复tb1这张表,而不会恢复别的表,因为这个用户只有tb1的权限,恢复时要加-f,强制执行,跳过报错
InnoDB Crash Recovery
innobase crash recovery
crash
innodb-plugin fast crash recovery
- LSN :Log Sequence Number
- 日志的唯一编号
- 在物理日志文件中存储位置(byte)
- Log sequence number:当前最新的LSN
- Log flushed up to :已经写入Redo中的LSN
- Pages flushed up to:buffer pool中获得脏页上的最小的LSN
- Last checkpoint at :数据文件中持久化到的LSN
- show engine innodb status\G
LOG
---
Log sequence number 2565545
Log flushed up to 2565545
Pages flushed up to 2565545
Last checkpoint at 2565536
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
- 测试redo文件定义是不是够一个小时使用
- pager grep sequence
- show engine innodb status\G #lsn1
- select sleep(60);
- show engine innodb status\G #lsn2
- select (lsn2-lsn1)/1024/1024*60 as MB_per_hour;
xtrabackup
- 安装
- 下载rpm包,yum install 安装
xtrabackup备份
- 全备
- 大于2.3版本innobackupex是xtrabackup软连接
- innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql.sock -uroot -pmysql /data/backup/
- xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf --backup S /tmp/mysql.sock -uroot -pmysql --target-dir=/data/backup/
- 特别注意
- 备份时要加上配置文件,不加配置文件备份的就是默认的实例,也就是/etc/my.cnf的实例的数据库,所以要加上--defaults-file参数
- 恢复
- innobackupex --apply-log /data/backup/2017-06-04_11-45-43/ #指定redo应用完毕,之后再恢复
- 需要恢复的MySQL需要关闭
- datadir清空
- 手工将apply的文件copy过去,或是innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --copy-back /data/backup/2017-06-04_11-45-43/
- 更改copy过去的权限
- 启动MySQL
xtrabackup实现原理
- xtrabackup_log就是复制的redo_log,在备份文件中是xtrabackup_logfile,redo的复制起点就是last checkpoint at的值,就是show engine innodb status中的值
- xtrabackup_binlog_info的值是show master status产生的
- xtrabackup_binlog_pos_innodb的值是xtrabackup_logfile中的,也就是binlog最后写回到redo中的binlog文件名和position,也可以理解为事务最后一次提交的位置
xtrabackup增量原理
- xtrabackup_info文件中有个值innodb_to_lsn,在ibd文件中只复制比这个值大的page
- ibdata1全拷贝
