mysql（十一）

MySQL逻辑备份-企业案例

-t:仅备份表结构

-d:仅备份数据

mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F -d "-1 day").sql.gz

企业故障恢复案例

背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本

[root@db02 data]# mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

故障时间点：
周三上午10点开发人员误删除一个核心业务表，如何恢复？

思路:

1.停库(避免二次伤害数据)

# 先停tomcat,php-fpm,nginx
[root@db02 ~]# /etc/init.d/mysqld stop

2.准备一个新环境

[root@db01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

[root@db01 scripts]# /etc/init.d/mysqld start

3.将昨天的全备,发送给新环境

[root@db02 ~]# scp /tmp/full_2019-12-11.sql.gz 172.16.1.51:/tmp

4.将全备导入新环境

[root@db01 scripts]# zcat /tmp/full_2019-12-11.sql.gz |mysql -uroot -p

5.截取昨天23点到今天10点之间的数据

[root@db02 data]# zcat /backup/full_2019-12-11.sql.gz |head -22

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=809492;

[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003|less
818556

[root@db02 data]# mysqlbinlog --start-position=809492 --stop-position=818556 mysql-bin.000003 > /backup/inc.sql


818679
863424

[root@db02 data]# mysqlbinlog --start-position=818679 --stop-position=863424 mysql-bin.000003 > /backup/inc2.sql

[root@db02 data]# scp /backup/inc* 172.16.1.51:/root

6.恢复业务

• 将业务应用割接
• 导出核心业务表,恢复到旧库

MySQL物理备份

备份方式

1.cp data

2.Xtrabackup

备份策略

1.全量备份

2.增量备份

3.差异备份

[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf

[root@db02 ~]# mkdir /backup/
# 1.全量备份
[root@db01 ~]# innobackupex --user=root --password=123 /backup/

[root@db01 2019-12-11_10-17-27]# cat xtrabackup_binlog_info 
mysql-bin.000004    48922

[root@db01 2019-12-11_10-17-27]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2308823
last_lsn = 2340198
compact = 0
recover_binlog_info = 0


[root@db01 2019-12-11_10-17-27]# cat xtrabackup_info
uuid = 650301eb-1bbc-11ea-b4c8-000c29e98743
name = 
tool_name = innobackupex
tool_command = --user=root --password=... --socket=/opt/mysql.sock /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.40-log
start_time = 2019-12-11 10:17:27
end_time = 2019-12-11 10:17:35
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '48922'
innodb_from_lsn = 0
innodb_to_lsn = 2308823
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

恢复全备

#前提1：被恢复的目录是空的
#前提2：被恢复的数据库的实例是关闭的

#1.停库
[root@db01 2019-12-11_10-17-27]# /etc/init.d/mysqld stop

#2.模拟CSR,做一遍redo,再做一遍undo
[root@db01 2019-12-11_10-17-27]# innobackupex --apply-log /backup/2019-12-11_10-17-27
#3.保证data目录是空的
[root@db01 mysql]# rm -fr /application/mysql/data
#4.恢复数据
[root@db01 mysql]# innobackupex --copy-back /backup/2019-12-11_10-54-55/
#5.授权
[root@db01 mysql]# chown -R mysql.mysql /application/mysql/data

增量备份

#1.先做全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

[root@db01 backup]# cat full_2019-12-11/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2578215
last_lsn = 2586862
compact = 0
recover_binlog_info = 0


#2.第一次，增量备份（基于全备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F) /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-11/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2578215
to_lsn = 2662633
last_lsn = 2662633
compact = 0
recover_binlog_info = 0


#3.第二次，增量备份（基于第一次增备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d "-1 hour") /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-12/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2662633
to_lsn = 2738509
last_lsn = 2739784
compact = 0
recover_binlog_info = 0


#4.第三次，增量备份（基于第二次增备）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d "-1 hour") /backup/inc_$(date +%F-%H)

[root@db01 backup]# cat inc_2019-12-11-13/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2738509
to_lsn = 2773700
last_lsn = 2777706
compact = 0
recover_binlog_info = 0

增量备份恢复

1）基于上一次备份进行增量
2）增量备份无法单独恢复，必须基于全备进行恢复
3）所有增量必须要按顺序合并到全备当中

准备备份

1）full+inc1+inc2
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行--apply-log

[root@db01 backup]# /etc/init.d/mysqld stop

第一步：在全备中apply-log时，只应用redo，不应用undo

[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_2019-12-11/

第二步：合并inc1合并到full中，并且apply-log，只应用redo，不应用undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-11-11 /backup/full_2019-12-11/

第三步：合并inc2合并到full中，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-11-12 /backup/full_2019-12-11/

第四步：将inc3合并到full中，redo undo都做

[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc_2019-12-11-13 /backup/full_2019-12-11/

第五步：整体full执行apply-log，redo和undo都应用

[root@db01 backup]# innobackupex --apply-log /backup/full_2019-12-11/

第六步：恢复数据

[root@db01 backup]# rm -fr /application/mysql/data/

[root@db01 backup]# innobackupex --copy-back /backup/full_2019-12-11/

[root@db01 backup]# chown -R mysql.mysql /application/mysql/data

企业案例

思考:

企业级增量恢复实战

背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M

备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。

故障场景：
周三下午2点出现数据库意外删除表操作。

全备：1次

增备：4次

00:00~14:00增量数据

模拟数据

#全备：周五的 24点（周六00点）
innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
[root@db01 backup]# cat full_2019-12-14/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23174130
last_lsn = 23174130
compact = 0
recover_binlog_info = 0


#第一次增备：周六的24点（周日的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23174130
to_lsn = 23199586
last_lsn = 23219800
compact = 0
recover_binlog_info = 0

#第二次增备：周日的24点（周一的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-16/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23199586
to_lsn = 23250777
last_lsn = 23250777
compact = 0
recover_binlog_info = 0

#第三次增备：周一的24点（周二的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23250777
to_lsn = 23250777
last_lsn = 23268489
compact = 0
recover_binlog_info = 0

#第四次增备：周二的24点（周三的00点）
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F -d "-1 day") /backup/inc_$(date +%F)
[root@db01 backup]# cat inc_2019-12-18/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23250777
to_lsn = 23299475
last_lsn = 23299475
compact = 0
recover_binlog_info = 0

思路：

1.先停库

/etc/init.d/mysqld stop

2.准备新环境

./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

3.清空新环境的data目录

rm -fr /application/mysql/data

4.数据合并

• 将周六的全备，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full_2019-12-14/

• 将周日的增备合并到全备中，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-15 /backup/full_2019-12-14/

• 将周一的增备合并到全备中，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-16 /backup/full_2019-12-14/

• 将周二的增备合并到全备中，只做redo不做undo

[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2019-12-17 /backup/full_2019-12-14/

• 将周三的增备合并到全备中，redo和undo都做

[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc_2019-12-18 /backup/full_2019-12-14/

• 将合并后的full目录整体做一次redo和undo

  [root@db01 backup]# innobackupex --apply-log /backup/full_2019-12-14/
  

恢复数据

  [root@db01 mysql]# innobackupex --copy-back /backup/full_2019-12-14/
  
  [root@db01 mysql]# chown -R mysql.mysql data

找数据位置点

#1.起始位置点
[root@db01 full_2019-12-14]# cat xtrabackup_binlog_info
mysql-bin.000001    13620168

#2.结束位置点
[root@db01 full_2019-12-14]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data_bak/mysql-bin.000001 |less
13638936

截取数据

[root@db01 full_2019-12-14]# mysqlbinlog --start-position=13620168 --stop-position=13638936 /application/mysql/data_bak/mysql-bin.000001 > /backup/inc_00_14.sql

13639060
13691523

[root@db01 full_2019-12-14]# mysqlbinlog --start-position=13639060 --stop-position=13691523 /application/mysql/data_bak/mysql-bin.000001 > /backup/inc_14.sql

恢复业务

• 应用割接(修改代码，连接数据库的IP)
• 备份表，导入旧库

[root@db01 mysql]# mysqldump zls1 student2 > /backup/student2.sql

[root@db01 mysql]# mysql zls1 < /backup/student2.sql 

差异备份，恢复

#全备
[root@db01 mysql]# 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 = 23436422
last_lsn = 23436422
compact = 0
recover_binlog_info = 0


#第一次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi1
[root@db01 backup]# cat chayi1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23445236
last_lsn = 23445236
compact = 0
recover_binlog_info = 0


#第二次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi2
[root@db01 backup]# cat chayi2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23450202
last_lsn = 23450202
compact = 0
recover_binlog_info = 0


#第三次差异备份
[root@db01 mysql]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi3
[root@db01 backup]# cat chayi3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23436422
to_lsn = 23452936
last_lsn = 23453235
compact = 0
recover_binlog_info = 0

恢复数据

#1.停库
[root@db01 backup]# /etc/init.d/mysqld stop

#2.准备新环境
#3.合并数据
    #3.1全备只做redo不做undo
    [root@db01 backup]# innobackupex --apply-log --redo-only /backup/full
    #3.2合并最后一次差异，redo和undo都做
    [root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi3 /backup/full
    #3.3全备整体做一次redo和undo
    [root@db01 backup]# innobackupex --apply-log /backup/full
#4.清空data目录
[root@db01 backup]# mv /application/mysql/data /application/mysql/data_bak

#5.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/full
#6.授权
[root@db01 backup]# chown -R mysql.mysql /application/mysql/data
#7.启动
[root@db01 backup]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql/data/db01.err'.
. SUCCESS!