binlog存在问题
1)企业中的binlog很大,位置点不好找 grep
2)企业中所有数据不会只在一个binlog中 配合全备(mysqldump)
3)想要恢复的库,中途有其他库表操作,如何只截取指定库的
binlog作用 永远只是为了恢复增量数据产生的
备份的原因
1)备份就是为了恢复。 2)尽量减少数据的丢失(公司的损失)
运维工作的核心简单概括就两件事:
1)第一个是保护公司的数据.
2)第二个是让网站能7*24小时提供服务(用户体验)。
mysql备份的类型
冷备份: (停库,维护页,进行备份)
这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
温备份: (不停库,但是备份时锁表)
这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
热备份: (不停库,不锁表,直接备份)
这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。
拓展:
rsync:
1)冷备:停机,停服务进行备份
2)热备:不停机,不停服务,直接备份
备份的方式
逻辑备份
基于SQL语句的备份
- binlog
- mysqldump
- into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';
可以cat查看/tmp/world_city.data
- replication
物理备份
基于数据文件进行备份
-直接打包datadir
# 停库打包数据目录
# scp给另一台
# 解压,授权为mysql权限
-Xtrabckup(percona)
备份策略
一般公司是每天一次全备,每小时一次增备 全量备份 增量备份 差异备份
MySQL逻辑备份工具mysqldump
-u:指定用户
-p:指定密码
-h:指定主机
-s:指定scoket文件
-P:指定端口
所有库备份
# -A:备份所有库
# --all-databases:备份所有库
[root@db01 data]# mysqldump -uroot -p123 -A > /tmp/full.sql
## 备份文件中注释
-- 注释内容
/* 注释内容 */
## MySQL恢复数据,是覆盖形式的
drop table
create table
insert
(想要看备份出来的内容可以vim看,cat不便捷)
备份单个库
# -B 备份单个库,指定库名(备份库和表,可以备份库和表,但不能直接指定表,前面要接库)
[root@db01 ~]# mysqldump -uroot -p123 -B hhh > /tmp/full.sql
# -B 备份多个库
[root@db01 ~]# mysqldump -uroot -p123 -B 库名1 库名2 库名3 > /tmp/full2.sql
# 不加选项直接指定库名(是备份表)
[root@db01 ~]# mysqldump -uroot -p123 hhh > /tmp/hhh.sql
# 不加选项,单表备份 [mysql库下的user表]
[root@db01 ~]# mysqldump -uroot -p123 mysql user > /tmp/mysql.sql
# 不加选项多表备份
[root@db01 ~]# mysqldump -uroot -p123 库名 表名1 表名2 > /tmp/mysql2.sql
备份时刷新binlog
# -F:备份数据,并且刷新binlog
[root@db01 ~]# mysqldump -uroot -p123 -B mysql hhh zls test -F > /tmp/mysql4.sql
## 缺陷:有多少数据库,刷新出来多少binlog
打点备份参数
# --master-data=0|1|2
0是关闭,是不开启打点备份的功能
1是备份一个change master语句,不被注释
2是备份一个change master语句,被注释
[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 > /tmp/full4.sql
change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000013',
master_log_pos=13138;
快照备份
--single-transaction:不锁表备份,需要指定一个快照,否则永远备份不完
[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/full_hot.sql
拓展选项
-R:备份MySQL的函数
--triggers:备份MySQL的触发器
----下面这俩千万不要用----
-d:只备份表结构
-t:只备份数据
备份语句
进阶:
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full_hot.sql
||
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_hot.sql.gz
||
#全备终极版本:#
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz
mysqldump的恢复
# 需要恢复的数据截取,导出之后开始恢复
# 先进入数据库,不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/full.sql
# 打开记录二进制日志
mysql> set sql_log_bin=1;
#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql
注意:
1)mysqldump在备份和恢复时都需要MySQL实例启动为前提
2)一般数据量级100G以内,大约15-30分钟可以恢复(PB、EB就需要考虑别的方式)
3)mysqldump是以覆盖的形式恢复数据的
物理备份
percona官网:https://www.percona.com
全量备份
增量备份
差异备份
安装xtrabackup
# 安装包在qq里
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 官网下载再yum
[root@db01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm
# 直接yum安装官网下载的URL
[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/PerconaXtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm
xtrabackup是老版本命令:会锁表(温备)
innobackupex是新版本命令:针对innodb存储引擎进行备份,不会锁表(热备)
备份方式(物理备份)
1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf
inobackupex命令基础选项
命令:innobackupex
选项:
--user:指定用户名
--password:指定密码
--socket:指定socket文件
--host:指定主机IP
--port:指定端口
--apply-log:模拟CSR
--copy-back:恢复数据
--incremental:开启增量备份
--incremental-basedir:指定上一次备份的目录
--no-timestamp:不需要时间戳
--redo-only:只做redo
innobackupex全备
# 全备(需要在配置文件指定socket,然后重启数据库)
[root@db01 backup]# innobackupex --user=root --password=123 /backup/
# 全备目录下的一些文件
[root@db01 backup]# ll /backup/2023-08-03_08-53-33/
-rw-r----- 1 root root 21 Aug 3 08:53 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Aug 3 08:53 xtrabackup_checkpoints
-rw-r----- 1 root root 464 Aug 3 08:53 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 3 08:53 xtrabackup_logfile // redo log
[root@db01 2023-08-03_08-53-33]# cat xtrabackup_binlog_info ( 类似于--master-data=2 打点文件)
mysql-bin.000004 120
undo:原数据
commit:标签
lsn:日志版本号
txid:事务提交号
[root@db01 2023-08-03_08-53-33]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1730489
last_lsn = 1730489
compact = 0
recover_binlog_info = 0
[root@db01 2023-08-03_08-53-33]# cat xtrabackup_info
uuid = 2e9db7fd-3198-11ee-9a9b-000c2923970d
name =
tool_name = innobackupex
tool_command = --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.50-log
start_time = 2023-08-03 08:53:36
end_time = 2023-08-03 08:53:38
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 1730489
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
全备恢复
# 全备恢复
1)前提:被恢复的目录是空的 (把数据目录移走)
2)前提:被恢复的数据库的实例是关闭的 停库恢复
## 1.先停库
[root@db01 ~]# /etc/init.d/mysqld stop
## 2.清空被恢复的目录(实际生产中最好是把它移走,不然binlog会被删掉)
[root@db01 ~]# mv /app/mysql/data /tmp/data
# 3.先手动CSR,将redo中的数据,重做一遍,然后将undo中的数据,回滚一遍
[root@db01 ~]# innobackupex --apply-log /backup/2023-08-03_08-53-33/
# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/2023-08-03_08-53-33/
# 5.授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data
innobackupex增量备份
###模拟生产中如何全备###
模拟情景:每天一次全备,每小时一次增备(比如说在2023-08-03上午十点(这里的第四次增备)某个库被删了)
# 1.全备 (全部过程中会自动生成/backup/full-2023-08-03目录)
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full-$(date +%F)
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 56926023
last_lsn = 56926023
compact = 0
recover_binlog_info = 0
# 2.第一次增量备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-2023-08-03/ /backup/inc1-$(date +%F-%H)
[root@db01 backup]# cat inc1-2023-08-03-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 56926023
to_lsn = 56942921
last_lsn = 56942921
compact = 0
recover_binlog_info = 0
--------这个时间段插入了表hhh-------------
# 3.第二次增备(如果不想在命令行加--user和--password就加在配置文件里的[client]下指定,这个不用重启数据库)
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc1-2023-08-03-15/ /backup/inc2-$(date +%F-%H)
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 56942921
to_lsn = 57029035
last_lsn = 57029326
compact = 0
recover_binlog_info = 0
# 4.第三次增备
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc2-2023-08-03-15/ /backup/inc3-$(date +%F-%H)
[root@db01 backup]# cat inc3-2023-08-03-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 57029035
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
------------这个时间段删了库---------------
xtrabackup增量备份恢复
1)基于上一次备份进行增量 2)增量备份无法单独恢复,必须基于全备进行恢复 3)所有增量必须要按顺序合并到全备当中
------#恢复----------------
# 1.停库
[root@db01 backup]# /etc/init.d/mysqld stop
# 2.清空data目录
[root@db01 ~]# mv /app/mysql/data /tmp/data
# 3.先模拟CSR
1)全备模拟CSR,只做redo,不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_checkpoints
backup_type = log-applied(状态发生了改变)
from_lsn = 0
to_lsn = 56926023
last_lsn = 56926023
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_binlog_info
mysql-bin.000003 1355
2)第一次增备合并到全备的时候,只做redo,不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/inc1-2023-08-03-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 56926023
to_lsn = 56942921
last_lsn = 56942921
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/inc1-2023-08-03-15/xtrabackup_binlog_info
mysql-bin.000003 5955
3)第二次增备合并到全备的时候,只做redo,不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 56942921
to_lsn = 57029035
last_lsn = 57029326
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_binlog_info
mysql-bin.000003 45070
4)第三次增备(最后一次增备)合并到全备的时候,redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat inc3-2023-08-03-16/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 57029035
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat inc3-2023-08-03-16/xtrabackup_binlog_info
mysql-bin.000003 48750
5)最后全部合并完成后,把全备redo和undo都做一遍
[root@db01 backup]# innobackupex --apply-log /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_binlog_info
mysql-bin.000003 48750
# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/full-2023-08-03/
# 5.给数据目录授权
[root@db01 backup]# chown mysql.mysql -R /app/mysql/data
# 6.重启数据库
[root@db01 backup]# /etc/init.d/mysqld restart
# 7.截取binlog
1)全备恢复后——drop database prod之前
起始位置点:48750
[root@db01 backup]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb
mysql-bin.000003 48750
结束位置点:57119
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /opt/data/mysql-bin.000003|grep -i 'drop database prod' -C 10
截取binlog
[root@db01 data]# mysqlbinlog --start-position=48750 --stop-position=57119 /opt/data/mysql-bin.000003 >/tmp/qb.sql
2)drop database prod之后——binlog结尾(停库之前)
# 在新环境里恢复
把截取的binlog导到新环境
-#进入数据库先关binlog
mysql> set sql_log_bin=1;
-#开始恢复
mysql> source /tmp/qb.sql
-#打开binlog
mysql> set sql_log_bin=0;
-在导回老库,或改代码
使用 Xtrabackup + 增量 + binlog
在企业中全备备份的名字可以自己根据公司备份次数要求去命名,一天备份一次或是一小时备份一次就是full_$(date +%F)或full_(date +%F-%H)
innobackupex差异备份
基于全备新增数据进行备份
###模拟生产中如何差异备份###
# 1.全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full
[root@db01 backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 57059063
last_lsn = 57059063
compact = 0
recover_binlog_info = 0
# 2.第一次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi1-$(date +%F-%H)
[root@db01 backup]# cat chayi1-2023-08-03-17/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 57059063
to_lsn = 57066393
last_lsn = 57066683
compact = 0
recover_binlog_info = 0
# 3.第二次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi2-$(date +%F-%H)
[root@db01 backup]# cat chayi2-2023-08-03-17/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 57059063
to_lsn = 57086385
last_lsn = 57086385
compact = 0
recover_binlog_info = 0
# 4.第三次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi3-$(date +%F-%H)
[root@db01 backup]# cat chayi3-2023-08-03-17/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 57059063
to_lsn = 57091193
last_lsn = 57091193
compact = 0
recover_binlog_info = 0
------#恢复----------------
## 恢复数据
[root@db01 backup]# /etc/init.d/mysqld stop
[root@db01 backup]# mv /app/mysql/data /opt/data
## 模拟CSR
1)全备只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full/
[root@db01 backup]# cat full/xtrabackup_checkpoints
backup_type = log-applied (状态变了)
from_lsn = 0
to_lsn = 57059063
last_lsn = 57059063
compact = 0
recover_binlog_info = 0
2)将最后一次差异备份合并到全备中,redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi3-2023-08-03-17/ /backup/full/
3)全备redo和undo都做
[root@db01 backup]# innobackupex --apply-log /backup/full/
# 恢复
[root@db01 backup]# innobackupex --copy-back /backup/full/
# 授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data
# 截取binlog
[root@db01 data]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb
mysql-bin.000001 14426
[root@db01 backup]# mysqlbinlog --base64-output=decode-rows /opt/data/mysql-bin.000001 |grep -i 'drop database prod' -C 5
46350
[root@db01 backup]# mysqlbinlog --start-position=14426 --stop-position=46350 /opt/data/mysql-bin.000001 > /tmp/chayi.sql
# 在新环境里恢复
把截取的binlog导到新环境
-#进入数据库先关binlog
mysql> set sql_log_bin=1;
-#开始恢复
mysql> source /tmp/chayi.sql
-#打开binlog
mysql> set sql_log_bin=0;
-在导回老库,或改代码