会挽雕弓如满月，西北望，射天狼。这篇文章主要讲述mysql备份相关的知识，希望能为你提供帮助。
1.备份类型
#1)
完全备份
部分备份
#2)
完全备份
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来,变化的数据,备份较快,还原复杂
差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
#注意二进制日志文件,最好不要与数据文件放在同一个磁盘
#3)
冷备份:读写操作均不可进行
温备份:读操作可执行,但写操作不可执行
热备份:读写操作均可执行
MyISAM:温备,不支持热备
INNODB:都支持
#4)
物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
原理:
--------------------------------------------------------------
#当表类型是MyISAM时,
数据文件则以"Table.frm""Table.MYD""Table.MYI""三个文件存储于"/data/$databasename/"目录中。
#当表类型是InnoDB时
数据文件则存储在"$innodb_data_home_dir/"中的ibdata1文件中(一般情况),结构文件存在于table_name.frm中。
MyISAM类型的表直接拷到另一个数据库就可以直接使用,但是InnoDB类型的表却不行。 解决方法就是同时拷贝
innodb数据库表"*.frm"文件和innodb数据"ibdata1"文件到合适的位置。
--------------------------------------------------------------
逻辑备份:从数据库中导出数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
2.备份内容
1)数据
2)二进制日志
3)Innodb的事务日志
4)程序代码(存储过程/函数/触发器/事件调度器)
5)服务器的配置文件
3.备份工具
#1)cp, tar等复制归档工具
物理备份工具,适用所有存储引擎
只支持冷备
完全和部分备份
#2)LVM的快照
先加锁,做快照后解锁,几乎热备
借助文件系统工具进行备份
#3)mysqldump
逻辑备份工具,适用所有存储引擎,温备;
支持完全或部分备份;
对InnoDB存储引擎支持热备,结合binlog的增量备份
#4)xtrabackup
由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
#5)MariaDB Backup
从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
#6)mysqlbackup
热备份, MySQL Enterprise Edition组件
#7)mysqlhotcopy
PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,
使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库
4.LVM的备份
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e SHOW MASTER STATUS > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷,执行数据备份
(6) 备份完成后,删除快照卷
(7) 制定好策略,通过原卷备份二进制日志
5.冷备份
systemctl stop mariadb
tar Jcvf all.bak /var/lib/mysql/
#二进制日志备份
tar Jcvf logbin.tar.xz/data/logbin
#配置文件
/etc/my.cnf
6.逻辑备份6.1.mysqldump
【mysql备份】Schema和数据存储在一起,巨大的sql语句,单个巨大的备份文件
6.1.1.mysqldump工具
mysqldump工具:客户端命令,通过Mysql协议连接至mysql服务器进行备份
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
6.1.2.mysqldump常见选项
-A, --all-databases备份所有数据库,含create database
-B , --databases db_name…指定备份的数据库,包括create database语句
-E, --events:备份相关的所有event scheduler
-R, --routines:备份所有存储过程和自定义函数
--triggers:备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8 指定字符集 #检验文件字符集的命令可以使用file -i
--master-data[=#]: 此选项须启用二进制日志
1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定,默认为1
2:记录为注释的CHANGE MASTER TO语句
此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--single-transaction)
-F, --flush-logs :备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A
或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--single-transaction或
-x,--master-data 一起使用实现,此时只刷新一次日志
--compact去掉注释,适合调试,生产不使用
-d, --no-data只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db不备份create database,可被-A或-B覆盖
--flush-privileges备份mysql或相关时需要使用
-f, --force忽略SQL错误,继续执行
--hex-blob使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的
列时使用,避免乱码
-q, --quick不缓存查询,直接输出,加快备份速度
--------------------------------------------------------------------------------------------
#MyISAM备份选项:
支持温备；不支持热备,所以必须先锁定要备份的库,而后启动备份操作
#锁定方法如下:
-x,--lock-all-tables:加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能
注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,
--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
#InnoDB备份选项: 支持热备,可用温备但不建议用
--single-transaction
此选项Innodb中推荐使用,不适用MyISAM,
此选项会开始备份前,先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表(目前
只有InnoDB可以); 转储不保证与其他存储引擎保持一致。在进行单事务转储时,要确保有效的转储文件(正确的表
内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥
备份大型表时,建议将--single-transaction选项和--quick结合一起使用
6.1.3.生产环境实战备份策略6.1.3.1.InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=https://www.songbingjia.com/android/1 --flush-privileges //
--triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
6.1.3.2.MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=https://www.songbingjia.com/android/1 --flush-privileges --triggers //
--default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
6.1.3.3.备份数据库
mysqldump hellodb> /data/hellodb.sql#只是备份了数据库里的表,没有备份数据库本身
mysqldump -B hello > /data/hello.sql
6.1.4.分库备份脚本
for db in `mysql -e show databases|grep -Ev ^(information_schema|performance_schema|Database)$`; \\
do mysqldump -B $db --single-transaction --master-data=https://www.songbingjia.com/android/2 |gzip > /data/$db.sql.gz ; //
done
mysql -e show databases|grep -Ev ^(information_schema|performance_schema|Database)$| \\
sed -rn s@(.*)@mysqldump -B \\1 --single-transaction --master-data=https://www.songbingjia.com/android/2 | //
gzip > /data/\\1\\.sql\\.gz@p|bash
6.1.5.故障模拟恢复
#1.二进制的启用
#2.全库备份
mysqldump -A --master-data=https://www.songbingjia.com/android/2 > /data/all.sql
#3.模拟增加数据
insert students (name,age)values(a,20);
insert students (name,age)values(b,30);
#4.查看二进制
确保无用户访问数据库
show master logs;
#为了保证某个二进制日志之前的都是新增的数据,可以在备份二进制日志前,做一次日志切换
FLUSH LOGS;
#5.二进制日志
mysqlbinlog --start-position=710 mysql-bin.000003 > /data/inc.sql #--start-position=710在全库备份的sql中查看
mysqlbinlog mysql-bin.000004 > /data/inc.sql
mysqlbinlog mysql-bin.000005 > /data/inc.sql
#6.模拟数据库坏了,删库
rm -rf /var/lib/mysql/*
#7.临时停掉二进制文件
set sql_log_bin=off/0
#8.还原全库和二进制日志的备份
source/data/all.sql
source /data/inc.sql
#开启二进制日志
set sql_log_bin=on
6.2.xtrabackup
percona提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具
#特点:
备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源,免费
Xtrabackup2.2版之前包括4个可执行文件:
innobackupex:Perl 脚本
xtrabackup:C/C++ 编译的二进制
xbcrypt:加解密
xbstream:支持并发写的流文件格式
xtrabackup 是用来备份InnoDB表的,不能备份非InnoDB表,和MySQL Server没有交互
innobackupex脚本用来备份非InnoDB 表,同时会调用xtrabackup命令来备份InnoDB 表,还会和
MySQL Server发送命令进行交互,如加全局读锁 (FTWRL)、获取位点(SHOW SLAVE STATUS)等。
即innobackupex是在xtrabackup 之上做了一层封装实现的
xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:
innobackupex 功能全部集成到 xtrabackup 里面,只有一个 binary程序,另外为了兼容考虑,innobackupex
作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,并且 Innobackupex 在下一版本中移除,
建议通过xtrabackup替换innobackupex
6.2.1.xtrabackup备份过程
6.2.2.xtrabackup用法6.2.2.1.安装
xtrabackup安装:
yum install percona-xtrabackup #在EPEL源中
#最新版本下载安装:
https://www.percona.com/downloads/XtraBackup/LATEST/
6.2.2.2.备份
innobackupex [option] BACKUP-ROOT-DIR
选项说明:https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
--user:#该选项表示备份账号
--password: #该选项表示备份的密码
--host:#该选项表示备份数据库的地址
--databases:#该选项接受的参数为数据库名,如果要指定多个数据库,彼此间需要以空格隔开;
如:"xtra_test dba_test", 同时,在指定某数据库时,也可以只指定其中的某张表。
如:"mydatabase.mytable"。该选项对innodb引擎表无效,还是会备份所有innodb表
--defaults-file:#该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
--incremental:#该选项表示创建一个增量备份,需要指定--incremental-basedir
--incremental-basedir:#该选项指定为前一次全备份或增量备份的目录,与--incremental同时使用
--incremental-dir:#该选项表示还原时增量备份的目录
--include=name:#指定表名,格式:databasename.tablename
#Prepare
innobackupex --apply-log [option] BACKUP-DIR
#选项说明:
--apply-log: #一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交
的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作用是通过回滚
未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory:#和--apply-log选项一起使用,当prepare 备份时,做crash recovery分配的内存大小,单位字节
,也可1MB,1M,1G,1GB等,推荐1G
--export:#表示开启,可导出单独的表之后再导入其他Mysql中
--redo-only: #此选项在prepare base full backup,往其中合并增量备份时候使用,但不包括对最后一个增量备份的合并
6.2.2.3.还原
innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明:
--copy-back:#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back:#这个选项与--copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地。
这个选项移除backup文件,用时候必须小心。使用场景:没有足够的磁盘空间同时保留数据文件和Backup副本
#还原注意事项:
推荐阅读
- #yyds干货盘点# Exchanger详解
- 如何向 github 上开源项目提交代码
- #yyds干货盘点# 解决剑指offer（数组中重复的数字）
- 简单介绍关于vue 的slot分发内容 (多个分发)
- # yyds干货盘点 # Python面向对象中的类变量，实例变量怎么来理解（）
- #yyds干货盘点# js学习笔记二十一手动封装call
- 实现深拷贝最少得用几行代码（）
- 类与对象—1
- #yyds干货盘点#return用法 - python基础学习系列（64）