MySQL备份恢复基础知识及MySQLdump讲解

2017-11-15 11:12:35来源:http://arm2012.blog.51cto.com/2418467/1980832作者:人点击

分享

数据库备份恢复知识要点:


时间轴备份类型分类:

完全备份:备份整个数据集

增量备份:上一次完全备份,或上一次增量备份以后变化的数据的备份(还原麻烦,节省空间)

差异备份:仅备份最近一次完全备份以来变化的数据(还原简单,空间消耗大)

什么是物理备份、逻辑备份:

物理备份:直接复制数据文件进行备份(有可能占用更多的空间,备份速度快,做热备较难)

逻辑备份:从数据库中导出数据“另存为”而进行的备份(从二进制转化为文本格式,有可能丢失精度,需要专门的协议客户端才能进行,和数据存储引擎无关、备份恢复时间较长、做热备容易)

应该备份什么?

① 数据

② 二进制日志、innodb的事务日志

③ 代码(存储过程、存储函数、触发器、事件调度器)

④ 服务器的配置文件

备份工具:

① MySQLdump 逻辑备份工具

② cp、tar 物理备份工具,冷备

③ lvm2的快照:几乎热备(请求施加全局持锁),借助于文件系统管理工具进行备份

④ mysqlhotcopy :几乎冷备,仅适用于myisam存储引擎

备份方案的选择:

方案一:MySQLdump+复制binlog(时间较慢,支持远程备份)

MySQLdump:完全备份

复制binlog中指定时间范围的event:增量备份

方案二:lvm2快照+复制binlog

lvm2快照:使用cp或tar等做物理备份:完全备份

复制binlog中指定时间范围的event:增量备份

方案三:XtraBackup

由percona提供的支持对innodb做热备(物理备份)的工具

支持完全备份,增量备份

MySQLdump备份工具讲解:


什么是MySQL的dump?

mysql或mariadb自带客户端命令,逻辑备份工具。是图形化备份管理工具mydumper、phpmyadmin的备份调用工具。基于mysql客户端协议,适用于所有存储引擎,温备:完全备份、部分备份。对innodb支持热备(时间较长)适合备份较小的数据库(GB以下).

对常见存储引擎支持:

InnoDB:热备或温备;

MyISAM:温备;

备份机制:

通过mysql协议连接至mysql服务器。向mysql服务器发起一个全量查询操作,把所有数据拿到本地以后,并且将读取到的数据保存在文件中从而完成备份。

库:CREATE DATABASE

表:CREATE TABLE

数据:INSERT INTO

使用方法Usage:

① mysqldump [OPTIONS] database [tables]# 备份单库,可以只备份其中的一部分表(部分备份,需手动创建数据库);

② mysqldump [OPTIONS] -B/--databases [OPTIONS] DB1 [DB2 DB3...] # 备份多库(推荐使用);

③ mysqldump [OPTIONS] -A/--all-databases [OPTIONS] # 备份所有库;

初级备份实验示例:

① 备份:mysqldump -uroot -p -B test > test.sql

② 登录mysql删除test库:drop database test;

③ 恢复:mysql -uroot -p <test.sql

MySQLdump使用进阶:


实际生产使用中,需要考虑到备份时长、备份精度、备份过程对线上体验的影响、从完全备份后产生的数据恢复方法等,本节介绍MySQLdump针对主流存储引擎额外选项,对事务记录,以及演示生产使用MySQLdump。

MyISAM存储引擎选项:支持温备,备份时要锁定表;

-x, --lock-all-tables:锁定所有库的所有表,读锁;

-l, --lock-tables:锁定指定库所有表;

InnoDB存储引擎:支持温备和热备;

--single-transaction:创建一个事务,基于此快照执行备份;后续要跟崩溃后恢复操作

其它选项:

-R, --routines:备份指定库的存储过程和存储函数;

--triggers:备份指定库的触发器;

-E, --events:备份指定数据库相关的所有event scheduler;

--master-data[=#]

1:记录为CHANGE MASTER TO语句,此语句不被注释;

2:记录为CHANGE MASTER TO语句,此语句被注释;

--flush-logs:锁定表完成后,即进行日志滚动操作;

MySQLdump备份实验:

实验场景:两台数据库主机A,B。全局备份主机A数据后,又产生了新的数据(自己手动修改表信息),将主机A的完全备份文件、二进制日志文件发送给主机B完成备份。

实验准备:

① 两台主机安装MySQL或者mariadb-server

② 开启二进制日志功能(默认关闭)

方法:vim /etc/my.cnf.d/server.cnf #安装方式不同,路径也会不同,本文以yum安装讲解

在[server]或者[mysqld]下添加 :log_bin=/路径/前缀 (mysql用户必须对日志路径具有rwx权限,本例log_bin=/app/test)

重启服务

③ 主机A数据示例:

select*fromshudian.mybook;

备份恢复流程:

1. 全局备份:

mysqldump-uroot-p--single-transaction--master-data=2--flush-logs-Bshudian>shudian.sql
--single-transaction:创建一个事务
--master-data=2:记录备份时二进制文件记录的位置,此语句被注释
--flush-logs:锁定表完成后,即进行日志滚动操作

2.修改主机A数据

insertintomybookvalues(6,'xuamingzhang',40,512);

修改后数据:

3.发送备份信息

① 全局备份文件

scpshudian.sqlB主机IP:/app

② 查看全局备份文件看备份到哪个节点并记录:

grep"MASTER_LOG_POS"shudian.sql
--CHANGEMASTERTOMASTER_LOG_FILE='test.000002',MASTER_LOG_POS=1545;

③ 备份节点后二进制日志

mysqlbinlog--start-position=1545/app/test.000002>incre.sql

发送二进制日志至主机B:

scpincre.sqlB主机IP:/app

④ 主机B数据恢复

setsql_log_bin=0;临时关闭记录二进制日志
mysql<shudian.sql
mysql<incre.sql
setsql_log_bin=1;打开记录二进制日志

注意:二进制文件不应该与数据文件放在同一块磁盘,并应放置于具有冗余功能的磁盘上如ride10.


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台